How to fix “wrong column type encountered” schema-validation errors with JPA and Hibernate

Last modified:

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

Mapping entities to database tables is usually a very straightforward process. However, if your mappings are rather unusual, you might bump into some rare issues like this one I found on the Hibernate forum.

In this article, I’m going to explain the mapping between Java objects to JDBC and database column types, and how you can fix the issue described in the aforementioned Hibernate question.

A mapping issue

Considering we have the following SQL Server database schema:

CREATE TABLE event (
    id NUMERIC(19,0) IDENTITY NOT NULL, 
    PRIMARY KEY (id)
)

This schema mapping is rather unusual since it’s much more common to use an INTEGER or BIGINT column type for an IDENTITY column. However, if you’re dealing with a legacy schema, you have to deal with it on the Java side.

And you provide the following entity mapping:

@Entity(name = "Event")
@Table(name = "event")
public class Event {

    @Id
    @GeneratedValue(
        strategy = GenerationType.IDENTITY
    )
    private Long id;
}

If you allow Hibernate to validate the mapping using the validate hbm2ddl option:

<property name="hibernate.hbm2ddl.auto" value="validate"/>

Hibernate is going to throw the following Exception during application bootstrap:

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: 
Schema-validation: wrong column type encountered in column [id] in table [event]; 
found [numeric (Types#NUMERIC)], but expecting [bigint (Types#BIGINT)]

Hibernate LongType mapping logic

To understand why the schema validation has failed, you have to understand how basic types are handled by Hibernate. For instance, the java.lang.Long type is handled by the org.hibernate.type.LongType, whose class diagram looks as follows:

The SqlTypeDescriptor is used to map a Java type to a JDBC type. The goal of this class is to bind the value to the PreparedStatement when executing INSERT, UPDATE or DELETE statements, and to extract the value from the ResultSet after executing a SELECT statement.

The JavaTypeDescriptor is used to handle the mapping between on Java type (e.g. Long) to another (e.g. Number or String) and is used when transforming the value read from the SqlTypeDescriptor to the expected Java type (e.g. Long in this case).

The LongType uses the following SqlTypeDescriptor and JavaTypeDescriptor:

public LongType() {
    super( 
        BigIntTypeDescriptor.INSTANCE, 
        LongTypeDescriptor.INSTANCE 
    );
}

The BigIntTypeDescriptor represents the SqlTypeDescriptor implementation, and so it expects a java.sql.Types.BIGINT on the JDBC side. However, our database mapping uses a NUMERIC column type, which is designated by the java.sql.Types.NUMERIC JDBC type, hence the error message we got during schema validation.

Fixing the issue

The fix is really simple. We just need to override the default JDBC type Hibernate using an explicit type declaration:

@Id
@GeneratedValue(
    strategy = GenerationType.IDENTITY
)
@Column(
    columnDefinition = "NUMERIC(19,0)"
)
private Long id;

Now Hibernate knows to expect a NUMERIC column on the database side, instead of a BIGINT one.

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!

Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September

High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino

High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet

Conclusion

The explicit mappings, such as @Table or @Column, are not only useful when generating the database schema using the hbm2ddl tool. For a production system, managing the database schema with a tool like Flyway is the way to go.

However, you need to make sure the JPA entity mapping schema is in sync with the database one, and this is what the validate hbm2ddl strategy is all about. Although the default mappings are suitable for most use cases, there are times when you need to provide an explicit type declaration, such a NUMERIC column type for an IDENTITY column.

Follow @vlad_mihalcea