100万级数据插入测试

/** * */ package com.spring.data; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Component; /** * @author yiyi * */ public class DataHandler { private static final String DRIVER = "com.mysql.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost:3306/big_data"; private static final String USER = "root"; private static final String PWD = "admin"; // conn private static Connection conn = null; static { // load driver try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL, USER, PWD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } // public static void showDbs() { String sql = "show databases"; try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rst = pst.executeQuery(); while (rst.next()) { System.out.println(rst.getString(1)); } rst.close(); pst.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void insert() { // 开时时间 Long begin = new Date().getTime(); // sql前缀 String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES "; try { // 保存sql后缀 StringBuffer suffix = new StringBuffer(); // 设置事务为非自动提交 conn.setAutoCommit(false); // Statement st = conn.createStatement(); // 比起st，pst会更好些 PreparedStatement pst = conn.prepareStatement(""); // 外层循环，总提交事务次数 for (int i = 1; i <= 100; i++) { // 第次提交步长 for (int j = 1; j <= 10000; j++) { // 构建sql后缀 suffix.append("(" + j * i + ", SYSDATE(), " + i * j * Math.random() + "),"); } // 构建完整sql String sql = prefix + suffix.substring(0, suffix.length() - 1); // 添加执行sql pst.addBatch(sql); // 执行操作 pst.executeBatch(); // 提交事务 conn.commit(); // 清空上一次添加的数据 suffix = new StringBuffer(); } // 头等连接 pst.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } // 结束时间 Long end = new Date().getTime(); // 耗时 System.out.println("cast : " + (end - begin) / 1000 + " ms"); } public static void insertRelease() { Long begin = new Date().getTime(); String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)"; try { conn.setAutoCommit(false); PreparedStatement pst = conn.prepareStatement(sql); for (int i = 1; i <= 100; i++) { for (int k = 1; k <= 10000; k++) { pst.setLong(1, k * i); pst.setLong(2, k * i); pst.addBatch(); } pst.executeBatch(); conn.commit(); } pst.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } Long end = new Date().getTime(); System.out.println("cast : " + (end - begin) / 1000 + " ms"); } public static void main(String[] args) { insertRelease(); // insert(); } }