jdbc-批量插入mssql数据库-原

package com.swift.aaa;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
 
public class Test2 {
 
    //static int count = 0;
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        SqlServerBatchInsert(); //100w  20s
        //OracleBatchInsert();    //100w  2s  1000w 20s
        //MySqlBatchInsert();     //100W  130S-150S
    }
 
    /***
     * Sqlserver 100W  20s
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void SqlServerBatchInsert() throws ClassNotFoundException, SQLException {
        // 起始时间
        long start = System.currentTimeMillis();
        // 连接
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection connection = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;databaseName=DEMO",
                "sa", "1");
 
        connection.setAutoCommit(false);
        // 执行插入
        PreparedStatement cmd = connection.prepareStatement(
                "INSERT INTO CUSTOMERS(NAME,AGE,ADDRESS,SALARY) values(?,?,?,?)");
 
        int n = 0;
 
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SSS");
 
        // 注意，此处一次性插入一亿条会溢出，导致程序崩溃，因此最好每3000万到5000万条数据一次，3000万条约需要20分钟
        // 把运行文件与数据库放在同一环境下速度更快
        // int num=1;  100000001
        for (int num = 1; num <= 8000000; num++) {// 1000000万条数据
            cmd.setObject(1, num);
            cmd.setObject(2, num);
            cmd.setObject(3, num);
            cmd.setObject(4, num);
            cmd.addBatch();
            if (num % 100000 == 0) {
                cmd.executeBatch();
                System.out.println("已插入:" + num);
            }
        }
        cmd.executeBatch();
        connection.commit();
 
        cmd.close();
        connection.close();
 
        long end = System.currentTimeMillis();
        System.out.println("SqlServer插入100W数据耗时：" + (end - start) / 1000 + "秒");
    }
 
 
    /***
     * Oracle  100W 2s
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void OracleBatchInsert() throws ClassNotFoundException, SQLException {
        // 起始时间
        long start = System.currentTimeMillis();
        // 连接
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/regent",
                "regent", "regent2021");
 
        connection.setAutoCommit(false);
        // 执行插入
        PreparedStatement cmd = connection.prepareStatement(
                "INSERT INTO ceshi(id,name,age) values(?,?,?)");
 
        int n = 0;
 
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SSS");
 
        // 注意，此处一次性插入一亿条会溢出，导致程序崩溃，因此最好每3000万到5000万条数据一次，3000万条约需要20分钟
        // 把运行文件与数据库放在同一环境下速度更快
        // int num=1;  100000001                     10000000
        for (int num = 1; num <= 1000000; num++) {// 1000000万条数据
            cmd.setInt(1, num);
            cmd.setInt(2, 921681220);
            cmd.setInt(3, 921681220);
            cmd.addBatch();
            if (num % 100000 == 0) {
 
                cmd.executeBatch();
                System.out.println("已插入:" + num);
            }
        }
        cmd.executeBatch();
        connection.commit();
 
        cmd.close();
        connection.close();
 
        long end = System.currentTimeMillis();
        System.out.println("Oracle插入100W数据耗时：" + (end - start) / 1000 + "秒");
    }
 
 
    /***
     * mysql 100W 130s-150s
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void MySqlBatchInsert() throws ClassNotFoundException, SQLException {
        // 起始时间
        long start = System.currentTimeMillis();
        // 连接
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatisplus?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                "root", "root");
 
        connection.setAutoCommit(false);
        // 执行插入
        PreparedStatement cmd = connection.prepareStatement(
                "INSERT INTO ceshi(id,name,age) values(?,?,?)");
 
        int n = 0;
 
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SSS");
 
        // 注意，此处一次性插入一亿条会溢出，导致程序崩溃，因此最好每3000万到5000万条数据一次，3000万条约需要20分钟
        // 把运行文件与数据库放在同一环境下速度更快
        // int num=1;  100000001                     10000000
        for (int num = 1; num <= 1000000; num++) {// 1000000万条数据
            cmd.setInt(1, num);
            cmd.setInt(2, 921681220);
            cmd.setInt(3, 921681220);
            cmd.addBatch();
            if (num % 100000 == 0) {
                cmd.executeBatch();
                System.out.println("已插入:" + num);
            }
        }
        cmd.executeBatch();
        connection.commit();
 
        cmd.close();
        connection.close();
 
        long end = System.currentTimeMillis();
        System.out.println("Mysql插入100W数据耗时：" + (end - start) / 1000 + "秒");
    }
 
 
}