由于MS SQL中UNIQUE约束的非标准行为，Spring / Hibernate的解决方法

There is a UNIQUE database constraint on an index which doesn't allow more than one record having identical columns.

索引上有一个UNIQUE数据库约束,它不允许多个记录具有相同的列。

There is a piece of code, managed by Hibernate (v2.1.8), doing two DAO
getHibernateTemplate().save( theObject )
calls which results two records entered into the table mentioned above.

有一段代码由Hibernate(v2.1.8)管理,执行两个DAO getHibernateTemplate()。save(theObject)调用,这会导致两个记录输入到上面提到的表中。

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

如果在没有事务的情况下执行此代码,则会生成INSERT,UPDATE,然后是另一个INSERT和另一个UPDATE SQL语句,并且工作正常。显然,序列是先插入包含DB NULL的记录,然后用适当的数据更新它。

If this code is executed under Spring (v2.0.5) wrapped in a single Spring transaction, it results two INSERTS, followed by immediate exception due to UNIQUE constraint mentioned above.

如果此代码在包含在单个Spring事务中的Spring(v2.0.5)下执行,则会产生两个INSERTS,然后由于上面提到的UNIQUE约束而导致立即异常。

This problem only manifests itself on MS SQL due to its incompatibility with ANSI SQL. It works fine on MySQL and Oracle. Unfortunately, our solution is cross-platform and must support all databases.

此问题仅在MS SQL上表现出来,因为它与ANSI SQL不兼容。它适用于MySQL和Oracle。不幸的是,我们的解决方案是跨平台的,必须支持所有数据库。

Having this stack of technologies, what would be your preferred workaround for given problem?

拥有这一堆技术,对于给定问题,您最喜欢的解决方法是什么?

2 个解决方案

#1

You could try flushing the hibernate session in between the two saves. This may force Hibernate to perform the first update before the second insert.

您可以尝试在两次保存之间刷新休眠会话。这可能会强制Hibernate在第二次插入之前执行第一次更新。

Also, when you say that hibernate is inserting NULL with the insert, do you mean every column is NULL, or just the ID column?

另外,当你说hibernate用insert插入NULL时,你是说每列都是NULL,还是只是ID列?

#2

I have no experience in Hibernate, so I don't know if you are free to change the DB at your will or if Hibernate requires a specific DB structure you cannot change.

我没有Hibernate的经验,所以我不知道你是否可以随意更改数据库,或者Hibernate是否需要特定的数据库结构,你无法改变。

If you can make changes then you can use this workaround in MSSQL tu emulate the ANSI behaviour :

如果您可以进行更改,那么您可以在MSSQL中使用此变通方法来模拟ANSI行为:

drop the unique index/constraint

删除唯一索引/约束

define a calc field like this:

定义一个这样的calc字段:

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

add the unique constraint on this new field you created.

在您创建的新字段上添加唯一约束。

Naturally this applies if MyUniqueField is not the primary key! :)

当然,如果MyUniqueField不是主键,这适用! :)

You can find more details in this article at databasejournal.com

您可以在databasejournal.com上找到本文的更多详细信息

#1