PreparedStatement实现批量插入

问题引入

假如我们要通过JDBC实现插入大量数据，如果只是普通的插入方法要消耗大量时间，下面我们将同通过两个方面进行改进减少程序运行时间

普通方法

public void test1() {

        MyConnection myConnection = new MyConnection();
        Connection con = myConnection.getMyConnection();
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            String insert = "insert into number(num) values(?);";
            ps = con.prepareStatement(insert);//预编译
            //插入1W条数据
            for (int i = 0; i < 10000; i++) {
                ps.setInt(1, (i+1));//设置属性值
                ps.executeUpdate();//执行SQL语句
            }

            long end = System.currentTimeMillis();

            System.out.println("消耗时间："+(end-start));//23531  1W条
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            myConnection.closeAll(con, ps);//关闭连接
        }

    }

因为连接数据库开始的步骤基本都是死步骤，因此为了避免麻烦我就把部分代码封装进了MyConnection类中，方面获得Connection对象和断开连接

MyConnection

public class MyConnection {

    private Connection con = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/xk";
    private String user = "root";
    private String password = "123456";

    public MyConnection() {

    }

    public Connection getMyConnection() {
        try {


            Class.forName(driver);

            con = DriverManager.getConnection(url,user,password);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) {
                System.out.println("数据库连接成功！");
            } else {
                System.out.println("数据库连接失败！");
            }
            return con;
        }
    }

    public void closeAll(Connection connection) {
        try {
            if (connection != null) connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public void closeAll(Connection connection, PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

输出结果

1W条数据插入花费23972毫秒

改进方式一

在上述方法中，我们地SQL语句是一条一条执行的
)

这样子就像是我们吃瓜子时剥一个吃一个，这样不是很浪费时间（毕竟我们要一个一个放进嘴里，当瓜子多的时候就很麻烦）于是我们就可以考虑等剥到一定量的瓜子然后一起放进嘴里

解决方式：攒sql语句

1.攒SQL语句–addBatch
2.执行Batch–executeBatch
3.清空Batch–clearBatch
注意： MySql服务器默认关闭批处理的，我们需要通过一个参数，让MySQL开启批处理的支持：?rewriteBatchedStatements=true(补充在url的后面)

MyConnection2 myConnection = new MyConnection2();
        Connection con = myConnection.getMyConnection();
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            String insert = "insert into number(num) values(?)";
            ps = con.prepareStatement(insert);//预编译
            //插入10W条数据
            for (int i = 0; i < 100000; i++) {
                ps.setInt(1, (i+1));//设置属性值

                //攒SQL语句
                ps.addBatch();
                //攒够500条执行一次
                if ((i+1)%500 == 0) {
                    //执行SQL语句
                    ps.executeBatch();
                    //清空batch
                    ps.clearBatch();
                }
            }

            long end = System.currentTimeMillis();

            System.out.println("消耗时间："+(end-start));//2480 --10W条
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            myConnection.closeAll(con, ps);//关闭连接
        }

注意
这里我是通过MyConnection2获得的Connection对象，MyConnection2和MyConnection的差别

输出结果


我们可以看出明显的差距这次插入10w条数据仅用了2459毫秒也就2秒多点

你以为这就完了吗，不我们还有压榨空间！

首先我们了解一下数据库的DDL、DML
DML: （Data Manipulation Language） 数据操纵语言(用来查询与更新记录): 就是UPDATE、INSERT、DELETE。
DDL:（ Data Definition Language） 数据定义语言(用来定义数据库结构)：CREATE 、ALTER、DROP。
DCL: （Data Control Language）数据控制语言(用来控制数据库的访问)：grant; revoke; commit; rollback; lock。

---

然后我们要了解什么是事务
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

---

注意
  当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
  也就是说我们没执行一个插入的SQL语句都会提交一次事务，就相当于我们每嚼一次瓜子就咽进肚子，如果我们可以等所有瓜子嚼完一起咽岂不是很省事。

    @Test
    public void test3() {
        MyConnection2 myConnection2 = new MyConnection2();
        Connection con = myConnection2.getMyConnection();
        PreparedStatement ps = null;

        try {
            long start = System.currentTimeMillis();
            //不允许自动提交,这是我们首先要做的
            con.setAutoCommit(false);

            String insert = "insert into number(num) values(?)";

            ps = con.prepareStatement(insert);
			//下面的步骤跟前免得改进方式一一样
            for (int i = 1; i <= 100000; i++) {
                ps.setInt(1, i);
                ps.addBatch();
                if(i%500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            con.commit();

            long end = System.currentTimeMillis();
            System.out.println("消耗："+(end-start));//2069 --10W
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            myConnection2.closeAll(con, ps);
        }
    }
}

输出结果

对比上面的方法这次10W条数据用了1.9秒

---

最后用通俗的话描述一下我们的改进过程

正常情况下我们吃瓜子步骤：
1.剥一个
2.放嘴巴里，嚼一嚼
3.咽下去

---

改进一：
1.剥指定数量瓜子
2.放嘴巴里，嚼一嚼
3.咽下去

---

改进二：
1.剥指定数量瓜子
2.剥完一波放嘴巴里嚼但是不咽
3.等所有瓜子嚼完后一起咽