在 H2 資料庫中執行 SQL 腳本

1.概述

在使用 Spring Boot 應用程式時，尤其是在整合測試中，使用 H2 記憶體資料庫提供了一種輕量級且快速的選項來模擬真實的資料庫互動。身為開發人員，我們經常需要在測試期間初始化模式、預先載入資料或執行自訂 SQL 腳本。

在本教學中，讓我們介紹在 Spring Boot 測試環境中使用 H2 執行 SQL 腳本的常用方法。

2. 在 JDBC URL 中指定腳本

H2 的一個便利功能是，它允許我們在初始化資料庫時直接從 JDBC URL 自動執行 SQL 腳本。接下來，讓我們透過一個範例來示範它的工作原理。

首先，讓我們在resources/sql目錄下建立一個 SQL 檔案init_my_db.sql ：

CREATE TABLE TASK_TABLE

 (

 ID INT PRIMARY KEY,

 NAME VARCHAR(255)

 );



 INSERT INTO TASK_TABLE (ID, NAME) VALUES (1, 'Start the application');

 INSERT INTO TASK_TABLE (ID, NAME) VALUES (2, 'Check if data table is filled');

該腳本創建了一張表並向其中插入了兩條記錄。接下來，讓我們看看如何在 Spring Boot 應用程式中指示 H2 資料庫自動執行此腳本。

讓我們建立一個簡單的 Spring Boot YAML 設定：

spring:

 datasource:

 driverClassName: org.h2.Driver

 url: jdbc:h2:mem:demodb;INIT=RUNSCRIPT FROM 'classpath:/sql/init_my_db.sql'

 username: sa

 password:

在此範例中，我們定義了一個連接到記憶體 H2 資料庫的資料來源。此外，我們在 JDBC URL 中新增了一個INIT子句：

INIT=RUNSCRIPT FROM 'classpath:/sql/init_my_db.sql'

RUNSCRIPT FROM <script_path>是用於執行給定腳本的 H2 資料庫指令。 因此，上面這行程式碼告訴 H2 在記憶體資料庫啟動後立即執行resource/sql/init_my_db.sql腳本。這是引導模式定義或種子測試資料的好方法。

接下來，讓我們建立一個測試來驗證應用程式啟動後表和預期資料是否存在：

List<String> expectedTaskNames = List.of("Start the application", "Check if data table is filled");

 List<String> taskNames = entityManager.createNativeQuery("SELECT NAME FROM TASK_TABLE ORDER BY ID")

 .getResultStream()

 .map(Object::toString)

 .toList();

 assertEquals(expectedTaskNames, taskNames);

如果我們執行這個測試，則測試通過。也就是說，該腳本在初始化期間已被 H2 執行。

值得注意的是， RUNSCRIPT指令能夠辨識 Java 的類別路徑。因此，我們在本例中使用了「 classpath:/sql/… 」。如果我們想要透過絕對路徑向 H2 提供腳本，可以使用「 file: 」前綴，例如「 RUNSCRIPT FROM 'file:/path/to/my/script.sql ' 」。

3. Spring Boot 內建腳本偵測： schema.sql和data.sql

我們了解到，如果我們將INIT=RUNSCRIPT FROM …子句新增至 H2 的 JDBC URL，H2 將自動執行腳本。

此外，當我們依賴 Spring Data JPA 約定時，Spring Boot 會自動從類別路徑中偵測並執行兩個特殊檔案：

• schema.sql – 用於定義資料庫模式，例如建立模式、表格或視圖
• data.sql – 用於填入初始數據

也就是說，如果我們的應用程式是基於 Spring Data JPA，我們可以將所需的 SQL 語句放入對應的檔案中。在建立資料來源後，Spring 會在應用程式啟動時自動執行這些語句。這樣，我們就不需要修改 H2 資料庫的 JDBC URL 了。

接下來我們透過一個例子來了解它是如何運作的。

首先，讓我們建立一個resources/schema.sql檔：

CREATE TABLE CITY

 (

 ID INT PRIMARY KEY ,

 NAME VARCHAR(255)

 );

這裡，我們創建了一個CITY表。然後，我們想要向CITY表中插入一些初始資料.因此，我們在resources/data.sql檔案中寫入一些INSERT SQL 語句：

INSERT INTO CITY (ID, NAME) VALUES (1, 'New York');

 INSERT INTO CITY (ID, NAME) VALUES (2, 'Hamburg');

 INSERT INTO CITY (ID, NAME) VALUES (3, 'Shanghai');

值得注意的是， Spring Boot 先執行schema.sql ，然後執行data.sql .

現在，讓我們編寫一個測試來驗證表格是否已建立並且 Spring Boot 是否自動插入了預期的資料：

List<String> expectedCityNames = List.of("New York", "Hamburg", "Shanghai");

 List<String> cityNames = entityManager.createNativeQuery("SELECT NAME FROM CITY ORDER BY ID")

 .getResultStream()

 .map(Object::toString)

 .toList();

 assertEquals(expectedCityNames, cityNames);

如果我們執行這個測試，它就通過了。因此，Spring Boot 按照我們的預期執行了這兩個腳本檔案。

有時，我們可能會想要將schema.sql和data.sql放在不同的目錄中，而不是放在 Classpath 的根目錄中。那麼，我們可以設定以下屬性來實現：

spring:

 sql:

 init:

 schema-locations: classpath:/the/path/to/schema.sql

 data-locations: classpath:/the/path/to/data.sql

schema.sql和data.sql的自動偵測功能預設為啟用。但是，如果需要，我們可以完全關閉此預設行為：

spring:

 sql:

 init:

 mode: never

這有助於防止在應用程式啟動期間載入不需要的 SQL。

4.透過EntityManager動態執行SQL腳本

有時，我們希望以程式設計方式從 Spring Boot 應用程式在 H2 資料庫上執行現有的 SQL 腳本，例如模擬特定的資料場景或重設資料庫狀態。然後，我們可以透過原生查詢執行 H2 的RUNSCRIPT指令。

像往常一樣，讓我們透過一個例子看看它是如何工作的。

為了簡單起見，我們將重複使用CITY表。讓我們建立resources/sql/add_cities.sql文件，將三個新的城市記錄插入CITY表：

INSERT INTO CITY (ID, NAME) VALUES (4, 'Paris');

 INSERT INTO CITY (ID, NAME) VALUES (5, 'Berlin');

 INSERT INTO CITY (ID, NAME) VALUES (6, 'Tokyo');

現在，讓我們在本機查詢中使用RUNSCRIPT執行此腳本：

entityManager.createNativeQuery("RUNSCRIPT FROM 'classpath:/sql/add_cities.sql'")

 .executeUpdate();

 List<String> expectedCityNames = List.of("New York", "Hamburg", "Shanghai", "Paris", "Berlin", "Tokyo");

 List<String> cityNames = entityManager.createNativeQuery("SELECT NAME FROM CITY ORDER BY ID")

 .getResultStream()

 .map(Object::toString)

 .toList();

 assertEquals(expectedCityNames, cityNames);

如果我們運行它，測試就通過了。

RUNSCRIPT指令是 H2 原生的指令，支援從類別路徑或檔案系統讀取 SQL 檔案。我們可以在測試方法或設定區塊中使用它來根據需要執行腳本。

值得注意的是，當我們使用RUNSCRIPT指令建立原生查詢時，應該會呼叫executeUpdate()方法來執行腳本。呼叫getResultList()或類似方法會引發異常，即使 SQL 腳本檔案包含SELECT語句。

5. 結論

在本文中，我們學習了在 H2 資料庫中執行腳本的幾種方法。無論是使用 JDBC URL 中的INIT子句初始化資料庫，透過EntityManager,還是依賴 Spring Boot 對schema.sql和data.sql,每種方法都能滿足測試生命週期中的特定需求。

採用這些策略使我們能夠編寫更清晰、更易於維護的整合測試，並最終編寫更強大的應用程式。

與往常一樣，範例的完整原始程式碼可在 GitHub 上找到。