JDBC批处理
一.JDBC批处理概述
- 连续执行多条SQL语句,若逐条执行这些SQL语句,效率会很低。为此,JDBC提供了批处理机制,用于执行多条SQL语句。
二.Statement实现批处理
1.批处理方法
方法 | 方法描述 |
---|
Statement.addBatch( ) | 添加SQL语句 |
Statement.executeBatch( ) | 执行批处理 |
Statement.clearBatch( ) | 清除批处理 |
2.使用示例
(1)JDBCUtils类
package cn.com.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
// 加载驱动并建立数据库连接
public static Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
String databaseUrl = "jdbc:mysql://localhost:3306/mydb";
String username = "root";
String password = "test";
Connection connection = DriverManager.getConnection(databaseUrl, username, password);
return connection;
}
// 关闭数据库连接释放资源
public static void release(Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
}
// 关闭数据库连接释放资源
public static void release(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection = null;
}
}
// 关闭数据库连接释放资源
public static void release(Statement statement, Connection connection) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection = null;
}
}
// 关闭数据库连接释放资源
public static void release(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
resultSet = null;
}
release(statement, connection);
}
}
(2)TestStatementBatch测试类
package cn.com.demo;
import java.sql.Connection;
import java.sql.Statement;
public class TestStatementBatch {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
connection = JDBCUtils.getConnection();
statement = connection.createStatement();
String sql1 = "DROP TABLE IF EXISTS student";
String sql2 = "CREATE TABLE student (studnet_num int,name varchar(20))";
String sql3 = "INSERT INTO student VALUES(1,'dodo')";
String sql4 = "INSERT INTO student VALUES(2,'xixi')";
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);
statement.addBatch(sql4);
statement.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(null, statement, connection);
}
}
}
三.PreparedStatement实现批处理
1.创建表
-- 创建表
DROP TABLE IF EXISTS student;
CREATE TABLE worker(
studnet_num INT,
name VARCHAR(20)
);
2.TestPreparedStatement测试类
package cn.com.demo;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class TestPreparedStatement{
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql = "INSERT INTO worker VALUES(?,?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 123; i++) {
preparedStatement.setInt(1, i);
preparedStatement.setString(2, "name" + i);
preparedStatement.addBatch();
// 为防止内存溢出,每20条执行一次批处理
if (i % 20 == 0) {
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
//将剩下的未处理SQL执行批处理
preparedStatement.executeBatch();
preparedStatement.clearBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.release(null, preparedStatement, connection);
}
}
}
四.Statement与PreparedStatement批处理比较
- 比较Statement,使用PreparedStatement实现批处理不仅安全而且效率更高。