Using “LIKE” Wildcards in Java PreparedStatements

1. Overview

When working with relational databases, text searching is one of the most common requirements we encounter. SQL’s LIKE operator is often the first tool we reach for when we need partial matches, prefix searches, or flexible filtering based on user input. At the same time, we usually use PreparedStatement to protect ourselves from SQL injection and improve performance.

At first glance, combining LIKE and PreparedStatement looks trivial, but subtle issues appear quickly, especially when user input itself contains wildcard characters.

In this tutorial, we’ll walk through common patterns of using LIKE with PreparedStatements. We use unit tests to demonstrate what works, what does not, and how to handle edge cases correctly.

2. Preparing an Example

Before discussing solutions, as usual, let’s first create a concrete example. To keep things straightforward, we start with a simple integration test setup using H2 in-memory database, plain JDBC, and JUnit 5. This combination keeps the example lightweight while still behaving like a real SQL database.

Our test class initializes a data source, creates a table, and inserts several rows of test data:

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

    // ...
}

We use the @BeforeAll annotation on the static setup() method to make the database available to all test methods in this class. We also use try-with-resources to handle database objects, such as connections, to avoid leaks.

The test data is intentionally chosen to highlight some cases:

• Two messages containing the word “hello”
• Two messages containing the literal string “50%”
• One message containing “50” without a percent sign

This gives us a clean baseline for validating correct and incorrect LIKE behavior.

Next, let’s explore how to use LIKE wildcards in PreparedStatements in action.

3. Adding Wildcard Characters to Parameters

The easiest and most frequently suggested method is to add wildcard characters directly to the parameter value before binding it. Next, let’s create a test to demonstrate this approach:

@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");
        }
    }
}

Our test verifies that this approach works as expected.

As we can see, this approach keeps the SQL statement clean and static. But we must not forget to concatenate wildcard characters to the parameter we want to bind to the LIKE clause.

In many applications, this approach is perfectly sufficient and easy to reason about.

4. Using the SQL CONCAT() Function

Sometimes, we prefer to keep wildcard logic on the database side rather than in application code. In such cases, SQL string functions like CONCAT() are a valid alternative:

@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");
        }
    }
}

The test above confirms that this approach does the job as well. As the code shows, this solution avoids string concatenation in Java code. However, it may reduce portability if certain databases handle string functions differently.

Functionally, both approaches are equivalent when dealing with simple keywords.

5. When the Keyword Contains Wildcard Characters

Things get tricky when the keyword we want to search for itself contains SQL wildcard characters, such as ‘%‘. Now, let’s say we want to find all rows in the MESSAGES table that contain “50%”. So, let’s pick the CONCAT() function approach:

@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
        }
    }
}

As the test demonstrates, the result is logically incorrect because the “… 50 emails” row shouldn’t appear in the result. This occurs because the % in the search keyword “50%” is interpreted as a wildcard, causing SQL to match any CONTENT containing “50“.

To fix this, we must escape wildcard characters before binding the parameter and specify the escape character we are using. We’ll use a helper method to use ‘!‘ to escape ‘%’ and ‘_’. Also, we need to escape ‘!’:

String escapeLikeSpecialChars(String input) {
    return input.replace("!", "!!")
      .replace("%", "!%")
      .replace("_", "!_");
}

Next, let’s see how this escapeLikeSpecialChars() method can solve our problem:

@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");
        }
    }
}

In the SQL statement, the ESCAPE clause tells SQL: “If you see the character ‘!’ inside the LIKE pattern, treat the next character literally, even if it is normally a wildcard.”

Similarly, we can apply the same technique to the CONCAT() function approach:

@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");
        }
    }
}

Now the ‘%’ character is treated as a literal character, and the results match our expectations.

6. Conclusion

Using LIKE with PreparedStatements requires careful attention to detail. While basic cases are straightforward, edge cases involving wildcard characters can easily lead to subtle bugs.

In this article, we’ve examined various approaches. While wrapping the parameter in ‘%’ is the easiest method, using CONCAT() can make the SQL purpose clearer. Most importantly, we should always remember to escape our inputs if there’s a chance our search keyword contains literal ‘%’ or ‘_’ characters.

As always, the complete source code for the examples is available over on GitHub.