employee.getPhone()); ps.addBatch();}ps.executeBatch();ps.close();connection.close();
Checkout the above code. Beautiful. We used java.sql.PreparedStatement and added insert query in the batch. This is the solution you must implement in your batch insert logic, instead of above Statementone.
Still there is one problem with this solution. Consider a scenario where you want to insert half million records into database using batch. Well, that may generate OutOfMemoryError:
java.lang.OutOfMemoryError:
Java heap space com.mysql.jdbc.ServerPreparedStatement$BatchedBindValues.
This is because you are trying to add everything in one batch and inserting once. Best idea would be to execute batch itself in batch. Check out the below solution.
Smart Insert: Batch within Batch
This is a simplest solution. Consider a batch size like 1000 and insert queries in the batches of 1000 queries at a time.
String
sql = "insert
into employee (name, city, phone) values ( , , )";Connection
connection = new
getConnection();PreparedStatement
ps = connection.prepareStatement(sql); final
int
batchSize = 1000;int
count = 0; for
(Employee employee: employees) { ps.setString(1,
employee.getName()); ps.setString(2,
employee.getCity()); ps.setString(3,
employee.getPhone()); ps.addBatch(); if(++count
% batchSize == 0)
{ ps.executeBatch(); }}ps.executeBatch();
//
insert remaining recordsps.close();connection.close();
This would be the idea solution. This avoids SQL Injection and also takes care of out of memory issue. Check how we have incremented a counter count and once it reaches batchSize which is 1000, we callexecuteBatch().
SQL批处理是JDBC性能优化的重要武器，经本人研究总结，批处理的用法有三种。
package lavasoft.jdbctest;
import lavasoft.common.DBToolkit;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/**
* JDBC的批量操作三种方式
*/
public class BatchExeSQLTest {
public static void main(String[] args) {
exeBatchStaticSQL();
}
/**
* 批量执行预定义模式的SQL
*/
public static void exeBatchParparedSQL() {
Connection conn = null;
try {
conn = DBToolkit.getConnection();
String sql = "insert into testdb.book (kind, name) values ( , )";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "java");
pstmt.setString(2, "jjjj");
pstmt.addBatch(); //添加一次预定义参数
pstmt.setString(1, "ccc");
pstmt.setString(2, "dddd");
pstmt.addBatch(); //再添加一次预定义参数
//批量执行预定义SQL
pstmt.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBToolkit.closeConnection(conn);
}
}