spring与mybatis整合的事务问题记录

接前文，继续将mybatis整合进入到spring3的框架中，

配置文件spring3-servlet.xml虽然用了transactionManager标记，但是总是尝试不成功事务，无论程序怎样抛出异常，插入语句都不会回滚。

  <!-- ================================事务相关控制=================================================    -->
  <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">    
          <property name="dataSource" ref="dataSource"></property>
    </bean>    
 
  <tx:advice id="userTxAdvice" transaction-manager="transactionManager">
    <tx:attributes>
      <tx:method name="delete*" propagation="REQUIRED" read-only="false"
                            rollback-for="java.lang.Exception" />
      <tx:method name="insert*" propagation="REQUIRED" read-only="false"
                            rollback-for="java.lang.Exception" />
      <tx:method name="update*" propagation="REQUIRED" read-only="false"
                            rollback-for="java.lang.Exception" />
      
      <tx:method name="find*" propagation="SUPPORTS"/>
      <tx:method name="get*" propagation="REQUIRED" read-only="false"
                            rollback-for="java.lang.Exception"/>
      <tx:method name="select*" propagation="SUPPORTS"/>
    </tx:attributes>
  </tx:advice>
 
  <aop:config>    
    <aop:pointcut id="pc" expression="execution(public * com.springdemo.usermgr.service.*.*(..))" /> <!--把事务控制在Service层-->
    <aop:advisor pointcut-ref="pc" advice-ref="userTxAdvice" />
  </aop:config>    
 
 

无奈之后，只好想别的办法。

最终解决方式，调用mysql存储过程，事务成功。存储过程代码如下：

DROP PROCEDURE IF EXISTS `test`;

CREATE PROCEDURE `test`(IN p1  VARCHAR(26), OUT pResult VARCHAR(512))
BEGIN
#声明变量必须在程序前面，置后会报错误
DECLARE flag INT DEFAULT 0;
DECLARE err INT DEFAULT 0; #声明一个整形变量err，默认值是0


   SET pResult := NULL;
   SET pResult :=CONCAT ( 'test',p1);
   SELECT * FROM tb2 WHERE commet like  Concat('%',p1, '%');-- this 返回一个结果集


START TRANSACTION; #声明事务开始
break_label:WHILE flag>10 DO
      insert into  suser(userName,pwd,signUpTime)values(CONCAT('user',flag),'8899',sysdate());
set flag = flag+1;
IF flag=5 THEN
        set err=1;    
         LEAVE  break_label;  #跳出循环
END if;
END WHILE break_label;

IF err=0 THEN
    commit;
ELSE
    rollback;
END IF;

END

TestMyBatisDao.java调用代码

package com.springdemo.usermgr.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.stereotype.Repository;

import com.springdemo.mybatis.maper.SUserMapper;
import com.springdemo.usermgr.vo.SUser;
import com.springdemo.usermgr.vo.Stb2;

@Repository("testDao")
public class TestMyBatisDao extends SqlSessionDaoSupport{
    /**
     * 按主键删除一条记录，
     * @param Stb2
     * @return
     * @throws Exception
     */
    public int testSQL(String id) throws Exception{
        SqlSession session =this.getSqlSession();
        SUser SUser = (SUser) session.selectOne("com.springdemo.mybatis.maper.SUserMapper.selectSUser", 1);
        SUser SUser2 = session.selectOne("com.springdemo.mybatis.maper.SUserMapper.selectSUser", 2);
        System.out.println(SUser);  
        System.out.println(SUser2.getPwd());
        
        Map<String, Object> parms = new HashMap<String, Object>();  
        parms.put("queryStr", "的");  
        
        SUserMapper spMapper = session.getMapper(SUserMapper.class);  
          
        List<Stb2> as=spMapper.getTestProc(parms);  
        String outPara=(String) parms.get("retStr");  
        System.out.println(outPara);
        System.out.println(as.toString());


//        SUser user3 = new SUser();  
//        user3.setUserName("中文名zhou3");  
//      
//        user3.setSignUpTime(new Date());
//        for(int i=0;i<=10;i++){    //插入10条记录
//            user3.setPwd("xxxx"+i);
//            System.out.println("插入前主键为："+user3.getId());  
//            session.update("com.springdemo.mybatis.maper.SUserMapper.insertSUser", user3);//插入操作  
//            System.out.println("插入后主键为："+user3.getId());
//            if(i>5) throw new Exception("测试事务异常类"); //非事务方式将会插入6条记录
//        }
        return 0;
    }
}

Stb2.xml存储过程的定义

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC   
    "-//mybatis.org//DTD Mapper 3.0//EN"  
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  
<mapper namespace="com.springdemo.mybatis.maper.SUserMapper">  

<resultMap type="com.springdemo.usermgr.vo.Stb2" id="itemResult">  
 
</resultMap>

<select id="getTestProc" parameterType="java.util.Map" statementType="CALLABLE"  
  resultMap="itemResult">  
  {call test.test(
  #{queryStr,jdbcType=VARCHAR,mode=IN},  
  #{retStr,jdbcType=VARCHAR,mode=OUT})
    }
</select>  
         
</mapper>