JDBC学习（七） --- 批处理

批处理

 批处理是指你将关联的 SQL 语句组合成一个批处理，并将他们当成一个调用提交给数据库。当你一次发送多个 SQL 语句到数据库时，批处理可以减少通信的资源消耗，从而提高了性能。

 对于 JDBC 的批处理，有以下几点需要进行说明：

• Statement，PreparedStatement 和 CallableStatement 的 addBatch() 方法用于添加单个语句到批处理。
• executeBatch() 方法用于启动执行所有组合在一起的语句。
• executeBatch() 方法返回一个整数数组，数组中的每个元素代表了各自的更新语句的更新数目。
• 你不仅可以添加语句到批处理，还可以用 clearBatch() 方法删除它们。此方法删除所有用 addBatch()方法添加的语句。但你不能选择性的对语句进行删除。

批处理操作流程

Statement 对象

• 使用 createStatement()方法创建一个 Statement 对象;
• 使用 setAutoCommit()方法将自动提交设置为 false;
• 使用 Statement 对象的 addBatch()方法添加你需要执行的一系列 SQL 语句;
• 使用 Statement 对象的 executeBatch()方法执行所有的 SQL 语句;
• 使用 commit()方法提交所有的更改。

 示例如下：

Statement stmt = conn.createStatement();
conn.setAutoCommit(false);

String SQL = "...";

stmt.addBatch(SQL);


String SQL = "...";

stmt.addBatch(SQL);


String SQL = "...";

stmt.addBatch(SQL);

conn.commit();

PrepareStatement 对象

• 使用占位符创建 SQL 语句;
• 创建 PrepareStatement 对象;
• 使用 setAutoCommit()方法将自动提交设置为 false;
• 使用被创建的 PrepareStatement 对象的 addBatch()方法来添加 SQL 语句;
• 使用被创建的 PrepareStatement 对象的 executeBatch()方法将所有的 SQL 语句执行;
• 使用 commit()方法提交所有更改。

 示例如下：

String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(?, ?, ?, ?)";
PreparedStatemen pstmt = conn.prepareStatement(SQL);

conn.setAutoCommit(false);


pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );

pstmt.addBatch();

pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );

pstmt.addBatch();

conn.commit();

练习

 我们分别用两种方式（一种非批处理，一种批处理）向 Order 表中插入 10000 条数据，观察两者所执行的时间。

 操作前 order 表的内容：

 表中只有 5 条数据（以自己的表为主），为了简单起见，我们插入的 id 要从 6 开始，同时，order_name 设为插入数据的序列号（从 0 开始）。

 为了便于操作，我们先用 JDBC 实现插入 10000 条数据后需要执行的删除操作：

@Test
public void deleteDateTest(){
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        conn = JDBCUtils.getJDBCConnection();
        // 删除id大于5的信息
        // 注意：order在MySQL中是一个关键字，因此要用两个``将它标识出来
        String sql = "delete from `order` where `order_id` > 5";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.execute();
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        try {
            JDBCUtils.closeResource(conn,preparedStatement,resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

 不使用批处理的代码：

@Test
public void insertTest(){
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        conn = JDBCUtils.getJDBCConnection();
        String sql = "Insert into `order` (order_id,order_name) VALUES (?,?) ;";
        preparedStatement = conn.prepareStatement(sql);
        // 获取当前时间
        Date start = new Date();
        long start_time = start.getTime();
        // 循环插入100条数据
        for(int i=0;i<10000;i++){
            preparedStatement.setInt(1,5+i+1);
            preparedStatement.setString(2,"" + i);
            // 直接执行SQL
            preparedStatement.executeUpdate();
        }
        Date end = new Date();
        long end_time = end.getTime();
        System.out.println("执行时间：" + (end_time - start_time));
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        try {
            JDBCUtils.closeResource(conn,preparedStatement,resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

 运行结果：

执行时间：35343

 批处理执行的代码,执行前请执行下删除数据的操作：

 @Test
public void insertBatchTest(){
    Connection conn = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try{
        conn = JDBCUtils.getJDBCConnection();
        conn.setAutoCommit(false);
        String sql = "insert into `order` (order_id, order_name) VALUES (?,?)";
        preparedStatement = conn.prepareStatement(sql);
        Date start_date = new Date();
        long start = start_date.getTime();
        for(int i=0;i<10000;i++){
            preparedStatement.setInt(1,6+i);
            preparedStatement.setString(2,""+i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        conn.commit();
        Date end_date = new Date();
        long end = end_date.getTime();
        System.out.println("执行时间：" + (end - start));
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        try {
            conn.setAutoCommit(true);
            JDBCUtils.closeResource(conn,preparedStatement,resultSet);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

 运行结果：

执行时间：3836

 通过上述练习，可以看出在执行一些列 SQL 语句时，批处理操作能够极大的提高执行效率，因此，在处理需要一次性执行多条 SQL 语句时，批处理是非常好的一个选择。

上述完整代码

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

/**
 * ClassName: JDBCBatchExample
 * Package: ning
 * Description:
 *
 * @Author: Ning
 */
public class JDBCBatchExample {

    @Test
    public void deleteDateTest(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtils.getJDBCConnection();
            String sql = "delete from `order` where `order_id` > 5";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                JDBCUtils.closeResource(conn,preparedStatement,resultSet);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Test
    public void insertTest(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = JDBCUtils.getJDBCConnection();
            String sql = "Insert into `order` (order_id,order_name) VALUES (?,?) ;";
            preparedStatement = conn.prepareStatement(sql);

            Date start = new Date();
            long start_time = start.getTime();
            // 循环插入100条数据
            for(int i=0;i<10000;i++){
                preparedStatement.setInt(1,5+i+1);
                preparedStatement.setString(2,"" + i);
                preparedStatement.executeUpdate();
            }
            Date end = new Date();
            long end_time = end.getTime();
            System.out.println("执行时间：" + (end_time - start_time));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ResultSet resultSet = null;
            try {
                JDBCUtils.closeResource(conn,preparedStatement,resultSet);
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Test
    public void insertBatchTest(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            conn = JDBCUtils.getJDBCConnection();
            conn.setAutoCommit(false);
            String sql = "insert into `order` (order_id, order_name) VALUES (?,?)";
            preparedStatement = conn.prepareStatement(sql);
            Date start_date = new Date();
            long start = start_date.getTime();
            for(int i=0;i<10000;i++){
                preparedStatement.setInt(1,6+i);
                preparedStatement.setString(2,""+i);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            conn.commit();
            Date end_date = new Date();
            long end = end_date.getTime();
            System.out.println("执行时间：" + (end - start));

        }catch (Exception e){
            e.printStackTrace();
        }finally {

            try {
                conn.setAutoCommit(true);
                JDBCUtils.closeResource(conn,preparedStatement,resultSet);
            }catch (Exception e){
                e.printStackTrace();
            }

        }
    }
}