JDBC进阶

• SQL注入
• 预编译语句对象
• CURD操作
• 批处理
• 事务及回滚点

SQL注入

介绍

SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

案例

以模拟登录为例:在前台输入用户名和密码，后台判断信息是否正确，并给出前台反馈信息，前台输出反馈信息。

创建实体类

package com.excepenxi.entity;

import java.io.Serializable;

// 1.创建实体类
public class Account implements Serializable {
    private int aid;
    private String username;
    private String password;
    private int money;

    // 5.方便测试 toString()
    @Override
    public String toString() {
        return "Account{" +
                "aid=" + aid +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", money=" + money +
                '}';
    }

    // 4.包含全部参数构造方法 Constructor ==> OK
    public Account(int aid, String username, String password, int money) {
        this.aid = aid;
        this.username = username;
        this.password = password;
        this.money = money;
    }

    // 3.无参构造方法 Constructor ==> Select None
    public Account() {
    }

    // 2.Getter Setter方法
    public int getAid() {
        return aid;
    }

    public void setAid(int aid) {
        this.aid = aid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }
}

测试代码

package com.excepenxi.test2;

import com.excepenxi.entity.Account;
import java.sql.*;
import java.util.Scanner;

public class TestInjection {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static String user="root";
    private static String password="root";

    public static void main(String[] args) {
        /*// 测试，成功返回数据，失败返回 null
        Account account = getAccount("zhangsan","12356");
        System.out.println(account);*/
        Scanner sc =new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username=sc.next();
        System.out.print("请输入密码：");
        String pwd =sc.next();
        Account account = getAccount(username, pwd);
        System.out.println(null != account?"登录成功":"登录失败");
        sc.close();
    }
    public static Account getAccount(String username,String pwd){
        Connection connection = null;
        Statement statement=null;
        ResultSet resultSet=null;
        Account account =null;

        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            statement = connection.createStatement();
            String sql="select * from account where username ='"+username+"' and password ='"+pwd+"'";
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            
            while(resultSet.next()){
                int aid = resultSet.getInt("aid");
                String usernamea = resultSet.getString("username");
                String pwda = resultSet.getString("password");
                int money = resultSet.getInt("money");
                account = new Account(aid,usernamea,pwda,money);
                System.out.println(account);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != resultSet){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != statement){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return account;
    }
}

测试结果

当输入了精心设计的用户名密码后，即使是错误的，也能登录成功。让登录功能形同虚设。这是为什么呢，这就是SQL注入风险，原因在于SQL语句是字符串拼接的。SQL语句中拼接的内容破坏了SQL语句原有的判断逻辑

防止SQL注入

如何解决呢?使用PreparedStatement预编译语句对象就可以解决掉。

预编译语句对象

使用预编译语句对象防止注入攻击

测试代码

package com.excepenxi.test2;

import com.excepenxi.entity.Account;

import java.sql.*;
import java.util.Scanner;

public class TestInjection2 {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static String user="root";
    private static String password="root";

    public static void main(String[] args) {
        /*// 测试，成功返回数据，失败返回 null
        Account account = getAccount("zhangsan","12356");
        System.out.println(account);*/
        Scanner sc =new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username=sc.next();
        System.out.print("请输入密码：");
        String pwd =sc.next();
        Account account = getAccount(username, pwd);
        System.out.println(null != account?"登录成功":"登录失败");
        sc.close();
    }
    public static Account getAccount(String username,String pwd){
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        Account account =null;

        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            /*
             * 1.使用PreparedStatement语句对象防止注入攻击
             * 2.PreparedStatement 可以使用 ? 作为参数的占位符
             * 3.使用?作为占位符,即使是字符串和日期类型,也不使用单独再添加 ''
             * 4.connection.createStatement();获得的是普通语句对象 Statement
             * 5.connection.prepareStatement(sql);可以获得一个预编译语句对象PreparedStatement
             * 6.如果SQL语句中有?作为参数占位符号,那么要在执行CURD之前先设置参数
             * 7.通过set***(问号的编号,数据) 方法设置参数
             * */
            String sql="select * from account where username = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql); //这里已经传入SQL语句
            //设置参数
            preparedStatement.setString(1,username );
            preparedStatement.setString(2,pwd );
            //执行CURD,这里不需要再传入SQL语句
            resultSet = preparedStatement.executeQuery();
            
            while(resultSet.next()){
                int aid = resultSet.getInt("aid");
                String usernamea = resultSet.getString("username");
                String pwda = resultSet.getString("password");
                int money = resultSet.getInt("money");
                account = new Account(aid,usernamea,pwda,money);
                System.out.println(account);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != resultSet){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return account;
    }
}

测试结果

prepareStatment对象在set***方法上，会对单引号进行转译处理，也就是说，?中的数据的单引号 ‘ 会被转义成 \’，这样就单引号就不会破坏sql语句的结构。

SELECT *   FROM users WHERE  userName =  ?    AND password =  ? 

preparedStatement.setString(1,"xiaoming");
preparedStatement.setString(2,'anything'   OR 'x'='x');
# 会被转义为
 SELECT *   FROM users WHERE  userName = 'xiaoming'  AND password =  'anything\'   OR\'x\'=\'x\''
 
# 而不是
SELECT *   FROM users WHERE  userName =  'xiaoming' AND password =  'anything' OR 'x'='x'

说白了就是把值当中的所有单引号给转义了! 这就达到了防止sql注入的目的，说白了mysql驱动的PreparedStatement实现类的setString();方法内部做了单引号的转义，而Statement不能防止sql注入，就是因为它没有把单引号做转义，而是简单粗暴的直接拼接字符串，所以达不到防止sql注入的目的。

预编译（mysql优化）

当客户端发送一条sql语句给DBMS时,MySQL的执行流程如下：

1. 客户端向服务器端发送SQL命令

2. 服务器端连接模块连接并验证

3. 缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行

4. 解析器解析SQL为解析树，如果出现错误，报SQL解析错误。如果正确，向下传递

5. 预处理器对解析树继续处理，处理成新的解析树。

6. 优化器根据开销自动选择最优执行计划，生成执行计划

7. 执行器执行执行计划，访问存储引擎接口

8. 存储引擎访问物理文件并返回结果

9. 如果开启缓存，缓存管理器把结果放入到查询缓存中。

10. 返回结果给客户端

当客户发送一条SQL语句给DBMS后，DBMS总是需要校验SQL语句的语法格式是否正确，然后把SQL语句编译成可执行的函数，最后才是执行SQL语句。其中校验语法，和编译所花的时间可能比执行SQL语句花的时间还要多。

预编译语句PreparedStatement 是java.sql中的一个接口，它是Statement的子接口。通过Statement对象执行SQL语句时，需要将SQL语句发送给DBMS，由DBMS首先进行编译后再执行。预编译语句和Statement不同，在创建PreparedStatement 对象时就指定了SQL语句，该语句立即发送给DBMS进行编译。当该编译语句被执行时，DBMS直接运行编译后的SQL语句，而不需要像其他SQL语句那样首先将其编译。预编译的SQL语句处理性能稍微高于普通的传递变量的办法。

例如:我们需要执行多次insert语句，但只是每次插入的值不同，MySQL服务器也是需要每次都去校验SQL语句的语法格式，以及编译，这就浪费了太多的时间。如果使用预编译功能，那么只对SQL语句进行一次语法校验和编译，所以效率要高。

开启预编译

我们可以通过设置URL中的参数来控制预编译是否开启

• useServerPrepStmts是否开启预编译

• cachePrepStmts 是否启用预编译缓存

"jdbc:mysql://localhost:3306/mydb *****&useServerPrepStmts=true&cachePrepStmts=true"; 
# useServerPrepStmts=true 开启预编译
# cachePrepStmts=true 开启预编译缓存，提升性能；如果长期查询一次，开启反而占用缓存

值得注意的是,我们的Connector/J 5.0.5及之后useServerPrepStmts默认false,就是默认没有开启预编译,之前默认为true, cachePrepStmts 一直默认为false,需要我们手动设置才可以启用预编译,在开启预编译的同时要同时开启预编译缓存才能带来些许的性能提升

Statement和PreparedStatment的关系和区别

关系：public interface PreparedStatement extends Statement

区别：

• PreparedStatment安全性高,可以避免SQL注入

• PreparedStatment简单不繁琐,不用进行字符串拼接

• PreparedStatment性能高，用在执行多个相同数据库DML操作时,可以减少sql语句的编译次数

PreparedStatment完成CURD操作

package com.excepenxi.test3;

import com.excepenxi.entity.Emp;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TestPreparedSstatement {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static String user="root";
    private static String password="root";

    public static void main(String[] args) {
        //testAdd();
        //testUpdate();
        //testDelete();
        testQuery();
    }

    public static void testAdd(){
        // 向 Emp表中增加一条数据
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            String sql="insert into emp values(DEFAULT,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            preparedStatement.setString(1,"Mark");
            preparedStatement.setString(2,"MANAGER" );
            preparedStatement.setInt(3,7839);
            preparedStatement.setDate(4,new Date(System.currentTimeMillis()));
            preparedStatement.setDouble(5,3000.12);
            preparedStatement.setDouble(6,0.0);
            preparedStatement.setDouble(7,30);
            //执行CURD
            int rows =preparedStatement.executeUpdate();// 这里不需要再传入SQL语句
            System.out.println(rows);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void testUpdate(){
        // 根据工号修改员工表中的数据
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            String sql="update emp set ename =? ,job=? where empno =?";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            preparedStatement.setString(1,"Jhon");
            preparedStatement.setString(2,"ANALYST" );
            preparedStatement.setInt(3,10001);
            //执行CURD
            int rows =preparedStatement.executeUpdate();// 这里不需要再传入SQL语句
            System.out.println(rows);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void testDelete(){
        // 根据工号删除员工表中的数据
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            String sql="delete from emp where empno =?";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            preparedStatement.setInt(1,10001);
            //执行CURD
            int rows =preparedStatement.executeUpdate();// 这里不需要再传入SQL语句
            System.out.println(rows);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void testQuery(){
        // 查询名字中包含字母A的员工信息
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<Emp> list =null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            /*
             * 1使用PreparedStatement语句对象防止注入攻击
             * 2PreparedStatement 可以使用 ? 作为参数的占位符
             * 3使用?作为占位符,即使是字符串和日期类型,也不使用单独再添加 ''
             * 4connection.createStatement();获得的是普通语句对象 Statement
             * 5connection.prepareStatement(sql);可以获得一个预编译语句对象PreparedStatement
             * 6如果SQL语句中有?作为参数占位符号,那么要在执行CURD之前先设置参数
             * 7通过set***(问号的编号,数据) 方法设置参数
             * */
            String sql="select * from emp where ename like ? ";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            preparedStatement.setString(1,"%A%");
            //执行CURD
            resultSet = preparedStatement.executeQuery();// 这里不需要再传入SQL语句
            list=new ArrayList<Emp>() ;
            while(resultSet.next()){
                int empno = resultSet.getInt("empno");
                String ename = resultSet.getString("ename");
                String job = resultSet.getString("job");
                int mgr = resultSet.getInt("mgr");
                Date hiredate = resultSet.getDate("hiredate");
                double sal= resultSet.getDouble("sal");
                double comm= resultSet.getDouble("comm");
                int deptno= resultSet.getInt("deptno");
                Emp emp =new Emp(empno, ename, job, mgr, hiredate, sal, comm, deptno);
                list.add(emp);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != resultSet){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        // 遍历集合
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }
}

批处理

当我们有多条sql语句需要发送到数据库执行的时候，有两种发送方式，一种是执行一条发送一条sql语句给数据库,另一个种是发送一个sql集合给数据库，也就是发送一个批sql到数据库。普通的执行过程是：每处理一条数据，就访问一次数据库；而批处理是：累积到一定数量，再一次性提交到数据库，减少了与数据库的交互次数，所以效率会大大提高,很显然两者的数据库执行效率是不同的，我们发送批处理sql的时候数据库执行效率要高。

statement语句对象实现批处理有如下问题

• 缺点：采用硬编码效率低，安全性较差。
• 原理：硬编码，每次执行时相似SQL都会进行编译

PreparedStatement+批处理

• 优点：语句只编译一次，减少编译次数。提高了安全性（阻止了SQL注入）
• 原理：相似SQL只编译一次，减少编译次数
• 注意: 需要设置批处理开启&rewriteBatchedStatements=true

批处理之增加数据

package com.excepenxi.test4;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestBatch {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    // 增加三个参数
    // &useServerPrepStmts=true 预编译
    // &cachePrepStmts=true     缓存预编译
    // &rewriteBatchedStatements=true   批处理
    /* 增加批量写的速度： useServerPrepStmts=false&rewriteBatchedStatements=true&useCompression=true
    增加读的速度： useServerPrepStmts=true&cachePrepStmts=true */
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true";
    private static String user="root";
    private static String password="root";

    public static void main(String[] args) {
        testAddBatch();
    }
    // 定义一个方法,向部门表增加1000条数据
    public static void testAddBatch(){
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            String sql="insert into dept values (DEFAULT ,?,?)";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            for (int i = 1; i <= 10663; i++) {
                preparedStatement.setString(1, "name");
                preparedStatement.setString(2, "loc");
                preparedStatement.addBatch();// 将修改放入一个批次中
                if(i%1000==0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();// 清除批处理中的数据
                }
            }
            /*
             * 整数数组中的元素代表执行的结果代号
             * SUCCESS_NO_INFO -2
             * EXECUTE_FAILED  -3
             * */
            /*int[] ints = */
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

SELECT * FROM dept WHERE deptno > 50;

SELECT COUNT(*) FROM dept WHERE deptno > 50;  -- 10663

DELETE FROM dept WHERE deptno > 50;

补充

1. 增加批量写的速度： useServerPrepStmts=false&rewriteBatchedStatements=true&useCompression=true
2. 增加读的速度： useServerPrepStmts=true&cachePrepStmts=true

批处理之修改数据

待补充……

批处理之删除数据

待补充……

事务及回滚点

JDBC中使用事务

事务概念：在逻辑上一组不可分割的操作,由多个sql语句组成,多个sql语句要么全都执行成功,要么都不执行. 原子性 一致性 隔离性 持久性

JDBC控制事物主要就是在学习如何让多个数据库操作成为一个整体,实现要么全都执行成功,要么全都不执行

在JDBC中，事务操作是自动提交。一条对数据库的DML(insert、update、delete)代表一项事务操作,操作成功后，系统将自动调用commit()提交，否则自动调用rollback()回滚,在JDBC中，事务操作方法都位于接口java.sql.Connection中,可以通过调用setAutoCommit(false)来禁止自动提交。之后就可以把多个数据库操作的表达式作为一个事务，在操作完成后调用commit()来进行整体提交，倘若其中一个表达式操作失败，都不会执行到commit()，并且将产生响应的异常；此时就可以在异常捕获时调用rollback()进行回滚,回复至数据初始状态.事务开始的边界则不是那么明显了，它会开始于组成当前事务的所有statement中的第一个被执行的时候。事务结束的边界是commit或者rollback方法的调用

使用事务保证转账安全性

package com.excepenxi.test5;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestTransaction {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true";
    private static String user="root";
    private static String password="root";
    public static void main(String[] args) {
        testTransaction();
    }
    // 定义一个方法,向部门表增加1000条数据
    public static void testTransaction(){
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        /*
         * JDBC 默认是自动提交事务
         * 每条DML都是默认提交事务的,多个preparedStatement.executeUpdate();都会提交一次事务
         * 如果想手动控制事务,那么就不能让事务自动提交
         * 通过Connection对象控制connection.setAutoCommit(false);
         * 如果不设置 默认值为true,自动提交,设置为false之后就是手动提交了
         * 无论是否发生回滚,事务最终会一定要提交的 提交我们建议放在finally之中进行提交
         * 如果是转账的过程中出现异常了,那么我们就要执行回滚,回滚操作应该方法catch语句块中
         * */
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            // 设置事务手动提交
            connection.setAutoCommit(false);
            String sql="update account set money =money- ? where aid = ?";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            // 转出
            preparedStatement.setDouble(1, 100);
            preparedStatement.setInt(2, 1);
            preparedStatement.executeUpdate(); // 执行
            // 测试产生异常
            //int i =1/0;
            // 转入
            preparedStatement.setDouble(1, -100);
            preparedStatement.setInt(2, 2);
            preparedStatement.executeUpdate();
        }catch (Exception e){
            if(null != connection){
                try {
                    connection.rollback();// 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            // 提交事务
            if(null != connection){
                try {
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

设置回滚点

package com.excepenxi.test5;

import java.sql.*;
import java.util.LinkedList;

public class TestTransaction2 {
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useServerPrepStmts=true&cachePrepStmts=true&&rewriteBatchedStatements=true";
    private static String user="root";
    private static String password="root";
    public static void main(String[] args) {
        testAddBatch();
    }
    // 定义一个方法,向部门表增加1000条数据
    public static void testAddBatch(){
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        LinkedList<Savepoint> savepoints =new LinkedList<Savepoint>();
        try{
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user,password);
            connection.setAutoCommit(false);
            String sql="insert into dept values (DEFAULT ,?,?)";
            preparedStatement = connection.prepareStatement(sql);//这里已经传入SQL语句
            //设置参数
            for (int i = 1; i <= 10663; i++) {
                preparedStatement.setString(1, "name");
                preparedStatement.setString(2, "loc");
                preparedStatement.addBatch();// 将修改放入一个批次中
                if(i%1000==0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();// 清除批处理中的数据
                    // 设置回滚点
                    Savepoint savepoint = connection.setSavepoint();
                    savepoints.addLast(savepoint);
                }
                // 数据在 100001条插入的时候出现异常
                if(i ==10001){
                    int x =1/0;
                }
            }
            /*
             * 整数数组中的元素代表执行的结果代号
             * SUCCESS_NO_INFO -2
             * EXECUTE_FAILED  -3
             * */
            /*int[] ints = */
            preparedStatement.executeBatch();
            preparedStatement.clearBatch();
        }catch (Exception e){
            if(null != connection){
                try {
                    //Savepoint sp = savepoints.getLast();
                    Savepoint sp = savepoints.get(4); //索引为4，回滚到第5个回滚点，5000条数据
                    if(null != sp){
                        // 选择回滚点
                        connection.rollback(sp);// 回滚
                    }
                } catch (SQLException e2) {
                    e2.printStackTrace();
                }
            }
            e.printStackTrace();
        }finally {
            if(null != connection){
                try {
                    connection.commit();// 提交
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != preparedStatement){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(null != connection){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}