Close

JPA - @TableGenerator Examples

[Last Updated: Mar 22, 2017]

Similar to @SequenceGenerator, the reference of @TableGenerator can be assigned to 'generator' element of @GeneratedValue. Instead of a database sequence, @TableGenerator uses a dedicated table to keep track of sequence values. This table needs to persist two column values: generator's reference and the 'current value'.

@TableGenerator has following elements:
'name', 'allocationSize', 'catalog', 'indexes', 'initialValue', 'pkColumnName', 'pkColumnValue', 'schema', 'table', 'uniqueConstraints' and 'valueColumnName'

All above elements are optional except for 'name' which is the generator name used as a reference in @GeneratedValue's 'generator' element and in the target sequence table. The elements 'initialValue' and 'allocationSize' has the same meanings as of @GeneratedSequence. The rest of the optional elements are used to specify the attributes of the mapped database table (generated or existing).

Let's understand the usage of @TableGenerator with examples.

Examples

In following examples we are going to use EclipseLink as JPA provider and H2 embedded database. The embedded database is different than in-memory database in that: the embedded database uses a local file to persist database. That means we can access our previously persisted data if run our application again.

To use embedded database in our examples, we need to use following url format in our persistence.xml:
javax. persistence. jdbc. url = "jdbc:h2:~/ourFileName".
Check out more info about the url format in H2 faqs

Using defaults of @TableGenerator

This example just specifies the required 'name' element of @TableGenerator

@Entity
public class MyEntity1 {
  @Id
  @GeneratedValue(generator = "myTableGen")
  @TableGenerator(name = "myTableGen")
  private int myId;
}
@Entity
public class MyEntity2 {
  @Id
  @GeneratedValue(generator = "myTableGen2")
  @TableGenerator(name = "myTableGen2")
  private int myId;
}
public class TableGeneratorExample1 {

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

      System.out.println("-----------------------\nbefore persisting entities");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.SEQUENCES");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ");
      Util.nativeQuery(em, "SELECT * FROM SEQUENCE");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SEQUENCE'");

      MyEntity1 entity1 = new MyEntity1();
      MyEntity1 entity2 = new MyEntity1();
      MyEntity2 entity3 = new MyEntity2();
      MyEntity2 entity4 = new MyEntity2();
      MyEntity2 entity5 = new MyEntity2();
      em.getTransaction().begin();
      em.persist(entity1);
      em.persist(entity2);
      em.persist(entity3);
      em.persist(entity4);
      em.persist(entity5);
      em.getTransaction().commit();

      System.out.println("-----------------------\nafter persisting entities");
      Util.nativeQuery(em, "SELECT * FROM MyEntity1");
      Util.nativeQuery(em, "SELECT * FROM MyEntity2");
      Util.nativeQuery(em, "SELECT * FROM SEQUENCE");

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

Output

-----------------------
before persisting entities
--------
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
--------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' 
[FILEDB1, PUBLIC, SEQUENCE, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.SEQUENCE(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 30, 9, null, org.h2.mvstore.db.MVTable, 2]
[FILEDB1, PUBLIC, MYENTITY1, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY1(
    MYID INTEGER NOT NULL
), , 12, 3, null, org.h2.mvstore.db.MVTable, 0]
[FILEDB1, PUBLIC, MYENTITY2, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY2(
    MYID INTEGER NOT NULL
), , 19, 6, null, org.h2.mvstore.db.MVTable, 0]
--------
SELECT * FROM SEQUENCE
[myTableGen2, 0]
[myTableGen, 0]
--------
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SEQUENCE'
[FILEDB1, PUBLIC, SEQUENCE, SEQ_NAME, 1, null, NO, 12, 50, 50, 50, 10, 0, Unicode, OFF, VARCHAR, 0, false, 50, , null, , null]
[FILEDB1, PUBLIC, SEQUENCE, SEQ_COUNT, 2, null, YES, 3, 38, 38, 38, 10, 0, Unicode, OFF, DECIMAL, 1, false, 50, , null, , null]
-----------------------
after persisting entities
--------
SELECT * FROM MyEntity1
1
2
--------
SELECT * FROM MyEntity2
1
2
3
--------
SELECT * FROM SEQUENCE
[myTableGen, 50]
[myTableGen2, 50]

In above output, following things are important to note

  1. The table 'SEQUENCE' is created to maintain primary key generation. EclipseLink, by default, uses 'SEQUENCE' name for the table. Hibernate uses name 'HIBERNATE_SEQUENCES' by default. We can change the table name by using 'table' element of the annotation.
  2. The table 'SEQUENCE' has two columns with names: 'SEQ_NAME' and 'SEQ_COUNT'. We can change these columns names by using @TableGenerator elements: 'pkColumnName' and 'valueColumnName' respectively.
  3. The SEQUENCE table is populated with two rows, as we used two different @TableGenerator for our two entities
  4. Each row of the SEQUENCE table is populated with the @TableGenerator 'name' (in SEQ_NAME column) and with 'initialValue' (in SEQ_COUNT column). Instead of using 'name' we can use element 'pkColumnValue' to use other value for the first column.
  5. After retrieving the value of SEQ_COUNT column, new value is incremented by the 'allocationSize' value which is 50 by default.
  6. The other two tables were created for our two entities MyEntity1 and MyEntity2.

Since we are using embedded database, we can continue with the previously persisted values if run again. Let's run the example in a new JVM instance once more:

-----------------------
before persisting entities
--------
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
--------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'
[FILEDB1, PUBLIC, SEQUENCE, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.SEQUENCE(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 0, 9, null, org.h2.mvstore.db.MVTable, 2]
[FILEDB1, PUBLIC, MYENTITY1, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY1(
    MYID INTEGER NOT NULL
), , 0, 3, null, org.h2.mvstore.db.MVTable, 2]
[FILEDB1, PUBLIC, MYENTITY2, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY2(
    MYID INTEGER NOT NULL
), , 0, 6, null, org.h2.mvstore.db.MVTable, 3]
--------
SELECT * FROM SEQUENCE
[myTableGen, 50]
[myTableGen2, 50]
-----------------------
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SEQUENCE'
[FILEDB1, PUBLIC, SEQUENCE, SEQ_NAME, 1, null, NO, 12, 50, 50, 50, 10, 0, Unicode, OFF, VARCHAR, 0, false, 50, , null, , null]
[FILEDB1, PUBLIC, SEQUENCE, SEQ_COUNT, 2, null, YES, 3, 38, 38, 38, 10, 0, Unicode, OFF, DECIMAL, 1, false, 50, , null, , null]
-----------------------
after persisting entities
--------
SELECT * FROM MyEntity1
1
2
51
52
--------
SELECT * FROM MyEntity2
1
2
3
51
52
53
--------
SELECT * FROM SEQUENCE
[myTableGen, 100]
[myTableGen2, 100]

There's a jump of values from 2 to 51 (MyEntity1) and 3 to 51 (MyEntity2) because the sequence tables are queried again for the next values at the beginning of the second run. Check out our last tutorial if confused here.

Specifying 'initialValue' and 'allocationSize' of @TableGenerator

We are going to use two entities with different @TableGenerator definitions.

@Entity
public class MyEntity3 {
  @Id
  @TableGenerator(name = "gen3", table= "myTableGen3", initialValue = 4, allocationSize = 500)
  @GeneratedValue(generator = "gen3")
  private int myId;
}
@Entity
public class MyEntity4 {
  @Id
  @TableGenerator(name = "gen4", table= "myTableGen4", initialValue = 6, allocationSize = 600)
  @GeneratedValue(generator = "gen4")
  private int myId;
}
public class TableGeneratorExample2 {

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

      System.out.println("-----------------------\nbefore persisting entities");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ");
      Util.nativeQuery(em, "SELECT * FROM myTableGen3");
      Util.nativeQuery(em, "SELECT * FROM myTableGen4");

      MyEntity3 entity1 = new MyEntity3();
      MyEntity3 entity2 = new MyEntity3();
      MyEntity4 entity3 = new MyEntity4();
      MyEntity4 entity4 = new MyEntity4();
      MyEntity4 entity5 = new MyEntity4();
      em.getTransaction().begin();
      em.persist(entity1);
      em.persist(entity2);
      em.persist(entity3);
      em.persist(entity4);
      em.persist(entity5);
      em.getTransaction().commit();
      System.out.println("-----------------------\nafter persisting entities");
      Util.nativeQuery(em, "SELECT * FROM MyEntity3");
      Util.nativeQuery(em, "SELECT * FROM MyEntity4");
      Util.nativeQuery(em, "SELECT * FROM myTableGen3");
      Util.nativeQuery(em, "SELECT * FROM myTableGen4");

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

Output

-----------------------
before persisting entities
--------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' 
[FILEDB2, PUBLIC, MYTABLEGEN3, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYTABLEGEN3(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 37, 12, null, org.h2.mvstore.db.MVTable, 1]
[FILEDB2, PUBLIC, MYTABLEGEN4, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYTABLEGEN4(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 28, 9, null, org.h2.mvstore.db.MVTable, 1]
[FILEDB2, PUBLIC, MYENTITY3, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY3(
    MYID INTEGER NOT NULL
), , 12, 3, null, org.h2.mvstore.db.MVTable, 0]
[FILEDB2, PUBLIC, MYENTITY4, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY4(
    MYID INTEGER NOT NULL
), , 19, 6, null, org.h2.mvstore.db.MVTable, 0]
--------
SELECT * FROM myTableGen3
[gen3, 3]
--------
SELECT * FROM myTableGen4
[gen4, 5]
-----------------------
after persisting entities
--------
SELECT * FROM MyEntity3
4
5
--------
SELECT * FROM MyEntity4
6
7
8
--------
SELECT * FROM myTableGen3
[gen3, 503]
--------
SELECT * FROM myTableGen4
[gen4, 605]

Since we are using two different @TableGenerator having different 'table' values, the two sequence tables 'myTableGen3' and 'myTableGen4' are created.

Running one more time:

-----------------------
before persisting entities
--------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'
[FILEDB2, PUBLIC, MYTABLEGEN3, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYTABLEGEN3(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 0, 9, null, org.h2.mvstore.db.MVTable, 1]
[FILEDB2, PUBLIC, MYTABLEGEN4, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYTABLEGEN4(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 0, 12, null, org.h2.mvstore.db.MVTable, 1]
[FILEDB2, PUBLIC, MYENTITY3, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY3(
    MYID INTEGER NOT NULL
), , 0, 3, null, org.h2.mvstore.db.MVTable, 2]
[FILEDB2, PUBLIC, MYENTITY4, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY4(
    MYID INTEGER NOT NULL
), , 0, 6, null, org.h2.mvstore.db.MVTable, 3]
--------
SELECT * FROM myTableGen3
[gen3, 503]
--------
SELECT * FROM myTableGen4
[gen4, 605]
-----------------------
after persisting entities
--------
SELECT * FROM MyEntity3
4
5
504
505
--------
SELECT * FROM MyEntity4
6
7
8
606
607
608
--------
SELECT * FROM myTableGen3
[gen3, 1003]
--------
SELECT * FROM myTableGen4
[gen4, 1205]

Sharing one @SequenceTable definition between two entities

@Entity
public class MyEntity5 {
  @Id
  @TableGenerator(name = "genShared", table = "myTableGenShared", initialValue = 10, allocationSize = 200)
  @GeneratedValue(generator = "genShared")
  private int myId;
}
@Entity
public class MyEntity6 {
  @Id
  @GeneratedValue(generator = "genShared")
  private int myId;
}
public class TableGeneratorExample3 {

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

      System.out.println("-----------------------\nbefore persisting entities");
      Util.nativeQuery(em, "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ");
      Util.nativeQuery(em, "SELECT * FROM myTableGenShared");

      MyEntity5 entity1 = new MyEntity5();
      MyEntity5 entity2 = new MyEntity5();
      MyEntity6 entity3 = new MyEntity6();
      MyEntity6 entity4 = new MyEntity6();
      MyEntity6 entity5 = new MyEntity6();
      em.getTransaction().begin();
      em.persist(entity1);
      em.persist(entity2);
      em.persist(entity3);
      em.persist(entity4);
      em.persist(entity5);
      em.getTransaction().commit();

      System.out.println("-----------------------\nafter persisting entities");
      Util.nativeQuery(em, "SELECT * FROM MyEntity5");
      Util.nativeQuery(em, "SELECT * FROM MyEntity6");
      Util.nativeQuery(em, "SELECT * FROM myTableGenShared");

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

Output

-----------------------
before persisting entities
--------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' 
[FILEDB3, PUBLIC, MYTABLEGENSHARED, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYTABLEGENSHARED(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 28, 9, null, org.h2.mvstore.db.MVTable, 1]
[FILEDB3, PUBLIC, MYENTITY5, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY5(
    MYID INTEGER NOT NULL
), , 12, 3, null, org.h2.mvstore.db.MVTable, 0]
[FILEDB3, PUBLIC, MYENTITY6, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY6(
    MYID INTEGER NOT NULL
), , 19, 6, null, org.h2.mvstore.db.MVTable, 0]
--------
SELECT * FROM myTableGenShared
[genShared, 9]
-----------------------
after persisting entities
--------
SELECT * FROM MyEntity5
10
11
--------
SELECT * FROM MyEntity6
12
13
14
--------
SELECT * FROM myTableGenShared
[genShared, 209]

Only one sequence table 'myTableGenShared' is created this time.

Running one more time:

-----------------------
before persisting entities
--------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC'
[FILEDB3, PUBLIC, MYTABLEGENSHARED, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYTABLEGENSHARED(
    SEQ_NAME VARCHAR(50) NOT NULL,
    SEQ_COUNT NUMERIC(38)
), , 0, 9, null, org.h2.mvstore.db.MVTable, 1]
[FILEDB3, PUBLIC, MYENTITY5, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY5(
    MYID INTEGER NOT NULL
), , 0, 3, null, org.h2.mvstore.db.MVTable, 2]
[FILEDB3, PUBLIC, MYENTITY6, TABLE, CACHED, CREATE CACHED TABLE PUBLIC.MYENTITY6(
    MYID INTEGER NOT NULL
), , 0, 6, null, org.h2.mvstore.db.MVTable, 3]
--------
SELECT * FROM myTableGenShared
[genShared, 209]
-----------------------
after persisting entities
--------
SELECT * FROM MyEntity5
10
11
210
211
--------
SELECT * FROM MyEntity6
12
13
14
212
213
214
--------
SELECT * FROM myTableGenShared
[genShared, 409]

Example Project

Dependencies and Technologies Used:

  • h2 1.4.193: H2 Database Engine.
  • eclipselink 2.6.4: EclipseLink build based upon Git transaction 44060b6.
    Related JPA version: org.eclipse.persistence:javax.persistence version 2.1.1
  • JDK 1.8
  • Maven 3.3.9
Table Genenerator Annotation Example Select All Download
  • jpa-table-gen-annotation
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • MyEntity3.java
          • resources
            • META-INF

    See Also