Get Nextval From Sequence With Spring JPA

1. Introduction

Sequences are number generators for unique IDs to avoid duplicate entries in a database. Spring JPA offers ways to work with sequences automatically for most situations. However, there might be specific scenarios where we need to retrieve the next sequence value manually before persisting an entity. For instance, we might want to generate a unique invoice number before saving the invoice details to the database.

In this tutorial, we’ll explore a few approaches for fetching the next value from a database sequence using Spring Data JPA.

2. Setting up Project Dependencies

Before we dive into using sequences with Spring Data JPA, let’s ensure our project is properly set up. We’ll need to add the Spring Data JPA and PostgreSQL driver dependencies to our Maven pom.xml file and create the sequence in the database.

2.1. Maven Dependencies

First, let’s add the necessary dependencies to our Maven project:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

2.2. Test Data

Below is the SQL script that we use to prepare our database before running the test cases. We can save this script in a .sql file and place it inside the src/test/resources directory of our project:

DROP SEQUENCE IF EXISTS my_sequence_name;
CREATE SEQUENCE my_sequence_name START 1;

This command creates a sequence starting from 1, which increments by 1 for each call to NEXTVAL.

Then, we use the @Sql annotation with the executionPhase attribute set to BEFORE_TEST_METHOD in the test class to insert test data into the database before each test method execution:

@Sql(scripts = "/testsequence.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)

3. Using @SequenceGenerator

Spring JPA can work with the sequence behind the scenes to automatically assign a unique number whenever we add a new item. We typically use the @SequenceGenerator annotation in JPA to configure a sequence generator. This generator can be used to automatically generate primary keys in entity classes.

Additionally, it’s often combined with the @GeneratedValue annotation to specify the strategy for generating primary key values. Here’s how we can use @SequenceGenerator to configure primary key generation:

@Entity
public class MyEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "mySeqGen")
    @SequenceGenerator(name = "mySeqGen", sequenceName = "my_sequence_name", allocationSize = 1)
    private Long id;

    // Other entity fields and methods
}

The @GeneratedValue annotation with the GenerationType.SEQUENCE strategy indicates that primary key values will be generated using a sequence. Subsequently, the generator attribute associates this strategy with a designated sequence generator named “mySeqGen“.

Moreover, the @SequenceGenerator annotation configures the sequence generator named “mySeqGen“. It specifies the name of the database sequence “my_sequence_name” and an optional parameter allocation size.

allocationSize is an integer value specifying how many sequence numbers to pre-fetch from the database at once. For example, if we set allocationSize to 50, the persistence provider requests 50 sequence numbers in a single call and stores them internally. It then uses these pre-fetched numbers for future entity ID generation. This can be beneficial for applications with high write volumes.

With this configuration, when we persist a new MyEntity instance, the persistence provider automatically obtains the next value from the sequence named “my_sequence_name“. The retrieved sequence number is then assigned to the entity’s id field before saving it to the database.

Here’s an example illustrating how to retrieve the sequence number along with the entity’s ID after persisting it:

MyEntity entity = new MyEntity();

myEntityRepository.save(entity);
long generatedId = entity.getId();

assertNotNull(generatedId);
assertEquals(1L, generatedId);

After saving the entity, we can access the generated ID using the getId() method on the entity object.

4. Spring Data JPA Custom Query

In some scenarios, we might need the next number or unique ID before saving it to the database. To do this, Spring Data JPA offers a way to query the next sequence using custom queries. This approach involves using a native SQL query within the repository to access the sequence.

The specific syntax for retrieving the next value depends on the database system. For instance, in PostgreSQL or Oracle, we use the NEXTVAL function to obtain the next value from the sequence. Here’s an example implementation using the @Query annotation:

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    @Query(value = "SELECT NEXTVAL('my_sequence_name')", nativeQuery = true)
    Long getNextSequenceValue();
}

In the example, we annotate the getNextSequenceValue() method with @Query. With the @Query annotation, we can specify a native SQL query that retrieves the next value from the sequence using the NEXTVAL function. This enables direct access to the sequence value:

@Autowired
private MyEntityRepository myEntityRepository;

long generatedId = myEntityRepository.getNextSequenceValue();

assertNotNull(generatedId);
assertEquals(1L, generatedId);

Since this approach involves writing database-specific code, if we change databases, we might need to adjust the SQL query.

5. Using EntityManager

Alternatively, Spring JPA also provides the EntityManager API, which we can use for directly retrieving the next sequence value. This method offers finer-grained control but bypasses JPA’s object-relational mapping features.

Below is an example of how we can use the EntityManager API in Spring Data JPA to retrieve the next value from a sequence:

@PersistenceContext
private EntityManager entityManager;

public Long getNextSequenceValue(String sequenceName) {
    BigInteger nextValue = (BigInteger) entityManager.createNativeQuery("SELECT NEXTVAL(:sequenceName)")
      .setParameter("sequenceName", sequenceName)
      .getSingleResult();
    return nextValue.longValue();
}

We use the createNativeQuery() method to create a native SQL query. In this query, the NEXTVAL function is called to retrieve the next value from the sequence. We can notice that the NEXTVAL function in PostgreSQL returns a value of type BigInteger. Therefore, we use the longValue() method to convert the BigInteger to a Long.

With the getNextSequenceValue() method, we can call it this way:

@Autowired
private MyEntityService myEntityService;

long generatedId = myEntityService.getNextSequenceValue("my_sequence_name");
assertNotNull(generatedId);
assertEquals(1L, generatedId);

6. Conclusion

In this article, we’ve explored various approaches to fetch the next value from a database sequence using Spring Data JPA. Spring JPA offers seamless integration with database sequences through annotations like @SequenceGenerator and @GeneratedValue. In scenarios where we need the next sequence value before saving an entity, we can use custom queries with Spring Data JPA.

As always, the source code for the examples is available over on GitHub.