mysql中批量插入数据(1万、10万、100万、1000万、1亿级别)

import Java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
 
import com.lk.entity.TUser;
 
public class BaseDao {
    private static ConfigManager cm = ConfigManager.getInstance();
 
    private static String Driver = null;
    private static String URL = null;
    private static String USER = null;
    private static String PWD = null;
 
    private static Connection conn = null;
    private static PreparedStatement psmt = null;
    public ResultSet rs = null;
    public int row = 0;
 
    static {
        Driver = cm.getString("DRIVER");
        URL = cm.getString("URL");
        USER = cm.getString("USER");
        PWD = cm.getString("PWD");
        try {
            Class.forName(Driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        getConnection();
        try {
            conn.setAutoCommit(false);
            psmt = conn.prepareStatement("");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    private static Connection getConnection() {
        try {
            conn = DriverManager.getConnection(URL, USER, PWD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
    
    public boolean affectRowMore(String sql, List<TUser> list, long flag) {
        try {
            psmt = conn.prepareStatement(sql);
            for (TUser tUser : list) {
                psmt.setLong(1, tUser.getId());
                psmt.setString(2, tUser.getName());
                psmt.setInt(3, tUser.getSex());
                psmt.setString(4, tUser.getPhone());
                psmt.setString(5, tUser.getPassword());
                // 添加执行sql
                psmt.addBATch();
            }
            // 执行操作
            int[] counts = psmt.executeBatch(); // 执行Batch中的全部语句
            conn.commit(); // 提交到数据库
            for (int i : counts) {
                if (i == 0) {
                    conn.rollback();
                }
            }
            closeAll(flag);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
 
    
    public boolean affectRowMore1(String sql, long flag) {
        try {
            psmt.addBatch(sql);
            // 执行操作
            int[] counts = psmt.executeBatch(); // 执行Batch中的全部语句
            conn.commit(); // 提交到数据库
            for (int i : counts) {
                if (i == 0) {
                    conn.rollback();
                    return false;
                }
            }
            closeAll(flag);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
 
    public void closeAll(long flag) {
        try {
            if (conn != null && flag == -1) {
                // 在完成批量操作后恢复默认的自动提交方式，提高程序的可扩展性
                conn.setAutoCommit(true);
                conn.close();
            }
            if (psmt != null && flag == -1) {
                psmt.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public void insertBenchMark() {
  long start = System.currentTimeMillis();
  List<TUser> list = new ArrayList<>();
  long row = 1;
  for (int j = 0; j < 1000; j++) {
   for (int i = 0; i < 10000; i++) {
    String uuid = UUID.randomUUID().toString();
    String name = uuid.substring(0, 4);
    int sex = -1;
    if(Math.random() < 0.51) {
     sex = 1;
    }else {
     sex = 0;
    }
    String phone = (String) RandomValue.getAddress().get("tel");
    list.add(new TUser(row,name, sex, phone, uuid));
    row++;
   }
   int flag = 1;
   if(j==999) {
    flag = -1;
   }
   //封装好的
   boolean b = userDao.insertMore(list,flag);
   if(!b) {
    System.out.println("出错了----");
    System.exit(0);
   }else {
    list.clear();
   }
  }
  long end = System.currentTimeMillis();
  System.out.println("生成1000万条数据共花费"+(end-start)+"毫秒");
 }

public boolean insertMore(List<TUser> list,long flag) {
  String sql = "insert into tuser(id,name,sex,phone,password) values(?,?,?,?,?)";
  return affectRowMore(sql,list,flag);
 }

public void insertBenchMark1() {
  long start = System.currentTimeMillis();
  StringBuffer suffix = new StringBuffer();
  long row = 1;
  for (int j = 0; j < 1000; j++) {
   for (int i = 0; i < 10000; i++) {
    String uuid = UUID.randomUUID().toString();
    String name = uuid.substring(0, 4);
    int sex = -1;
    if(Math.random() < 0.51) {
     sex = 1;
    }else {
     sex = 0;
    }
    String phone = (String) RandomValue.getAddress().get("tel");
    suffix.append("(" + row + ",'" + name + "'," + sex + ",'" + phone + "','" + uuid + "'),");
    row++;
   }
   boolean b = userDao.insertMore1(suffix.substring(0, suffix.length()-1),j);
   if(!b) {
    System.out.println("出错了----");
    System.exit(0);
   }else {
    // 清空上一次添加的数据
    suffix = new StringBuffer();
   }
  }
  long end = System.currentTimeMillis();
  System.out.println("生成1000万条数据共花费"+(end-start)+"毫秒");
 }

public boolean insertMore1(String sql_suffix,long flag) {
  String sql_prefix = "insert into tuser(id,name,sex,phone,password) values ";
  return affectRowMore1(sql_prefix + sql_suffix,flag);
 }