Make Hibernate respect sequences in both MySQL and PostgreSQL

Question

I am working on an existing application which uses Hibernate and MySQL to store objects in the database. Since all objects in the application share an id property that is used to fill a primary key id column, the original developers decided to put the id in an abstract superclass, like so:

public abstract class BaseModel implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;
}

Now, every class in the system that inherits from BaseModel gets the id property, and Hibernate wil neatly store it in the database. So far so good.

The project is currently using Hibernate 4.3.5.Final in it's maven dependencies:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
    <version>4.3.5.Final</version>
</dependency>

This application needs to be changed to work on both MySQL and PostgreSQL.

I ran into a problem where Hibernate does not respect the id/auto increment columns in PostgreSQL. When I manually insert a row in the database, PostgreSQL inserts the row and updates the sequence. When I start up the application after that, Hibernate tries to insert a row with an id of "1", effectively ignoring the PostgreSQL sequence.

As far as I can tell, in order to solve this I have to tell Hibernate to use a sequence generator with a specific database sequence, like so:

public abstract class BaseModel implements Serializable {
    @Id 
    @SequenceGenerator(name="pk_sequence",sequenceName="entity_id_seq", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="pk_sequence")
    @Column(name = "ID")
    private Long id;
}

There are two problems with this solution (in order of importance):

This will break things when the new version of the application runs against an existing MySQL database.
It will require me to push the id column "down" into the class hierarchy, and explicitly manage all the sequences of all the classes and tables, which turns out to be quite a chore.

The first point can be solved with a @TableGenerator annotation and I am looking into that now, but it would still require me to refactor a lot. Is there a better/smarter way to tell Hibernate to initialize and respect existing sequences or ids of existing records in the database at application startup?

Additional research: I noticed that the application creates a sequence with the name hibernate_sequence and ignores the sequences PostgreSQL created for the serial id columns. This feels pretty dangerous and broken.

What Hibernate version are you using? There seem to have been some significant changes in Hibernate's sequence generation in various versions but I haven't found much documentation about it. — Craig Ringer
– Craig Ringer, Commented Apr 7, 2015 at 14:03
4.3.5.Final, I added that info to the question, thanks for pointing out the omission. — Rolf
– Rolf, Commented Apr 7, 2015 at 14:45
I agree with your "dangerous and broken" sentiment. It also uses an allocation size of 50 instead of the PostgreSQL default of 1 but that is the JPA spec's fault. Hibernate expects to own the db and does not play very well with others unless its configuration is overridden. — Craig Ringer
– Craig Ringer, Commented Apr 7, 2015 at 15:59

Rolf · Accepted Answer · 2015-04-09 08:54:25Z

In order to make Hibernate play nice with both MySQL and PostgreSQL, I changed the SERIAL columns in the PostgreSQL version of the database into BIGINT. Hibernate uses the mentioned hibernate_sequence to generate ids for the newly inserted rows.

The downside of this is that developers need to understand the following difference when switching between MySQL and PostgreSQL:

On MySQL, Hibernate uses the auto increment columns in the table definitions, resulting in ids which are unique per table.
On PostgreSQL, Hibernate uses it's hibernate_sequence to generate ids for all tables, making ids unique accross all tables, and resulting in larger gaps in the ids of rows in a table.

This solution enables me to use the same Java code on both MySQL and PostgreSQL. Only the DDL scripts to create/change the table definitions differ between databases.

Collectives™ on Stack Overflow

Make Hibernate respect sequences in both MySQL and PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related