JPA(Hibernate) and postgres sql batch upsert using nativequery

Question

I would like to perform a batch upsert with JPA and Postgres. I can't use merge as I am checking conflict on a Unique Constraint which is not PK. I found that to upsert in postgres we can now use the ON Conflict functionality. So basically I would like to perform a native query execution in JPA. The query would be like :

INSERT INTO user (user_id, display_name )
VALUES('1', 'sg27')  
    ON CONFLICT (user_id) DO UPDATE 
SET display_name = 'sg27' RETURNING *;

I will do looping over list of objects and do the querying.

So my question is in that case of native query insert , can we use manual flushing like em.flush(). Will it work for batch insert.

If not then can somebody please tell me what are the possible solutions for this problem ?

Thanks for your time.

Shira

Answer 1

Using native query in hibernate JPA is same as your normal hibernate query except its specific to the underline data store. You can do the batch upsert and do the manual flushing as well. based on your batch size. no issues with that.

Brahms