利用jdbc往mysql添加100W数据测试

1.建表

create table user(

     id int auto_increment primary key,

     name varchar(24),

     age int,phone varchar(24),

     address varchar(24)

     )auto_increment=1 default character set=utf8;

2.编写代码

package com.atguigu.crud.test;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;

import org.junit.Test;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class dd {
    private String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&characterEncoding=utf8";
    private String user = "root";
    private String password = "";    //换成你自己的
    @Test
    public void Test(){
        Connection conn = null;
        PreparedStatement pstm =null;
        ResultSet rt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection(url, user, password);        
            String sql = "INSERT INTO user(id,name,age,phone,address) VALUES(?,?,?,?,?)";
            pstm = (PreparedStatement) conn.prepareStatement(sql);
            conn.setAutoCommit(false);  //设置事务不自动提交
            Long startTime = System.currentTimeMillis();
            Random rand = new Random();
            int a,b,c,d;
            for (int i = 1; i <= 1000000; i++) {
                    pstm.setInt(1, i);
                    pstm.setString(2, "阿弥陀佛"+i);
                    pstm.setInt(3, 20+rand.nextInt(10));
                    a = rand.nextInt(10);
                    b = rand.nextInt(10);
                    c = rand.nextInt(10);
                    d = rand.nextInt(10);
                    pstm.setString(4, "188"+a+"88"+b+c+"66"+d);
                    pstm.setString(5, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
                    pstm.addBatch();
            }
            pstm.executeBatch();   //批量执行
            conn.commit();    //事务提交
            Long endTime = System.currentTimeMillis();
            System.out.println("OK,用时：" + (endTime - startTime)); 
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            if(pstm!=null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

3.测试