jdbc 批量操作

假設我們要在 table 新增多個 user
原本的寫法像這樣

Statement statement = connection.createStatement();
for (User user : users) {
    String name = user.getName();
    String gender = user.getGender();
    int age = user.getAge();
    
    String sql = "INSERT INTO user (name, gender, age) VALUES ('" + name + "', '" + gender + "', " + age + ")";
    statement.executeUpdate(sql);
}

executeUpdate() 在迴圈裡會造成頻繁讀寫資料庫
徒增效能損耗
因此可以用批量處理優化
先在迴圈中 addBatch()
再使用 statement.executeBatch() 一口氣執行

Statement statement = connection.createStatement();
connection.setAutoCommit(false);
for (User user : users) {
    String name = user.getName();
    String gender = user.getGender();
    int age = user.getAge();
    
    String sql = "INSERT INTO user (name, gender, age) VALUES ('" + name + "', '" + gender + "', " + age + ")";
    statement.addBatch(sql);
}
int[] count = statement.executeBatch();
connection.commit();

另一種方法是使用 PreparedStatement 物件
此方式更符合物件導向的邏輯
字串拼接麻煩且容易出錯
參數替換的方式解決了這個困擾

String sql = "INSERT INTO user (name, gender, age) VALUES (?, ?, ?)";
PreparedStatement pStatement = connection.prepareStatement(sql);
connection.setAutoCommit(false);
for (User user : users) {
    String name = user.getName();
    String gender = user.getGender();
    int age = user.getAge();
    
    pStatement.setString(1, name);
    pStatement.setString(2, gender);
    pStatement.setInt(3, age);
    pStatement.addBatch();
}
int[] count = pStatement.executeBatch();
connection.commit();
Tags: , ,