Spring / Hibernate workaround due to custom UNIQUE constraint behavior in MS SQL

Question

There is a UNIQUE database restriction for an index that does not allow more than one record with the same columns.

There is a piece of code managed by Hibernate (v2.1.8) that makes two DAO
 getHibernateTemplate().save( theObject )
calls, the results of which are given in the table mentioned above.

If this code is executed without transactions, it leads to INSERT, UPDATE, then another INSERT and other UPDATE SQL statements and works fine. Apparently, the sequence is to first insert a record containing DB NULL, and then update it with the appropriate data.

If this code runs under Spring (v2.0.5) enclosed in a single Spring transaction, this leads to two INSERTS followed by an immediate exception due to the UNIQUE constraint mentioned above.

This problem only appears in MS SQL because of its incompatibility with ANSI SQL. It works great on MySQL and Oracle. Unfortunately, our solution is cross-platform and must support all databases.

With this technology stack, what would be your preferred workaround?

Answer 1

You can try to reset the sleep mode between two saves. This may cause Hibernate to perform the first update before the second insertion.

, , hibernate NULL , , NULL ?

Answer 2

Hibernate, , Hibernate , .

, MSSQL, ANSI:

/

calc :

alter table MyTable Add MyCalcField as 
case when MyUniqueField is NULL 
      then cast(Myprimarykey as MyUniqueFieldType) 
      else MyUniqueField end

, .

, , MyUniqueField !:)

databasejournal.com