在mysql中进行事务操作
1、使用C3p0在mysql进行事务操作
1 /** 2 * 事务测试 3 */ 4 public void TestTran() { 5 Connection connection = null; 6 try { 7 QueryRunner queryRunner = new QueryRunner(); 8 connection = C3p0PoolBase.getConnection(); 9 connection.setAutoCommit(false); 10 String sql01 = "update test set type = 'E' where id = 843"; 11 Object params01[] = {}; 12 int i = queryRunner.update(connection,sql01,params01); 13 14 String sql02 = "update test set type = 'F' where id = 842"; 15 Object params02[] = {}; 16 int j = queryRunner.update(connection,sql02,params02); 17 18 //throw new SQLException("error");//模拟异常 19 20 } catch (SQLException e) { 21 try { 22 connection.rollback(); 23 } catch (SQLException e1) { 24 logger.error(e1.getMessage()); 25 //e1.printStackTrace(); 26 } 27 logger.error(e.getMessage()); 28 //e.printStackTrace(); 29 }finally { 30 try { 31 //不管成功 失败 都提交 32 connection.commit(); 33 //关闭连接 34 DbUtils.closeQuietly(connection); 35 } catch (SQLException e) { 36 logger.error(e.getMessage()); 37 //e.printStackTrace(); 38 } 39 } 40 }
2、使用spring boot 、mybatis在mysql中进行事务操作
1 @EnableTransactionManagement 2 @MapperScan("com.test.*.mapper") 3 public class TestApplication 4 { 5 public static void main(String[] args) 6 { 7 SpringApplication.run(TestApplication.class, args); 8 System.out.println("系统启动成功 \n"); 9 } 10 }
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <mapper namespace="com.test.system.mapper.SysTestMapper"> 6 7 <update id="updateMoneyIn"> 8 update sys_test 9 set balance = balance + #{money} 10 where id= #{id,jdbcType=INTEGER} 11 </update> 12 13 <update id="updateMoneyOut"> 14 update sys_test 15 set balance = balance - #{money} 16 where id= #{id,jdbcType=INTEGER} 17 </update> 18 </mapper>
1 package com.test.system.service; 2 3 /** 4 * 接口 5 * 6 * @author 7 */ 8 public interface ISysTestService 9 { 10 public int TestTrans(); 11 }
1 package com.test.system.service.impl; 2 3 import org.springframework.beans.factory.annotation.Autowired; 4 import org.springframework.stereotype.Service; 5 import org.springframework.transaction.annotation.Transactional; 6 import com.test.system.mapper.SysTestMapper; 7 import com.test.system.service.ISysTestService; 8 9 /** 10 * 实现类 11 * 12 * @author 13 */ 14 @Service 15 public class SysTestServiceImpl implements ISysTestService 16 { 17 @Autowired 18 private SysTestMapper testMapper; 19 20 @Override 21 @Transactional 22 public int TestTrans() 23 { 24 int rows = 0; 25 rows = testMapper.updateMoneyIn(1, 100); 26 //int i = 1/0; // 抛出异常 27 rows = testMapper.updateMoneyOut(2, 100); 28 29 return rows; 30 } 31 }
1 package com.test.system.mapper; 2 3 import org.apache.ibatis.annotations.Param; 4 5 /** 6 * 映射接口 7 * 8 * @author 9 */ 10 public interface SysTestMapper 11 { 12 public int updateMoneyIn(@Param("id") int id, @Param("money")float money); 13 14 public int updateMoneyOut(@Param("id") int id, @Param("money")float money); 15 16 }