如何在 JDBC 中將多個 SQL 語句作為一個語句執行

1. 概述

當使用 Java 和 JDBC 處理資料庫時，有時我們需要將多個 SQL 語句作為單一操作執行。它可以幫助我們**提高應用程式效能，確保原子性，或更有效地管理複雜的工作流程。**

在本教程中，我們將探討在 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表，並為其執行多個插入查詢：

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. 在 Java 中將多個查詢作為一個查詢執行

在執行多個查詢之前，我們必須確保資料庫連線配置正確以允許多個語句。具體來說，對於 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 語句。預設情況下，MySQL 不允許在單一execute()呼叫中執行多語句，除非連接字串包含allowMultiQueries=true屬性.

在 JDBC 中執行多個 SQL 語句有三種主要方法。

3.1.使用Statement對象

[Statement](https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Statement.html)物件允許我們透過將多個 SQL 查詢組合成以分號分隔的單一字串來執行它們。以下是向新建立的users表插入多筆記錄的範例：

public boolean executeMultipleStatements() throws SQLException {

 String sql = "INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');" +

 "INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');";



 try (Statement statement = connection.createStatement()) {

 statement.execute(sql);

 return true;

 }

 }

我們還將新增一個測試來驗證使用單一 Statement 物件執行多個 SQL 語句的功能。使用的 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', '[email protected]')");

 statement.addBatch("INSERT INTO users (name, email) VALUES ('Diana', '[email protected]')");



 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.");

 }

 }

此測試可確保批次正確執行所有新增的語句並驗證插入的行數。

3.3.處理儲存過程

預存程序預先編譯 SQL 程式碼並將其儲存在資料庫中，允許它們在一次呼叫中執行多個語句。為了演示這一點，我們將建立一個預存程序：

DELIMITER //



 CREATE PROCEDURE InsertMultipleUsers()

 BEGIN

 INSERT INTO users (name, email) VALUES ('Eve', '[email protected]');

 INSERT INTO users (name, email) VALUES ('Frank', '[email protected]');

 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 查詢包含多個以分號分隔的select語句，而我們想要單獨處理每個結果集時，它很有用。

public List<User> executeMultipleSelectStatements() throws SQLException {

 String sql = "SELECT * FROM users WHERE email = '[email protected]';" +

 "SELECT * FROM users WHERE email = '[email protected]';";



 List<User> users = new ArrayList<>();



 try (Statement statement = connection.createStatement()) {

 statement.execute(sql); // Here we execute the multiple queries



 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();



 // Here we verify that exactly two users are fetched and their names match the expected ones

 assertThat(users)

 .hasSize(2)

 .extracting(User::getName)

 .containsExactlyInAnyOrder("Alice", "Bob");

 }

上述方法和相應的單元測試簡化了在單一資料庫呼叫中處理多個查詢，確保高效執行和更容易的結果處理。

4. 結論

在單一 JDBC 呼叫中執行多個 SQL 語句可以提高效能和程式碼可讀性。在本教程中，我們探討了在 JDBC 中執行多條 SQL 語句的三種不同方法：使用Statement物件、批次和預存程序。每種方法都有其用例，取決於效能還是可維護性是優先考慮的。

透過了解這些技術，我們可以更好地決策如何建立資料庫操作並優化應用程式以提高效率和清晰度。

與往常一樣，該實作的原始程式碼可在 GitHub 上取得。