Using Sequences in the H2 Database

1. Overview

Our applications running in production usually support sequence-based primary key generation for efficiency and scalability. However, during development or testing, we often switch to in-memory databases like H2.

In this tutorial, we’ll explore how we use sequence numbers in H2.

2. A Word on Sequence-Based ID Generation

Database sequences provide database-side control of ID generation, offering better performance than IDENTITY under high concurrency.

Many popular databases support sequences, including PostgreSQL, Oracle, SQL Server, and others.

In this tutorial, we focus on using sequences in the H2 database. We’ll first learn how to create a sequence, get the next value, and drop a sequence via native SQL statements. Then, we’ll show an example of using H2 sequences as auto-generated @Id in Spring Boot and JPA.

Since we often need to use H2 to simulate the production database in testing, we’ll take Oracle as an example to show how to make H2 compatible with Oracle’s sequence query statements.

For simplicity, we’ll leverage Spring Boot tests to verify the results.

Next, let’s dive in.

3. Using H2’s Sequences

In this section, we’ll demonstrate how to work with H2 sequences through Spring Boot tests.

3.1. Configuration

Let’s first create an embedded in-memory H2 database in the Spring Boot configuration file application-h2-seq.yml:

spring:
  datasource:
    driverClassName: org.h2.Driver
    url: jdbc:h2:mem:seqdb
    username: sa
    password:
  jpa:
    hibernate:
      ddl-auto: none

To load the file application-seq-h2.yml, our test should activate the h2-seq profile:

@ExtendWith(SpringExtension.class)
@ActiveProfiles("h2-seq")
@Transactional
public class H2SeqDemoIntegrationTest {
}

Next, let’s create and use some sequences.

3.2. Creating, Querying, and Dropping an H2 Sequence

Let’s first look at a test method that covers creating a sequence, obtaining its “next values”, and dropping the sequence using native SQL statements. Then, we’ll summarize these usages:

private final String sqlNextValueFor = "SELECT NEXT VALUE FOR my_seq";
private final String sqlNextValueFunction = "SELECT nextval('my_seq')";

@Test
void whenCreateH2SequenceWithDefaultOptions_thenGetExpectedNextValueFromSequence() {
    entityManager.createNativeQuery("CREATE SEQUENCE my_seq").executeUpdate();

    Long nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();
    assertEquals(1, nextValue);

    nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFor).getSingleResult();
    assertEquals(2, nextValue);

    nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();
    assertEquals(3, nextValue);

    entityManager.createNativeQuery("DROP SEQUENCE my_seq").executeUpdate();
}

As the example shows, we can use the following SQL statements to create, obtain values, and drop a sequence in the H2 database:

• Create – CREATE SEQUENCE <name>
• Obtain the next value – “SELECT NEXT VALUE FOR <name>” or using the nextval() function: “SELECT nextval(<name>)“
• Drop – DROP SEQUENCE <name>

It’s worth noting that the above CREATE statement will create a sequence with the initial value one and increment by one, which are the default options. However, sometimes, we want to create a sequence with a different start value and a custom increment.

Next, let’s see how to achieve that.

3.3. Customizing the Start and Increment Values

We can use START WITH and INCREMENT BY in the create statement to set a desired initial and increment value:

@Test
void whenCustomizeH2Sequence_thenGetExpectedNextValueFromSequence() {
    entityManager.createNativeQuery("CREATE SEQUENCE my_seq START WITH 1000 INCREMENT BY 10")
      .executeUpdate();

    Long nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFor).getSingleResult();
    assertEquals(1000, nextValue);

    nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();
    assertEquals(1010, nextValue);

    nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFor).getSingleResult();
    assertEquals(1020, nextValue);

    entityManager.createNativeQuery("DROP SEQUENCE my_seq").executeUpdate();
}

In this example, we created the my_seq sequence, which starts at 1000 and increments by 10.

3.4. Using Sequences in a JPA Entity

Next, let’s use an H2 sequence to generate a JPA entity’s primary key:

@Entity
@Table(name = "book")
class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "book_seq_gen")
    @SequenceGenerator(name = "book_seq_gen", sequenceName = "book_seq", allocationSize = 1)
    private Long id;
    private String title;

    public Book() {
    }

    public Book(String title) {
        this.title = title;
    }

    // ... getters and setters are omitted
}

In the Book entity:

• @GeneratedValue with GenerationType.SEQUENCE tells JPA to use a sequence
• @SequenceGenerator maps our entity to a named database sequence
• allocationSize = 1 ensures IDs are sequential instead of batched

Next, let’s create a test to verify the sequence behavior:

@Test
void whenSaveEntityUsingSequence_thenCorrect() {
    entityManager.createNativeQuery("CREATE SEQUENCE book_seq").executeUpdate();
    Book book1 = new Book("book1");
    assertNull(book1.getId());
    entityManager.persist(book1);
    assertEquals(1, book1.getId());

    Book book2 = new Book("book2");
    entityManager.persist(book2);
    assertEquals(2, book2.getId());
}

As the test shows, the sequence ID generator works as expected.

In the test, we manually created the book_seq sequence since we have “jpa.hibernate.ddl-auto=none.” If we had ddl-auto=create-drop or create, JPA would automatically create the sequence for us.

4. Making H2 Sequence Work with Oracle-Specific SQL Statement

We often use an in-memory H2 database in tests in practice to simulate the database used in production. However, by default, H2 uses its own dialect and SQL behavior, which might differ from our target production database.

For instance, we can get the next value from an Oracle sequence using SELECT <seq_name>.nextval FROM dual. But H2’s dialect doesn’t support the dual table, so the statement with dual won’t work with H2 by default.

To solve this issue, we can specify the MODE=… option in the JDBC URL to set SQL compatibility mode. This option tells H2 to behave like another database.

For example, we can add MODE=Oracle in the H2 database’s JDBC URL (application-h2-seq-oracle.yml):

spring:
  datasource:
    driverClassName: org.h2.Driver
    url: jdbc:h2:mem:seqdb;MODE=Oracle
    username: sa
    password:

Then, the Oracle sequence query will work as expected:

@ExtendWith(SpringExtension.class)
@ActiveProfiles("h2-seq-oracle")
@Transactional
public class H2SeqAsOracleDemoIntegrationTest {
    @Autowired
    private EntityManager entityManager;

    @Test
    void whenCreateH2SequenceWithDefaultOptions_thenGetExpectedNextValueFromSequence() {
        entityManager.createNativeQuery("CREATE SEQUENCE my_seq").executeUpdate();

        String sqlNextValueFor = "SELECT NEXT VALUE FOR my_seq";
        BigDecimal nextValueH2 = (BigDecimal) entityManager
          .createNativeQuery(sqlNextValueFor).getSingleResult();
        assertEquals(0, BigDecimal.ONE.compareTo(nextValueH2));

        String sqlNextValueOralceStyle = "SELECT my_seq.nextval FROM dual";
        BigDecimal nextValueOracle = (BigDecimal) entityManager.createNativeQuery(sqlNextValueOralceStyle)
          .getSingleResult();
        assertEquals(0, BigDecimal.TWO.compareTo(nextValueOracle));

        String sqlNextValueFunction = "SELECT nextval('my_seq')";
        nextValueOracle = (BigDecimal) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();
        assertEquals(0, BigDecimal.valueOf(3).compareTo(nextValueOracle));

        entityManager.createNativeQuery("DROP SEQUENCE my_seq").executeUpdate();
    }
}

As we can see, with MODE=Oracle, both Oracle query and the standard H2 queries work and give the correct result.

It’s also important to note that with MODE=Oracle, the sequence value is mapped to BigDecimal instead of Long.

5. Conclusion

In this article, we’ve explored how to use sequence numbers in the H2 database and learned that setting H2’s compatibility mode can make tests more realistic and less likely to break in production.