MySQL的批处理

批处理，降低与数据库的连接次数，提高执行效率。
BatchDemo.java文件：

package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.Statement;

import org.junit.Test;

import com.itheima.util.JdbcUtil;

/**
 * 批处理，降低与数据库的连接次数，提高执行效率
 * @author wym
 *
 */
public class BatchDemo {
    /*
     create database day16;
     use day16;
     create table t1(
        id int primary key,
        name varchar(100)
     );
     */

    //向数据库中插入2条记录，再把第1条删掉
    @Test
    public void test1() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        Statement stmt = conn.createStatement();
        String sql1 = "insert into t1 values(1,'aaa')";
        String sql2 = "insert into t1 values(2,'bbb')";
        String sql3 = "delete from t1 where id=1";
        stmt.addBatch(sql1);//Statement实例内部有一个List，sql语句加到List中了
        stmt.addBatch(sql2);
        stmt.addBatch(sql3);

        int[] ii = stmt.executeBatch();//返回的是一个数组，数组的元素为每条语句影响到的行数
        for(int i : ii){
            System.out.println(i);
        }
        JdbcUtil.release(null, stmt, conn);
    }

    //插入10条记录
    @Test
    public void test2() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        java.sql.PreparedStatement stmt = conn.prepareStatement("insert into t1 values(?,?)");
        for(int i=0; i<10; i++){
            stmt.setInt(1, i+1);
            stmt.setString(2,"aaaa"+(i+1));
            stmt.addBatch();
        }

        stmt.executeBatch();//返回的是一个数组，数组的元素为每条语句影响到的行数
        JdbcUtil.release(null, stmt, conn);
    }

    //插入10000条数据
    @Test
    public void test3() throws Exception{
        Connection conn = JdbcUtil.getConnection();
        java.sql.PreparedStatement stmt = conn.prepareStatement("insert into t1 values(?,?)");
        for(int i=0; i<10000; i++){
            stmt.setInt(1, i+1);
            stmt.setString(2,"aaaa"+(i+1));
            stmt.addBatch();
            if(i%1000 ==0){
                stmt.executeBatch();
                stmt.clearBatch();
            }
        }

        stmt.executeBatch();//返回的是一个数组，数组的元素为每条语句影响到的行数
        JdbcUtil.release(null, stmt, conn);
    }
}

JdbcUtil.java文件：

package com.itheima.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

//工具类
public class JdbcUtil {

    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    static{
        try {
            ClassLoader cl = JdbcUtil.class.getClassLoader();
            InputStream in = cl.getResourceAsStream("dbcfg.properties");
            Properties props = new Properties();
            props.load(in);
            driverClass = props.getProperty("driverClass");
            url = props.getProperty("url");
            user = props.getProperty("user");
            password = props.getProperty("password");

            Class.forName(driverClass);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }


    public static Connection getConnection() throws Exception{
        Connection conn = DriverManager.getConnection(url,user, password);
        return conn;
    }
    public static void release(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

dbcnfg.properties文件：

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day16
user=root
password=admin

运行结果：
1 aaaa1
2 aaaa2
3 aaaa3
4 aaaa4
5 aaaa5
。。。共10000条