Hi,
I would like to know what are the available options for doing a batch/bulk upsert using Hibernate.
I tried using the “merge” method, but this performs N select queries and so it hurts my performance.
I have seen a post by Vlad Mihalcea where it shows an example using JPQL: Hibernate ON CONFLICT DO clause - Vlad Mihalcea
However I could not make batch works on this approach and neither I could come up with a way of setting multiple values into this, except for one approach where I would have to build a string with N * M parameters, where N is the number of rows to upsert and M the number of columns.
Edit: I forgot to add. I want also to store the rows that were in fact updated (previous record and updated record). I am thinking about doing it with Native SQL queries, but would appreciate if there is some better option that I still didn’t figure out.
Merge will only use upsert under very specific circumstances e.g. there are no entity listeners, no interceptors etc. You might be able to use StatelessSession#upsert, but that won’t give you the previous state.
Using the on conflict clause is definitely an option, but since you want to also retrieve previous values, I would suggest you to rather use native SQL for now and leverage the returning clause of your database (I assume PostgreSQL?).
Sometime in the future, Hibernate ORM will also add support for the returning clause, so once that happened, you can upgrade and change your code to use the portable variant, but for now using native SQL is as good as it gets.
