By / 07 August 2015 / development / est. 2 min read

Analysing JPA DDL Generation in Hibernate

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

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 {
&hellip;
    @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.

Analysing JPA DDL Generation in Hibernate

Author

Tan-Vinh Nguyen