Manually insert Data from database in table which has hibernate_sequence @GeneratedValue(strategy = GenerationType.TABLE)

Question

I have table with ID @GeneratedValue(strategy = GenerationType.TABLE). Need to insert 800 records manually on postgresql production database.We had migration from oracle to postgresql , few User mistakenly access old link and data inserted in oracle . Please explain how to maintain hibernate_sequence in this case. As its production database.

Answer 1

In the first step check your key generation table and the SEQUENCE_NAME.

If you use the default setting you will see something as follows - adapt the further steps for different configuration.

select * from hibernate_sequences;

SEQUENCE_NAME   NEXT_VAL
--------------- ----------
default         nnnnn

Now increase the NEXT_VAL by the requested number of the skipped records. To avoid the problem of concurent insert via Hibernate I use LOCK TABLE - all insert from Hibernate are blocked until you finish the action bellow.

lock table hibernate_sequences in exclusive mode;
select * from hibernate_sequences;
-- remember the value of NEXT_VAL nnnn
update hibernate_sequences
set NEXT_VAL = NEXT_VAL + 800
where SEQUENCE_NAME = 'default';
commit;

Now you have 800 ID's nnnn + 1 .. nnnn + 800 to be used for your skipped rows.

Note that the LOCK will work if you connect with the OWNER of the table hibernate_sequences. You may skip the LOCK at all if the application is down and no insert can be performed.

Use the identical INSERT as you see in Hibernate show_sql and with your data pass the reserved ID.

Example

-- Hibernate: insert into AUTHOR (name, AUTHOR_ID) values (?, ?)
insert into AUTHOR (name, AUTHOR_ID) values ('Psik', nnnn +1);
insert into AUTHOR (name, AUTHOR_ID) values ('Tuzka', nnnn+2);

Warning

As mentioned in comment, you should plan to switch to SEQUENCE mapping, which would make the adding of skipped rows even simpler. See also why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate.

Having said that, the TABLE generator could work fine for some use cases e.g. with low number of concurrent inserts. Using similar argumentation the claim could be: why-you-should-never-use-the-jpa-and-hibernate if performance matter.