Thursday, December 18, 2014

How to configure primary key's generator in JPA entity for oracle

In JPA there are 4 strategies to automatically generate value for a primary key column:

  • Auto
  • Identity
  • Sequence
  • Table

To some extent, there are only 3 strategies, the last 3 ones. Because the first 'Auto' just let the JPA implementation to choose which one of the rest 3 strategies it will use. For example working with Oracle + EclipseLink 2.5.0,  the "Auto" will result in the strategy "Table" being choosen . This behavior depends on the database and the JPA implementation in your project.

0. Before we start

In this tutorial, Oracle is database and EclipseLink is JPA implementation.  The table name in this demo is T_JUST_FOR_TEST, which only have 1 field 'id' as its primary key for simplicity's sake.

In JPA the annotation for value generation of  primary key is @GeneratedValue

Note : The value generation strategy only  take effect when you try to write back to database from your java application. In other words, if the table is read-only in the Java application,  Any specified strategy is meaningless in Entity class.

1. Use Strategy Identity

Oracle don't support Identity.

2. Use Strategy Sequence

2. 1 Define Sequence in Database

Make sure there is sequence that you can use or you can define a new one like this.

-- Create sequence 
create sequence SEQ_JUST_FOR_TEST
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1;

The created sequence is named 'SEQ_JUST_FOR_TEST'. it starts from 1 and increase 1 for every next value.


2.2 Define Entity class


Use Annotations for primary key column. First define a sequence generator, then use the generator.

package com.shengwang.example;

import java.io.Serializable;
import javax.persistence.*;

@Entity
@Table(name="T_JUST_FOR_TEST")
public class TJustForTest implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@SequenceGenerator(name="SEQ_GEN", sequenceName="SEQ_JUST_FOR_TEST", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_GEN")
private long id;

public TJustForTest() {}

public long getId() {
return this.id;
}

public void setId(long id) {
this.id = id;
}
}

In the above Entity definition class, The annotation @SequenceGenerator is used to define a Sequence Generator named "SEQ_GEN", which is then used in the @GeneratedValue annotation.  The attribute sequenceName is the real sequence  name in Oracle. The allocationSize is the increment for every step.  Make sure the value of allocationSize matches the corresponding value in the sequence definition in Oracle.  In the annotation @GeneratedValue, strategy "GenerationType.SEQUENCE" is specified, and generator set to the sequence generator "SEQ_GEN" defined one line above.


Every time when insert a new TJustForTest entity record into the table like below, the primary key id will increase one.

  // em is the injected EntityManager;
TJustForTest t = new TJustForTest();
em.persist(t);

3. Use Strategy Table


3.1 Define table in database


A table need to be defined in database.

create table T_GENERATOR1
(
gen_key VARCHAR2(255) not null,
gen_value NUMBER,
constraint pk_gen_key PRIMARY KEY (gen_key)
)

The table works somehow like a key-value pair or hash table. Every row of the table can  logically be treated as a sequence.  Normally, the data type of key column is string and data type of value column is number. To make it more readable, A string combines schema(MONITOR2012) and table name(T_JUST_FOR_TEST) is used as key in this tutorial.


3.2 Insert one row into the table

insert into T_GENERATOR (Gen_Key,GEN_VALUE) values ('MONITOR2012.T_JUST_FOR_TEST',0)

This one row data has to be insert in to the table T_GENERATOR before we can use it as generator in JPA entity class.


3.3 Define Entity Class


Use Annotations for primary key column. First define a table generator, then use the generator.

package com.shengwang.example;

import java.io.Serializable;
import javax.persistence.*;

@Entity
@Table(name = "T_JUST_FOR_TEST")
public class TJustForTest implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@TableGenerator(name="TABLE_GEN",table="T_GENERATOR", pkColumnName = "GEN_KEY", pkColumnValue = "MONITOR2012.T_JUST_FOR_TEST", valueColumnName = "GEN_VALUE", initialValue = 1, allocationSize = 1 )
@GeneratedValue(strategy = GenerationType.TABLE, generator="TABLE_GEN")
private long id;

public TJustForTest() {
}

public long getId() {
return this.id;
}

public void setId(long id) {
this.id = id;
}
}

The annotation @TableGenerator is used to define a table generator named "TABLE_GEN". This table generator is used as the generator for @GeneratedValue.


Let's go through all the rest attributes in annotation @TableGenerator in details.


table is the table name in database used. pkColumnName is the column name of the table.. pkColumnValue is the value of the key. These 2 attributes are logically combined as a where phase in SQL, " Select ... from T_GENERATOR where GEN_KEY='MONITOR2012.T_JUST_FOR_TEST'", to locate the single record in the database table. valueColumnName is the column name to store real id value. initialValue is the first value of the generator. allocationSize is the step to increase. 


This is how these attributes work in logic: Every time JPA implementation need to generate value, it will first find the table table, then find the row using pkColumnName and pkColumnValue, then read the value of valueColumnName, add allocationSize, use it and update the value back to valueColumnName.


Every time when insert a new TJustForTest entity record into the table like below, the primary key id will increase one.

  // em is the injected EntityManager;
TJustForTest t = new TJustForTest();
em.persist(t);

4. Summary

Strategy sequence and strategy table can be used in JPA entity class if working with Oracle. The strategy sequence is oracle dependent, it does not work if you change to other database like MySQL. The Strategy table is database independent, but seems a little bit more complex than sequence strategy.

4 comments:

  1. Great article ! Thanks !

    ReplyDelete
  2. Here my problem is i want to generate a sequence number for the primary key that is in the embeddable class.
    The embeddable class contains more than one primary key. i want to generate sequence no for a particular field only. can you please suggest me a solution

    ReplyDelete
  3. I have one doubt..im not able to retrieve Id from oracle Database using spring hibernate.....even after i used..
    @Id
    @SequenceGenerator(name="SEQ_GEN", sequenceName="SEQ_JUST_FOR_TEST", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_GEN")

    ReplyDelete

Powered by Blogger.

About The Author

My Photo
Has been a senior software developer, project manager for 10+ years. Dedicate himself to Alcatel-Lucent and China Telecom for delivering software solutions.

Pages

Unordered List