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

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 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.

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements

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

  1. How about GenerationType.AUTO ?

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = “post_seq”)
    @SequenceGenerator(name = “post_seq”, sequenceName = “post_seq”)
    private Long id;

  2. Great post, Vlad.

    Instead of using ORM.xml to change entities mapping you can use AUTO as strategy in @GeneratedValue annotation. This way Hibernate configures the correct strategy based on underlying database – and the best thing is: Hibernate does not ignore your @SequenceGenerator configuration.

    So you can have a portable mapping between different databases. I blogged about this years ago (6 tips on how to keep database portability with JPA) : http://blog.triadworks.com.br/6-dicas-para-manter-a-portabilidade-da-sua-aplicacao-com-jpa (pt_BR)

    If you have another good tip about keeping database portability (besides th those six) with JPA/Hibernate, please, tell me 😉

    Keep going with your great posts!

      1. It doesn’t make any sense! Besides breaking backward compatibility with older Hibernate versions they still are picking the worst strategy to MySQL!

  3. Great post, Vlad.

    In enhanced generators like SequenceStyleGenerator it chooses between a table or a sequence. My question if I used it with (mysql) as table-based. It will behave the same TableGenerator.

      1. Recently, I moved from Identity to SequenceStyleGenerator to take advantage of JDBC batch.
        But now I, can’t go with it because I have many applications access my database(Mysql). And use SequenceStyleGenerator as table-based does not scale and it will performance issue. Do you have any suggestion? by the way, my app working in Cluster environment

      2. The IDENTITY entity identifier does not take advantage of batching just for insert statements. For update and delete, it works just fine. As long as insert statement batching is not a problem for you, you should probably optimize other more important parts of your application. If you do heavy data inserts, then you could use jOOQ to do the insert part.

  4. Hi!, what about sequences in Oracle RAC?, If cache sequence is minimun you get contention at Database level, but if the cache is large, your id will be out of order, ex:
    first insert: got id 1
    seconf insert: got id 2
    third insert: got id 21
    4th insert got id 3

    The id got depends on the rac node where the app did the insert.

    so in oracle rac you can’t assure order using Sequences. What other strategies could be use to assure order in Oracle RAC?

    thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s