JDBC事务 和 批量处理

@Test
public void useTransaction(){
    //1.得到连接
    Connection connection = null;

    //2.组织一个sql语句
    String sql = "update account set balance = balance - 100 where id = 1";
    String sql2 = "update account set balance = balance + 100 where id = 2";
    PreparedStatement preparedStatement = null;
    try {
        connection = JDBCUtils.getConnection();//在默认情况下, connection是默认自动提交

        //将 connection设置为不自动提交
        connection.setAutoCommit(false);//相当于开启了事务
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.executeUpdate();//执行第一条

        PreparedStatement preparedStatement2 = connection.prepareStatement(sql2);
        preparedStatement2.executeUpdate();//执行第二条

        //这里提交事务
        connection.commit();

    } catch (SQLException e) {
        //这里我们可以进行回滚, 即撤销执行的sql
        //如果没设置 保存点,默认回滚到事务开始的状态
        System.out.println("执行发生了异常,回滚撤销执行的sql");
        try {
            connection.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        e.printStackTrace();
    } finally {
        //关闭资源
        JDBCUtils.close(null,preparedStatement,connection);
    }

}


批处理机制

@Test
public void batch() throws Exception{
    Connection connection = JDBCUtils.getConnection();
    String sql = "insert into admin2 values(null,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    System.out.println("开始执行");
    long start = System.currentTimeMillis();
    for (int i = 0; i<5000; i++){
        preparedStatement.setString(1,"jack"+i);
        preparedStatement.setString(2,"666");
        //将sql 语句加入到批处理包中
        preparedStatement.addBatch();
        //当有1000条记录时, 再批量执行
        if((i + 1) % 1000 ==0){//说明满了1000条了
            preparedStatement.executeBatch();
            //清空一把
            preparedStatement.clearBatch();
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("批量方式耗时"+(end-start));

    JDBCUtils.close(null,preparedStatement,connection);
}