JDBC 批处理 SQL 语句
首先在 jdbc 的 url 中加上 rewriteBatchedStatements=true，只有开启了这个 Mysql 才会执行批处理，否则还是一条一条执行
Statement 不使用 Batch
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test; import java.io.InputStream;
import java.sql.*;
import java.util.Date;
import java.util.Properties; public class BatchTest { private Connection connection;
private PreparedStatement preparedStatement;
private Statement statement; @BeforeEach
public void start() throws Exception {
Properties properties = new Properties();
InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(in); String driver = properties.getProperty("driver");
String jdbcUrl = properties.getProperty("jdbcUrl");
String user = properties.getProperty("user");
String password = properties.getProperty("password"); Class.forName(driver); connection = DriverManager.getConnection(jdbcUrl, user, password);
} @AfterEach
public void end() throws Exception {
if (statement != null) {
statement.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} @Test
public void testStatement() {
String sql = null;
try {
connection.setAutoCommit(false);
statement = connection.createStatement();
long begin = System.currentTimeMillis();
for (int i = 0; i < 1000; i++) {
sql = "INSERT INTO batch_test (name) VALUES(" + i + ")";
statement.execute(sql);
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
}
Statement 使用 Batch
@Test
public void testBatchWithStatement() {
String sql = null;
try {
connection.setAutoCommit(false);
statement = connection.createStatement();
long begin = System.currentTimeMillis();
for (int i = 0; i < 1000; i++) {
sql = "INSERT INTO batch_test (name) VALUES('" + i + "')";
statement.addBatch(sql);
}
statement.executeBatch();
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
PreparedStatement 不使用 Batch
@Test
public void testPreparedStatement() {
String sql = "INSERT INTO batch_test (name) VALUES(?)";
try {
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql);
long begin = System.currentTimeMillis();
for (int i = 0; i < 1000; i++) {
preparedStatement.setString(1, "name_" + i);
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
PreparedStatement 使用 Batch
@Test
public void testBatchWithPreparedStatement() {
String sql = "INSERT INTO batch_test (name) VALUES(?)";
try {
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(sql);
long begin = System.currentTimeMillis();
for (int i = 0; i < 1000; i++) {
preparedStatement.setString(1, "name_" + i);
preparedStatement.addBatch();
// 添加SQL到一定数量就统一的执行一次，清空之前添加的 SQL
if ((i + 1) % 300 == 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
// 总条数不是批量数值的整数倍需要再额外的执行一次
if (1000 % 300 != 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
long end = System.currentTimeMillis();
System.out.println("Time: " + (end - begin));
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}