The problem of AUTO JPA GenerationType with MySQL and Hibernate

Last modified:

Are you struggling with performance issues in your Spring, Jakarta EE, or Java EE application?

What if there were a tool that could automatically detect what caused performance issues in your JPA and Hibernate data access layer?

Wouldn’t it be awesome to have such a tool to watch your application and prevent performance issues during development, long before they affect production systems?

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

So, rather than fixing performance issues in your production system on a Saturday night, you are better off using Hypersistence Optimizer to help you prevent those issues so that you can spend your time on the things that you love!

Introduction

In this post, I’ll show you why you should not rely on the AUTO GenerationType strategy if you’re using MySQL and Hibernate.

As I already mentioned, you should never use the TABLE identifier generator since it does not scale properly.

Prior to Hibernate 5

On Hibernate 4, if you had the following entity mapping:

@Entity(name = "Post")
@Table(name = "post")
public class Post {
⠀
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
⠀
    private String title;
⠀
    public Long getId() {
        return id;
    }
⠀
    public Post setId(Long id) {
        this.id = id;
        return this;
    }
⠀
    public String getTitle() {
        return title;
    }
⠀
    public Post setTitle(String title) {
        this.title = title;
        return this;
    }
}

When persisting 3 Post entities:

for (int i = 1; i <= 3; i++) {
    entityManager.persist(
        new Post()
            .setTitle(
                String.format(
                    "High-Performance Java Persistence, Part %d", i
                )
            )
    );
}

Hibernate would generate the following insert statements:

INSERT INTO post (title) 
VALUES ('High-Performance Java Persistence, Part 1')

INSERT INTO post (title) 
VALUES ('High-Performance Java Persistence, Part 2')

INSERT INTO post (title) 
VALUES ('High-Performance Java Persistence, Part 3')

That’s great! Hibernate used the IDENTITY column to generate the entity identifier which is the only reasonable option for MySQL.

Hibernate 5 or 6

If you run the same unit test on Hibernate 5 or 6, you’ll get the following SQL statements:

SELECT next_val as id_val 
FROM hibernate_sequence FOR UPDATE

UPDATE hibernate_sequence 
SET next_val= 2 where next_val=1

SELECT next_val as id_val 
FROM hibernate_sequence FOR UPDATE

UPDATE hibernate_sequence 
SET next_val= 3 where next_val=1

SELECT next_val as id_val 
FROM hibernate_sequence FOR UPDATE

UPDATE hibernate_sequence 
SET next_val= 4 where next_val=3

INSERT INTO post (title, id) 
VALUES ('High-Performance Java Persistence, Part 1', 1)

INSERT INTO post (title, id) 
VALUES ('High-Performance Java Persistence, Part 2', 2)

INSERT INTO post (title, id) 
VALUES ('High-Performance Java Persistence, Part 3', 3)

What’s just happened? Well, Hibernate picks the TABLE generator instead of IDENTITY when the underlying database does not support sequences. However, TABLE generator is not a good choice. Check out the HHH-11014 Jira issue for more details related to this behavior change.

How to fix it?

The fix is extremely easy. You just need to use the IDENTITY strategy instead:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

Now, when running the previous test case, Hibernate will use the IDENTITY column instead:

INSERT INTO post (title) 
VALUES ('High-Performance Java Persistence, Part 1')

INSERT INTO post (title) 
VALUES ('High-Performance Java Persistence, Part 2')

INSERT INTO post (title) 
VALUES ('High-Performance Java Persistence, Part 3')

If you want to use a portable solution that manages to customize the SEQUENCE generator while still allowing you to pick the IDENTITY generator for MySQL, then check out this article.

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

Conclusion

JPA portability is a myth! In reality, you have to know the details of the underlying JPA provider if you want a high-performance enterprise application.

Follow @vlad_mihalcea