What are the best workarounds for known problems with Hibernate's schema validation of floating point columns when using Oracle 10g?

Question

I have several Java classes with double fields that I am persisting via Hibernate. For example, I have

@Entity
public class Node ...

  private double value;

When Hibernate's org.hibernate.dialect.Oracle10gDialect creates the DDL for the Node table, it maps the value field to a "double precision" type.

create table MDB.Node (... value double precision not null, ...

It would appear that in Oracle, "double precision" is an alias for "float". So, when I try to verify the database schema using the org.hibernate.cfg.AnnotationConfiguration.validateSchema() method, Oracle appears to describe the value column as a "float". This causes Hibernate to throw the following Exception

org.hibernate.HibernateException: Wrong column type in DBO.ACL_RULE for column value. Found: float, expected: double precision

A very similar problem is listed in Hibernate's JIRA database as HHH-1961. I'd like to avoid doing anything that will break MySql, Postgres, and Sql Server support so extending the Oracle10gDialect appears to be the most promising of the workarounds mentioned in HHH-1961. But extending a Dialect is something I've never done before and I'm afraid there may be some nasty gotchas. What is the best workaround for this problem that won't break our compatibility with MySql, Postgres, and Sql Server?

Answer 1

This is a known limitation of the schema validator, check HHH-2315. So you have three options here (actually four but I guess that deactivating validation is not wanted). Either:

• Use a float instead of a double at the Java level - this might not be an option though.

• Patch org.hibernate.mapping.Table.validateColumns(Dialect dialect, Mapping mapping, TableMetadata tableInfo) to add a special condition for this particular case - this isn't really a light option.

• Extends the org.hibernate.dialect.Oracle10gDialect to make it use float for the SQL type DOUBLE

  public class MyOracle10gDialect extends Oracle10gDialect {
      public MyOracle10gDialect() {
          super();
      }
      protected void registerNumericTypeMappings() {
          super.registerNumericTypeMappings();
          registerColumnType( Types.DOUBLE, "float" );
      }
  }
  

The later option seems safe but will require some testing to see if it doesn't introduce any regression. I didn't look at Oracle's JDBC driver code, so I can't say how float and double precision differ at the driver level.

Answer 2

just adding (columnDefinition = "NUMBER(9,2)") works!

@Column(name = "CREDIT_AMOUNT", columnDefinition = "NUMBER(9,2)")
@Basic
private double creditAmount;