Sequential IDs using JPA @TableGenerator

Introduction

When using the Java Persistence Architecture (JPA) to perform object-relational mapping, it can become difficult to manage entities with complex keys. Having a unique identifier is a keystone of the mapping approach, and complex keys can make coding awkward and difficult. An alternative is to create entities with sequential unique numeric keys, and use the capabilities of the query facility to identify unique entities within the relational store.

JPA has several techniques to generate unique keys for entity objects. This article focuses on one technique: using a database table to keep track of an incremental ID. The @TableGenerator annotation is used to facilitate this feature.

Database Setup

In order to use @TableGenerator, we will need a database table that is accessible by JPA. Here is a sample script of how to create such a table in SQLServer:

CREATE TABLE dbo.SEQUENCES (
	SEQ_NAME char(10) NOT NULL,
	SEQ_NUMBER int NOT NULL
) ON [PRIMARY]
GO

This will create a table called dbo.SEQUENCES in your current database with two columns:

• The first column is a sequence name and can be used to manage multiple sequences within your object architecture. In most cases, a single sequence can be used to create incremental unique keys for all entities.
• The second column is the sequence number itself. It is vitally important that this value not be externally editable or modified in any way. If this value is changed, you might find yourself in a situation where you inadvertently re-use ID numbers, and this can wreak havoc in your code, especially if the generated ID is the primary key of your entity.

You will need to insert at least one row into this table to make it useful. We will assume a single generator will be used for all key values:

INSERT INTO dbo.SEQUENCES (SEQ_NAME, SEQ_NUMBER)
        VALUES('SEQUENCE', 100)
GO

This will create a sequence name of 'SEQUENCE' with an initial value of 100. The sequence name is used in the annotation to identify which value is to be used if there are multiple sequences being maintained. Lastly, we will create a table to map a simple entity:

CREATE TABLE dbo.EVENTS(
	[ID] int NOT NULL,
	[DESCRIPTION] varchar(200) NOT NULL,
        CONSTRAINT [PK_PRICING_EVENTS] PRIMARY KEY CLUSTERED
        ( [ID] ASC )    /* make ID the primary key */
) ON [PRIMARY]
GO

Creating the Mapping

Now we can create our mapping in our Java class. We will create an entity mapping and use field mapping for it's two properties. The first one will be mapped as the unique ID and the second as the value of the DESCRIPTION column in the table we have created.

@Entity
@Table(name = "EVENTS")
public class SequentialEvent implements Serializable
{
    private static final long serialVersionUID = 3107897896655094094L;

     @Id
     @TableGenerator(name = "EVENT_GEN",
                table = "SEQUENCES",
		pkColumnName = "SEQ_NAME",
                valueColumnName = "SEQ_NUMBER",
		pkColumnValue = "SEQUENCE",
                allocationSize=1)
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "EVENT_GEN")
    @Column(name = "ID")
    protected int _id;
    @Column(name = "DESCRIPTION")
    protected String _description;

    // REMAINDER OF CLASS OMITTED
}

Here is an explanation of the annotations in this class:

• @Entity - marks this class as a JPA persistable entity
• @Table - denotes the name of the table in which this entity is stored
• @Id - declares the field it refers to as the unique identifier for this entity
• @TableGenerator - informs JPA how to generate unique values for this entity's identifier. It has several parameters:
  • name - identifier for the generator binding. This value must match the parameter in the @GeneratedValue annotation as described below.
  • table - must match the name of the table created to store the sequence values.
  • pkColumnName - the primary key column name that contains the name of the sequence we are using.
  • valueColumnName - the name of the column that contains the numeric sequence value
  • pkColumnValue - the value of the primary key column that identifies the sequence
  • allocationSize - the amount by which this sequence should be incremented each time a new entity is created. The default value for this is fifty (50).
• @GeneratedValue - marks the field as having a generated value, either from the database or from some other ID generation strategy. This has two important parameters:
  • strategy - a value from the GenerationType enumeration that declares the the way in which values will be generated. In this example GenerationType.TABLE is appropriate since we are letting the value be managed in the relational store.
  • generator- must match the name of the @TableGenerator tag to provide the specifics on how the value is to be generated.
• @Column - declares the field to be mapped to a database column.

It is important to note that when employing this particular strategy, the ID of the entity that you are creating will be undefined until the object is persisted. This must be a consideration when determining how to architect your application code.

For more information on @TableGenerator and other key generation techniques, please refer to JPA documentation.