Hibernate throwing validation exception "wrong column type encountered in column" even when my DDL script and JPA entity are in sync

Question

I am starting my spring container in validate mode

autoddl=validate

I am getting a validation exception like this

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema- 
validation: wrong column type encountered in column [amount] in table [Balance]; 
found [numeric (Types#NUMERIC)], but expecting [int8 (Types#BIGINT)]

and my DDL script goes like this

CREATE TABLE Balance(stratr VARCHAR(25), histFromDate TIMESTAMP WITHOUT TIME 
ZONE,amount  numeric(11, 0))

and my attribute in JPA entity goes like this

 @Column(name="amount", precision=11, scale=0) //have specified precision and scale
 private Long amount  ;

where I have used import javax.persistence.Column.Since I have annotated the exact precision and scale, Shouldn't hibernate validate with these info that I have provided through the column annotation? What could have I missed ?

I cannot do the following

  @Column(
   columnDefinition = "NUMERIC(11,0)"
   )
  private Long amount;

because I don't know the data store of this JPA entity.

I also tried generating the script by the following property

<prop key="javax.persistence.schema-generation.scripts.action">drop-and-create</prop>
<prop key="javax.persistence.schema-generation.scripts.create-target">./l/create.sql</prop>
<prop key="javax.persistence.schema-generation.scripts.drop-target">./l/drop.sql</prop> 

This is also generating as int8 and not numeric(11,0). What can be done to solve this ?

Answer 1

It's really quite difficult to grasp what you're trying to accomplish, but if I understood correctly:

• you want to keep your application portable by not fixing the column definition on the entity level to be NUMERIC(11,0), which would make it Postgres-specific
• at the same time, you want your column to use NUMERIC(11,0) for Postgres and not INT8 that Hibernate would normally use for a Long in Postgres (and is hoping to find in your schema upon validation)

In short, you want a per-database customization that is not reflected in your entity mapping. The only way to accomplish that is to customize the dialect that Hibernate is using for your version of Postgres. What you need to do is:

1. determine which dialect version is being selected for your Postgres database (it will be one of the following: PostgresPlusDialect, PostgreSQL81Dialect, PostgreSQL82Dialect, PostgreSQL91Dialect, PostgreSQL92Dialect,PostgreSQL93Dialect, PostgreSQL94Dialect, PostgreSQL95Dialect, PostgreSQL9Dialect)

2. extend from that class, adding the following definition:

public MyCustomPostgresDialect() {
    super();
    registerColumnType(Types.BIGINT, "NUMERIC(11, 0)");
}

(If you want to be able to control the precision and scale using @Column(precision = ..., scale = ...), use registerColumnType(Types.BIGINT, "NUMERIC($p, $s)") instead)

3. add the hibernate.dialect property to persistence.xml, pointing to the fully qualified class name of your custom dialect

Note that this will, of course, affect all Long properties in your data model, not just the specific field in question.