使用jdbc批量插入数据

使用jdbc 批量插入数据，速度也不是很快1万条数据大概需要4.5秒左右

package com.example.studyspringboot.studyboot.utils.stuMysqlBatchInsert;

import com.example.studyspringboot.studyboot.utils.jdbc.DBUtils;
import org.junit.Test;

import java.sql.*;

//大批量数据插入
//参考文档https://www.cnblogs.com/myseries/p/11191134.html
public class InsertBatch {
    @Test
    public void method() {
        System.out.println((int)(Math.random()*10000));
    }

    @Test
    public void insertBatch() throws ClassNotFoundException {
        //思路：将100万条数据分成n等份，1等份为1000条数据
        //如何实现？
        //1、必须将Connection接口的自动提交方式改为手动
        //2、利用Statement接口中的如下三个方法：addBatch、clearBath、executeBatch
        Connection conn;
        PreparedStatement pstm;
        ResultSet rt = null;
        try {

            conn =  DBUtils.getConn("jdbc:mysql://localhost:3306/redistest?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai","root","lps123");

            conn.setAutoCommit(false);
            String sql = "INSERT INTO user(name,sex,hobby,height) values(?,?,?,?)";
            pstm = conn.prepareStatement(sql);

            conn.setAutoCommit(false);//关闭自动提交
            int random;
            Long startTime = System.currentTimeMillis();

            Long headTime=startTime;
            long tailTime;
            for (int i = 1; i <= 1000025; i++) {//100万用了近8分钟（443秒，一万条数据大概4.5秒）
                random= (int)(Math.random()*10000);
                pstm.setString(1,i+"_name_"+random);
                pstm.setString(2, i+"_sex_"+random);
                pstm.setString(3, i+"_hobby_"+random);
                pstm.setString(4, i+"_height_"+random);
                pstm.addBatch();
                if((i%10000)==0){
                    tailTime=System.currentTimeMillis();
                    pstm.executeBatch();//执行插入
                    conn.commit();
                    pstm.clearBatch();
                    System.out.println("每插入一万条所用时间："+(tailTime-headTime));
                    headTime=tailTime;
                }
            }
            pstm.executeBatch();//执行插入
            conn.commit();
            pstm.clearBatch();
            Long endTime = System.currentTimeMillis();
            System.out.println("总用用时：" + (endTime - startTime)+"ms");
            DBUtils.close(conn,pstm,null);//关闭资源

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

DBUtil

package com.example.studyspringboot.studyboot.utils.jdbc;

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

public class DBUtils {

    //1. 封装获取连接的方法
    public static Connection getConn(String url,String user,String password) throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接对象//url="jdbc:mysql://localhost:3306/tempwork" ;user = "root"  ;password = "lps123";
        Connection conn = DriverManager.getConnection(url,user, password);
        //返回连接对象
        return conn;
    }

    //2. 封装关闭资源的方法
    public static void close(Connection conn,
                             Statement stat, ResultSet rs) {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (stat != null) {
                stat.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}