本篇内容主要讲解“MySQL JDBC Statement.executeBatch举例分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL JDBC Statement.executeBatch举例分析”吧!
计算
安全
数据库
网络和加速
企业服务
mport java.sql.*;
/**
* @Author cherrishccl
* @Date 2020/9/4 17:01
* @Version 1.0
* @Description
* 用JDBC执行批量提交正确方式:
* 1. Statement stmt = conn.createStatement();
* for(int i = 0; i < 10000; i++){
* String batchSql = "insert into t_user(name, sex, age) values('name', 'F', 22)";
* stmt.addBatch(batchSql);
* }
* stmt.executeBatch();
*
* 2. 连接参数添加&rewriteBatchedStatements=true
* PreparedStatement stmt = conn.prepareStatement("insert into t_user(name, sex, age) values (?, ?, ?)");
* for(int i = 0; i < 10000; i++){
* stmt.setString(1, "name");
* stmt.setString(2, "F");
* stmt.setString(3, 22);
* stmt.addBatch();
* }
*/
public class BatchTest1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false" +
"&allowMultiQueries=true&rewriteBatchedStatements=true";
String url1 = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&allowMultiQueries=true";
String username = "root";
String password = "123456";
/************************插入10000条数据**************************************/
// url 和 url1 效率差不多
// batch2(driver, url, username, password); // 平均耗时4175
// batch2(driver, url1, username, password); // 平均耗时4156
// url 和 url1 效率差不多
// batch3(driver, url, username, password); // 平均耗时1951
// batch3(driver, url1, username, password); // 平均耗时1873
/***********************插入100条数据***************************************/
// url 和 url1 效率差不多
// batch4(driver, url, username, password); // 平均耗时7730
// batch4(driver, url1, username, password); // 平均耗时6208
// url 和 url1 效率差不多
// batch5(driver, url, username, password); // 平均耗时6096
// batch5(driver, url1, username, password); // 平均耗时6056
// url 和 url1 效率差不多
// batch6(driver, url, username, password); // 平均耗时6466
// batch6(driver, url1, username, password); // 平均耗时6310
// url 和 url1 效率差不多
// batch7(driver, url, username, password); // 平均耗时5969
// batch7(driver, url1, username, password); // 平均耗时6201
/***********************插入10000条数据***************************************/
// batch7(driver, url, username, password); // 平均耗时990
// batch7(driver, url1, username, password); // 平均耗时>>>>>120000
// batch8(driver, url, username, password); // 平均耗时602
// batch8(driver, url1, username, password); // 平均耗时>>>>>120000
}
private static Connection connect(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
Class.forName(driver);
return DriverManager.getConnection(url, username, password);
}
private static void batch2(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
Statement stmt = conn.createStatement();
long start = System.currentTimeMillis();
for(int i = 0; i < 10000; i++){
String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" +
"'name" + i + "'," + i + "," + i + "," + i +
")";
stmt.addBatch(batchSql);
}
stmt.executeBatch();
// 4175
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch3(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
for(int i = 0; i < 10000; i++){
String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" +
"'name" + i + "'," + i + "," + i + "," + i +
")";
stmt.addBatch(batchSql);
}
stmt.executeBatch();
conn.commit();
// 1398
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch4(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
Statement stmt = conn.prepareStatement("select * from dual");
long start = System.currentTimeMillis();
for(int i = 0; i < 100; i++){
String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" +
"'name" + i + "'," + i + "," + i + "," + i +
")";
stmt.addBatch(batchSql);
}
stmt.executeBatch();
// 7730
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch5(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
Statement stmt = conn.prepareStatement("select * from dual");
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
for(int i = 0; i < 100; i++){
String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" +
"'name" + i + "'," + i + "," + i + "," + i +
")";
stmt.addBatch(batchSql);
}
stmt.executeBatch();
conn.commit();
// 7730
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch6(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
PreparedStatement stmt = conn.prepareStatement("select * from dual");
long start = System.currentTimeMillis();
for(int i = 0; i < 100; i++){
String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" +
"'name" + i + "'," + i + "," + i + "," + i +
")";
stmt.addBatch(batchSql);
}
stmt.executeBatch();
// 6162
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch7(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
PreparedStatement stmt = conn.prepareStatement("select * from dual");
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
for(int i = 0; i < 100; i++){
String batchSql = "insert into t_user(user_name, age, salary, max_size) values (" +
"'name" + i + "'," + i + "," + i + "," + i +
")";
stmt.addBatch(batchSql);
}
stmt.executeBatch();
conn.commit();
// 6162
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch7(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
PreparedStatement stmt = conn.prepareStatement("insert into t_user(user_name, age, salary, max_size) values (?, ?, ?, ?)");
long start = System.currentTimeMillis();
for(int i = 0; i < 10000; i++){
stmt.setInt(1, i + 1);
stmt.setInt(2, i + 1000);
stmt.setInt(3, i + 1);
stmt.setInt(4, i);
stmt.addBatch();
}
stmt.executeBatch();
// 6162
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
private static void batch8(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
Connection conn = connect(driver, url, username, password);
PreparedStatement stmt = conn.prepareStatement("insert into t_user(user_name, age, salary, max_size) values (?, ?, ?, ?)");
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
for(int i = 0; i < 10000; i++){
stmt.setInt(1, i + 1);
stmt.setInt(2, i + 1000);
stmt.setInt(3, i + 1);
stmt.setInt(4, i);
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
// 6162
System.out.println("耗时======>" + (System.currentTimeMillis() - start));
}
}到此，相信大家对“MySQL JDBC Statement.executeBatch举例分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
原文链接：https://my.oschina.net/u/574036/blog/4603288
