Loading...

Analysing JPA DDL Generation in Hibernate

Using the JPA (Java Persistence API) 2.0 with Hibernate for a database integration test against a in-memory db like H2 or HSQLDB, is a good way to test the JPA ORM (object relational mapping). In the process, errors might occur depending on the database vendor. Following example illustrate a problem with Hibernate and HSQLDB.

The output is truncated.

13:50:55.262 [main] DEBUG org.hibernate.SQL - insert into BLACKLIST (MANDANT,..,BLOCKEDID) values (?,..,?)
13:50:55.263 [main] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 3403, SQLState: 22003
13:50:55.263 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - data exception: numeric value out of range;  table: BLACKLIST column: ID
13:50:55.272 [main] WARN  Transaction - Unexpected exception from beforeCompletion; transaction will roll back

The entity

@Entity
@Table(name="BLACKLIST")
public class Blacklist {
…
    @Column(nullable = false)
    private BigInteger blockedId;
}

The value of used id

BigInteger id = BigInteger.valueOf(6710203000580400250L);
blacklist.setBlockedId(id);

Numeric value out of range seems not to be true. H2 maps BigInteger to BigInt, so HSQLDB should behave the same way?

Activate SQL debug in logging configuration (+logback-test.xml+).

<logger name="org.hibernate.SQL" level="DEBUG"/>

The trace:

13:50:49.228 [main] DEBUG org.hibernate.SQL - create table BLACKLIST (id bigint not null, BLOCKEDID numeric(19,2) not null, .., primary key (id))

Obviously Hibernate does not take BigInt for HSQLDB, and tries to use 19 digits with 2 decimal places. By changing the mapping, we enforce a correct mapping.

@Entity
@Table(name="BLACKLIST")
public class Blacklist {
&hellip;
    @Column(nullable = false, precision = 38, scale = 0)
    private BigInteger blockedId;
}
14:02:26.744 [main] DEBUG org.hibernate.SQL - create table BLACKLIST (id bigint not null, BLOCKEDID numeric(38,0) not null, .., primary key (id))

Now the value will fit into the column.