How to get the next value in a sequence with @GeneratedValue in spring-jpa

Question

There is already some data available in table, where id being the primary key for me. So when i trying to invoke my spring jpa code to add values to the table i'm using @GeneratedValue for the primary key.

But this way is generating value which is already present in DB. so my application is throwing an exception.

Is there any way i can get the current value from the table and increment my primary key id wrt the previous value of ID present in the table

@Id @GeneratedValue(strategy = GenerationType.SEQUENCE) @Column(name = "id", updatable = false, nullable = false) private int id;

No. This would be horribly slow, and impossible to do safely and efficiently with multiple conurrent threads. Set the sequence initial value to the correct value before running your application. — JB Nizet
– JB Nizet, Commented Sep 1, 2019 at 14:03
Is there a chance i create a nativequery to get the next value, and increment the sequence wrt that value ? — Srikanth Josyula
– Srikanth Josyula, Commented Sep 1, 2019 at 14:29

Minar Mahmud · Accepted Answer · 2019-09-02 06:01:10Z

2

Let's say your max id in DB currently is 500. Run this:

CREATE SEQUENCE seq_name_in_db START WITH 501;

And change to:

@Id @SequenceGenerator(name = "some_seq", sequenceName = "seq_name_in_db") @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "some_seq") private int id;

UPDATE: (Solution to OP's comment)

While inserting directly, do this:

INSERT INTO some_table (id, ...) VALUES (seq_name_in_db.NEXTVAL, ...);

edited Sep 2, 2019 at 6:01

answered Sep 1, 2019 at 20:29

Minar Mahmud

2,6636 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Srikanth Josyula Over a year ago

Thanks for the help, i have a doubt say there are happening operations on table using sql query like a row has been added say 502 has been added to table. So to my understanding when my application is running it will try creating a row with 502. Now my application will throw an error as primary key with 502 is already existing but to an end user he may not be able to understand what went wrong! .. can you help me how can we solve this kind of issue

yesIamFaded Over a year ago

@SrikanthJosyula Did you solve the issue? I am having a similar one with hibernate.

Rasha Elsayed · Accepted Answer · 2019-09-01 14:14:16Z

0

When You create the sequence, you can let it start with a certain value e.g.

CREATE SEQUENCE XX_SEQ START WITH 100 INCREMENT BY 1 ;

answered Sep 1, 2019 at 14:14

Rasha Elsayed

7401 gold badge10 silver badges27 bronze badges

3 Comments

Srikanth Josyula Over a year ago

Say, some one has updated the table by adding or removing a row using sql query. will my application pick up the next sequence value for example:: currently my generator is at 10, and someone has deleted the row with id 9, so in DB current sequence is at 8. so will my application create a row with id 9 or id 10

JB Nizet Over a year ago

@SrikanthJosyula a sequence generator uses a database sequence. It doesn't care about the previous ID values in the table.

Rasha Elsayed Over a year ago

In SQL Server you can execute EXEC setSequenceAfterMaxExistId 'table_name'. This should set the DB sequence next value to the one after the max existing in the table.

Collectives™ on Stack Overflow

How to get the next value in a sequence with @GeneratedValue in spring-jpa

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related