Wednesday, September 16, 2009

Using Oracle Sequences with JPA

Oracle doesn't allow auto increment like MySQL. Auto increment, on big applications can cause all sorts of problems. Most JPA examples are targeted for MySQL.

First, I use Number(19) for id columns.

Second, the generator name is not important so long as it is unique and generator in GeneratedValue annotation matches name in SequenceGenerator annotation.

Sequence Name in SequenceGenerator is the name of your Oracle sequence.

Example entity code:

import javax.persistence.Id;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.SequenceGenerator;
...
@Id
@Basic(optional = false)
@Column(name = "MY_ID")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "MYNOTIFSEQ")
@SequenceGenerator(name="MYNOTIFSEQ", sequenceName = "MY_SEQ")
private Long userNotificationId;
...


Here's a sample sequence:
------------------------------------
--
-- Sequence MY_SEQ
--
------------------------------------
CREATE SEQUENCE MY_SEQ
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999999999999999
NOORDER
CACHE 20
CYCLE
/

2 comments:

Anonymous said...

Obrigado salvou minha vida.
Thanks a lot save my live.

Do you have same study material about this line of development!?

afsar said...

How to make the same code to work on both mysql (auto increment) and oracle (sequence). I stuck in it and can't find a way to handle it for both the database.

Labels

Blog Archive

Contributors