How to get Next Value from a PostgreSQL Sequence in a Spring Bean?

In some cases you may need to get nextval of a sequence in your Spring bean, say for example you need to generate a CSV file with a unique id column.

 

In this tutorial, we are explaining how to create a sequence in PostgreSQL and how to get the next value from the sequence in a Spring bean.

Creating a sequence in PostgreSQL

First we will create a sequence named texient_uuid_seq in PostgreSQL. For this, execute the following command in the terminal:

-- Sequence: texient_uuid_seq

-- DROP SEQUENCE texient_uuid_seq;

CREATE SEQUENCE texient_uuid_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 100000
  CACHE 1;
ALTER TABLE texient_uuid_seq
  OWNER TO postgres;

A sequence named texient_uuid_seq is created.

Defining the Beans

A straight forward approach would be to use plain sql as given below:

 

Query q = entityManager.createNativeQuery(
                "SELECT texient_uuid_seq.nextval from DUAL");
BigDecimal result = (BigDecimal)q.getSingleResult();   
return result.longValue();

Spring has some in-built classes to deal with sequences, those are:

• DB2SequenceMaxValueIncrementer
• OracleSequenceMaxValueIncrementer
• PostgreSQLSequenceMaxValueIncrementer

All these classes implements DataFieldMaxValueIncrementer, so this can be used in your DAO classes without worrying about the implementation.

 

In this tutorial, we will use PostgreSQLSequenceMaxValueIncrementer to get the next value from the sequence texient_uuid_seq. Define the following beans in your application context:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
  destroy-method="close">
 <property name="driverClassName" value="${texient.jdbc.driver}" />
 <property name="url" value="${texient.jdbc.url}" />
 <property name="username" value="${texient.jdbc.user}" />
 <property name="password" value="${texient.jdbc.password}" />
 <property name="maxActive" value="${texient.jdbc.pool.size}"/>
 <property name="validationQuery" value="${texient.jdbc.validationQuery}"/>
 <property name="testWhileIdle" value="${texient.jdbc.testWhileIdle}"/>
</bean>

<bean id="transactionManager" 
  class="org.springframework.jdbc.datasource.DataSourceTransactionManager" 
  lazy-init="true">
 <property name="dataSource" ref="dataSource" />
</bean>

<bean id="uidIncrementer" 
 class="org.springframework.jdbc.support.incrementer.PostgreSQLSequenceMaxValueIncrementer">
 <property name="dataSource" ref="dataSource" />
 <property name="incrementerName" value="texient_uuid_seq" />
</bean>

First, we defined a dataSource bean, then a transactionManager and finally a uidIncrementer bean. Let us concentrate on the uidIncrementer bean, there are two properties set, first the dataSource and the second, the incrementerName. incrementerName is nothing but the PostgreSQL sequence we created in the first step.

Getting the next value in a Spring Bean

Now let us write a utility bean named UidGenerator which returns the next value from the sequence.

import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.jdbc.support.incrementer.
          PostgreSQLSequenceMaxValueIncrementer;
import org.springframework.stereotype.Component;

@Component
public class UidGenerator implements ApplicationContextAware {
 
 private static ApplicationContext context;

 public Long getUid() {
  PostgreSQLSequenceMaxValueIncrementer uidIncrementer = 
      (PostgreSQLSequenceMaxValueIncrementer)context.getBean("uidIncrementer");
  return uidIncrementer .nextLongValue();
 }

 public void setApplicationContext(ApplicationContext ac) {
  context = ac;
 }

}

Now you can inherit other classes from UidGenerator to get the next value from the sequence.

Conclusion

In this tutorial, we learnt how to get the next value of a sequence in a Spring bean. A detailed tutorial will be published soon on how to use a CSV file generator using Spring batch, stay tuned.