Hibernate and Oracle Sequence

Question

I have a problem with Oracle sequence and Hibernate. I used this code to get Oracle Sequence with hibernate

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "student_id_seq")
@SequenceGenerator(name = "student_id_seq", sequenceName = "Student_seq")
@Column(name = "StudentID")
public Long getStudentId() {
    return this.studentId;
}

public void setStudentId(Long studentId) {
    this.studentId = studentId;
}

but when i insert a new value to the table, the generated value is incorrect. For example: when I had two records in database with id 2 and 3, and when I inserted new one, it's id was not 4 but 25. I have no idea what to do with it.

Use 'strategy = GenerationType.AUTO' instead of 'strategy = GenerationType.SEQUENCE'. — Rajesh, Feb 24 '15 at 10:13
why to do that? (by the way I have allready tried it but no luck) — Ismail Sahin, Jul 27 '16 at 13:17

score 5 · Answer 1 · answered Oct 25 '13 at 06:10

You should set allocationSize to 1

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "student_id_seq")
@SequenceGenerator(name = "student_id_seq", 
                   sequenceName = "Student_seq",
                   allocationSize = 1)

You can read more in documentation SequenceGenerator doc

gvo · Answer 2 · 2015-02-24T10:08:49.770

When I see your question, I wonder : Do you really need to have the id 4 instead of 25, or is it just a technical primary key? Behind the cache issue, if you ask a value from your sequence (id=4), and then rollback your transaction, the next time you'll ask a number you will have a gap (id=5), behind any hibernate or cache-related issue.

As stated in Przemyslaw's second link, Sequence gaps - Oracle : "You should never count on a sequence generating anything even close to a gap free sequence of numbers. They are a high speed, extremely scalable multi-user way to generate surrogate keys for a table."

If, as I understand, there is no need to have contiguous value in your application, the good answer to the "what to do with it" question is : nothing, just live with those gaps.

Yes: there are ways how to guarantee contiguous IDs on Oracle level(with sequences). It *only* limitation is: "It won't scale at all". One transaction has to wait for the other to complete. — ibre5041, Feb 24 '15 at 10:24

score 0 · Answer 3 · edited May 23 '17 at 11:54

0

The reason is that probably your sequence has a CACHE value specified during its creation. This has been asked a few times already, please check the two following links:

Sequence gaps in Hibernate

Sequence gaps - Oracle

edited May 23 '17 at 11:54

Community

1
1

answered Oct 25 '13 at 06:10

Przemyslaw Kruglej

8,003
2
26
41

score 0 · Answer 4 · edited Jan 22 '19 at 09:51

you should create in your database the sequence like:

CREATE SEQUENCE  "Student_seq"  MINVALUE 0 MAXVALUE 1000000000 INCREMENT BY 1 START WITH 1 CACHE 500 NOORDER  NOCYCLE ;

and in your student.hbm.xml configuration make :

<class name="StudentPersistant" table="Student">

<id  name="id"  type="java.lang.Long" column="StudentID" >
      <generator class="sequence"> 
           <param name="sequence">Student_seq</param>   
      </generator>
</id>

Hibernate and Oracle Sequence

4 Answers4

Linked