Unique constraints and Hibernate

Question

I am working with Hibernate 3.6.4 and MySQL.
I have a table with unique constraints on four columns and 3 other columns. When the UI application create new instances of the corresponding Object it may create it with those four properties with values already in the table. the result, upon save, is, of course JDBC Exception of duplicate entry.

Is there a way to tell Hibernate to not insert new entry but update the rest of the three columns or upon each save I need to manually query the DB to see if exist and update accordingly?

Thanks.

Answer 1

The clean and database independent approach for this problem is to first check if such an instance exists and depending on that do an insert or update in your application logic.

That said, there might be a way to take advantage of the MySQL INSERT ... ON DUPLICATE KEY UPDATE feature documented here. In this case you must specify a custom SQL INSERT statement for your entity like described in this related question. But if this works depends on the way your entity IDs are generated to begin with. Take a look at this blog article concerning this issue.

Generally, you must deal with every aspect of the problem that Hibernate thinks a transient instance is persisted, when in fact a persistent instance is updated. This might be an issue with generated entity IDs, other generated entity values, entity versions, concurrency, expected insert/update row count, 2nd level and query cache, etc.

So, I think while this would be a nice thing to experiment with I would definitely not use this feature in a production application.

Answer 2

You must indeed explicitely get the entity with the four unique values, and then update it if it exists or create a new one if it does not. There is no way around that.

BTW, note that even with such a mechanism, you might end up with exceptions if two transactions get the entity concurrently, find that it doesn't exist, and both try to create a new one.