Java批量插入MySQL-优快云博客

依赖jar包

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

java插入工具类


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class MySqlBigDataInsert {

    private static String prefix = "INSERT INTO ";
    private static String VALUES = " VALUES ";

    public interface SqlValuesBuild {
        String build(int index);
    }
    public static void insert(String url, String username, String password,
                              String tableName,
                              String cols,
                              SqlValuesBuild sqlValuesBuild) {
        Connection connection;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException se) {
            System.out.println("数据库连接失败");
            return;
        }
        // 开始时间
        Long begin = new Date().getTime();
        // sql前缀
        try {
            // 保存sql后缀
            StringBuffer suffix;
            // 设置事务为非自动提交
            connection.setAutoCommit(false);
            // 比起st，pst会更好些+
            PreparedStatement pst = connection.prepareStatement(" ");//准备执行语句
            // 外层循环，总提交事务次数
            for (int i = 1; i <= 100; i++) {
                System.out.println("插入批次:" + i);
                suffix = new StringBuffer();
                // 第j次提交步长
                for (int j = 1; j <= 100000; j++) {
                    suffix.append(sqlValuesBuild.build(i * j)).append(",");
                }
                // 构建完整SQL  
                String sql = prefix + tableName + cols + VALUES + suffix.substring(0, suffix.length() - 1);
                // 添加执行SQL
                pst.addBatch(sql);
                // 执行操作
                pst.executeBatch();
                // 提交事务
                connection.commit();
                // 清空上一次添加的数据
            }
            // 头等连接
            pst.close();
            connection.close();
        } catch (SQLException e) {
            System.out.println("sql异常");
        }
        // 结束时间
        Long end = new Date().getTime();
        // 耗时
        System.out.println("插入完成,花费时间：" + (end - begin) / 1000 + " s");
    }
}

使用示例

public static void main(String[] args) {
        MySqlBigDataInsert.insert("jdbc:mysql://localhost:3306/bond",
                "root",
                "123456",
                "table1",
                "(name,age,address)",
                (int index) -> "('test',12,'address')");
    }