CODEWITHSUNDEEP
javahibernatespring-boot
JITHIN_PATHROSE
JITHIN_PATHROSE

Reputation: 1396

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

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 ?

Upvotes: 5

Views: 9721

Answers (2)

crizzis
crizzis

Reputation: 10716

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)

  1. 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.

Upvotes: 3

Milan Desai
Milan Desai

Reputation: 1306

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.

enter image description here

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

Upvotes: 1

Related Questions