在 JDBC 的一个查询中执行多条 SQL 语句
1、概览
在使用 Java 和 JDBC 处理数据库时，有时我们需要将多条 SQL 语句作为单个操作来执行。这可以提高应用性能、确保原子性或更有效地管理复杂的工作流。
本文将带你了解如何使用 Statement 对象、批处理和存储过程来演示如何高效执行多个 SQL 查询。
在本教程中，我们将使用 MySQL 作为数据库。
2、JDBC 和数据库设置
2.1、Maven 依赖
首先，在 pom.xml 中添加以下依赖项，以包含 MySQL JDBC 驱动：
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId
<version>8.0.33</version>
</dependency>
2.2、数据库配置
在本例中，我们要创建一个名为 user_db 的 MySQL 数据库和一个名为 users 的表，并为其执行多个 insert 查询：
CREATE DATABASE user_db;
USE user_db;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
数据库设置完毕后，我们就可以使用 JDBC 运行多条 SQL 语句了。
3、在一次执行中运行多条 SQL 语句
在执行查询之前，必须确保数据库连接配置正确，以允许多语句。具体来说，对于 MySQL，连接 URL 应包含 allowMultiQueries=true 属性，以启用此功能。首先，设置连接：
public Connection getConnection() throws SQLException {
String url = "jdbc:mysql://localhost:3306/user_db?allowMultiQueries=true";
String username = "username";
String password = "password";
return DriverManager.getConnection(url, username, password);
}
此配置允许 MySQL 在一次执行中接受多个以分号分隔的 SQL 语句。默认情况下，除非连接字符串包含 allowMultiQueries=true 属性，否则 MySQL 不允许在一次 execute() 调用中执行多个语句。
在 JDBC 中执行多条 SQL 语句有三种主要方法。
3.1、使用 Statement 对象
通过 Statement 对象，我们可以将多个 SQL 查询合并为一个字符串，并用分号分隔，从而执行多个 SQL 查询。
如下，在新建的 users 表中插入多条记录：
public boolean executeMultipleStatements() throws SQLException {
String sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');" +
"INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');";
try (Statement statement = connection.createStatement()) {
statement.execute(sql);
return true;
}
}
再添加一个测试，验证上例中使用单个 Statement 对象执行多个 SQL 语句的功能：
@Test
public void givenMultipleStatements_whenExecuting_thenRecordsAreInserted() throws SQLException {
boolean result = executeMultipleStatements(connection);
assertTrue(result, "The statements should execute successfully.");
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
"SELECT COUNT(*) AS count FROM users WHERE name IN ('Alice', 'Bob')");) {
resultSet.next();
int count = resultSet.getInt("count");
assertEquals(2, count, "Two records should have been inserted.");
}
}
注意，在一次 execute() 调用中执行多条 SQL 语句的情况因数据库系统而异。有些数据库本机支持这一功能，而有些数据库则需要额外配置或根本不支持。
如上例，我们可以看到 MySQL 数据库支持多语句执行，但需要在连接 URL 中启用连接字符串属性 allowMultiQueries=true。PostgreSQL 和 SQL Server 数据库允许在一次 execute() 调用中执行多个 SQL 语句，无需额外配置。
其他数据库，如 Oracle 和 H2 数据库，不支持在一次 execute() 调用中执行多条语句。必须多次调用 execute() 或使用 addBatch() 进行批处理，才能单独执行每条 SQL 语句。
3.2、使用批处理
当多个查询不需要作为单个原子单元执行时，批处理是一种更有效的执行方式。
public int[] executeBatchProcessing() throws SQLException {
try (Statement statement = connection.createStatement()) {
connection.setAutoCommit(false);
statement.addBatch("INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com')");
statement.addBatch("INSERT INTO users (name, email) VALUES ('Diana', 'diana@example.com')");
int[] updateCounts = statement.executeBatch();
connection.commit();
return updateCounts;
}
}
测试：
@Test
public void givenBatchProcessing_whenExecuting_thenRecordsAreInserted() throws SQLException {
int[] updateCounts = executeBatchProcessing(connection);
assertEquals(2, updateCounts.length, "Batch processing should execute two statements.");
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
"SELECT COUNT(*) AS count FROM users WHERE name IN ('Charlie', 'Diana')");) {
resultSet.next();
int count = resultSet.getInt("count");
assertEquals(2, count, "Two records should have been inserted via batch.");
}
}
该测试可确保批处理正确执行所有 INSERT 的语句，并验证插入的行数。
3.3、处理存储过程
存储过程预编译 SQL 代码并将其存储在数据库中，这样就可以在一次调用中执行多条语句。
创建一个存储过程：
DELIMITER //
CREATE PROCEDURE InsertMultipleUsers()
BEGIN
INSERT INTO users (name, email) VALUES ('Eve', 'eve@example.com');
INSERT INTO users (name, email) VALUES ('Frank', 'frank@example.com');
END //
DELIMITER ;
运行这个存储过程：
public boolean callStoredProcedure() throws SQLException {
try (CallableStatement callableStatement = connection.prepareCall("{CALL InsertMultipleUsers()}")) {
callableStatement.execute();
return true;
}
}
添加一个测试，验证存储过程是否按预期将记录插入数据库：
@Test
public void givenStoredProcedure_whenCalling_thenRecordsAreInserted() throws SQLException {
boolean result = callStoredProcedure(connection);
assertTrue(result, "The stored procedure should execute successfully.");
try (Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(
"SELECT COUNT(*) AS count FROM users WHERE name IN ('Eve', 'Frank')");) {
resultSet.next();
int count = resultSet.getInt("count");
assertEquals(2, count, "Stored procedure should have inserted two records.");
}
}
这种方法可确保在一次存储过程调用中高效执行多个插入操作。此外，它还能减少多个单独查询的开销，从而提高性能。
3.4、执行多个 Select 语句
在上例中，我们演示了执行多条 SQL insert 语句。现在来看看如何使用 JDBC 在一次调用中执行多个 SQL select 语句。它使用 statement.getMoreResults() 方法遍历多个结果集。当 SQL 查询包含多个用分号分隔的选择语句，而我们又想单独处理每个结果集时，这种方法就非常有用。
public List<User> executeMultipleSelectStatements() throws SQLException {
String sql = "SELECT * FROM users WHERE email = 'alice@example.com';" +
"SELECT * FROM users WHERE email = 'bob@example.com';";
List<User> users = new ArrayList<>();
try (Statement statement = connection.createStatement()) {
statement.execute(sql); // 在此，执行多个查询
do {
try (ResultSet resultSet = statement.getResultSet()) {
while (resultSet != null && resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
users.add(new User(id, name, email));
}
}
} while (statement.getMoreResults());
}
return users;
}
statement.getMoreResults() 方法会将光标移动到序列中的下一个结果，从而允许我们按顺序处理多个结果集。如果下一个结果是一个 ResultSet，则返回 true；如果是更新计数或没有更多结果可用，则返回 false。
测试，以验证多个 SQL select 语句的执行及其结果：
@Test
public void givenMultipleSelectStatements_whenExecuting_thenCorrectUsersAreFetched()
throws SQLException {
MultipleSQLExecution execution = new MultipleSQLExecution(connection);
execution.executeMultipleStatements();
List<User> users = execution.executeMultipleSelectStatements();
// 验证是否正好获取了两个用户，并且他们的姓名与预期的相符
assertThat(users)
.hasSize(2)
.extracting(User::getName)
.containsExactlyInAnyOrder("Alice", "Bob");
}
上述方法和相应的单元测试简化了在一次数据库调用中处理多个查询的过程，确保了执行效率和结果处理的简便性。
4、总结
本文介绍了在 JDBC 中执行多条 SQL 语句的三种不同方法：使用 Statement 对象、批处理和存储过程。每种方法都有自己的应用场景，取决于优先考虑的是性能还是可维护性。
Ref：https://www.baeldung.com/java-jdbc-execute-multiple-statements
