Analysing JPA DDL Generation in Hibernate

:heavy_exclamation_mark: This post is older than a year. Consider some information might not be accurate anymore. :heavy_exclamation_mark:

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

public class Blacklist {
    @Column(nullable = false)
    private BigInteger blockedId;

The value of used id

BigInteger id = BigInteger.valueOf(6710203000580400250L);

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.

public class Blacklist {
    @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.

Please remember the terms for blog comments.