EJB3 JPA: 3 Ways Of Generating Primary Key Through @GeneratedValue

Let’s face it, different Databases have different ways of generating primary key values, either in the form of identity or through a sequence generator, if the primary key field is of a numerical data type. While coding Entity classes, it is necessary to define whether the primary key value (if is it of numerical type) could be generated from the sequence generator, self generated and inserted in the database during persistent or just self define. There are also times whereby your application would rather depend on a custom created table in the DB just to store generated sequential numbers as primary keys for the Entity. So, in this article, we’ll explore 3 ways in dealing with generating primary key values with the databases that support it and how to use the @GeneratedValue annotation effectively.

No. 1: GenerationType.IDENTITY

Some databases like MySQL or Microsoft SQL Server do provide facility in numeric ID generation for the primary key field during insertion. To illustrate this, let’s go through a simple table for MySQL shown below:

Sample table script for MySQL:

 SQL | 
 copy code |
?
 
1
CREATE TABLE APP_USERS
2
(
3
    APP_USERS_PK BIGINT NOT NULL AUTO_INCREMENT,
4
    USERNAME VARCHAR(255) NOT NULL,
5
    PASSWORD VARCHAR(255) NOT NULL,
6
    PRIMARY KEY(APP_USERS_PK),
7
    UNIQUE(USERNAME)
8
);

We have a table which stores User login info with the APP_USERS_PK field as the primary key field and it is marked as AUTO_INCREMENT, which tells the database to insert a sequentially generated number when a record is inserted.

To map this in the Entity class, just define the strategy attribute in the @GeneratedValue annotation as GenerationType.IDENTITY as shown in the below:

 Java(TM) 2 Platform Standard Edition 5.0 | 
 copy code |
?
 
01
@Entity
02
@Table( name = "APP_USERS", catalog = "SampleDBName", schema = "" )
03
public class AppUsersEntity implements Serializable
04
{
05
    @Id
06
    @GeneratedValue( strategy = GenerationType.IDENTITY )
07
    @Column( name = "APP_USERS_PK" )
08
    private Long appUsersPk;
09
 
10
    //The rest of the codes...
11
}

This will enable the Entity to leverage on the AUTO_INCREMENT feature in automatically generating a sequential number as primary key when inserted into the database.

Databases which are compatible:

• MySQL
• Microsoft SQL Server
• IBM DB2 ver 7.1 and later

No. 2: GenerationType.SEQUENCE

Databases like Oracle DB is known for having custom defined sequence generator, which generates a running number that could be used in any query not restricting having just a row ID for primary key field. In certain aspect, it has more flexibility and it provides more control for applications. Taking an example of a table creation script in Oracle DB shown below:

Sample table creation script for Oracle DB:

 SQL | 
 copy code |
?
 
01
CREATE TABLE APP_USERS
02
(
03
    APP_USERS_PK NUMBER(10) NOT NULL,
04
    USERNAME VARCHAR2(255) NOT NULL,
05
    PASSWORD VARCHAR2(255) NOT NULL
06
);
07
 
08
ALTER TABLE APP_USERS ADD CONSTRAINT APP_USERS_C1 PRIMARY KEY(APP_USERS_PK);
09
ALTER TABLE APP_USERS ADD CONSTRAINT APP_USERS_C2 UNIQUE(USERNAME);
10
 
11
CREATE SEQUENCE APP_USERS_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Looking at the SQL script, the APP_USERS_SEQ is the sequence generator to generate numerical value and it will be used for the primary key field APP_USERS_PK. In order to call the APP_USERS_SEQ to generate the sequential number for the primary key field, you should use have the strategy attribute in the @GeneratedValue annotation define as GenerationType.SEQUENCE and do mark the @Id field with @SequenceGenerator annotation shown below as well:

 Java(TM) 2 Platform Standard Edition 5.0 | 
 copy code |
?
 
01
@Entity
02
@Table( name = "APP_USERS", catalog = "", schema = "SampleDatabaseSchema" )
03
public class AppUsersEntity implements Serializable
04
{
05
    @Id
06
    @SequenceGenerator( name = "appUsersSeq", sequenceName = "APP_USERS_SEQ", allocationSize = 1, initialValue = 1 )
07
    @GeneratedValue( strategy = GenerationType.SEQUENCE, generator = "appUsersSeq" )
08
    @Column( name = "APP_USERS_PK" )
09
    private Long appUsersPk;
10
 
11
    //The rest of the codes...
12
}

Looking in the @SequenceGenerator at the above sample code, the “name” attribute is a given name by you, it can be anything as long as it is unique through out the whole application. The “sequenceName” attribute should be filled with the name of the sequence generator when you execute the “CREATE SEQUENCE APP_USERS_SEQ …” DB statement, in this case, it is “APP_USERS_SEQ“. Then at the @GeneratedValue annotation, have the strategy attribute as GenerationType.SEQUENCE and the generator attribute value as the name of the @SequenceGenerator (NOT THE SEQUENCE GENERATOR IN Oracle DB), which is “appUsersSeq“.

Databases which are compatible:

• Oracle DB
• PostgreSQL
• IBM DB2 ver 7.2

No. 3: GenerationType.TABLE

If you want to build an Enterprise Web Application that is portable and highly adaptable and deployable to various databases, the best way is to have a separate table which stores the sequence name with column for running numbers and incrementally update it whenever the number is used through the application. This approach is free from depending on the databases’ identity or sequence generator facilities, which gives you much freedom especially if you are writing apps that could be adaptive to a myriad of databases. I’ll just use a simple database table creation script for MySQL to illustrate this:

Database script written for MySQL:

 SQL | 
 copy code |
?
 
01
CREATE TABLE APP_USERS
02
(
03
    APP_USERS_PK BIGINT NOT NULL,
04
    USERNAME VARCHAR(255) NOT NULL,
05
    PASSWORD VARCHAR(255) NOT NULL,
06
    PRIMARY KEY(APP_USERS_PK),
07
    UNIQUE(USERNAME)
08
);
09
 
10
CREATE TABLE APP_SEQ_STORE
11
(
12
	APP_SEQ_NAME VARCHAR(255) NOT NULL,
13
	APP_SEQ_VALUE BIGINT NOT NULL,
14
	PRIMARY KEY(APP_SEQ_NAME)
15
);
16
 
17
INSERT INTO APP_SEQ_STORE VALUES ('APP_USERS.APP_USERS.PK', 0);

Over here, we are consistently using the same table structure as the first example, with an additional table APP_SEQ_STORE to store the sequential numbers for the primary key. The APP_SEQ_STORE is more like a name-value pair table and we’ll have “APP_USERS.APP_USERS.PK” as the name which refers to the sequential number for this single entity.

** DO TAKE SPECIAL NOTE that I have initialized the APP_SEQ_STORE with an insert statement with the initial APP_SEQ_VALUE as 0. The initialization is required when running on GenerationType.TABLE mode!

As for the Java Entity codes, here it is:

 Bash | 
 copy code |
?
 
01
@Entity
02
@Table( name = "APP_USERS", catalog = "SampleDBName", schema = "" )
03
public class AppUsersEntity implements Serializable
04
{
05
    @Id
06
    @Column( name = "APP_USERS_PK" )
07
    @TableGenerator( name = "appSeqStore", table = "APP_SEQ_STORE", pkColumnName = "APP_SEQ_NAME", pkColumnValue = "APP_USERS.APP_USERS_PK", valueColumnName = "APP_SEQ_VALUE", initialValue = 1, allocationSize = 1 )
08
    @GeneratedValue( strategy = GenerationType.TABLE, generator = "appSeqStore" )
09
    private Long appUsersPk;
10
 
11
    //The rest of the codes...
12
}

Firstly, we need to have the @TableGenerator to be properly defined. Just give a unique name for the @TableGenerator through name attribute. Let’s name it as “appSeqStore” for now. The table attribute refers to the table which stores all sequence name and sequential number, which is “APP_SEQ_STORE“. The pkColumnName attribute should have the value referring to the table column of the sequence name, which is “APP_SEQ_NAME“. As for pkColumnValue, it is the unique sequence name that you’ve assigned during the insert statement earlier, which is “APP_USERS.APP_USERS_PK” and lastly, the valueColumnName is the table column which holds the running number, which is “APP_SEQ_VALUE“. Do define the initialValue and allocationSize according to your application needs.

After that, just declare the @GeneratedValue annotation with strategy attribute as “GenerationType.TABLE” and the generator attribute value as what you’ve named the @TableGenerator, which is “appSeqStore“.

There you go, a working table-stored sequence generator for the primary key fields, without having to write a load-some of persisting codes for the APP_SEQ_STORE table.

How To Choose The Best Strategy

If you and your team are developing enterprise application that would only be deployed on fix pre-specified Database, The IDENTITY and SEQUENCE strategy should be the way for coding your Entity classes. Do always check if the primary key generation strategy type is compatible with the underlying Database that the application should be deployed.

However, if the application is a “Product” that could well deploy and data could be stored in most RDBMS, the TABLE strategy would be the wisest choice.

Summary

Primary key value generation for GenerationType.SEQUENCE and GenerationType.IDENTITY is pretty straight forward. The GenerationType.TABLE generation strategy would need a little more configuration. Flexibility does come with the cost of Configurability. But with JPA in EJB3, a lot of things are much easier.

Hope this gives you a better picture in generating values for your @Id/primary key field.

Related Articles

• EJB3 JPA: Dealing with Date, Time and Timestamp
• EJB3.x JPA: How To Store An Image Or A Binary File As BLOB In Database