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

(Last Updated On: January 4, 2018)


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 {

        generator = "sequence", 
        strategy = GenerationType.SEQUENCE
        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();
            String.format("Post nr %d", i + 1)

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"?>

    xsi:schemaLocation=" orm_2_1.xsd"
    <entity class="Post" access="FIELD">
            <id name="id">
                <generated-value strategy="IDENTITY"/>

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) {

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

protected Class<?>[] entities() {
    return new Class<?>[] {

protected String[] resources() {
    return new String[] {

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-unit name="persistenceUnit">





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.


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!


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

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


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

  3. 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) : (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!

  4. How about GenerationType.AUTO ?

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

Leave a Reply

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