BigDecimal precision not persisted with JPA annotations

Question

I am using the javax.persistence API and Hibernate to create annotations and persist entities and their attributes in an Oracle 11g Express database.

I have the following attribute in an entity:

@Column(precision = 12, scale = 9)
private BigDecimal weightedScore;

The goal is to persist a decimal value with a maximum of 12 digits and a maximum of 9 of those digits to the right of the decimal place.

After calculating weightedScore, the result is 0.1234, but once I commit the entity with the Oracle database, the value displays as 0.12.

I can see this by either using an EntityManager object to query the entry or by viewing it directly in the Oracle Application Express (Apex) interface in a web browser.

How should I annotate my BigDecimal attribute so that the precision is persisted correctly?

Note: We use an in-memory HSQL database to run our unit tests, and it does not experience the issue with the lack of precision, with or without the @Column annotation.

Update:

Looking at the table description, the definition of the weightedScore column is NUMBER(19, 2). I have now also tried changing the annotation to @Column(columnDefinition="Number(12, 9)"), but this has had no effect. Does anyone know why Oracle is unresponsive to these annotations?

Solution 1

I found the answer. Huzzah!

I attempted to execute the following query through the Oracle Apex interface:

alter table NODE modify (WEIGHTEDSCORE NUMBER(12, 9));

I received an error stating that a column containing data cannot be modified to have less precision or less scale. This was my issue!

Because I was attempting to alter table with existing data, I needed to either drop the table and re-initialize it, or alter the column to only have more precision and scale.

I attempted the following query with success:

alter table NODE modify (WEIGHTEDSCORE NUMBER(26, 9));

The reasoning is that I want to add 7 places of precision to the right of the decimal, so I am adding 7 to the overall precision to compensate for the increase in scale. That way, the column can keep all existing precision on the left of the decimal while adding precision on the right side.

Solution 2

It seems you're not the only one having trouble with this: http://www.eclipse.org/forums/index.php/m/716826/