在 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 上找到。