CODEWITHSUNDEEP
javaoracle-databasehibernate
Vlad
Vlad

Reputation: 99

Hibernate and Oracle Sequence

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.

Upvotes: 1

Views: 16008

Answers (4)

bedjaoui djounaydi
bedjaoui djounaydi

Reputation: 21

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>

Upvotes: 0

gvo
gvo

Reputation: 855

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.

Upvotes: 3

Ilya
Ilya

Reputation: 29703

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

Upvotes: 5

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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

Upvotes: 0

Related Questions