7

I am looking for a way to save or update records, according to the table's unique key which is composed of several columns).

I want to achieve the same functionality used by INSERT ... ON DUPLICATE KEY UPDATE - meaning to blindly save a record, and have the DB/Hibernate insert a new one, or update the existing one if the unique key already exists.

I know I can use @SQLInsert( sql="INSERT INTO .. ON DUPLICATE KEY UPDATE"), but I was hoping not to write my own SQLs and let Hibernate do the job. (I am assuming it will do a better job - otherwise why use Hibernate?)

Galz
  • 6,713
  • 4
  • 33
  • 39

3 Answers3

3

Hibernate may throw a ConstraintViolationException when you attempt to insert a row that breaks a constraint (including a unique constraint). If you don't get that exception, you may get some other general Hibernate exception - it depends on the version of Hibernate and the ability of Hibernate to map the MySQL exception to a Hibernate exception in the version and type of database you are using (I haven't tested it on everything).

You will only get the exception after calling flush(), so you should make sure this is also in your try-catch block.

I would be careful of implementing solutions where you check that the row exists first. If multiple sessions are updating the table concurrently you could get a race condition. Two processes read the row at nearly-the-same time to see if it exists; they both detect that it is not there, and then they both try to create a new row. One will fail depending on who wins the race.

A better solution is to attempt the insert first and if it fails, assume it was there already. However, once you have an exception you will have to roll back, so that will limit how you can use this approach.

rghome
  • 8,529
  • 8
  • 43
  • 62
0

This doesn't really sound like a clean approach to me. It would be better to first see if an entity with given key(s) exists. If so, update it and save it, if not create a new one.

EDIT

Or maybe consider if merge() is what you're looking for:

  • if there is a persistent instance with the same identifier currently associated with the session, copy the state of the given object onto the persistent instance
  • if there is no persistent instance currently associated with the session, try to load it from the database, or create a new persistent instance
  • the persistent instance is returned
  • the given instance does not become associated with the session, it remains detached

< http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html

Stijn Geukens
  • 15,454
  • 8
  • 66
  • 101
  • I'm not sure since the DB itself would also need to check on the existence of the row first before doing update/insert when using 'ON DUPLICATE KEY'. – Stijn Geukens Mar 10 '11 at 13:29
  • also, I'm not sure if ON DUPLICATE KEY is supported by all DB servers (like MS-SQL). – Stijn Geukens Mar 10 '11 at 13:32
  • I think the DB will be able to use the index much more efficiently - for me to do so if I have an insert of 100 records, I can either loop over them and run 100 different SELECT queries and 100 different INSERT/UPDATE queries, or use `IN ()` in some way? Sounds to me like it's safer on the DB (I am using `ON DUP KEY` for a while in very big DBs, it's very efficient). – Galz Mar 10 '11 at 13:40
  • @galz can you please link what feature ```ON DUP KEY``` refers to? – Levente Pánczél Nov 25 '21 at 23:52
  • @LeventePánczél - https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html – Galz Nov 30 '21 at 13:54
-3

You could use saveOrUpdate() from Session class.

Raman
  • 1,507
  • 3
  • 15
  • 25
  • 8
    `saveOrUpdate()` does *not* solve this problem, specially in the use case described by Galz. `saveOrUpdate` will try to update if the identifier is set (and it's not `assigned`) and will try to `insert` if it's a new object instance (no matter if a record with the same identifier exists in the database). – jpkroehling Mar 10 '11 at 12:39