Main Tutorials

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

References

  1. Oracle / PLSQL: Sequences (Autonumber)
  2. Spring Boot + Spring Data JPA + Oracle example

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
7 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Rakesh
4 years ago

Hi, This example is not working with Oracle 12c.

Guest
5 years ago

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?

Patrick
3 years ago

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).

Homer Simpson
4 years ago

Nice~

Guest
4 years ago

Thanks

dhanushka
6 years ago

thanks

NAZIA TARANNUM
6 years ago

what version of oracle did you use? if 12c then sequence cannot be used i guess?