Hibernate 6 Upgrade - H2 Query Syntax Issue

I am trying to upgrade an existing app from Spring Boot 2.7/Hibernate 5.4 to Spring Boot 3.1.2/Hibernate 6.2.6. Both setups use H2 2.1.214.

Tests that used to work in the original setup are failing. I’ve debugged this to a point that I know it is because of the way a JPQL query is getting translated in SQL. In the working version, the query gets translated as below.

update account_balances set balance_magnitude=balance_magnitude+?,
where account_id=?

Whereas, with the new combination, the query gets translated as below.

update account_balances set
balance_magnitude=(balance_magnitude+cast(? as
as numeric($p,$s))) where account_id=?

And the error thrown is as below.

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement
*** expected “long”;

The *_magnitude columns have type DECIMAL(14,2) in DB and BigDecimal in Java. The dialect used is H2Dialect.

The jdbc url is as given below

What could be the reason?


1 Like

I was able to resolve this by using MySQLDialect instead of H2Dialect.

But still curious to know how to make this work with H2Dialect?

You should definitely not be using the MySQLDialect when you are using a H2 database. This looks like a bug to me, so please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) that reproduces the issue.

I have the same issue. Did you find a solution?
The MySqlDialect did not solve it for me. Neither bumping to latest Hibernate (6.4.3.Final)
It works if I change the datatype of the entity property to long - but not with the BigDecimal.

Hi @mallju, if the issue is still occuring in the latest Hibernate version it might have not been fixed yet. You should not use a Dialect meant for a different database.

Please try to create a reproducer test case and attach it to a new ticket in our issue tracker, like Christian suggested in his previous comment.