How to use existing Oracle sequence to generate id in hibernate?

Question

I have legacy Oracle db with a sequence named PRODUCT_ID_SEQ.

Here is the mapping of Product class for which I need generate correct ids:

public class Product {
   @GeneratedValue(strategy = GenerationType.SEQUENCE, 
                       generator = "retailerRaw_seq")
   @SequenceGenerator(name = "retailerRaw_seq", 
                      sequenceName = "PRODUCT_ID_SEQ")
   private Long id;

   ...
}

But looks like ids are generated with an interval of 50, like 1000, 1050, 1100 etc. This corresponds to the default value of allocationSize property = 50. So that means that Hibernate doesn't actually use the sequence which is already defined in the db.

How do I make Hibernate use the sequence?

Duplicate question: See http://stackoverflow.com/questions/1729723/hibernate-does-not-generate-identifier-when-using-oracle-sequence/1729753#1729753 — Arthur Ronald, Jan 28 '10 at 16:03

score 29 · Answer 1 · answered Oct 24 '11 at 23:13

29

The answer to the original question:

@SequenceGenerator(name="EL_SEQ", sequenceName="EL_SEQ",allocationSize=1)

It is allocationSize that sets the value to increment by.

answered Oct 24 '11 at 23:13

Mike Demenok

791
8
15

8

Upvoted because this is a correct JPA solution rather than being Hibernate specific. However an allocationSize=1 means that a number needs to be obtained from the database for every insert rather than caching a lot of Ids at once, so it comes with a very small performance degradation. – shonky linux user Jul 30 '13 at 23:58

score 20 · Answer 2 · answered Jan 28 '10 at 15:28

20

I'm not used to use annotations, this is what I have in my *.hbm.xml:

<id name="id" type="java.lang.Integer">
    <column name="ID_PRODUCT" />
    <generator class="sequence-identity" >
        <param name="sequence">PRODUCT_ID_SEQ</param>
    </generator>
</id>

You can easily map this to annotations. The generator sequence-identity uses auto increment with sequences.

answered Jan 28 '10 at 15:28

rsilva4

1,915
1
23
39

1

This is the right way to do it (with xml) see below for an example with annotations – Tristan Aug 14 '11 at 08:40
2

+1 and remember to add `true` in your `hibernate.cfg.xml` file. – Withheld Feb 13 '14 at 13:37

Tristan · Answer 3 · 2011-08-14T08:43:37.347

9

Here is a working example with annotations, this way, the existing DB sequence will be used (you can also use the "sequence" strategy, but with less performance on insertion) :

@Entity
@Table(name = "USER")
public class User {

    // (...)

    @GenericGenerator(name = "generator", strategy = "sequence-identity", parameters = @Parameter(name = "sequence", value = "USER_SEQ"))
    @Id
    @GeneratedValue(generator = "generator")
    @Column(name = "ID", unique = true, nullable = false, precision = 22, scale = 0)
    public Long getId() {
        return this.id;
    }

edited Aug 14 '11 at 08:43

answered Aug 14 '11 at 08:14

Tristan

8,733
7
48
96

I found when using this technique it worked but my Database was incrementing it's id value by 2 instead of 1. Any ideas why? – user898465 Oct 16 '13 at 00:05
check your sequence definition in Oracle, you may have something like "CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 2" – Tristan Oct 17 '13 at 06:59

score 6 · Answer 4 · answered Dec 27 '15 at 22:57

I had the same issue while upgrading from 3.5.5 to 5.0.6.Final.

I solved it by re-configuring mapping in the HBM file from:

    <generator class="sequence">
        <param name="sequence">PRODUCT_ID_SEQ</param>
    </generator>

to:

    <generator class="org.hibernate.id.enhanced.SequenceStyleGenerator"> 
        <param name="prefer_sequence_per_entity">true</param> 
        <param name="optimizer">none</param>
        <param name="increment_size">1</param>
        <param name="sequence_name">PRODUCT_ID_SEQ</param>
    </generator>

Is it necessary to set increment_size to 1? Isn't it the default value? — Florian F, Oct 26 '17 at 09:12

score 5 · Answer 5 · edited Dec 23 '15 at 14:10

5

Create your sequence name in Oracle, for example, contacts_seq. In Your POJO Class . Define the following annotation for your sequence.

@Id
@GeneratedValue(strategy=GenerationType.AUTO, generator="my_seq_gen")
@SequenceGenerator(name="my_seq_gen", sequenceName="contacts_seq")

edited Dec 23 '15 at 14:10

Aurasphere

3,841
12
44
71

answered May 27 '14 at 07:12

user1256936

141
2
4

score 4 · Answer 6 · answered Sep 10 '13 at 13:43

If you use javax.persistence.SequenceGenerator, hibernate use hilo and will possibly create large gaps in the sequence. There is a post addressing this problem: https://forum.hibernate.org/viewtopic.php?t=973682

there are two ways to fix this problem

In the SequenceGenerator annotation, add allocationSize = 1, initialValue= 1
instead of using javax.persistence.SequenceGenerator, use org.hibernate.annotations, like this:

@javax.persistence.SequenceGenerator(name = "Question_id_sequence", sequenceName = "S_QUESTION")

@org.hibernate.annotations.GenericGenerator(name="Question_id_sequence", strategy = "sequence", parameters = { @Parameter(name="sequence", value="S_QUESTION") } )

I have tested both ways, which works just fine.

shakhawat · Answer 7 · 2016-05-25T09:19:31.200

allocationSize and incrementBy are completely different things.

Hibernate is of course using your sequence created in DB but depending on allocationSize you might find gap in generated value.

For example- Let assume current sequence value is 5, increment by 1 in db, and allocationSize default 50.

Now you want to save a collection of 3 element through hibernate, then Hibernate will assign generated id 250, 251, 252

This is for optimization purpose. Hibernate doesn't have to go back to db and fetch next incremented value.

If you don't want this just setting allocationSize = 1 as already answered will do the purpose

score 1 · Answer 8 · answered Feb 25 '14 at 12:32

1

I use following on PostgreSQL and works just fine.

 @Id
 @GeneratedValue(generator = "my_gen")
 @SequenceGenerator(name = "my_gen", sequenceName = "my_seq_in_db")
 private int userId;

answered Feb 25 '14 at 12:32

Dhaval D

1,087
2
14
25

PostgreSQL and Oracle is different database types – Dumbo Nov 06 '18 at 12:32

Eyal Lupu · Answer 9 · 2011-05-09T11:19:38.393

0

By default Hibernate uses sequence HiLo generator which ,unless you have special needs, it is good (performance wise). You can read more of that in my blog here

Eyal

edited May 09 '11 at 11:19

answered Jan 29 '11 at 08:58

Eyal Lupu

822
9
9

Any idea to change algorithm, saying to pooled instead of hilo using JPA annotations? – Oct 21 '13 at 15:57

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

First : you should create in your database the sequence like:

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

and in your file Product.hbm.xml configuration make :

 <class name="ProductPersistant" table="Product">

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

How to use existing Oracle sequence to generate id in hibernate?

10 Answers10

Linked