Hibernate and sequence generation

The topic of sequence generation was one that took me a little time to comprehend.

Essentially different databases have used different mechanisms in order to generate a number for the primary key….which should itself be unique.

Some databases use a sequence as a mechanism for creating the unique primary key.

If we have some simultaneous transactions then it is possible that the same key could be generated – because there is no transaction isolation when generating the primary key.

Within JPA – there is a section in the Java Persistence WikiBook that outlines the table sequence mechanism – which it states:

Table sequencing is the most portable solution because it just uses a regular database table, so unlike sequence and identity can be used on any database.

In summary, rather than rely on a vendor ( e.g. Oracle) specific mechanism of creating a sequence, we can use a Table Sequence method.

You create a Sequence table, and within it, record the name of the sequence that you want to use…e.g. for a configuration table, you may have an entry called “CONFIG_SEQ”.

The “config_seq” will have an associated value – which is the last ID that was allocated.

 

In the case where EclipseLink is used with Oracle, then the following is sufficient on the table.

@Id
@TableGenerator(name = "CONFIG_TABLE_GEN", pkColumnValue = "CONFIG_SEQ", allocationSize = 100)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "CONFIG_TABLE_GEN")
@Column(unique = true, nullable = false, precision = 22)
private long configid;

This is insufficient if you move to Hibernate – though can be made work if it reverts to the more specific version that the Java Persistence Wiki calls for – where we are specific about the column name for the SequenceName and the SequenceCount columns, and the associated value for the Sequence name, and the allocation size.

@Id
@TableGenerator(name = "CONFIG_TABLE_GEN", table = "SEQUENCE", pkColumnName ="SEQ_NAME", valueColumnName = "SEQ_COUNT", pkColumnValue = "CONFIG_SEQ", allocationSize = 100)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "CONFIG_TABLE_GEN")
@Column(unique = true, nullable = false, precision = 22)
private long configid;

 

 

 

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

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