JDBC-07：PreparedStatement批量插入数据

PreparedStatement批量插入数据

批量执行SQL语句

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率

JDBC的批量处理语句包括下面三个方法：

• addBatch(String)：添加需要批量处理的SQL语句或是参数；
• executeBatch()：执行批量处理语句；
• clearBatch():清空缓存的数据

通常我们会遇到两种批量执行SQL语句的情况：

• 多条SQL语句的批量处理；
• 一个SQL语句的批量传参；

使用Statement

Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
for(int i = 1;i <= 20000;i++){
	String sql = "insert into goods(name) values('name_' + "+ i +")";
	st.executeUpdate(sql);
}

使用PreparedStatement批量插入数据

方式一

新建一个goods表

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(25)
);

操作代码

⭕️：批量插入:使用preparedStatement

package com.jsm6;

import com.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/*
PreparedStatement实现批量数据操作
update、delete本身就具有批量操作的效果
此时的批量操作主要指批量插入，使用PreparedStatement如何实现更高效的批量操作
 */
public class InsertTest {
    //批量插入:使用preparedStatement
    @Test
    public void test1() throws Exception {
        long l = System.currentTimeMillis();
        Connection conn = JDBCUtils.getCollections();
        String sql = "insert into goods(name)values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 1; i <=20000 ; i++) {
            ps.setObject(1,"name_"+i);
            ps.execute();
        }
        long l1 = System.currentTimeMillis();
        System.out.println("花费的时间为："+(l1-l)/1000+"秒");//我电脑大概8-9秒
        JDBCUtils.closeResource(conn,ps);
    }
}

运行结果

体现的是PreparedStatement的预编译

• DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
• 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次

方式二（方式一的优化）

方法

方式一中花费的时间大概8-9秒（见代码注释）

前面说到JDBC的批量处理语句包括下面三个方法：

• addBatch(String)：添加需要批量处理的SQL语句或是参数；
• executeBatch()：执行批量处理语句；
• clearBatch():清空缓存的数据

前提条件

* 修改1： 使用 addBatch() / executeBatch() / clearBatch()
* 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
*         ?rewriteBatchedStatements=true 写在配置文件的url后面
* 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar

操作代码

/*
 * 修改1： 使用 addBatch() / executeBatch() / clearBatch()
 * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
 *         ?rewriteBatchedStatements=true 写在配置文件的url后面
 * 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
 */
@Test
public void test2() throws Exception{
    long l = System.currentTimeMillis();
    Connection conn = JDBCUtils.getCollections();
    String sql = "insert into goods(name)values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    for (int i = 1; i <=20000 ; i++) {
        ps.setObject(1,"name_"+i);
        //“攒”sql
        ps.addBatch();
        if (i%500==0){//条数除不尽就再写个if就ok
            //执行batch
            ps.executeBatch();
            //清空batch
            ps.clearBatch();
        }

    }
    long l1 = System.currentTimeMillis();
    System.out.println("花费的时间为："+(l1-l)+"毫秒");
    JDBCUtils.closeResource(conn,ps);
}

运行结果

方法三（进一步优化）

注意：这时候把添加的数据条数改为1百万条

清空goods表

TRUNCATE TABLE goods;

在上述代码中，if语句里面每500条一次攒，也是提交数据道理数据库当中，也需要花费一点时间，所以可以设置为不可以自动提交，等全部数据传完了再提交，这里运用MySql 中的setAutoCommit方法

代码

/*
* 层次四：在层次三的基础上操作
* 使用Connection 的 setAutoCommit(false)  /  commit()
*/
@Test
public void test3() throws Exception{
    long l = System.currentTimeMillis();
    Connection conn = JDBCUtils.getCollections();
    //设置不允许自动提交数据
    conn.setAutoCommit(false);
    String sql = "insert into goods(name)values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    for (int i = 1; i <=1000000 ; i++) {
        ps.setObject(1,"name_"+i);
        //“攒”sql
        ps.addBatch();
        if (i%500==0){//条数除不尽就再写个if就ok
            //执行batch
            ps.executeBatch();
            //清空batch
            ps.clearBatch();
        }
    }
    //提交数据
    conn.commit();
    long l1 = System.currentTimeMillis();
    System.out.println("花费的时间为："+(l1-l)+"毫秒");
    JDBCUtils.closeResource(conn,ps);
}