lost precision when storing BigDecimal into H2 using Hibernate

Question

We are using H2 database for testing purposes, but when I store BigDecimal value into it using Hibernate and then load it back the value is truncated to two decimal places:

The field definition looks like this

@Column(name = "Rate", nullable = true)
private BigDecimal rate;

so 1.456 is truncated into 1.46.

I don't know the precision upfront (it is different for every entity), so I can't define them on the annotation.

Is there some way how to resolve this?

Answer 1

Even though you don't know the precision/scale up-front, I think you still need to define the maximum precision and scale in the @Column annotation. You need to have a look at the database schema generated to see how Hibernate is defining the column.

By the way, I can tell you that the creation of the BigDecimal value coming back from the database is done by the proprietary JDBC driver's implementation of the getBigDecimal method of the database-specific ResultSet sub-class.

I found this out by stepping thru the Hibernate source code with a debugger, while trying to find the answer to my own question.

It seems that some implementations of the getBigDecimal method will either use the precision/scale defined in the database schema, or will try to optimise the BigDecimal returned by only defining the minimum precision/scale required to hold the value retrieved.

See also my question here and this other question.

Answer 2

We ran into a situation very similar to what is described here, but the problem was partly due to how we manage our environment. We are using Spring Data JPA (uses Hibernate) but manage our schema migrations using Flyway.

When interacting with Postgres, everything was fine, but when using H2 for testing, all our DECIMAL types were persisted into the database with only two digits of rounded (half up) scale with no obvious reason.

In our case, the problem was resolved by setting this flag in our Spring Boot application.properties:

spring.jpa.hibernate.ddl-auto=none

So, the root cause was incomplete JPA definitions for DDL generation being applied by Hibernate. Our solution was to disable the automatic DDL generation because we are taking care of it with SQL scripts, but this should also be fixable via a more complete JPA definition.