Passionate about data

Data and its implications on software design and development.

ID Generation With ORM’s Table, Sequence or Identity Strategy

When using hibernate or any other ORM mapping framework. There is a need to generate primary key values for the “id” columns. These values can be generated by using IDENTITY, SEQUENCE or TABLE strategies. In this blog post we are using Hibernate, JPA, Java8 and Oracle12c.

TABLE Id generation strategy:

When using this strategy, a table in the database is used to store the current id value and the next value is generated and written back to the table.

1
2
3
4
CREATE TABLE hibernate_sequences(
    sequence_name VARCHAR2(40) NOT NULL,
    next_val NUMER(18) NOT NULL
)

The above table is used by hibernate to generate next_val for sequence_name specified in the entity mapping, if no sequence_name is specified then default is used.

The ID generation involves a transaction for its own use and care has to be taken when multiple processes are generating ID’s, and involves locking the ROW in the table. This strategy is the least efficient and should be avoided.

IDENTITY Id generation strategy:

Identity columns in databases like MySQL, MSSQL, PostgreSQL and Oracle 12c are used to auto generate numeric values for a number column. There can be only one identity column in a table, the column has to be not-null and cannot accept any other default values. Identity columns are generally used as synthetic primary key columns. Shown below is an example of creating a table with identity column id in Oracle12c.

1
2
3
4
5
6
7
8
9
CREATE TABLE person
(
  id NUMBER(18) GENERATED ALWAYS AS IDENTITY
                INCREMENT BY 1 START WITH 1 NOT NULL,
  name VARCHAR2(40) NOT NULL
);

ALTER TABLE person ADD CONSTRAINT pk_person PRIMARY KEY (id)
;

The id column from the person table can now be mapped to the Person entity. The generation type we are using is IDENTITY and hibernate now understands that the database will provide the value for id at insert time.

1
2
3
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

Mapping in Hibernate is simple, without having to specify any sequence names, All inserts into the table automatically use the same method for getting ID’s either through the application or directly into the database by other users.

Since ID’s are assigned at insert time, JDBC batching does not work when using identity columns. In Oracle12c dropping of tables does not drop the sequence associated with the identity column leaving stale sequences in the database. In a Parent Child table, child.parent_id cannot be “not null” as hibernate (the ORM framework) cannot get the ID of the parent before the parent is inserted, hence it does parent.insert, child.insert then child.update(parentId).

SEQUENCE Id generation strategy:

Sequences in Oracle, Postgres and SQLServer 2012 are database objects that generate unique numbers and are generated independent of transactions. Sequences improve the concurrency of number generation. Sequences can be increment by any number, can start at any number, can cycle the numbers after maximum number is reached, have upper limit on the number generated and can cache certain number of values. Shown below is an example of creating sequence in Oracle12c.

1
2
3
4
5
6
7
8
9
10
CREATE SEQUENCE seq_person
  START WITH 10000 INCREMENT BY 1;

CREATE TABLE PERSON
(
  id NUMBER(18) NOT NULL,
  NAME VARCHAR2(40)
);
ALTER TABLE PERSON ADD CONSTRAINT PK_PERSON PRIMARY KEY (ID)
;

Using the sequence above and the table definition, we can map the table with the object and use the SEQUENCE GenerationType and the sequence name seq_person. When a new Person is created, hibernate will get the next_id from the sequence and assign it to id attribute on the object.

1
2
3
4
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "person_generator")
@SequenceGenerator(name="person_generator", sequenceName = "seq_person")
private Long id;

Sequences provide a easy way to create an object to provide unique numbers and are not bound by transaction boundaries. Various configuration options, such as start number, increment interval, cache size, max number, min number, cycle the numbers etc.

ORM frameworks can use batch operations for inserts as they can get the sequence values for ObjectID’s without inserting the data in the tables.

Applications have to remember to use the correct sequence to insert the data in the table, hibernate mapping is somewhat verbose. Other utilities have to remember to use the correct sequence when inserting data directly into the database.

Recommendation:

Based on the above observations and usage, you should use sequences over identity columns