Searching text using SQL’s LIKE operator is a common requirement in many applications. When working with JDBC, developers typically use PreparedStatement instead of dynamically building SQL strings. This improves security, prevents SQL injection, and allows the database to reuse query execution plans. However, many developers are unsure how wildcard characters such as % and _ should be used with prepared statements. Let us delve into understanding how Java PreparedStatements LIKE wildcards work and how they help perform flexible pattern matching in database queries.
1. Understanding the LIKE Operator and Wildcards
The SQL LIKE operator allows pattern-based searching in text fields. It is commonly used when we want to retrieve rows where a column partially matches a given value rather than matching it exactly. This is particularly useful for features such as search bars, filtering by keywords, or implementing autocomplete functionality. In PostgreSQL, the LIKE operator supports two main wildcard characters that make pattern matching possible:
%— Matches zero or more characters. For example,'java%'matchesjava,javascript, orjava programming._— Matches exactly one character. For example,'j_va'matchesjavaorjiva, but notjaava.
These wildcard characters allow SQL queries to perform flexible searches against text columns in a database table. However, when working with Java database applications, we typically use PreparedStatement instead of directly concatenating SQL strings. Prepared statements offer several advantages:
- They help prevent SQL injection attacks.
- They improve performance by allowing query reuse.
- They separate SQL logic from dynamic parameter values.
A PreparedStatement uses placeholders (?) to represent parameter values that are supplied at runtime. For example:
SELECT * FROM books WHERE title = ?
The value for the placeholder is later supplied using methods such as setString(). However, a common mistake when working with the LIKE operator is attempting to embed the placeholder directly inside wildcard characters, as shown below:
SELECT * FROM books WHERE title LIKE '%?%'
This does not work because the ? placeholder represents an entire parameter value and cannot be interpreted as part of a string literal within the SQL statement. In this case, the database treats '%?%' as a literal string rather than a placeholder.
2. Setting Up PostgreSQL Using Docker
Before writing our Java code, we first need a running PostgreSQL database. One of the easiest ways to set up a local database for development is by using Docker. Docker allows us to run PostgreSQL inside a container without installing it directly on our machine. Using Docker also makes the setup reproducible and easy to clean up once the demo or development work is complete.
2.1 Pull the PostgreSQL Docker Image
First, download the official PostgreSQL image from Docker Hub.
docker pull postgres:16
This command downloads PostgreSQL version 16. If the image is not already present on your system, Docker will fetch it from the registry. Once the image is downloaded, you can use it to start containers whenever needed.
2.2 Run the PostgreSQL Container
Next, start a PostgreSQL container using the following command:
docker run -d \ --name postgres-like-demo \ -e POSTGRES_USER=postgres \ -e POSTGRES_PASSWORD=postgres \ -e POSTGRES_DB=library \ -p 5432:5432 \ postgres:16
2.2.1 Docker Command Explanation
The docker run command starts a new PostgreSQL container using the postgres:16 image. The -d flag runs the container in detached mode so it runs in the background without occupying the terminal. The --name postgres-like-demo option assigns a readable name to the container, making it easier to manage later using Docker commands. The -e flags define environment variables used by the PostgreSQL image during initialization: POSTGRES_USER=postgres creates the default database user, POSTGRES_PASSWORD=postgres sets the password for that user, and POSTGRES_DB=library automatically creates a database named library when the container starts. The -p 5432:5432 option maps port 5432 inside the container (PostgreSQL’s default port) to port 5432 on the host machine, allowing applications such as Java programs or database clients to connect to the database locally. Finally, postgres:16 specifies the Docker image that will be used to create the container.
You can confirm that the container is running by executing:
docker ps
2.3 Connect to the PostgreSQL Database
To access the PostgreSQL command-line interface inside the container, run the following command:
docker exec -it postgres-like-demo psql -U postgres -d library
2.3.1 Command Explanation
The docker exec command is used to run a command inside an already running container. In this case, it opens an interactive PostgreSQL session inside the postgres-like-demo container. The -it flags enable an interactive terminal so that we can type commands directly into the container. The command psql launches the PostgreSQL command-line client, which allows us to interact with the database. The -U postgres option specifies that we are connecting using the postgres user, while -d library indicates that we want to connect to the library database created when the container started. Once executed, this command opens the PostgreSQL prompt where we can run SQL queries, create tables, and insert data into the database.
3. Creating a Sample Table for the Demo
Let’s create a simple table that we will use in our Java example.
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
author VARCHAR(100)
);
INSERT INTO books(title, author) VALUES
('Java Fundamentals', 'John Smith'),
('Advanced Java Programming', 'Alice Brown'),
('Spring Boot Guide', 'David Lee'),
('Java Concurrency in Practice', 'Brian Goetz'),
('Learning Python', 'Mark Lutz');
We will use this dataset later to demonstrate how LIKE queries work with Java PreparedStatement parameters.
4. Using LIKE Wildcards with Java PreparedStatement
In this example we demonstrate three important concepts:
- Adding wildcard characters to parameters
- Using SQL
CONCAT()to build patterns - Escaping wildcard characters if the user input already contains them
// PostgresLikeWildcardExample.java
package org.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PostgresLikeWildcardExample {
private static final String URL =
"jdbc:postgresql://localhost:5432/library";
private static final String USER = "postgres";
private static final String PASSWORD = "postgres";
public static void main(String[] args) {
String keyword = "Java";
// Escape wildcard characters if they exist in input
keyword = keyword
.replace("\\", "\\\\")
.replace("%", "\\%")
.replace("_", "\\_");
String sql =
"SELECT id, title, author " +
"FROM books " +
"WHERE title LIKE CONCAT('%', ?, '%') ESCAPE '\\'";
try (
Connection conn =
DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement ps =
conn.prepareStatement(sql)
) {
ps.setString(1, keyword);
ResultSet rs = ps.executeQuery();
System.out.println("Search Results:\\n");
while (rs.next()) {
int id = rs.getInt("id");
String title = rs.getString("title");
String author = rs.getString("author");
System.out.println(
id + " - " + title + " (" + author + ")"
);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
4.1 Java Code Explanation
The PostgresLikeWildcardExample class demonstrates how to perform a flexible text search in a PostgreSQL database using Java’s JDBC PreparedStatement with the SQL LIKE operator. The program first defines the database connection details including the JDBC URL (jdbc:postgresql://localhost:5432/library), the database username (postgres), and password. Inside the main method, a search keyword ("Java") is defined, which represents the text the user wants to search for in the books table. Since SQL LIKE queries interpret the characters % and _ as wildcard symbols, the code first sanitizes the input keyword by escaping these characters using replace() calls. This ensures that if the user enters characters such as % or _, they are treated as literal characters rather than pattern wildcards. Next, the SQL query is defined, which selects the id, title, and author columns from the books table where the title matches a pattern constructed using LIKE CONCAT('%', ?, '%'). The CONCAT function dynamically adds wildcard characters before and after the keyword so that PostgreSQL searches for the keyword anywhere within the title. The ESCAPE '\\' clause specifies that the backslash character should be used as the escape character for any escaped wildcard symbols. The program then opens a database connection using DriverManager.getConnection() and prepares the SQL query using PreparedStatement, which protects the application from SQL injection and allows the database to reuse compiled query plans. The keyword is bound to the query parameter using ps.setString(1, keyword). After executing the query with executeQuery(), the program retrieves the results using a ResultSet object and iterates through each row using a while (rs.next()) loop. For every matching row, it extracts the id, title, and author values and prints them to the console in a formatted output. The database connection and statement are managed using Java’s try-with-resources block, which automatically closes them after execution to prevent resource leaks. If any error occurs during execution, it is caught in the catch block and printed using e.printStackTrace(). Overall, this example demonstrates how to safely implement pattern-based searches in PostgreSQL using JDBC while correctly handling wildcard characters and user input.
4.2 Program Output
Search Results: 1 - Java Fundamentals (John Smith) 2 - Advanced Java Programming (Alice Brown) 4 - Java Concurrency in Practice (Brian Goetz) 6 - 100% Java Handbook (Tech Writer)
When the program runs with the keyword Java, it executes the SQL query that searches for any book titles containing the word “Java”. Because the query uses the pattern %Java%, PostgreSQL returns all rows where the word appears anywhere in the title field. The program then iterates through the ResultSet and prints each matching record in the format id - title (author). As shown in the output, multiple book titles containing the keyword are successfully retrieved and displayed.
5. Conclusion
Using the LIKE operator with PreparedStatement in Java requires proper handling of wildcard characters to ensure both flexibility and security in database queries. The safest and most common approach is to append wildcard characters such as % to the parameter value before executing the query rather than embedding them directly in the SQL string. Developers should always prefer PreparedStatement over string concatenation to prevent SQL injection and improve query performance through prepared execution plans. Wildcard characters can be added to the parameter value to enable partial text matching, and in some cases SQL functions like CONCAT() can be used to construct search patterns directly within the query. Additionally, if user input may contain wildcard characters such as % or _, these should be properly escaped so they are treated as literal characters rather than pattern operators. Using containerized tools like Docker also makes it very easy to quickly spin up a PostgreSQL instance for local development and testing. By following these practices, developers can implement flexible search functionality while keeping their JDBC code secure, maintainable, and efficient.
