JDBC控制事务

偏执的太偏执、 2022-05-16 12:44 151阅读 0赞

**事务特性**：一致性、原子性、隔离性、永久性

当JDBC程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在他上面发送的SQL语句。  
JDBC控制事务语句：  
\- Connection.setAutoCommit(false)这个方法相当于在数据库中输入start transaction命令  
\- Connection.rollback()相当于rollback  
\- Connection.commit()相当于commit  
\- Connextion.setTransactionIsolation()设置事务隔离级别

Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try{
                conn = JdbcUtils_DBCP.getConnection();
                conn.setAutoCommit(false);    //start transaction;
    
                String sql1 = "update account set money=money-100 where name='aaa'";
                String sql2 = "update account set money=money+100 where name='bbb'";
    
                st = conn.prepareStatement(sql1);
                st.executeUpdate();
    
                int x = 1/0;
    
                st = conn.prepareStatement(sql2);
                st.executeUpdate();
    
                conn.commit();
            }finally{
                JdbcUtils_DBCP.release(conn, st, rs);
            }

设置回滚点（不要忘记手动提交）

Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            Savepoint sp = null;
            try{
                conn = JdbcUtils_DBCP.getConnection();   //mysql repeatable read
                conn.setAutoCommit(false);    //start transaction;
    
                String sql1 = "update account set money=money-100 where name='aaa'";
                String sql2 = "update account set money=money+100 where name='bbb'";
                String sql3 = "update account set money=money+100 where name='ccc'";
    
                st = conn.prepareStatement(sql1);
                st.executeUpdate();
    
                sp = conn.setSavepoint();
    
                st = conn.prepareStatement(sql2);
                st.executeUpdate();
    
                int x = 1/0;
    
                st = conn.prepareStatement(sql3);
                st.executeUpdate();
    
                conn.commit();
            }catch (Exception e) {
                e.printStackTrace();
                conn.rollback(sp);
                conn.commit();  //手动回滚后，一定要记得提交事务
            }finally{
                JdbcUtils_DBCP.release(conn, st, rs);
            }