Building up a SQL Query String in Java

1. Overview

When writing SQL queries, we often need to build the query dynamically. For instance, dynamic string building is usually required when writing logic for filters, runtime-specified sorts, and optional join operations.

In this tutorial, we’ll see how Java creates SQL strings. Notably, we’ll use the Baeldung Simple University Schema.

2. The Challenge of Dynamic Queries

Let’s suppose we’re building a student search feature. Consequently, a user can search for students using one or more optional filters:

• name
• student ID
• enrollment date
• others

Since filters may vary depending on search criteria, we need different or dynamic SQL queries.

If no filters are selected, we simply retrieve all students:

SELECT * FROM Student;

However, if we search only by name, the query becomes:

SELECT * FROM Student WHERE name LIKE ?;

If we search by both name and student ID, we need an additional condition:

SELECT * FROM Student WHERE name LIKE ? AND id = ?;

Finally, if all filters are provided, the query becomes:

SELECT * FROM Student WHERE name LIKE ? AND id = ?
AND enrollment_date = ?;

As the query grows, it becomes hard to maintain:

String sql = "SELECT s.id, s.name, s.national_id, s.birth_date, " 
  + "s.enrollment_date, s.graduation_date, s.gpa " 
  + "FROM students s " 
  + "WHERE s.gpa >= " + minGpa + " " 
  + "AND s.enrollment_date >= '" + enrollmentDate + "' " 
  + (includeGraduated ? "" : "AND s.graduation_date IS NULL ") 
  + "ORDER BY s.gpa DESC, s.name";

As a result, readability becomes a key issue.

3. Dynamic SQL Generation

When querying a database, users often sort the results with different filters. For example, they may choose to sort by name or date. Thus, we should be able to create an SQL query with dynamic parameters.

3.1. Using the StringBuilder Class

The StringBuilder class in Java enables changes to string objects that are normally immutable in the String class.

Thus, we can assemble SQL queries in steps:

StringBuilder sb = new StringBuilder("SELECT id, name FROM Student");
if (enrollmentDate != null) {
    sb.append(" WHERE enrollment_date = ?");
}
if (birthDate != null) {
    sb.append(" AND birth_date = ?");
}

Still, the above solution isn’t complete. For example, if the user provides a null value to enrollment_date, the WHERE clause doesn’t complete:

SELECT id, name FROM Student AND birth_date = ?

Thus, the resulting SQL becomes invalid.

3.2. Building Conditions Separately

Let’s consider another variant of the above code:

StringBuilder sb = new StringBuilder("SELECT id, name " + "FROM Student WHERE 1=1");
...

In this case, we start with a new clause WHERE 1=1, which is always true. So, we can now start every conditional statement with AND:

if (enrollmentDate != null) {
    sb.append(" AND enrollment_date = ?");
}
if (birthDate != null) {
    sb.append(" AND birth_date = ?");
}

The parameters are now dynamically added to the SQL query. Lastly, we can execute this query using PreparedStatement.

3.3. Execution Using PreparedStatement

Since we’ve used parameterized queries in the above code, we can use PreparedStatement to execute the query:

ps.setString(parameterIndex++, enrollmentDate);
ps.setString(parameterIndex++, birthDate);

In the above code, ps is the PreparedStatement object.

4. Using StringJoiner (Java 8)

The StringJoiner class in Java can join multiple strings. Thus, we can use it to connect multiple SQL strings:

String baseQuery = "SELECT * FROM Student";
StringJoiner whereClause = new StringJoiner(" AND ");
whereClause.add("name LIKE ?");
whereClause.add("id = ?");
whereClause.add("enrollment_date = ?");

In the above example, the search criteria (name, id, and enrollment_date) are dynamic and optional. As a result, we can dynamically build a SQL WHERE clause.

5. Conclusion

In this article, we went through building a SQL query string in Java. First, we saw how long queries become unmanageable. Then, we looked at how the StringBuilder and StringJoiner classes help build dynamic SQL queries.

The code backing this article is available over on GitHub.