How to replace the TABLE identifier generator with either SEQUENCE or IDENTITY in a portable way

(Last Updated On: January 4, 2018)

Introduction

As previously explained, the TABLE identifier generator does not scale, so you should avoid id. However, some enterprise applications might need to run on both MySQL (which does not support database sequences), as well as Oracle, PostgreSQL, and SQL Server 2012.

This is article is going to explain how easily you can achieve this goal using the JPA mapping overriding.

Sequences, for the win

Database sequences are the best identifier generator choice when using JPA and Hibernate. This is because, unlike IDENTITY generator, you can take advantage of JDBC batch updates. Therefore, you should always use a database sequence if the underlying database supports this feature.

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    @GeneratedValue(
        generator = "sequence", 
        strategy = GenerationType.SEQUENCE
    )
    @SequenceGenerator(
        name = "sequence", 
        allocationSize = 10
    )
    private Long id;

    private String title;

    //Getters and setters omitted for brevity sake
}

When persisting 5 Post entities on PostgreSQL:

doInJPA(entityManager -> {
    for (int i = 0; i < 5; i++) {
        Post post = new Post();
        post.setTitle(
            String.format("Post nr %d", i + 1)
        );
        entityManager.persist(post);
    }
});

Hibernate generates the following SQL statements:

SELECT nextval ('hibernate_sequence')
SELECT nextval ('hibernate_sequence')

INSERT INTO post (title, id) VALUES ('Post nr 1', 1)
INSERT INTO post (title, id) VALUES ('Post nr 2', 2)
INSERT INTO post (title, id) VALUES ('Post nr 3', 3)
INSERT INTO post (title, id) VALUES ('Post nr 4', 4)
INSERT INTO post (title, id) VALUES ('Post nr 5', 5)

As you can see, there are only 2 sequence calls which determine the minimum and maximum boundaries of the pooled optimizer.

What about MySQL?

For MySQL, since we don’t want to use the TABLE generator, we are bound to use the IDENTITY identifier strategy. However, we want to reuse the previous entity mapping, so all we need to to is provide a JPA XML mapping that overrides the Java-based annotation mapping:

<?xml version="1.0" encoding="UTF-8"?>

<entity-mappings 
    xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm orm_2_1.xsd"
    version="2.1"
        >
    <package>com.vladmihalcea.book.hpjp.hibernate.identifier.global</package>
    <entity class="Post" access="FIELD">
        <attributes>
            <id name="id">
                <generated-value strategy="IDENTITY"/>
            </id>
        </attributes>
    </entity>
</entity-mappings>

This is all possible because the JPA specification defines the following behavior:

XML metadata may be used as an alternative to these annotations, or to override or augment annotations

— JPA 2.1 specification, 11.1 Annotations for Object/Relational Mapping

Now, since I’m bootstrapping JPA without the persistence.xml file, my PersistenceUnitInfo looks like this:

PersistenceUnitInfoImpl persistenceUnitInfo = 
    new PersistenceUnitInfoImpl(
        name, entityClassNames(), properties()
);

String[] resources = resources();
if (resources != null) {
    persistenceUnitInfo
        .getMappingFileNames()
        .addAll(Arrays.asList(resources));
}

So, for MySQL, the Java-based mapping is done as follows:

@Override
protected Class<?>[] entities() {
    return new Class<?>[] {
        Post.class,
    };
}

@Override
protected String[] resources() {
    return new String[] {
        "mappings/identifier/global/mysql-orm.xml"
    };
}

So, when I run the previous test case on MySQL, Hibernate generates the following SQL statements:

INSERT INTO post (title) VALUES ('Post nr 1')
INSERT INTO post (title) VALUES ('Post nr 2')
INSERT INTO post (title) VALUES ('Post nr 3')
INSERT INTO post (title) VALUES ('Post nr 4')
INSERT INTO post (title) VALUES ('Post nr 5')

As you can see, the XML mapping has overridden the Annotation mapping for the identifier attribute only. This is great since we can reuse everything we defined in Java annotations while still tackling the database identifier portability issue.

The persistence.xml configuration file

Chances are you are going to use a persistence.xml in your enterprise application, so the configuration looks as follows:

<persistence 
    xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
    http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
    version="2.1">

    <persistence-unit name="persistenceUnit">

        <provider>
            org.hibernate.jpa.HibernatePersistenceProvider
        </provider>

        <mapping-file>
            mappings/identifier/global/mysql-orm.xml
        </mapping-file>

        <class>
            com.vladmihalcea.book.hpjp.hibernate.identifier.global.Post
        </class>

    </persistence-unit>
</persistence>

You can even use mapping files that are located outside the JAR file so that the MySQL environment just supplies the proper orm.xml configuration file in the designated external configuration directory.

That’s it!

Why not using AUTO?

GenerationType.AUTO is not a good pick for MySQL since, from Hibernate 5, it falls back to TABLE generator, which is bad for performance.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

There’s no need to use the TABLE identifier generator. If portability is your primary concern, you can just use SEQUENCE by default, and override this with the IDENTITY strategy for MySQL. Just make sure that the MySQL environment comes with the orm.xml configuration file that overrides the SEQUENCE identifier mapping.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

2 thoughts on “How to replace the TABLE identifier generator with either SEQUENCE or IDENTITY in a portable way

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.