jdbc中PreparedStatment实现高效的批量插入

方式一：使用Statement实现

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

方式二：使用PreparedStatement

/**
     * 批量插入的方式二：使用PreparedStatement
     */
    @Test
    public void testInsert1(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection("test");

            String sql = "insert into goods(name) values (?)";

            ps = conn.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 1; i <= 500; i++) {
                ps.setString(1,"name" + i);
                ps.execute();
            }

            long end = System.currentTimeMillis();

            System.out.println("一共花费的时间为：" + (end - start));//17901
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

方式三：攒SQL：addBatch()

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 testInsert2(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection("");

            String sql = "insert into goods(name) values (?)";

            ps = conn.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 1; i <= 500; i++) {
                ps.setString(1,"name" + i);
                //1."攒"sql
                ps.addBatch();

                if(i % 50 == 0){
                    //2.执行batch
                    ps.executeBatch();

                    //3.清空batch
                    ps.clearBatch();
                }
            }

            long end = System.currentTimeMillis();

            System.out.println("一共花费的时间为：" + (end - start));//378
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }

方式四：设置连接不允许自动提交数据（最佳）

   /**
     * 批量插入的方式四：设置连接不允许自动提交数据
     */
    @Test
    public void testInsert3(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection("");

            String sql = "insert into goods(name) values (?)";

            ps = conn.prepareStatement(sql);

            //设置不允许自动提交数据
            conn.setAutoCommit(false);

            long start = System.currentTimeMillis();
            for (int i = 1; i <= 500; i++) {
                ps.setString(1,"name" + i);
                //1."攒"sql
                ps.addBatch();

                if(i % 50 == 0){
                    //2.执行batch
                    ps.executeBatch();

                    //3.清空batch
                    ps.clearBatch();
                }
            }

            //提交数据
            conn.commit();

            long end = System.currentTimeMillis();

            System.out.println("一共花费的时间为：" + (end - start));//138
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

DBUtil：

package loey.DBUtil;

import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * JDBC工具类，简化JDBC编程
 *
 */
public class DBUtil {

    /**
     * 工具类中的构造方法是私有的
     * 因为工具类中的方法都是静态的，直接通过类名去调即可。
     */
    private DBUtil() {

    }

    private static Properties getProperties(){

        //ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        //String driver = bundle.getString("driver");
        //String url = bundle.getString("url");
        //String user = bundle.getString("user");
        //String password = bundle.getString("password");
        //String sql = bundle.getString("sql");

        // 1.读取配置文件中的4个基本信息
        Properties pros = new Properties();

        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        try {
            pros.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return pros;
    }

    /**
     * 静态代码块，类加载的时候执行
     * 把注册驱动程序的代码放在静态代码块中，避免多次获取连接对象时重复调用
     */
    static{
        Properties pros = getProperties();
        String driver = pros.getProperty("driver");
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * @return 获取连接
     * @throws SQLException
     */
    public static Connection getConnection(String database) throws Exception {

        Properties pros = getProperties();

        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");

        Connection conn = DriverManager.getConnection(url + database, user, password);

        return conn;
    }

    public static void close(Connection conn, Statement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

配置文件：
jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
user=root
password=1127