使用 Java 編寫 H2 的預存程序

1. 簡介

在本教程中，我們將學習如何用 Java 編寫用於 H2 資料庫引擎的預存程序。我們將了解它們是什麼、如何創建它們以及如何使用它們。

2.什麼是預存程序？

預存程序是許多資料庫引擎支援的機制，允許我們在資料庫中建立自己的自訂功能。

與其他程式語言一樣，我們可以建立一個新的流程，該流程具有名稱和一組輸入參數，用於執行必要的功能並傳回適當的結果。然後，我們將這些結果直接儲存在資料庫中。

在 H2 資料庫引擎中，這些被稱為使用者定義函數，儘管它們是同一個概念，只是名稱不同。此名稱用於將它們與 H2 已經提供給我們的內建函數（例如UPPER()或DATEDIFF()區分開來。

我們會看到大多數資料庫引擎都使用「預存程序」這個名稱，因此我們只需要記住，在 H2 中，預存程序和使用者定義函數是同一回事。這使我們能夠在 H2 中使用使用者定義函數來取代我們在其他資料庫引擎中使用預存程序實現的功能。

它具有廣泛的用途，包括預存程序的所有正常用途，但如果我們將 H2 用於測試目的，也允許我們用存根版本取代它們。

3. 在 H2 中使用函數

我們可以在 H2 的 SQL 查詢中直接使用函數。例如，我們常會看到以下 SQL 語句：

SELECT * FROM posts WHERE UPPER(title) = UPPER(?)

這將UPPER()函數應用於title列和綁定參數，從而有效地使查詢不區分大小寫。

這同樣適用於使用者定義函數。一旦我們創建它們，它們的行為就像內建函數一樣，我們可以以相同的方式使用它們：

SELECT * FROM numbers WHERE IS_PRIME(number) = TRUE

這裡， IS_PRIME()是一個使用者定義函數。它不是 H2 提供的。相反，我們必須自己編寫它。然而，它的使用方式與內建函數完全相同。

4.建立使用者定義函數

現在我們已經了解如何使用使用者定義函數，接下來我們需要真正地建立自己的函數。在 H2 中有兩種方法可以實現這一點：我們可以將函數的原始程式碼直接提供給資料庫，或者我們可以用 Java 編寫函數並告知 H2 它的存在。

4.1. 提供原始碼

我們在資料庫中使用CREATE ALIAS指令建立一個新的使用者定義函數。此命令接受函數名稱和一個作為函數原始碼的字串：

CREATE ALIAS SAY_HELLO AS '

 String sayHello() {

 return "Hello, World!";

 }

 ';

這將創建一個使用者定義函數，該函數始終傳回字串“Hello, World!”.完成此操作後，我們可以按預期呼叫函數：

SELECT SAY_HELLO();

然後執行我們的新函數並傳回其結果。

要建立使用者定義函數，我們只需提供一個 Java 方法定義。 H2 會自動將其包裝到適當的樣板檔案中，建立一個可編譯的類，然後將其編譯為類別路徑上可供呼叫的真實 Java 類別。這樣，我們就可以引用其他類別：

CREATE ALIAS JAVA_TIME_NOW AS '

 String javaTimeNow() {

 return java.time.Instant.now().toString();

 }

 ';

這引用了 JVM 中的java.time.Instant類，但對於類路徑上的任何其他類（包括我們自己的程式碼和相依性），也可以執行相同的操作。唯一的要求是，載入了 H2 的類別載入器必須能夠存取我們要呼叫的類別。

然而，如果每個類別都需要完全限定，可能會變得很麻煩。因此，H2 允許我們將函數分成兩部分，並使用字串@CODE將兩者分隔開來：

CREATE ALIAS JAVA_TIME_NOW AS '

 import java.time.Instant;

 @CODE

 String javaTimeNow() {

 return Instant.now().toString();

 }

 ';

第一部分位於類別定義上方，允許我們包含 import 語句。第二部分是我們已經看過的函數定義。這樣我們就可以編寫與之前相同的程式碼，而且更加簡潔，因為我們可以像往常一樣導入其他類別。

4.2. 預編譯程式碼

除了直接在使用者定義函數中指定函數的 Java 程式碼外，我們還可以建立一個指向類別路徑中已存在程式碼的 Java 程式碼。這意味著我們可以以任何我們想要的方式編寫程式碼，只要它最終能夠被 H2 存取即可。這裡唯一需要注意的是，目標方法必須是靜態的，而該方法和包含它的類別都必須是 public 的。

我們再次使用CREATE ALIAS語句來建立這種形式的使用者定義函數，只是這次我們指向完全限定的函數名稱而不是提供原始碼：

CREATE ALIAS JAVA_RANDOM FOR "java.lang.Math.random";

這會產生與我們自己編寫的程式碼相同的結果，並且我們可以以相同的方式呼叫它：

SELECT JAVA_RANDOM();

這裡，我們指向了 Java 標準函式庫中的方法 – Math.random() 。然而，我們可以輕鬆地指向類路徑上任何位置的任何方法，包括我們自己的程式碼：

CREATE ALIAS HELLO FOR "com.baeldung.h2functions.CompiledFunctionUnitTest.hello";

這樣，我們就可以透過使用者定義函數執行任何可以透過靜態方法執行的操作 - 例如，查詢遠端服務、存取 Spring bean，任何符合我們需求的操作。

5.方法參數

我們已經了解如何編寫自訂函數並在查詢中使用它們。然而，通常情況下，我們希望能夠為這些函數提供值，以便它們可以發揮作用。

很多時候，這完全符合我們的預期。只要我們使用的參數類型遵循與 JDBC 相同的資料類型轉換規則，它們就能正常運作：

CREATE ALIAS IS_ODD AS '

 Boolean isOdd(Integer value) {

 if (value == null) {

 return null;

 }

 return (value % 2) != 0;

 }

 ';

此函數接受與Integer相容的任何類型的單一參數並傳回Boolean ：

SELECT IS_ODD(5); -- True

值得注意的是，由於我們使用的是盒裝類型，所以我們也可以使用NULL值來調用，我們需要處理這個問題：

SELECT IS_ODD(NULL); -- NULL

如果我們願意，我們可以接受原始類型：

CREATE ALIAS IS_ODD AS '

 boolean isOdd(int value) {

 return (value % 2) != 0;

 }

 ';

這樣做意味著我們永遠不能使用NULL值來呼叫該函數。如果發生這種情況，H2 將不會呼叫該函數，而是傳回NULL 。

5.1. 存取資料庫

作為一種特殊情況，我們也可以接受java.sql.Connection類型的方法參數。這必須是該方法的第一個參數，並將接收與目前查詢相同的資料庫連線。這樣我們就可以在使用者定義函數內部與資料庫進行互動。

讓我們來看一個例子：

CREATE ALIAS SUM_BETWEEN AS '

 int sumBetween(Connection con, int lower, int higher) throws SQLException {

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

 ResultSet rs = statement.executeQuery("SELECT number FROM numbers");

 int result = 0;

 while (rs.next()) {

 int value = rs.getInt(1);

 if (value > lower && value < higher) {

 result += value;

 }

 }

 return result;

 }

 }

 ';

此使用者定義函數執行另一個查詢，並對傳回的結果執行邏輯運算。在本例中，它從表中選擇一組數字，並計算所有落在特定範圍內的數字總和。

值得注意的是，我們獲得了與呼叫使用者定義函數相同的連接。這意味著它參與了同一個事務，並且可以像函數外部的程式碼一樣存取所有內容。

6. 例外

在某些情況下，我們的使用者定義函數可能需要拋出異常來指示錯誤。 H2允許我們這樣做，並且按照預期進行處理。

我們可以簡單地編寫使用者定義函數來拋出我們需要的任何異常，與任何其他 Java 程式碼完全相同：

CREATE ALIAS EXCEPTIONAL AS '

 int exceptional() {

 throw new IllegalStateException("Oops");

 }

 ';

由於這是標準 Java 程式碼，我們需要在方法簽名中聲明我們想要拋出的任何已檢查例外：

CREATE ALIAS EXCEPTIONAL AS '

 import java.io.IOException;

 @CODE

 int exceptional() throws IOException {

 throw new IOException("Oops");

 }

 '

當發生這種情況時，H2 會自動捕獲異常並將其包裝在SQLException中，以便它能夠正確地遇到 JDBC API：

SQLException exception = assertThrows(SQLException.class, () -> statement.executeQuery("SELECT EXCEPTIONAL()"));

 assertTrue(exception.getCause() instanceof IllegalStateException);

 assertEquals("Oops", exception.getCause().getMessage());

這裡我們可以看到， SQLException的原因正是我們使用者定義函數拋出的。

7. 結論

本文簡要介紹如何在 H2 資料庫中編寫自訂函數以及如何使用它們。下次您需要在 H2 資料庫中編寫自訂程式碼時，不妨嘗試一下。

與往常一樣，本文中的所有範例都可以在 GitHub 上找到。