Primary key generation in JPA

Generation of primary key values is a very important functionality of relational database management systems. The main idea is to let RDBMS automatically calculate and assign primary key value to the row being inserted into the database table. This not only simplifies the source code of the application using database but also makes the application more robust. There are two widely known methods to perform the generation of primary key values on the database level: auto-increment/identity columns or sequences.

Due to these differences JPA provide three different ways to automatically generate primary key values. Let’s explore them below.

Using auto-increment/identity columns

MySQL and Microsoft SQL Server provide a functionality to automatically generate a unique number for the primary key when a new row is added into a table. For example, in MySQL it is done by marking primary key column with AUTO_INCREMENT keyword:

CREATE TABLE JPAGEN_ADDRESS
(
  ID INT PRIMARY KEY AUTO_INCREMENT,
  CITY VARCHAR(255) NOT NULL,
  STREET VARCHAR(255) NOT NULL
);

and in Microsoft SQL Server with IDENTITY keyword:

CREATE TABLE JPAGEN_ADDRESS
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CITY VARCHAR(255) NOT NULL,
    STREET VARCHAR(255) NOT NULL
);

JPA must be informed to use auto-increment/identity for primary key by specifying IDENTITY generation strategy on the ID column:

@Entity
@Table(name = "JPAGEN_ADDRESS")
public class Address implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    // other fields and methods are omitted 
}

Now, when a new Address entity is persisted, the value of the primary key column will be automatically generated by the database.

Using sequences

Oracle Database and PostgreSQL use explicit sequence type to generate unique primary key numbers. For example, in Oracle Database the table and its sequence can be defined like this:

CREATE TABLE JPAGEN_ADDRESS
(
    ID NUMBER PRIMARY KEY,
    CITY VARCHAR(255) NOT NULL,
    STREET VARCHAR(255) NOT NULL
);
CREATE SEQUENCE JPAGEN_ADDRESS_SEQ START WITH 100;

When using sequence for given entity, the SEQUENCE strategy must be defined on its ID column and additionally the name of the sequence must be specified using @SequenceGenerator annotation:

@Entity
@Table(name = "JPAGEN_ADDRESS")
public class Address implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
            generator = "addressGen")
    @SequenceGenerator(name = "addressGen",
            sequenceName = "JPAGEN_ADDRESS_SEQ")
    private long id;

    // other fields and methods are omitted 
}

When a new Address entity is persisted, JPA implementation will obtain the next value of the sequence and use it to insert a new row into the database table.

Using table

The third method to generate primary keys is to have a separate table which stores in a single row the sequence name along with the next number to use for the primary key. For performance reasons the next value is not increased by one, whenever JPA implementation needs a new value for the primary key but by a much higher number (e.g. 50). Once the range of values becomes reserved, JPA implementation can assign primary keys without accessing the database. When every value in the range becomes used, JPA implementation reserves a new range and the cycle continues.

Additionally, in such table we can have multiple rows with each row serving different entity. The only requirement is to use unique sequence names for each entity.

The table strategy is the most complicated one but it is the only strategy that is really portable across different databases. If you are developing an application which can use multiple RDBMS or there may be a need to port to a new RDBMS in the future, using table strategy is the most viable option.

The table to store the Person entity and the table to generate sequences can be created like this:

CREATE TABLE JPAGEN_PERSON
(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    ADDRESS_ID NUMBER
);
CREATE TABLE JPAGEN_GENERATORS
(
    NAME VARCHAR(255) PRIMARY KEY,
    VALUE NUMBER
);

The table with sequences must have two columns. The first one should contain the name of the sequence and the second one should be of numeric type. JPA implementation will automatically update the second column when reserving a new range of values.

Additionally, JPA must be instructed to use TABLE generation strategy:

@Entity
@Table(name = "JPAGEN_PERSON")
public class Person implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE,
            generator = "personGen")
    @TableGenerator(name = "personGen",
            table = "JPAGEN_GENERATORS",
            pkColumnName = "NAME",
            pkColumnValue = "JPAGEN_PERSON_GEN",
            valueColumnName = "VALUE")
    private long id;

    // other fields and methods are omitted 
}

We also have to add @TableGenerator annotation to inform JPA about the name of the generator table (table element), the names of its both columns (pkColumnName and valueColumnName elements) and also the name of the sequence (pkColumnValue element).

Conclusion

The choice between IDENTITY, SEQUENCE and TABLE strategies should be pretty obvious. If you application is going to use only one RDBMS, the first or the second option should be fine (depending on your RDBMS vendor). In other cases, TABLE strategy is preferred, if not the only possible.

The source code for the article can be found at GitHub. It has been tested with Oracle Database 11gR2 so it supports only SEQUENCE and TABLE strategies.

Advertisements

About Robert Piasecki

Husband and father, Java software developer, Linux and open-source fan.
This entry was posted in Database, Java, Java EE, JPA and tagged , , , . Bookmark the permalink.

3 Responses to Primary key generation in JPA

  1. www.ask.com says:

    It’s a pity you don’t have a donate button! I’d definitely donate to
    this excellent blog! I suppose for now i’ll settle for bookmarking and adding
    your RSS feed to my Google account. I look forward to fresh
    updates and will share this website with my Facebook group.
    Chat soon!

  2. ventjejeremias says:

    thank you so much for this article. I literally searching up and down in google, before find your blog

  3. prasad says:

    Its really such a nice and useful information regarding the primary key generation concepts in JPA.Thanks for sharing…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s