JPA Part2: Identifier Generation (PK)

There are four primary key generators.

1. GenerationType.AUTO
2. GenerationType.IDENTITY
3. GenerationType.SEQUENCE
4. GenerationType.TABLE

AUTO

In this case provider will use whatever the strategy support by the database. For example, MySQL 5.6 use the SEQUENCE table as follows

CREATE TABLE \`sequence\` (  
  \`SEQ\_NAME\` varchar(50) NOT NULL,  
  \`SEQ\_COUNT\` decimal(38,0) DEFAULT NULL,  
  PRIMARY KEY (\`SEQ\_NAME\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

As such, provider can decide the strategy. The above table is auto generated.

IDENTITY

Some databases support identity column referred to as auto-number where row is inserted in to the table, the identity column will get a unique identifier. Databases like MySQL 5.6 support this but not all databases. However, this is not efficient way to identity generation because identifier is not available until after the commit, therefore provider need to reread the record again after the persist. In the MySQL 5.6, there is no difference between IDENTITY and SEQUENCE as I saw. I kindly request the reader to comment if I am wrong.

SEQUENCE

Most of the databases support id generation via sequences. For example, MySQL 5.6.

CREATE TABLE \`employee\` (  
  \`EMP\_ID\` int(11) NOT NULL **AUTO\_INCREMENT**,  
  \`FIRST\_NAME\` varchar(255) DEFAULT NULL,  
  \`LAST\_NAME\` varchar(255) DEFAULT NULL,  
  \`STATE\` varchar(255) DEFAULT NULL,  
  \`STREET\` varchar(255) DEFAULT NULL,  
  \`SUBURB\` varchar(255) DEFAULT NULL,  
  PRIMARY KEY (\`EMP\_ID\`)  
) ENGINE=InnoDB **AUTO\_INCREMENT=3** DEFAULT CHARSET=utf8;

As shown in the above script, EMP_ID is AUTO_INCREMENT. In the MySQL 5.6 there is no difference SEQUENCE and the IDENTITY. However, if you are using another database, then you have to first create the sequence and use the @GeneratedValue conjunction with @SequenceGenerator.

TABLE

This is the most portable way. In the MySQL 5.6, this is the chosen strategy for the AUTO explained above. The main problem is that sequence table keep only one value which will be based value for the next transaction. Therefore, you will never get the proper id according to the 1,2,3… sequence. But instead of sequence table, there is more eligible solution available to use conjunction with @TableGenerator.

@Entity  
public class Employee extends Person{  
    @Id  
    @TableGenerator(name = "empId\_Gen",   
        table = "ID\_GEN",   
        pkColumnName = "ID\_NAME",   
        valueColumnName = "LAST\_VAL",   
        initialValue = 0,  
        allocationSize=1)  
    @GeneratedValue(generator="empId\_Gen")  
    @Column(name = "EMP\_ID")  
    private int empId;  
  
    private Contact contact;  
    ............  
    ............  
    ............  
    ............

as shown in the above listing, Employee entity define the empId with the id generating table ID_GEN. In this case the last used value is stored with the empId_Gen. if you wish to change this value, use the pkColumnValue attribute.

@Entity  
@Table(name = "DEPT")  
public class Department {  
    @Id  
    @TableGenerator(name = "deptId\_Gen",   
        table = "ID\_GEN",   
        pkColumnName = "ID\_NAME",   
        valueColumnName = "LAST\_VAL",   
        initialValue = 0,  
        allocationSize=1)  
    @GeneratedValue(generator="deptId\_Gen")  
    @Column(name = "DEPT\_ID")  
    private int deptId;  
       ..................  
       ..................  
       ..................

As shwon in the above listing, the last used value is stored in the record where the ID_NAME is “deptId_Gen”. if you wish to change this value, use the pkColumnValue attribute. Here the data in the table “ID_GEN” after inserting two departments and three employees.

Here the data model.

You can download the source from the GitHub.

Written with StackEdit.