在 Java PreparedStatements 中使用“LIKE”通配符

1. 概述

在使用關係型資料庫時，文字搜尋是最常見的需求之一。當需要進行部分匹配、前綴搜尋或基於使用者輸入的靈活篩選時，SQL 的LIKE運算子通常是我們首先想到的工具。同時，我們通常使用PreparedStatement來防止 SQL 注入並提高效能。

乍一看，將LIKE和PreparedStatement結合起來似乎很簡單，但很快就會出現一些微妙的問題，尤其是當使用者輸入本身包含通配符時。

在本教程中，我們將逐步講解在 PreparedStatement 中使用LIKE的常見模式PreparedStatements.我們將透過單元測試來示範哪些方法有效，哪些無效，以及如何正確處理特殊情況。

2. 準備範例

在討論解決方案之前，我們先照例舉個具體的例子。為了方便理解，我們從一個簡單的整合測試環境入手，使用 H2 記憶體資料庫、純 JDBC 和 JUnit 5。這樣的組合既保證了範例的輕量級，又使其行為與真實的 SQL 資料庫類似。

我們的測試類別初始化資料來源，建立表格，並插入幾行測試資料：

public class LikeUsageInPreparedStatementIntegrationTest {

 private static JdbcDataSource ds;



 @BeforeAll

 static void setup() throws SQLException {

 ds = new JdbcDataSource();

 ds.setURL("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;");

 ds.setUser("sa");

 ds.setPassword("");

 // first create the messages table

 try (Connection conn = ds.getConnection();

 PreparedStatement pstmt1 =

 conn.prepareStatement("CREATE TABLE MESSAGES (ID INT PRIMARY KEY, CONTENT VARCHAR(255))")) {

 pstmt1.execute();

 }

 // Let's insert some test data

 try (Connection conn = ds.getConnection();

 PreparedStatement stmt2 = conn.prepareStatement("INSERT INTO MESSAGES (ID, CONTENT) VALUES " +

 " (1, 'a hello message')," +

 " (2, 'a long hello message')," +

 " (3, 'We have spent 50% budget for marketing')," +

 " (4, 'We have reached 50% of our goal')," +

 " (5, 'We have received 50 emails')")) {

 stmt2.executeUpdate();

 }

 }



 // ...

 }

我們在靜態的setup()方法上使用@BeforeAll註解，使資料庫可供該類別中的所有測試方法使用。我們也使用 try-with-resources 語句來處理資料庫物件（例如連接），以避免記憶體洩漏。

測試數據是特意挑選的，旨在突出某些案例：

兩則包含「 hello”一詞的訊息
兩個包含字串“ 50%”訊息
一則包含「50」但缺少百分號的訊息

這為我們驗證LIKE行為的正確性和錯誤性提供了一個清晰的基準。

接下來，讓我們來探討一下如何在PreparedStatements中使用LIKE通配符。

3. 在參數中新增通配符

最簡單且最常用的方法是在綁定參數之前，直接在參數值中加入通配符。接下來，我們建立一個測試來示範這種方法：

@Test

 void whenConcatenatingWildcardCharsInParamForLike_thenCorrect() throws SQLException {

 String keyword = "hello"

 try (Connection conn = ds.getConnection();

 PreparedStatement pstmt =

 conn.prepareStatement("SELECT ID, CONTENT FROM MESSAGES WHERE CONTENT LIKE ?")) {

 pstmt.setString(1, "%" + keyword + "%");

 try (ResultSet rs = pstmt.executeQuery()) {

 List<String> contents = new ArrayList<>();

 while (rs.next()) {

 contents.add(rs.getString("CONTENT"));

 }

 assertThat(contents).containsExactlyInAnyOrder("a hello message", "a long hello message");

 }

 }

 }

我們的測試驗證了這種方法能夠如預期般發揮作用。

如我們所見，這種方法可以保持 SQL 語句的簡潔性和靜態性。但我們不能忘記在要綁定到LIKE子句的參數前加入通配符。

在許多應用場景中，這種方法完全足夠，而且很容易理解。

4. 使用 SQL `CONCAT()`函數

有時，我們更傾向於將通配符邏輯放在資料庫端，而不是應用程式程式碼中。在這種情況下，SQL 字串函數（例如CONCAT()是一個有效的替代方案：

@Test

 void whenUsingSqlConcatFunctionForLike_thenCorrect() throws SQLException {

 String keyword = "hello";

 try (Connection conn = ds.getConnection();

 PreparedStatement pstmt =

 conn.prepareStatement("SELECT ID, CONTENT FROM MESSAGES WHERE CONTENT LIKE CONCAT('%', ?, '%')")) {

 pstmt.setString(1, keyword);

 try (ResultSet rs = pstmt.executeQuery()) {

 List<String> contents = new ArrayList<>();

 while (rs.next()) {

 contents.add(rs.getString("CONTENT"));

 }

 assertThat(contents).containsExactlyInAnyOrder("a hello message", "a long hello message");

 }

 }

 }

上述測試證實了這種方法同樣有效。如程式碼所示，該方案避免了在 Java 程式碼中進行字串拼接。但是，如果某些資料庫對字串函數的處理方式不同，則可能會降低程式碼的可移植性。

從功能上看，處理簡單關鍵字時，這兩種方法是等效的。

5. 當關鍵字包含通配符時

當我們要搜尋的關鍵字本身包含 SQL 通配符（例如「 % 」）時，情況就會變得複雜。現在，假設我們要找MESSAGES表中所有包含「50%」的行。那麼，讓我們選擇使用CONCAT()函數：

@Test

 void whenKeywordContainsWildcardChar_thenIncorrect() throws SQLException {

 try (Connection conn = ds.getConnection();

 PreparedStatement pstmt =

 conn.prepareStatement("SELECT ID, CONTENT FROM MESSAGES WHERE CONTENT LIKE CONCAT('%', ?, '%')")) {

 pstmt.setString(1, "50%");

 try (ResultSet rs = pstmt.executeQuery()) {

 List<String> contents = new ArrayList<>();

 while (rs.next()) {

 contents.add(rs.getString("CONTENT"));

 }

 assertThat(contents).containsExactlyInAnyOrder(

 "We have spent 50% budget for marketing",

 "We have reached 50% of our goal",

 "We have received 50 emails"); //<-- we do not expect this one

 }

 }

 }

如測試所示，結果在邏輯上是錯誤的，因為「 … 50 emails 」這一行不應該出現在結果中。這是因為搜尋關鍵字「50%」中的百分號被解釋為通配符，導致 SQL 匹配任何包含「 50 」的CONTENT 。

為了解決這個問題，我們必須在綁定參數之前轉義通配符，並指定所使用的轉義字元。我們將使用一個輔助方法，用「 ! 」來轉義「%」和「_」。此外，我們還需要轉義「!」 。

String escapeLikeSpecialChars(String input) {

 return input.replace("!", "!!")

 .replace("%", "!%")

 .replace("_", "!_");

 }

接下來，我們來看看escapeLikeSpecialChars()方法如何解決我們的問題：

@Test

 void whenEscapeInSqlForLike_thenCorrect() throws SQLException {

 try (Connection conn = ds.getConnection();

 PreparedStatement pstmt =

 conn.prepareStatement("SELECT ID, CONTENT FROM MESSAGES WHERE CONTENT LIKE ? ESCAPE '!'")) {

 pstmt.setString(1, "%" + escapeLikeSpecialChars("50%") + "%");

 try (ResultSet rs = pstmt.executeQuery()) {

 List<String> contents = new ArrayList<>();

 while (rs.next()) {

 contents.add(rs.getString("CONTENT"));

 }

 assertThat(contents).containsExactlyInAnyOrder(

 "We have spent 50% budget for marketing",

 "We have reached 50% of our goal");

 }

 }

 }

在 SQL 語句中， ESCAPE子句告訴 SQL：“如果在LIKE模式中看到字元 '!'，則將下一個字元視為字面值，即使它通常是通配符。”

類似地，我們可以將相同的技巧應用於CONCAT()函數的方法：

@Test

 void whenEscapeInSqlWithConcatFunctionForLike_thenCorrect() throws SQLException {

 try (Connection conn = ds.getConnection();

 PreparedStatement pstmt =

 conn.prepareStatement(

 "SELECT ID, CONTENT FROM MESSAGES WHERE CONTENT LIKE CONCAT('%',?,'%') ESCAPE '!'")) {

 pstmt.setString(1, escapeLikeSpecialChars("50%"));

 try (ResultSet rs = pstmt.executeQuery()) {

 List<String> contents = new ArrayList<>();

 while (rs.next()) {

 contents.add(rs.getString("CONTENT"));

 }

 assertThat(contents).containsExactlyInAnyOrder(

 "We have spent 50% budget for marketing",

 "We have reached 50% of our goal");

 }

 }

 }

現在，'%' 字符被視為字面字符，結果符合我們的預期。

6. 結論

在PreparedStatements中使用LIKE需要格外注意細節。雖然基本情況很簡單，但涉及通配符的特殊情況很容易導致難以察覺的錯誤。

本文探討了多種方法。雖然用百分號 (%) 將參數括起來是最簡單的方法，但使用CONCAT()函數可以使 SQL 語句的意圖更加清晰。最重要的是，如果搜尋關鍵字可能包含百分號 (%) 或下劃線 (_) 等字符，我們務必記住要對輸入進行轉義。

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

本作品係原創或者翻譯，採用《署名-非商業性使用-禁止演繹4.0國際》許可協議