java三十秒插入Mysql100w数据

 建表语句

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

java代码

package com.example.springbootjdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class InsertDataDemo {
    static Connection conn = null;
 
    public static void initConn()  {
 
        String url = "jdbc:mysql://localhost:3306/sakila?useSSL=false&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai"
                + "&user=root&password=zy5564@qq.com";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("Successfully loaded MySQL driver");
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void insert(int insertNum) {

        Long begin = System.currentTimeMillis();
        System.out.println("Start Inserting Data...");
        String prefix = "insert into employees(id,fname,lname,hired,separated,job_code,store_id)  values ";
 
        try {
            StringBuffer suffix = new StringBuffer();

            conn.setAutoCommit(false);

            for (int i = 1; i <= insertNum; i++) {

                suffix.append("(" + i +",'zhangsan','lisi',now(),now(),1,100000000),");
            }

            String sql = prefix + suffix.substring(0, suffix.length() - 1);

            PreparedStatement pst = conn.prepareStatement(sql);

            pst.addBatch(sql);

            pst.executeBatch();

            conn.commit();

            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Long end = System.currentTimeMillis();
        System.out.println("insert"+insertNum+" data data is completed!");
        System.out.println("Time-consuming : " + (end - begin) / 1000 + "seconds");
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
 
        initConn();
        insert(1000000);
 
    }
}

输出结果

Successfully loaded MySQL driver
Start Inserting Data...
insert1000000 data data is completed!
Time-consuming : 34seconds

Process finished with exit code 0