hibernate and multiple databases

We’ve had an app running on SQL Server and MySQL under Hibernate for over a year now — but I failed (rather miserably) getting Postgres running well with the same mapping files.

The main problem was that our mapping files use a native generator:

    <generator class="native" />

In SQL Server and MySQL this maps to an auto incrementing column, but in Postgres (I think) you end up with an int or bigint (depending upon your domain object data type), and a separate sequence (named hibernate_sequence), used to populate it.

Thus if you’re applying raw SQL statements outside of Hibernate’s control, you can’t use the same SQL across all 3 databases. In other words, assuming a table:

The following sql statement…

    insert into mytable (col1, col2) values ('a', 'b')

…will work on SQL Server and MySQL (in these cases, the column “id” is created as an identity or auto_increment), but not on Postgres (the column is created as a basic int).

However, I’ve just found a workaround — not sure if it’s the recommended approach (I’ve seen a couple of other references describing other ways to accomplish the same result, but these involve changing the mapping files), but this way works for me.

The answer is to define your own Hibernate dialect, subclassing the default Postgres dialect:

import java.sql.Types;
import org.hibernate.Hibernate;
import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.id.IdentityGenerator;

public class CustomPostgresqlDialect extends PostgreSQLDialect {

    public CustomPostgresqlDialect() {
        super();

        // register some functions and column types in here
        ...
    }

    public Class getNativeIdentifierGeneratorClass() {
        return IdentityGenerator.class;
    }
}

This dialect returns an identity generator instead of a sequence generator, which means we don’t need to change our mappings across all 3 databases.

Result!