测试JDBC操作事务的基本方法

一.建立连接并开启事务与设置事务隔离级别：

    SET AUTOCOMMIT = 0;

    connection.setAutoCommit(false); 

 

    private String url = "jdbc:mysql://127.0.0.1:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
    private String userName = "root";
    private String password = "123456";
    private String jdbcDriver = "com.mysql.jdbc.Driver"; //jdbc 驱动
    private Connection connection;

    public void createConnection() {
        try {
            Class.forName(jdbcDriver);
            try {
                connection = DriverManager.getConnection(url, userName, password);
                //getConnection:就是jdbc提供给我们建立连接的方法   登录到mysql服务器的过程
                if (!connection.isClosed()) {
                    System.out.println("连接建立成功");
                    connection.setAutoCommit(false);  // SET AUTOCOMMIT = 0;
                    connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                } else {
                    System.out.println("连接建立失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

二.事务的基本操作：

（1）根据数据库中的表添加数据

    public void insertTest(PreparedStatement pre,int id,String name) {
        try {
            pre.setInt(1, id);
            pre.setString(2, name);
            pre.executeUpdate();  //执行这条sql
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

（2）查询方法

 public void selectTest(PreparedStatement sel,String sql){
        try {
            ResultSet resultSet = sel.executeQuery(sql);
            while (resultSet.next()) {//resultSet.next()  判断集合是否有数据
                System.out.println(
                        "id: " + resultSet.getInt(1) +
                                " name: " + resultSet.getString(2)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

（3）roolback回滚方法的测试

    public void rollbackTest(){  //测试回滚方法正常
        String insert = "insert test values(?,?)";
        String select = "select * from test";
        try {
            PreparedStatement pre = connection.prepareStatement(insert);
            PreparedStatement sel = connection.prepareStatement(select);
            insertTest(pre,4,"李四");
            selectTest(sel,select);
            System.out.println("rollback:");
            connection.rollback();
            selectTest(sel,select);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

（4）通过保存点进行rollback回滚方法的测试

 public void rollbackBypoint(){

        String insert = "insert test values(?,?)";
        String select = "select * from test";
        try {
            PreparedStatement pre = connection.prepareStatement(insert);
            PreparedStatement sel = connection.prepareStatement(select);
            insertTest(pre,6,"王五");
            Savepoint p1 = connection.setSavepoint();
            insertTest(pre,7,"小芳");
            selectTest(sel,select);
            System.out.println("rollback to p1:");
            connection.rollback(p1);
            selectTest(sel,select);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

（5）commit方法的测试，提交数据之后就对数据造成持久性修改，无法再进行回滚

  public void commitTest(){  
        String insert = "insert test values(?,?)";
        String select = "select * from test";
        try {
            PreparedStatement pre = connection.prepareStatement(insert);
            PreparedStatement sel = connection.prepareStatement(select);
            insertTest(pre,5,"小刘");
            selectTest(sel,select);
            connection.commit();
            connection.rollback();
            System.out.println("commit:");
            selectTest(sel,select);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

（6）关闭连接，避免造成资源浪费

 public void closeConnection() {
        try {
            connection.close();  //当不在使用mysql时关闭mysql连接 方式资源浪费
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }