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 ?

what do you mean by "I don't know the data store of this JPA entity.?" — Abhijeet
– Abhijeet, Commented Jul 3, 2019 at 6:32
Numeric(11,0) is a specific type in Postgres Data Store, If it is Oracle it would be Number(11,0). I don't have that info here. — JITHIN_PATHROSE
– JITHIN_PATHROSE, Commented Jul 3, 2019 at 6:36
Then change your DDL script to create amount column as BIGINT — Abhijeet
– Abhijeet, Commented Jul 3, 2019 at 7:18
BIGINT can take values upto numeric(19,0). This works for me since its a higher type. But I am trying to generate the exact type which is needed. — JITHIN_PATHROSE
– JITHIN_PATHROSE, Commented Jul 3, 2019 at 7:21
Try using BigInteger instead of long for the amount in your entity class — Abhijeet
– Abhijeet, Commented Jul 3, 2019 at 8:39

crizzis · Accepted Answer · 2019-07-12 22:18:16Z

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:

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)
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)

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.

Milan Desai · Accepted Answer · 2019-07-08 14:40:43Z

1

I can think on only reason is because in your entity amount type is Long but in JPA creation script your DDL specified as amount numeric(11, 0) here second param suggest decimal precision. As you can see java tries to enter data in Long type (ie. 10.0000), similar to BigInt in Database but database does not accept such decimal value being type numeric (11,0)

You should be able to resolve it by either changing your java code to have entity amount type int or change DDL to have scaleInt. ie. NUMERIC(11,5). However best bet would be to have DECIMAL type for any non Integer type.

http://www.h2database.com/html/datatypes.html#decimal_type

answered Jul 8, 2019 at 14:40

Milan Desai

1,3369 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related