Java快速插入100W笔数据

---

---

1.code

application.properties

server.port=8088

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.jdbc-url=jdbc:sqlserver://10.xxx.xxx.140:1433;DatabaseName=xxxxx
spring.datasource.username=xxxx
spring.datasource.password=xxxx

controller

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;


import java.math.BigDecimal;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Random;

import static javafx.scene.input.KeyCode.CONVERT;

@Controller
public class SimulationController {


    @Value("${spring.datasource.driver-class-name}")
    private String driver;
    @Value("${spring.datasource.jdbc-url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;


    /***
     * Sqlserver 100W  20s
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void SqlBatchInsert() throws ClassNotFoundException, SQLException {

        long start = System.currentTimeMillis();

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username,password);
        connection.setAutoCommit(false);

        //主键自增，从第二个栏位开始
        PreparedStatement cmd = connection.prepareStatement(
                "INSERT INTO mock_data(mock_attribute,description,mock_height,mock_salary,mock_datetime,mock_timestamp) values(?,?,?,?,?,?)");


        Timestamp now= new Timestamp(System.currentTimeMillis());

        Date date = new Date( System.currentTimeMillis());
        String time = String.valueOf(date.getTime());

        for (int num = 1; num <= 1100000; num++) {// 1000000万条数据
            cmd.setString(1, getRandomString(50));
            cmd.setString(2, createDesc());
            cmd.setInt(3,(int)(Math.random()*1000)+num);
            cmd.setBigDecimal(4,(new BigDecimal(Math.random()+num)));
            //cmd.setFloat(4,(float) (Math.random()+num));
            cmd.setTimestamp(5,now);
            cmd.setString(6,time);
            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 + "秒");
    }

    //指定长度的随机字符串
    public String getRandomString(int length){
        String str="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random=new Random();
        StringBuffer sb=new StringBuffer();
        for(int i=0;i<length;i++){
            int number=random.nextInt(62);
            sb.append(str.charAt(number));
        }
        return sb.toString();
    }
    
    //随机长度的随机字符串
    public String createDesc(){

        int result = (int)(Math.random()*100);
        return getRandomString(result);
    }
}

2.数据类型对比

3.特殊情况

• 遇到一个不太好搞定的类型decimal
  
• 偶尔double转过来就会有溢出的问题
  Arithmetic overflow error converting numeric to data type numeric
  
• 最后循环套循环，终于插完了100W笔

        for(int x = 1 ;x <= 1000; x++){
            for (int num = 1; num <= 1000; num++) {// 1000000万条数据
                cmd.setString(1, getRandomString(50));
                cmd.setString(2, createDesc());
                cmd.setInt(3,(int)(Math.random()*1000)+num);
                cmd.setBigDecimal(4,(new BigDecimal(Math.random()+num)));
                //cmd.setFloat(4,(float) (Math.random()+num));
                cmd.setTimestamp(5,now);
                cmd.setString(6,time);
                cmd.addBatch();
                if (num % 100000 == 0) {
                    cmd.executeBatch();
                    System.out.println("已插入:" + num);
                }
            }
            cmd.executeBatch();
            connection.commit();
            System.out.println(x);
        }

        cmd.close();
        connection.close();