JPA Insert + Oracle Sequences example
A quick JPA + Oracle sequences example, for self reference.
1. Oracle Database
Issue the following SQL script to create a table and a sequence.
CREATE TABLE CUSTOMER(
ID NUMBER(10) NOT NULL,
NAME VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(100) NOT NULL,
CREATED_DATE DATE NOT NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY (ID)
);
CREATE SEQUENCE customer_seq
MINVALUE 1
MAXVALUE 9999999999
START WITH 4
INCREMENT BY 1;
2. JPA
In Java, add JPA annotations like this :
Customer.java
package com.mkyong.model;
import javax.persistence.*;
import java.util.Date;
@Entity
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CUST_SEQ")
@SequenceGenerator(sequenceName = "customer_seq", allocationSize = 1, name = "CUST_SEQ")
Long id;
String name;
String email;
@Column(name = "CREATED_DATE")
Date date;
//...
}
Download Source Code
Download – spring-data-jpa-insert-oracle-example.zip (6 KB)
Hi, This example is not working with Oracle 12c.
What if the sequence isn’t at the ID field. I have a table with a composite key and some another field that stores a sequence value. How would be possible to do it?
This works if you aren’t concerned about someone using something like SQL Developer to submit their own inserts. The issue is I have a trigger that is messing up my logic since I’m using a Many-2-Many relationship where 2 tables are associated by a relationship table. So for example, I have a Department object I want to associate with a new Employee so I create the new Employee which generates Employee Id 10401, gets the Department Id 132 and then I add the Employee record to the Department entity and save it. The thing is, it didn’t work. Under the hood Hibernate says it all got saved fine but looking at the database there’s an issue – the new Employee record actually shows as 10402 but the relationship table shows Department 132 with an Employee Id of 10401. If I pull that record, I’ll get a not found because there is no 10401. What happens is Hibernate generates a new sequence and once the record is committed to the DB, the trigger generates a new sequence before the insert is committed. I need either to remove the autogenerator from the code and let the DB handle it or modify the trigger or get rid of it (which presents its own set of issues if you are used to manually submitting inserts via a tool like SQL Developer).
Nice~
Thanks
thanks
what version of oracle did you use? if 12c then sequence cannot be used i guess?