public static void main(String[] args) throws SQLException {
boolean originalAutoCommitSetting = false;
Connection connection = null;
try {
connection = DbPoolConnection.getDataSource().getConnection();
/**
* 在创建QueryRunner对象时,不传递数据源给它,是为了保证这两条SQL在同一个事务中进行,
* 我们手动获取数据库连接,然后让这两条SQL使用同一个数据库连接执行
*/
QueryRunner runner = new QueryRunner();
originalAutoCommitSetting = connection.getAutoCommit();
System.out.println("originalAutoCommitSetting:" + originalAutoCommitSetting);
//开启事务
connection.setAutoCommit(false);
System.out.println("originalAutoCommitSetting2:" + connection.getAutoCommit());
String sql1 = "insert into ssq(qishu) values(?)";
String sql2 = "insert into ssq(qishu) values(?)";
Object[] paramArr1 = {123};
Object[] paramArr2 = {456};
runner.update(connection,sql1,paramArr1);
//模拟程序出现异常让事务回滚
int x = 1/0;
runner.update(connection,sql2,paramArr2);
connection.commit();
}catch (Exception e) {
if(null != connection){
//出现异常之后就回滚事务
connection.rollback();
}
e.printStackTrace();
}finally{
//关闭数据库连接
if(null != connection){
connection.setAutoCommit(originalAutoCommitSetting);
connection.close();
}
}
}
注意,mysql必须是InnoDB引擎,否则事务无效!