依赖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();
try {
StringBuffer suffix;
connection.setAutoCommit(false);
PreparedStatement pst = connection.prepareStatement(" ");
for (int i = 1; i <= 100; i++) {
System.out.println("插入批次:" + i);
suffix = new StringBuffer();
for (int j = 1; j <= 100000; j++) {
suffix.append(sqlValuesBuild.build(i * j)).append(",");
}
String sql = prefix + tableName + cols + VALUES + suffix.substring(0, suffix.length() - 1);
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')");
}