jdbc-批量插入百万数据-5秒

package com.swift.aaa;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class APP1 {

    public static void main(String[] args) throws Exception {
        
        //1.加载MySQL数据库驱动包
//        Class.forName("com.mysql.jdbc.Driver");
//        //2.连接MySQL数据库服务器
//        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/oa", "root", "root");
//        //3.创建执行语句对象
//        Statement st = conn.createStatement();
//        //4.执行语句
//        st.executeUpdate("INSERT INTO `user` SET username='刘诗华 77154113',password=28");
//        //5.释放资源
//        st.close();
//        conn.close();
        
        
        bulkSubmissionTest4();

    }
    
    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/oa", "root", "root");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    
    
    
    
    /**
     * 方式一
     * 普通批量插入，直接将插入语句执行多次即可
     * @throws Exception 
     */
    @Test
    public void bulkSubmissionTest1() throws Exception {
        long start = System.currentTimeMillis();//开始计时【单位：毫秒】
        Connection conn =getConnection();//获取数据库连接
        String sql = "INSERT INTO `user` SET username=?,password=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);//填充sql语句种得占位符
                ps.setObject(2, i);//填充sql语句种得占位符
                ps.execute();//执行sql语句
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
        //打印耗时【单位：毫秒】
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    
    
    /**
     * 方式二
     * 在方式一的基础上使用批处理
     * 使用PreparedStatement ps;的
     *      ps.addBatch();      将sql语句打包到一个容器中
     *      ps.executeBatch();  将容器中的sql语句提交
     *      ps.clearBatch();    清空容器，为下一次打包做准备
     * 这三个方法实现sql语句打包，累计到一定数量一次提交
     * @throws Exception 
     */
    @Test
    public void bulkSubmissionTest2() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = getConnection();//获取数据库连接
        String sql = "insert into a(id, name) VALUES (?,null)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.addBatch();//将sql语句打包到一个容器中
                if (i % 500 == 0) {
                    ps.executeBatch();//将容器中的sql语句提交
                    ps.clearBatch();//清空容器，为下一次打包做准备
                }
            }
            //为防止有sql语句漏提交【如i结束时%500！=0的情况】，需再次提交sql语句
            ps.executeBatch();//将容器中的sql语句提交
            ps.clearBatch();//清空容器
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    
    
    
    public static Connection getConnection2() {
    
        String url="jdbc:mysql://localhost:3306/oa?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, "root", "root");
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
        
        /**
     * 方式三
     * 在方式二的基础上允许重写批量提交语句，获取连接的url需加上
     * 【&rewriteBatchedStatements=true】(重写批处理语句=是)
         * @throws Exception 
     */
    @Test
    public void bulkSubmissionTest3() throws Exception {
        long start = System.currentTimeMillis();
        Connection conn = getConnection();//获取数据库连接
        String sql = "INSERT INTO `user` SET username=?,password=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, i);
                ps.setObject(2, i);
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }
    
    
    
    /**
     * 方式四
     * 在方式三的基础上，取消自动提交sql语句，当sql语句都提交了才手动提交sql语句
     * 需将Connection conn;连接的【conn.setAutoCommit(false)】(设置自动提交=否)
     * @throws SQLException   String sql = "INSERT INTO `user` SET username=?,password=?";
     */
    @Test
    public static void bulkSubmissionTest4() throws SQLException {
        long start = System.currentTimeMillis();
        Connection conn = getConnection2();//获取数据库连接
        String sql = "INSERT INTO user(username,password) values(?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            for (int i = 1; i <= 5000000; i++) {
                ps.setObject(1, i);
                ps.setObject(2, i);
                ps.addBatch();
                if (i % 10000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
        //truncate table user;
        //select count(*) from user;
        System.out.println("百万条数据插入用时：" + (System.currentTimeMillis() - start)+"【单位：毫秒】");
    }

}