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?

Kilowatt

I take Hibernate is generating the schema, right? – Indra

If the column is created without precision and scale, then H2 doesn't truncate. So either the column is created with precision / scale, or Hibernate, or your application truncates the value. I can't say which one it is however. – Linchpin

Could you get the schema of this database, by executing the SQL statement SCRIPT in the H2 database? – Linchpin

I assume it will be the creation script (thanks for now), will verify it soon – Kilowatt

Ever find an answer to this? We're also experiencing this problem. – Ovary

I found a helpful answer here: #15568548 – Greenaway

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.

Basswood

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.

Julius

Answer 3

ran into this, trying to store values like: 0.08559, 0.000000012, would be stored as 0.00. One has to use scale + precision attribute on the Entity definition eg:

@Column(scale = 15, precision = 30)
private BigDecimal myValue;

remember: Precision specifies total number of digits in a number scale specifies number of digits after the decimal point.

Optics