Introduction

In the dynamic world of software development, efficient data management is crucial for creating robust and scalable applications. One essential aspect is the generation of unique identifiers for various entities in databases. Enter @GeneratedValue, a powerful annotation widely used in modern programming languages to automate and simplify the process of generating these identifiers. For example :-

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

Some commonly used strategy types :-

GenerationType.IDENTITY: This strategy relies on the auto-increment functionality provided by the database to generate unique identifier values automatically.

GenerationType.SEQUENCE: With this strategy, the @GeneratedValue annotation fetches unique identifier values from a predefined sequence generator.

GenerationType.TABLE: In the table-based strategy, the annotation utilizes a separate table to manage and generate unique identifier values.

GenerationType.AUTO: This strategy lets the JPA provider choose the most appropriate strategy based on the underlying database and configuration. It typically maps to either IDENTITY or SEQUENCE, depending on database capabilities.

GenerationType.UUID: While not a standard JPA strategy, some frameworks and libraries may offer a custom strategy for generating universally unique identifiers (UUIDs) for entities.

Problem

The issue with using GenerationType.AUTO is that it can lead to inconsistent behaviour across different database systems and configurations. The AUTO strategy lets the JPA provider (e.g., Hibernate) decide the most appropriate strategy based on the underlying database and its capabilities.

The problem arises when you develop an application on one database system (e.g., MySQL) and then deploy it on another database system (e.g., PostgreSQL). The JPA provider might automatically choose GenerationType.IDENTITY on MySQL, which uses auto-increment columns for primary keys, but switch to GenerationType.SEQUENCE on PostgreSQL, which uses sequence generators.

This inconsistency can lead to unexpected behaviour, such as duplicate primary key errors or inefficient use of database resources. It might also require additional configuration or database-specific adjustments during the deployment process.

We faced this issue without change of database also. While upgrading from Spring Boot 2 to 3 we faced this issue. In Spring Boot 2 we were using AUTO strategy which internally used IDENTITY strategy but when we upgraded spring boot version then AUTO strategy switched to SEQUENCE internally. While running our application and testing a api we faced this error :-

org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work [Table 'orderdata.order_items_seq' doesn't exist]

In above orderdata is database and order_items is table. So after Spring Boot upgrade hibernate was finding the order_items_seq table to generate id as AUTO strategy switched to SEQUENCE internally due to upgrade.

Summary

So in this way we were able to find the root cause for weird error. This article also shows importance of avoiding GenerationType.AUTO Strategy for ID generation in Spring Boot instead explicitly specify the strategy like IDENTITY OR SEQUENCE etc.

Explicitly setting the generation strategy and sequence generator provides more control and consistency across different databases and Hibernate versions. It also ensures that your application behaves as expected during upgrades and migrations. Please comment if you have any query, thanks.