Spring + Hibernate事务 - 原生SQL回滚失败 [英] Spring + Hibernate Transaction -- Native SQL rollback failure

问题描述

最近我遇到了这个问题 - 当我使用本机SQL时，我的事务性方法（标记为@Transactional）无法执行回滚。如果我使用Hibernate API来保存一个实体，它会回滚！

背景

我有一个Employee（）类，我有一个表tbl_employee。我想使用本地SQL将行插入到tbl_employee，并且每个插入最多可以插入500行。例如，如果我有600名员工要插入，我将创建两个SQL插入查询（一个用于前500个，另一个用于下100个）。我的插入方法看起来像这样（为了简单起见，我只考虑插入（500,1000）行）：

  @ Transactional 
 public void insert（List< Employee> list）throws RuntimeException {
 Dao dao = new Dao（）; 
 String sql1 = buildSQL（list.subList（0,500））; //第一个500. buildSQL是一个简单的方法，为列表建立插入查询
 String sql2 = buiildSQL（list.subList（500 ，list.size（）））; //剩余的
 dao.executeSQL（sql1）; // dao执行sql查询到数据库
 dao.executeSQL（sql2）; 
} 
  

这里是弹簧配置。我使用HibernateTransactionManager。

 < bean id =txManagerclass =org.springframework.orm.hibernate3.HibernateTransactionManager> ; 
< property name =sessionFactory> 
< ref local =mySessionFactory/> 
< / property> 
< / bean> 
  

My Dao（）类对数据库执行我的sql，如果在插入期间有异常，
$ b

问题

<我尝试将501名员工插入一张空桌子。前500名拥有独特的ID，最后一名拥有与第一名相同的ID。因此，当我插入最后一个时，会出现重复主键错误。我期望前面的insert（）方法会回滚 - 不应该将行插入到表中。但是，我注意到前500个插入到数据库中！

然后我尝试使用Hibernate API。我的插入方法变成：

  @Transactional 
 public void insert（List< Employee> list）throws RuntimeException {
道道=新道（）; 
 for（Employee employee：list）
 dao.save（employee）; 
} 
  

它实际上做了我期望的！

我的问题

1. 我怀疑spring / hibernate是否支持原生SQL的事务管理。
   
2. 如果我直接保存一个使用Hibernate的实体。我想使用native sql的原因是，我实际上需要根据插入日期时间将员工插入到不同的表中。 （例如，今年的新员工将被插入到tbl_employee_2015）。因此，使用本机SQL映射到不同的表更容易。有没有其他优雅的方式来处理映射到不同的表？


3. 我假设一个用于多行插入的sql非常有效。因此我想通过一个sql插入500行。我不想让Hibernate在一个循环中逐个保存。这个假设是否正确？

非常感谢您的关注！

I recently came across this problem -- my transactional method (marked as @Transactional) failed to do the rollback when I use native SQL. If I instead use Hibernate API to save an entity, it did roll back!

Background

I have a class Employee() and I have a table tbl_employee. I want to use native SQL to insert rows to tbl_employee, and each insertion will insert 500 rows at maximum. For example, if I have 600 employees to insert, I will create two SQL insert queries (one for the first 500 and the other for the next 100). My insert method looks like this (to make it simple, I only consider insertion of (500,1000) rows):

@Transactional
public void insert(List<Employee> list) throws RuntimeException{
  Dao dao = new Dao();
  String sql1 = buildSQL (list.subList(0,500));//the first 500. buildSQL is a simple method which build an insert query for the list
  String sql2 = buiildSQL (list.subList(500,list.size()));//the remaining
  dao.executeSQL(sql1);//dao performs the sql query to the database
  dao.executeSQL(sql2);
}

And here is the spring configuration. I use HibernateTransactionManager.

  <bean id="txManager"  class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory">
         <ref local="mySessionFactory"/>
    </property>
  </bean>

My Dao() class performs my sql to the database and if there is an exception during insertion, I will throw a RuntimeException.

The problem

I tried to insert 501 employees to an empty table. The first 500 have unique ids and the last one has the same id as the first one. Therefore when I insert the last one, there will be a "DUPLICATE　PRIMARY KEY" error. I expect that the previous insert() method will rollback -- No rows should be inserted to the table. However, I noticed that the first 500 got inserted to the database!

I then tried to use Hibernate API. My insert method becomes:

@Transactional
public void insert(List<Employee> list) throws RuntimeException{
  Dao dao = new Dao();
  for (Employee employee: list)
     dao.save(employee);
}

And it actually did what I expect! No rows are inserted to the database once there is an exception.

My questions

1. I wonder if spring/hibernate support transaction management for native SQL.
2. The rollback works if I directly "save" an entity using Hibernate.The reason why I want to use native sql is that I actually need to insert employees to different tables based on, say, the insertion datetime. (e.g the new emloyees this year will be inserted to tbl_employee_2015).Therefore it's easier to map to different tables using native SQL. Is there any other elegant way to handle mapping to different tables?
3. I am assuming that one sql for multiple row insertion is very efficient. Therefore I want to insert 500 rows by one sql. I don't want to do Hibernate save one by one in a loop. Is this assumption correct?

Thank you so much for your attention！

解决方案

I too had faced a similar situation in my project and even i had to resort to the fact @Transactional supports rollback only during entity based transaction and not while using Native Sql query. Finally i chose to handle the transactions manually. Perhaps if you have found a better way do let me know.

这篇关于Spring + Hibernate事务 - 原生SQL回滚失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！