Building SQL queries dynamically is a common requirement in Java applications. Search screens, filtering APIs, reporting modules, and administrative dashboards often allow users to select different combinations of filters. Since every filter is optional, developers need an efficient way to construct SQL statements without duplicating code or creating dozens of static queries. Although modern applications often use JPA Criteria API, QueryDSL, or jOOQ for dynamic query generation, there are many situations where constructing SQL manually is the simplest solution. When done correctly, it results in clean, readable, and maintainable code. However, developers must also be careful to avoid SQL injection vulnerabilities by using parameterized queries.
1. Overview
Consider an employee search screen that allows users to filter results by department, minimum salary, maximum salary, employee status, and joining date, with every filter being optional. Since users can choose any combination of these criteria, the application may need to generate numerous SQL query variations. Creating and maintaining a separate SQL statement for every possible combination quickly becomes impractical, making dynamic SQL generation a far more scalable and maintainable solution. Instead of writing multiple SQL statements, we can dynamically construct the query by appending only the conditions required at runtime. Java provides several utilities for building dynamic SQL, including StringBuilder, StringJoiner, Collectors.joining(), and Apache Commons StringUtils. Among these, StringJoiner offers a clean and elegant approach by automatically inserting separators between conditions, eliminating the need for additional logic to manage delimiters.
2. Challenges of Dynamic Queries
Building SQL dynamically appears straightforward, but several common issues arise.
2.1 Managing WHERE Claudes
A naive implementation of dynamic SQL generation often starts with a base query and appends conditions as they are required:
String sql = "SELECT * FROM employees";
if (department != null) {
sql += " WHERE department = ?";
}
if (salary != null) {
sql += " AND salary >= ?";
}
This approach quickly becomes problematic because it assumes the first condition is always present. If the department filter is not provided but the salary filter is, the generated SQL begins with AND instead of WHERE, resulting in an invalid SQL statement.
2.2 Readability
As the number of optional filters increases, the SQL construction logic often becomes cluttered with multiple if statements and string concatenations. This not only reduces code readability but also makes the query harder to understand, debug, and maintain. Even small changes, such as adding or modifying a filter, can require updates in several places, increasing the likelihood of introducing bugs. A cleaner approach to building dynamic SQL helps keep the code concise, organized, and easier to extend.
2.3 SQL Injection
Another common mistake when building dynamic SQL is directly concatenating user input into the query string. This approach exposes the application to SQL injection attacks, where malicious input can alter the intended SQL statement and potentially compromise the database.
sql += " AND department = '" + department + "'";
To prevent SQL injection, always use prepared statements with parameter placeholders (?) and bind the user-supplied values separately. This approach not only protects the application from malicious input but also improves code reliability and allows the database to reuse execution plans for better performance.
2.4 Maintaining Parameters
When building SQL dynamically, it is equally important to maintain the parameter values in the same order as their corresponding placeholders (?) appear in the query. Each time a new condition is appended, its parameter must also be added to the parameter list at the correct position. If the order of parameters does not match the order of placeholders, the query may execute with incorrect values, leading to invalid results or runtime errors. Keeping the SQL conditions and parameter list synchronized is therefore essential for building reliable dynamic queries.
3. Code Example
3.1 Dataset Preparation on PostgreSQL
To demonstrate dynamic SQL generation, we’ll use a simple employees table in PostgreSQL. The table stores basic employee information, including the employee’s department, salary, employment status, and joining date. These columns will serve as optional search criteria when building SQL queries dynamically.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC(10,2),
status VARCHAR(20),
joining_date DATE
);
Next, insert a few sample records into the table.
INSERT INTO employees (name, department, salary, status, joining_date)
VALUES ('Alice', 'IT', 75000, 'ACTIVE', '2021-01-15'),
('Bob', 'Finance', 68000, 'ACTIVE', '2022-03-01'),
('Charlie', 'IT', 92000, 'INACTIVE', '2020-08-10'),
('David', 'HR', 52000, 'ACTIVE', '2023-05-20'),
('Emma', 'IT', 81000, 'ACTIVE', '2019-11-12');
Throughout the examples in this article, we’ll dynamically generate SQL queries to filter this dataset based on any combination of the optional search parameters. This allows us to construct only the required WHERE conditions while keeping the code clean, maintainable, and secure.
If you don’t already have PostgreSQL installed, an easy way to get started is by running it in a Docker container. You can use the official PostgreSQL image from Docker Hub and expose the default port 5432. This provides a lightweight, isolated database environment for following along with the examples in this article.
3.2 Creating Dynamic SQL Generation Using StringJoiner
Instead of manually managing WHERE and AND, we can collect all conditions into a StringJoiner.
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.StringJoiner;
public class EmployeeQueryBuilder {
public static void main(String[] args) {
String department = "IT";
Double minimumSalary = 70000.0;
Double maximumSalary = 90000.0;
String status = "ACTIVE";
Date joiningDate = Date.valueOf("2020-01-01");
Query query = buildQuery(
department,
minimumSalary,
maximumSalary,
status,
joiningDate
);
System.out.println("Generated SQL:");
System.out.println(query.sql());
System.out.println("\nParameters:");
query.parameters().forEach(System.out::println);
}
public static Query buildQuery(
String department,
Double minimumSalary,
Double maximumSalary,
String status,
Date joiningDate) {
StringBuilder sql = new StringBuilder();
sql.append("""
SELECT id,
name,
department,
salary,
status,
joining_date
FROM employees
""");
StringJoiner whereClause = new StringJoiner(" AND ");
List<Object> parameters = new ArrayList<>();
if (department != null) {
whereClause.add("department = ?");
parameters.add(department);
}
if (minimumSalary != null) {
whereClause.add("salary >= ?");
parameters.add(minimumSalary);
}
if (maximumSalary != null) {
whereClause.add("salary <= ?");
parameters.add(maximumSalary);
}
if (status != null) {
whereClause.add("status = ?");
parameters.add(status);
}
if (joiningDate != null) {
whereClause.add("joining_date >= ?");
parameters.add(joiningDate);
}
if (whereClause.length() > 0) {
sql.append(" WHERE ");
sql.append(whereClause);
}
sql.append(" ORDER BY salary DESC");
return new Query(sql.toString(), parameters);
}
}
record Query(String sql, List<Object> parameters) {}
3.2.1 Code Explanation
The EmployeeQueryBuilder class demonstrates how to dynamically construct a parameterized SQL query in Java using StringBuilder, StringJoiner, and a list of query parameters. The main() method initializes optional search criteria such as department, minimum salary, maximum salary, employee status, and joining date before passing them to the buildQuery() method. Inside this method, a StringBuilder is used to create the base SELECT statement, while a StringJoiner efficiently combines conditional WHERE clause expressions with the AND operator only when corresponding filter values are provided. Instead of concatenating user input directly into the SQL string, each condition uses a ? placeholder, and the actual values are stored in a List<Object> called parameters. This approach produces a clean, flexible, and secure parameterized query that can be executed with a PreparedStatement, helping prevent SQL injection attacks. If at least one filter is supplied, the generated conditions are appended to the query, followed by an ORDER BY salary DESC clause to sort the results. Finally, the method returns a Query record containing both the generated SQL statement and its associated parameter values, making the solution easy to reuse and maintain for dynamic search operations.
3.2.2 Code Output
The code execution starts from the main() method, where different employee filtering criteria such as department, salary range, employee status, and joining date are defined. These values are passed to the buildQuery() method, which dynamically creates the SQL query based on the available conditions. The method uses StringJoiner to combine multiple filter conditions with the AND operator and stores the corresponding values separately in a parameter list. The generated SQL query contains placeholders (?) instead of directly embedding values, allowing it to be safely executed using a PreparedStatement. After building the query, the program prints the generated SQL statement and displays the parameter values that will be bound during query execution. The output shows a dynamically generated query containing all the provided filters: department, minimum salary, maximum salary, status, and joining date.
Generated SQL:
SELECT id,
name,
department,
salary,
status,
joining_date
FROM employees
WHERE department = ? AND salary >= ? AND salary <= ? AND status = ? AND joining_date >= ? ORDER BY salary DESC
Parameters:
IT
70000.0
90000.0
ACTIVE
2020-01-01
4. Conclusion
Dynamic SQL generation is a common requirement in Java applications that support optional search criteria. While simple string concatenation may work for small examples, it quickly becomes difficult to maintain and is prone to errors. Using StringJoiner provides a clean, readable way to assemble conditional SQL fragments while automatically handling separators.
Combining StringJoiner with parameterized queries and PreparedStatement results in code that is secure, maintainable, and easy to extend. As applications grow more complex, this approach offers an excellent balance between simplicity and flexibility before moving to more advanced query-building frameworks.
