溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“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舉例分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
