How force hibernate to sync sequences?

user_8000111 0
Viewed 1115

I try to prepare an integration test with test data. I read insert queries from an external file and execute them as native queries. After the insertions I execute select setval('vlan_id_seq', 2000, true );. Here is the entity ID definition:

@Id
@Column(name = "id", unique = true, nullable = false)
@GeneratedValue(strategy = IDENTITY)
private Integer id;

When I try tor persist a new entry, I got a Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "vlan_pkey" Detail: Key (id)=(1) already exists. exception. The ID of the sequence is 2000. The column definition is done by the serial macro and is id integer NOT NULL DEFAULT nextval('vlan_id_seq'::regclass).

I executed the native queries in a user transaction, so all test entries are stored in the postgresql data base, but it seems that hibernate not sync the sequence. The entityManager.flush(); also didn't force a sequence synchronisation. It seems that hibernate did not use sequences with @GeneratedValue(strategy = IDENTITY). I use a XA-Datasource and wildfly 13.

I tested now an other initialisation method. I defined a SQL data script (I generated the script with Jailer) in the persitence.xml (javax.persistence.sql-load-script-source) and end the script with select pg_catalog.setval('vlan_id_seq', (SELECT max(id) FROM vlan), true );. I set a breakpoint before the first persist command, check the sequence in the postgresql db, the sequence has the max id value 16. Now persisting works and the entry has the id 17. The scripts are executed before the entity manager is started and hibernate read the the updated sequences while starting. But this solution did not answer my question.

Is there a possibility that hibernate reread the sequences to use the nextval value?

jpa postgresql hibernate java
1 Answers

if the strategy is Identity this means hibernate will create a sequence table and fetch the IDs from it, by using native sql you are just inserting your own values without updating that table so you have TWO solutions

  • Insert using hibernate itself which will be fairly easy, in your integration test inject your DAOs and let hibernate do the insertion for you which is recommended so you do not need to rehandle what hibernate already handled
  • Update the sequence table whenever you do the insert by increment the value which I do not recommend.
user_31509480
Related