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.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW