Hibernate having issues with sequence object - unable to call next val

Question

I am using hibernate entity - and sequence generator with it.

My database is Oracle 12c.

In my sequence generator - it fetches value which is already present in table.

I tried looking out for issue - found one simmilar thread Hibernate sequence nextVal resolved but not used (Oracle)

But still it did not help . The issue am facing is - it works some times and it does not work some times

Below is my code snippet -

@Entity
@Table(name="TABLE_NAME", schema = "SCHEMA")
@SequenceGenerator(name="TABLE_ID_SEQ_GEN", sequenceName="SCHEMA.TABLE_ID_SEQ",allocationSize=1)
public class ImportTransactionDataEntity {

    @Id
    @Column(name="TABLE_ID",unique=true,nullable=false)
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="TABLE_ID_SEQ_GEN" )
    private int IDColumnPk;

ANy help is appreciated , thank you :)

can you try by replacing `sequenceName="SCHEMA.TABLE_ID_SEQ"` with `sequenceName="TABLE_ID_SEQ"` ? — Leviand, Aug 28 '18 at 10:28
yes i tried it already , the issue actually is - this thing does not work sometimes and works sometimes , edited the question accordingly — Ashish Shetkar, Aug 28 '18 at 10:33
have you go through with this ? https://www.objectdb.com/java/jpa/entity/generated — tharanga-dinesh, Aug 28 '18 at 10:49
thanks for the comment - i went through it - it does not point me towards solution actually ... i have used the sequence strategy same as mentioned in the above URL — Ashish Shetkar, Aug 28 '18 at 11:32

DevTest28 · Answer 1 · 2018-08-28T13:20:53.967

0

try this

@Entity
@Table(name="TABLE_NAME")
@SequenceGenerator(name="TABLE_ID_SEQ_GEN",sequenceName="TABLE_ID_SEQ",allocationSize=1)
public class ImportTransactionDataEntity {

@Id
@Column(name="TABLE_ID",unique=true,nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO,generator="TABLE_ID_SEQ_GEN" )
private int IDColumnPk;

it is stored in you database like this the last nummber is the last id used so dont worry about loosing count cause the database does that for you

edited Aug 28 '18 at 13:20

answered Aug 28 '18 at 12:49

DevTest28

146
1
2
10

I could have used this , but then i will lose the track of my - TABLE_ID count , so this won't be a good solution to add auto generation – Ashish Shetkar Aug 28 '18 at 13:10
your table_id count is kept in the database. ar eyou using sql developper? – DevTest28 Aug 28 '18 at 13:12
i am using Oracle 12 C , if I use auto generation policy - how can i track it , can you please explain that part as well – Ashish Shetkar Aug 28 '18 at 13:13
in auto generation, the database will take the last vlue and add 1 to it. i am using it and i can know what id i am currently at from the database. all sequences in the database are in a folder called sequence that you can find under yor database if you are using sqldevelopper – DevTest28 Aug 28 '18 at 13:15
lets say for exemple you create a sequence that starts with 1 and increments each time you insert. the last id value is always stored in the database you won't lose anything – DevTest28 Aug 28 '18 at 13:16
thank for the answer , i thought of it but can not go with it - in my scenario - i have 5 different sequences - in 5 different tables - so by this way - my generated IDs will be of irregular interval , so this will affect the reports later and will be unmanageable – Ashish Shetkar Aug 30 '18 at 07:09
can you expain a little bit further. do you need all the table to have the same equence? – DevTest28 Aug 30 '18 at 10:13
unfortunately yes , need them in sequence – Ashish Shetkar Aug 31 '18 at 07:30
so if your first table has an id of 110 in the second one you need to also use 110 or will you use 111?? – DevTest28 Aug 31 '18 at 08:27
all tables need to have IDs in sequence - my different table can have same IDs but a single table needs IDs in sequence - your strategy for generating AUTO sequence is fine - no issues with that - but it will not be applicable in my case – Ashish Shetkar Aug 31 '18 at 09:50
that is what i did in first place , which was not working - i think you lost the track of question – Ashish Shetkar Sep 05 '18 at 06:44

score 0 · Answer 2 · answered Aug 28 '18 at 12:58

According to Oracle documentation, the @SequenceGenerator should be added to the field, not to the class:

https://docs.oracle.com/cd/E16439_01/doc.1013/e13981/cmp30cfg001.htm#BCGGHADG

so something like:

@Entity
@Table(name="TABLE_NAME", schema = "SCHEMA")

public class ImportTransactionDataEntity {

    @Id
    @Column(name="TABLE_ID",unique=true,nullable=false)
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="TABLE_ID_SEQ_GEN" )
    @SequenceGenerator(name="TABLE_ID_SEQ_GEN", sequenceName="SCHEMA.TABLE_ID_SEQ",allocationSize=1)
    private int IDColumnPk;

This works for me.

thanks for the answer , am trying this .. let me try and observe it for a day or two , then will be able to come to know — Ashish Shetkar, Aug 28 '18 at 13:10

score 0 · Accepted Answer · answered Aug 30 '18 at 07:45

well finally - i had come to a solution that i was already using oracle 12 C - decide to go with Identity column on the table it self .

NOTE - but the identity column is not available before Oracle 12c .

I ended up getting better performance also with some fraction , so this work around was sigh of relief for me

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "TABLE_ID", updatable = false, nullable = false)
private int IDColumnPk;

Hibernate having issues with sequence object - unable to call next val

3 Answers3

Linked