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

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!


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.

Seize the deal! 40% discount. Seize the deal! 40% discount.


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.

Transactions and Concurrency Control eBook

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.