When developing Java applications with databases, auto-generating primary keys is a common requirement. Sequences are a reliable way to generate unique IDs, especially in databases like Oracle and PostgreSQL. The H2 database, often used for testing and development, also supports sequence generation. Let us delve into understanding how java h2 sequences work for generating unique primary keys in Spring Boot applications.
1. What is H2 Database?
The H2 Database is a lightweight, fast, and open-source relational database management system (RDBMS) written entirely in Java. It is widely used for unit testing, development, and prototyping due to its small footprint, ease of use, and flexible deployment options.
- In-memory mode: Stores all data in RAM, perfect for quick testing without persistence.
- Embedded/file-based mode: Stores data on disk while running inside your Java application.
- Server mode: Can also act as a standalone server database accessible via TCP/IP.
1.1 What is Sequence-Based ID Generation?
A SEQUENCE in a database is a special kind of object that automatically generates a sequential series of unique numeric values. These are commonly used for generating primary key values to ensure uniqueness across table rows. In relational databases like Oracle and PostgreSQL, sequences are a standard way to implement auto-incremented values with high performance and control over increment steps, starting values, and caching.
1.1.1 Why use sequence-based ID generation?
- Ensures unique primary keys across rows.
- Decouples key generation logic from the application.
- Allows for custom incrementing logic (e.g., increment by 10).
- Enables compatibility with Oracle-style SQL when working with H2.
2. Code Example
This section demonstrates a full working Spring Boot example using H2 and sequence-based ID generation with JPA. It includes Maven setup, configuration properties, entity creation, repository definition, application logic, and expected output.
2.1 Maven Dependencies
We first need to add the necessary dependencies for Spring Boot JPA and H2 database in the pom.xml. These dependencies enable JPA integration and provide an in-memory H2 database for testing and development.
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
2.2 application.properties
The application configuration ensures that H2 is used as an in-memory database and enables automatic table creation. It also activates the H2 web console for testing.
spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password= spring.h2.console.enabled=true spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.jpa.hibernate.ddl-auto=create
2.3 Entity Definition
The Employee entity uses a sequence-based ID generation strategy. We define a named sequence generator and use it for generating primary key values.
import jakarta.persistence.*;
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "emp_seq_gen")
@SequenceGenerator(
name = "emp_seq_gen", sequenceName = "emp_seq", allocationSize = 1)
private Long id;
private String name;
public Employee() {}
public Employee(String name) {
this.name = name;
}
// Getters and setters
}
2.4 Repository
This interface extends JpaRepository to enable basic CRUD operations for the `Employee` entity. No implementation is needed as Spring Data generates it automatically.
import org.springframework.data.jpa.repository.JpaRepository;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {}
2.5 Application Code
This is the main Spring Boot application class. It inserts two employee records and retrieves all the records from the database using the repository.
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
@SpringBootApplication
public class SequenceExampleApp {
public static void main(String[] args) {
SpringApplication.run(SequenceExampleApp.class, args);
}
@Bean
CommandLineRunner run(EmployeeRepository repository) {
return args -> {
repository.save(new Employee("Alice"));
repository.save(new Employee("Bob"));
repository.findAll().forEach(emp ->
System.out.println("Employee: " + emp.getId() + ", " + emp.getName()));
};
}
}
2.6 Output
When you run the application, you will see the following output printed to the console, confirming that the sequence is working as expected.
Employee: 1, Alice Employee: 2, Bob
3. Using Oracle-Style SQL Sequences in H2
If you’re using H2 as a test replacement for Oracle, and your SQL or ORM expects Oracle-style sequence syntax like emp_seq.NEXTVAL, you can configure H2 to work in Oracle compatibility mode. This section demonstrates how to adjust your Spring Boot configuration and use native queries to align with Oracle behavior.
3.1 application.properties
Update the H2 JDBC URL to enable Oracle compatibility mode using ;MODE=Oracle. This allows you to use Oracle-style SQL syntax such as emp_seq.NEXTVAL in your application.
spring.datasource.url=jdbc:h2:mem:testdb;MODE=Oracle spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password= spring.h2.console.enabled=true spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.jpa.hibernate.ddl-auto=none
3.2 Schema Initialization (schema.sql)
Since we want to use Oracle-style sequences, we must manually initialize the schema. Add a schema.sql file in the src/main/resources directory:
CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 1;
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(255)
);
3.3 Repository with Native SQL Insert
Create a custom Spring Data repository method to insert using emp_seq.NEXTVAL. This mimics Oracle behavior during inserts.
import jakarta.transaction.Transactional;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
@Modifying
@Transactional
@Query(value = "INSERT INTO employee (id, name) VALUES (emp_seq.NEXTVAL, ?1)", nativeQuery = true)
void insertWithSequence(String name);
}
3.4 Application Code
Use a simple Spring Boot runner to insert a new record using Oracle-style sequence and verify the result.
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
@SpringBootApplication
public class OracleModeSpringBootApp {
public static void main(String[] args) {
SpringApplication.run(OracleModeSpringBootApp.class, args);
}
@Bean
CommandLineRunner run(EmployeeRepository repository) {
return args -> {
repository.insertWithSequence("Charlie");
};
}
}
3.5 Output
When the application starts, you’ll see the following in the H2 console or by querying the database:
Employee ID: 100, Name: Charlie
4. Conclusion
H2 supports sequence-based ID generation similar to Oracle, making it a great in-memory database for development and testing. By configuring sequence generators in JPA and enabling Oracle compatibility mode, you can closely simulate Oracle database behavior. This helps ensure that development and production environments behave consistently, especially when working with legacy or enterprise systems.
