数据库-JDBC批处理

批处理

将多条语句, 放到一起批量处理 .
批处理的原理: 将多条SQL语句, 转换为一个SQL指令. 显著的提高大量SQL语句执行时的数据库性能.

Statement对象使用流程:
1. 得到Statement对象
Statement state = conn.createStatement();
2. 将一条SQL语句, 加入到批处理中.
state.addBatch(String sql);
3. 执行批处理
state.executeBatch();
4. 清空批处理
state.clearBatch();

PreparedStatement对象使用流程:
1. 得到PreparedStatement对象
PreparedStatement state = conn.prepareStatement("预编译的SQL");
2. 填充预编译的参数
state.setXXX(1,填充参数);
3. 将一条填充完毕参数的SQL, 加入到批处理中.
state.addBatch();
4. 执行批处理
state.executeBatch();
5. 清空批处理
state.clearBatch();

• 案例

package com.java.demo5;

import java.sql.*;

public class Main {
    //statment
    public static void insert1_1() throws SQLException {

        for (int i=0;i<1000;i++) {
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
            Statement state = conn.createStatement();
            state.executeUpdate("insert into person values('admin','123"+i+"')");
            state.close();
            conn.close();
        }

    }
    //statement 批处理
    public static void insert1_2() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        Statement state = conn.createStatement();
        for (int i=0;i<1000;i++) {
            //向批处理中 加入一条SQL
            state.addBatch("insert into person values('admin','123"+i+"')");
        }
        //执行批处理
        state.executeBatch();
        //清空批处理指令
        state.clearBatch();
        state.close();
        conn.close();
    }

    //PreparedStatement
    public static void insert2_1() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        PreparedStatement state = conn.prepareStatement("insert into person values(?,?)");
        for (int i=0;i<10000;i++) {
            state.setString(1,"admin");
            state.setString(2,"123"+i);
            state.executeUpdate();
        }
        state.close();
        conn.close();
    }
    //PreparedStatement批处理
    public static void insert2_2() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        PreparedStatement state = conn.prepareStatement("insert into person values(?,?)");
        for (int i=0;i<10000;i++) {
            state.setString(1,"admin");
            state.setString(2,"123"+i);
            state.addBatch();
        }
        //执行批处理
        state.executeBatch();
        //清空批处理指令
        state.clearBatch();
        state.close();
        conn.close();
    }

}