LDSTechForumWikiProjects

Hibernate, JPA, and Sequences

This article describes how Hibernate and JPA interact with database sequences in Stack 3.x. It will give you an overview of common use cases but does not describe Hibernate id generation functionality in detail. This article will help to avoid common pitfalls including answers to...

  • Why are my objects persisted with negative values for the id after calling entityManager.persist(obj) when the database sequence is returning positive values?
  • Why am I getting ORA-00001: unique constraint violated persisting new objects to the database?
  • What does this info message I found in my console mean? "Pooled optimizer source reported -x as the initial value; use of 1 or greater highly recommended"

The Basics

A database sequence is often used to populate a surrogate primary key in Oracle. Hibernate/JPA can be configured to use the sequence to populate the corresponding field before persisting a new object to the database.

Executive Summary

JPA sets the allocationSize of a SequenceGenerator annotation to 50 by default. This is likely not the behavior you are looking for, and, if it is, you should be explicit about it anyway. Be sure to always set the allocationSize on your SequenceGenerator definitions. Set it to 1 if your database sequences are created to advance by 1 with each call to nextval() (which is typical, and is the default).

New Hibernate Generators and Optimizers

As of Hibernate 3.2.3, Hibernate introduced 2 new "enhanced" identifier generators targeted at portability and optimization. One of those is the SequenceStyleGenerator. When hibernate is configured to use this new mapping (Stack 3.x currently is), and is backed with an Oracle database, the generator behaves in the manner described above. In addition, with this enhanced generator, if you create your database sequence with an INCREMENT BY of greater than 1, and set the allocationSize to match the INCREMENT BY clause, Hibernate will fill in the holes between the values returned by the sequence and therefore save trips to the database.

Recomendations

The behavior of the new generators is vastly different from that of the older generators, setting the allocatiionSize to a value greater than 1 is discouraged when using the older generators. Doing so can cause primary key violations when all sources of data insertion are not configured to behave in the exact same manner. When using the new generators we recommend setting the allocationSize to 1 unless you need the minor performance improvement.

Examples

These examples assume you are using the LDS Java Stack version 3.x which is configured to use the enhanced generators for AUTO, TABLE and SEQUENCE with the hibernate.id.new_generator_mappings property as per the Hibernate Annotations Reference Guide setup instructions.

Increment By 1 Example

If you don't need the performance improvement, or can't tollerate gaps in the id's of your primary keys, configure your database sequences and hibernate/jpa generators to increment by 1 without caching/pooling optimizations.


In the database...

CREATE SEQUENCE example_sq START WITH 1 INCREMENT BY 1;

On the entity object...

@Id
@SequenceGenerator(name = "ExampleSequence", sequenceName = "SEQ_EXAMPLE_PK", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ExampleSequence")
private Long id;

With this configuration, hibernate will retrieve the next value from the database sequence before persisting new entities. It will use the value returned from the sequence on the new entity without any adjustment.

Increment By 50 Example

If you need the performance improvement, and can tollerate gaps in the id's of your primary keys, configure your database sequences and hibernate/jpa generators to increment by some value greater than 2 wich will enable hibernate optimization.


In the database...

CREATE SEQUENCE example_sq START WITH 50 INCREMENT BY 50;

On the entity object...

@Id
@SequenceGenerator(name = "ExampleSequence", sequenceName = "SEQ_EXAMPLE_PK", allocationSize=50)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="ExampleSequence")
private Long id;

With this configuration, Hibernate will retrieve the next value from the database sequence (250 for example), decrements it by the allocationSize (250 - 50 = 200), increments it by 1, and uses the result (201) as the id of the new object/database record. On the next new object, hibernate will increment the previously used value by 1 and persist the object without calling for the next value from the sequence. This continues up to and including until it reaches the value initially returned from the database (250). It then returns again to the database to retrieve the next value (300), decrements it by the allocationSize... and repeats the pattern.

Consequences of Non-Compliance

If you create the sequence with the default "increment by" of 1, but do not set the incrementBy to 1 on the SequenceGenerator annotation, you will end up with a unique constraint violation trying to persist new objects to the database. The hibernate generator operates under the assumption that it can behave as described above, using a pool of id's. It doesn't verify that the sequence is not incrementing by 50. The first 50 rows will insert properly with id's ranging from -48 to 1. Hibernate will attempt to insert the 51st record with an id of -47 which will generate an ORA-00001: unique constraint violated.

This page was last modified on 23 December 2011, at 15:05. This page has been accessed 20,734 times.

Note: Content found in this wiki may not always reflect official Church information. See Terms of Use.