JPA - @SequenceGenerator with existing database Sequence example

JPA 

In this example, we are going to show that specified 'allocationSize' of @SequenceGenerator cannot be greater than pre existing database sequence's 'increment' value.

Prerequisite

Basic understanding of @SequenceGenerator.

Assuming that we already have an existing database sequence with current_value = 25 and increment = 2 and our @ Sequence Generator is:

@SequenceGenerator(name = "mySeqGen", sequenceName = "myDbSeq", initialValue = 5, allocationSize = 3)

In this case 'initialValue' defined by @ Sequence Generator will be ignored.

If allocationSize is smaller than database sequence 'increment' value, for example 'allocationSize' = 5 and database 'increment' = 100 then there will be a jump of 95 values after each 5 entities allocations on the application side. That is because after 'allocationSize' is used up, the database is queried again for the next sequence value.

If 'allocationSize' is greater than the sequence 'increment by' value an EntityExistsException will be thrown because at some point the next new application entity cannot be assigned to an existing id value after allocationSize has reached. Let's understand that with example.

Example

In this example we are going to use H2 database and Hibernate as JPA provider.

Download H2 (zipped one), extract it and run <h2-folder>/bin/h2.bat (h2.sh).

Create a database in server mode as shown:

After connecting, create a sequence 'myDbSeq' and a table 'myTable' :

 CREATE SEQUENCE myDbSeq START WITH 25 INCREMENT BY 2

 CREATE TABLE myTable (myID INT PRIMARY KEY)

Create the JPA application

@Entity
@Table(name = "myTable")
public class MyEntity {
  @Id
  @SequenceGenerator(name = "mySeqGen", sequenceName = "myDbSeq",
          initialValue = 5, allocationSize = 3)
  @GeneratedValue(generator = "mySeqGen")
  private int myId;
}

src/main/resources/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">


    <persistence-unit name="test-unit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
         <class>com.logicbig.example.MyEntity</class>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>

        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:tcp://localhost/~/myDb"/>
            <property name="hibernate.connection.user" value="sa" />
        </properties>
    </persistence-unit>

</persistence>

public class ExampleMain1 {

  public static void main(String[] args) throws Exception {
      EntityManagerFactory emf = Persistence.createEntityManagerFactory("test-unit");
      EntityManager em = emf.createEntityManager();

      System.out.println("before persisting new entities");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.SEQUENCES");
      Util.nativeQuery(em, "SELECT * FROM MyTable");
      MyEntity entity1 = new MyEntity();
      MyEntity entity2 = new MyEntity();
      em.getTransaction().begin();
      em.persist(entity1);
      em.persist(entity2);
      em.getTransaction().commit();

      System.out.println("after persisting new entities");
      Util.nativeQuery(em, "SELECT * FROM MyTable");

      em.close();
      emf.close();
  }
}

Output

before persisting new entities
--------
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
[MYDB, PUBLIC, MYDBSEQ, 23, 2, false, , 32, 1, 9223372036854775807, false, 3]
--------
SELECT * FROM MyTable
after persisting new entities
--------
SELECT * FROM MyTable
23
24

Run one more time

before persisting new entities
--------
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
[MYDB, PUBLIC, MYDBSEQ, 25, 2, false, , 32, 1, 9223372036854775807, false, 3]
--------
SELECT * FROM MyTable
23
24
after persisting new entities
--------
SELECT * FROM MyTable
23
24
25
26

Now try to persist more than 3 entities at once. (We are again starting a new JVM instance)

public class ExampleMain2 {

  public static void main(String[] args) throws Exception {
      EntityManagerFactory emf = Persistence.createEntityManagerFactory("test-unit");
      EntityManager em = emf.createEntityManager();

      System.out.println("before persisting new entities");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.SEQUENCES");
      Util.nativeQuery(em, "SELECT * FROM MyTable");

      MyEntity entity1 = new MyEntity();
      MyEntity entity2 = new MyEntity();
      MyEntity entity3 = new MyEntity();
      MyEntity entity4 = new MyEntity();
      em.getTransaction().begin();
      em.persist(entity1);
      em.persist(entity2);
      em.persist(entity3);
      em.persist(entity4);
      em.getTransaction().commit();

      System.out.println("after persisting new entities");
      Util.nativeQuery(em, "SELECT * FROM MyEntity");

      em.close();
      emf.close();
  }
}

Output

 before persisting new entities
--------
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
[MYDB, PUBLIC, MYDBSEQ, 27, 2, false, , 32, 1, 9223372036854775807, false, 3]
--------
SELECT * FROM MyTable
23
24
25
26
Exception in thread "main" javax.persistence.EntityExistsException: A different object with the same identifier value was already associated with the session : [com.logicbig.example.MyEntity#29]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:116)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162)
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:780)
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:758)
	at com.logicbig.example.ExampleMain2.main(ExampleMain2.java:26)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

The exception message 'A different object with the same identifier value was already associated with the session : [com.logicbig.example.MyEntity#29]' indicates that exception occurred after myId=29 was persisted and next one is attempted. Since our allocation size is 3 (which is used up at myId=29), database is queried again for the next id. The expected id should be 30, but database 'increment' size is 2 so 27+2=29 which is already assigned to the previous entity, hence the exception is thrown.

Example Project

Dependencies and Technologies Used:

• h2 1.4.193: H2 Database Engine.
• hibernate-core 5.2.8.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9