Creating a type-safe, flexible SQL query builder using Java's String Templates (preview feature in Java 21+) provides a powerful alternative to traditional string concatenation or complex ORM frameworks.
Basic String Templates SQL Builder
import java.util.*;
import java.lang.StringTemplate;
import java.util.function.Function;
public class SQLTemplateBuilder {
// Basic SQL clauses as String Templates
public static class SQL {
public static StringTemplate SELECT(StringTemplate.Processor<String, RuntimeException> st, String columns) {
return st."""
SELECT \{columns}
""";
}
public static StringTemplate FROM(StringTemplate.Processor<String, RuntimeException> st, String table) {
return st."""
FROM \{table}
""";
}
public static StringTemplate WHERE(StringTemplate.Processor<String, RuntimeException> st, String condition) {
return st."""
WHERE \{condition}
""";
}
public static StringTemplate AND(StringTemplate.Processor<String, RuntimeException> st, String condition) {
return st."""
AND \{condition}
""";
}
public static StringTemplate OR(StringTemplate.Processor<String, RuntimeException> st, String condition) {
return st."""
OR \{condition}
""";
}
}
// Safe SQL processor that prevents SQL injection
public static final StringTemplate.Processor<String, RuntimeException> SQL =
StringTemplate.Processor.of((StringTemplate st) -> {
StringBuilder sb = new StringBuilder();
Iterator<String> fragments = st.fragments().iterator();
for (Object value : st.values()) {
sb.append(fragments.next());
if (value instanceof SafeValue safeValue) {
sb.append(safeValue.value());
} else {
// Parameterize unsafe values
sb.append("?");
}
}
sb.append(fragments.next());
return sb.toString();
});
// Marker interface for safe values
public interface SafeValue {
String value();
}
// Safe string implementation
public record SafeString(String value) implements SafeValue {}
// Safe identifier (table/column names)
public record SafeIdentifier(String value) implements SafeValue {}
// Helper methods to create safe values
public static SafeString safe(String value) {
return new SafeString(value);
}
public static SafeIdentifier ident(String identifier) {
return new SafeIdentifier(identifier);
}
}
Complete SQL Query Builder Implementation
Example 1: Basic Query Builder
import java.util.*;
import java.lang.StringTemplate;
import java.util.stream.Collectors;
public class SimpleSQLBuilder {
public static final StringTemplate.Processor<String, RuntimeException> SQL =
StringTemplate.Processor.of((StringTemplate st) -> {
StringBuilder sb = new StringBuilder();
Iterator<String> fragments = st.fragments().iterator();
List<Object> parameters = new ArrayList<>();
for (Object value : st.values()) {
sb.append(fragments.next());
if (value instanceof SafeValue safeValue) {
sb.append(safeValue.value());
} else if (value instanceof QueryPart queryPart) {
sb.append(queryPart.sql());
parameters.addAll(queryPart.parameters());
} else {
sb.append("?");
parameters.add(value);
}
}
sb.append(fragments.next());
return sb.toString();
});
public interface SafeValue {
String value();
}
public record SafeString(String value) implements SafeValue {}
public record SafeIdentifier(String value) implements SafeValue {}
public static class QueryPart {
private final String sql;
private final List<Object> parameters;
public QueryPart(String sql, List<Object> parameters) {
this.sql = sql;
this.parameters = parameters;
}
public String sql() { return sql; }
public List<Object> parameters() { return parameters; }
@Override
public String toString() { return sql; }
}
// Builder methods
public static QueryPart select(String... columns) {
String cols = columns.length == 0 ? "*" :
Arrays.stream(columns)
.map(SimpleSQLBuilder::ident)
.collect(Collectors.joining(", "));
return new QueryPart("SELECT " + cols, List.of());
}
public static QueryPart from(String table) {
return new QueryPart("FROM " + ident(table), List.of());
}
public static QueryPart where(String condition, Object... params) {
return new QueryPart("WHERE " + condition, Arrays.asList(params));
}
public static QueryPart and(String condition, Object... params) {
return new QueryPart("AND " + condition, Arrays.asList(params));
}
public static QueryPart or(String condition, Object... params) {
return new QueryPart("OR " + condition, Arrays.asList(params));
}
public static QueryPart eq(String column, Object value) {
return new QueryPart(ident(column) + " = ?", List.of(value));
}
public static QueryPart like(String column, String value) {
return new QueryPart(ident(column) + " LIKE ?", List.of(value));
}
public static QueryPart in(String column, List<?> values) {
String placeholders = values.stream().map(v -> "?").collect(Collectors.joining(", "));
return new QueryPart(ident(column) + " IN (" + placeholders + ")", new ArrayList<>(values));
}
private static String ident(String identifier) {
// Simple quoting - in real implementation, use proper SQL quoting
return "`" + identifier.replace("`", "``") + "`";
}
// Usage examples
public static void main(String[] args) {
// Simple SELECT query
var query1 = SQL."""
\{select("id", "name", "email")}
\{from("users")}
\{where("age > ?", 18)}
""";
System.out.println("Query 1: " + query1);
// Complex condition
var query2 = SQL."""
\{select()}
\{from("products")}
\{where("price BETWEEN ? AND ?", 10.0, 100.0)}
\{and("category = ?", "electronics")}
\{or("status = ?", "active")}
""";
System.out.println("Query 2: " + query2);
// IN clause
var query3 = SQL."""
\{select("name")}
\{from("users")}
\{in("id", List.of(1, 2, 3, 4, 5))}
""";
System.out.println("Query 3: " + query3);
}
}
Example 2: Type-Safe Fluent Query Builder
import java.util.*;
import java.lang.StringTemplate;
public class FluentSQLBuilder {
private final StringTemplate.Processor<String, RuntimeException> sqlProcessor;
private final List<Object> parameters = new ArrayList<>();
private final StringBuilder sqlBuilder = new StringBuilder();
public FluentSQLBuilder(StringTemplate.Processor<String, RuntimeException> sqlProcessor) {
this.sqlProcessor = sqlProcessor;
}
public FluentSQLBuilder select(String... columns) {
String cols = columns.length == 0 ? "*" : String.join(", ", columns);
sqlBuilder.append("SELECT ").append(cols);
return this;
}
public FluentSQLBuilder from(String table) {
sqlBuilder.append(" FROM ").append(table);
return this;
}
public FluentSQLBuilder where(String condition, Object... params) {
sqlBuilder.append(" WHERE ").append(condition);
Collections.addAll(parameters, params);
return this;
}
public FluentSQLBuilder and(String condition, Object... params) {
sqlBuilder.append(" AND ").append(condition);
Collections.addAll(parameters, params);
return this;
}
public FluentSQLBuilder or(String condition, Object... params) {
sqlBuilder.append(" OR ").append(condition);
Collections.addAll(parameters, params);
return this;
}
public FluentSQLBuilder orderBy(String... columns) {
sqlBuilder.append(" ORDER BY ").append(String.join(", ", columns));
return this;
}
public FluentSQLBuilder limit(int count) {
sqlBuilder.append(" LIMIT ?");
parameters.add(count);
return this;
}
public FluentSQLBuilder offset(int count) {
sqlBuilder.append(" OFFSET ?");
parameters.add(count);
return this;
}
public String build() {
return sqlBuilder.toString();
}
public List<Object> getParameters() {
return Collections.unmodifiableList(parameters);
}
public QueryResult buildWithParams() {
return new QueryResult(sqlBuilder.toString(), parameters);
}
public record QueryResult(String sql, List<Object> parameters) {
@Override
public String toString() {
return "SQL: " + sql + ", Params: " + parameters;
}
}
// Static factory method
public static FluentSQLBuilder create() {
return new FluentSQLBuilder(StringTemplate.Processor.of(st -> st.interpolate()));
}
// Usage example
public static void main(String[] args) {
var query = FluentSQLBuilder.create()
.select("id", "name", "email")
.from("users")
.where("age > ?", 18)
.and("status = ?", "active")
.orderBy("name")
.limit(10)
.buildWithParams();
System.out.println(query);
}
}
Example 3: Advanced SQL Builder with Joins and Subqueries
import java.util.*;
import java.lang.StringTemplate;
public class AdvancedSQLBuilder {
public static final StringTemplate.Processor<Query, RuntimeException> SQL =
StringTemplate.Processor.of((StringTemplate st) -> {
StringBuilder sql = new StringBuilder();
List<Object> params = new ArrayList<>();
Iterator<String> fragments = st.fragments().iterator();
for (Object value : st.values()) {
sql.append(fragments.next());
if (value instanceof QueryPart part) {
sql.append(part.sql());
params.addAll(part.parameters());
} else {
sql.append("?");
params.add(value);
}
}
sql.append(fragments.next());
return new Query(sql.toString(), params);
});
public record Query(String sql, List<Object> parameters) {
public Query and(Query other) {
return new Query(
this.sql + " AND " + other.sql,
combineParameters(this.parameters, other.parameters)
);
}
public Query or(Query other) {
return new Query(
this.sql + " OR " + other.sql,
combineParameters(this.parameters, other.parameters)
);
}
private List<Object> combineParameters(List<Object> p1, List<Object> p2) {
List<Object> combined = new ArrayList<>(p1);
combined.addAll(p2);
return combined;
}
@Override
public String toString() {
return "Query{sql='" + sql + "', parameters=" + parameters + "}";
}
}
public static class QueryPart {
private final String sql;
private final List<Object> parameters;
public QueryPart(String sql, List<Object> parameters) {
this.sql = sql;
this.parameters = parameters;
}
public String sql() { return sql; }
public List<Object> parameters() { return parameters; }
}
// SQL clause builders
public static QueryPart select(String... columns) {
String cols = String.join(", ", columns);
return new QueryPart("SELECT " + cols, List.of());
}
public static QueryPart from(String table) {
return new QueryPart("FROM " + table, List.of());
}
public static QueryPart join(String table, String onClause, Object... params) {
return new QueryPart(" JOIN " + table + " ON " + onClause, Arrays.asList(params));
}
public static QueryPart leftJoin(String table, String onClause, Object... params) {
return new QueryPart(" LEFT JOIN " + table + " ON " + onClause, Arrays.asList(params));
}
public static QueryPart where(String condition, Object... params) {
return new QueryPart("WHERE " + condition, Arrays.asList(params));
}
public static QueryPart groupBy(String... columns) {
return new QueryPart("GROUP BY " + String.join(", ", columns), List.of());
}
public static QueryPart having(String condition, Object... params) {
return new QueryPart("HAVING " + condition, Arrays.asList(params));
}
// Condition builders
public static Query condition(String column, String operator, Object value) {
return new Query(column + " " + operator + " ?", List.of(value));
}
public static Query eq(String column, Object value) {
return condition(column, "=", value);
}
public static Query gt(String column, Object value) {
return condition(column, ">", value);
}
public static Query lt(String column, Object value) {
return condition(column, "<", value);
}
public static Query like(String column, String pattern) {
return condition(column, "LIKE", pattern);
}
public static Query in(String column, List<?> values) {
String placeholders = String.join(", ", Collections.nCopies(values.size(), "?"));
return new Query(column + " IN (" + placeholders + ")", new ArrayList<>(values));
}
// Subquery support
public static Query exists(Query subquery) {
return new Query("EXISTS (" + subquery.sql() + ")", subquery.parameters());
}
public static Query notExists(Query subquery) {
return new Query("NOT EXISTS (" + subquery.sql() + ")", subquery.parameters());
}
// Usage examples
public static void main(String[] args) {
// Complex join query
var userOrderQuery = SQL."""
\{select("u.name", "u.email", "o.order_date", "o.total_amount")}
\{from("users u")}
\{innerJoin("orders o", "u.id = o.user_id")}
\{where("u.created_at > ?", "2023-01-01")}
\{and("o.status = ?", "completed")}
""";
System.out.println("Join Query: " + userOrderQuery);
// Subquery example
var expensiveProductsQuery = SQL."""
\{select("name", "price")}
\{from("products")}
\{where("price > (SELECT AVG(price) FROM products)")}
""";
System.out.println("Subquery: " + expensiveProductsQuery);
// Complex conditions with AND/OR
var complexCondition = eq("status", "active")
.and(gt("age", 18))
.or(like("name", "John%"));
var complexQuery = SQL."""
\{select("*")}
\{from("users")}
\{where(complexCondition.sql())}
""";
System.out.println("Complex Condition: " + complexQuery);
}
// Helper method for inner join
public static QueryPart innerJoin(String table, String onClause, Object... params) {
return new QueryPart(" INNER JOIN " + table + " ON " + onClause, Arrays.asList(params));
}
}
Example 4: Safe SQL Template Processor with Validation
import java.util.*;
import java.lang.StringTemplate;
import java.util.regex.Pattern;
public class SafeSQLTemplate {
private static final Pattern SQL_INJECTION_PATTERN =
Pattern.compile("([';]+|(--)+|(\\|\\|)+|(&&)+)", Pattern.CASE_INSENSITIVE);
public static final StringTemplate.Processor<String, SQLException> SAFE_SQL =
StringTemplate.Processor.of((StringTemplate st) -> {
StringBuilder sql = new StringBuilder();
Iterator<String> fragments = st.fragments().iterator();
List<Object> parameters = new ArrayList<>();
for (Object value : st.values()) {
String fragment = fragments.next();
sql.append(fragment);
if (value instanceof SafeValue safeValue) {
// Safe values are inserted directly
sql.append(safeValue.value());
} else if (value instanceof SQLExpression expr) {
// SQL expressions are validated
sql.append(expr.toSQL());
parameters.addAll(expr.parameters());
} else {
// Unsafe values are parameterized
validateValue(value);
sql.append("?");
parameters.add(value);
}
}
sql.append(fragments.next());
// Validate final SQL
validateSQL(sql.toString());
return sql.toString();
});
public interface SafeValue {
String value();
}
public record SafeString(String value) implements SafeValue {}
public record SafeIdentifier(String value) implements SafeValue {}
public interface SQLExpression {
String toSQL();
List<Object> parameters();
}
public static class Column implements SQLExpression {
private final String name;
public Column(String name) {
this.name = name;
validateIdentifier(name);
}
@Override
public String toSQL() {
return "`" + name.replace("`", "``") + "`";
}
@Override
public List<Object> parameters() {
return List.of();
}
public Condition eq(Object value) {
return new Condition(this, "=", value);
}
public Condition gt(Object value) {
return new Condition(this, ">", value);
}
public Condition like(String pattern) {
return new Condition(this, "LIKE", pattern);
}
}
public static class Condition implements SQLExpression {
private final Column column;
private final String operator;
private final Object value;
public Condition(Column column, String operator, Object value) {
this.column = column;
this.operator = operator;
this.value = value;
validateOperator(operator);
}
@Override
public String toSQL() {
return column.toSQL() + " " + operator + " ?";
}
@Override
public List<Object> parameters() {
return List.of(value);
}
}
// Validation methods
private static void validateValue(Object value) throws SQLException {
if (value instanceof String str) {
if (SQL_INJECTION_PATTERN.matcher(str).find()) {
throw new SQLException("Potential SQL injection detected in value: " + str);
}
}
}
private static void validateIdentifier(String identifier) throws SQLException {
if (!identifier.matches("[a-zA-Z_][a-zA-Z0-9_]*")) {
throw new SQLException("Invalid identifier: " + identifier);
}
}
private static void validateOperator(String operator) throws SQLException {
Set<String> validOperators = Set.of("=", "!=", "<>", "<", ">", "<=", ">=", "LIKE", "IN");
if (!validOperators.contains(operator.toUpperCase())) {
throw new SQLException("Invalid operator: " + operator);
}
}
private static void validateSQL(String sql) throws SQLException {
// Basic SQL validation
if (sql.toUpperCase().contains("DROP TABLE") ||
sql.toUpperCase().contains("DELETE FROM")) {
throw new SQLException("Dangerous SQL operation detected");
}
}
// Helper methods
public static Column col(String name) throws SQLException {
return new Column(name);
}
public static SafeIdentifier ident(String identifier) throws SQLException {
validateIdentifier(identifier);
return new SafeIdentifier("`" + identifier + "`");
}
public static SafeString str(String value) throws SQLException {
validateValue(value);
return new SafeString("'" + value.replace("'", "''") + "'");
}
// Usage example
public static void main(String[] args) {
try {
Column id = col("id");
Column name = col("name");
Column age = col("age");
var query = SAFE_SQL."""
SELECT \{id}, \{name}, \{age}
FROM users
WHERE \{age.gt(18)} AND \{name.like("J%")}
ORDER BY \{name}
""";
System.out.println("Safe SQL: " + query);
} catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
}
}
}
Example 5: Dynamic Query Builder with String Templates
import java.util.*;
import java.lang.StringTemplate;
public class DynamicSQLBuilder {
public static final StringTemplate.Processor<DynamicQuery, RuntimeException> SQL =
StringTemplate.Processor.of((StringTemplate st) -> {
StringBuilder sql = new StringBuilder();
List<Object> params = new ArrayList<>();
Iterator<String> fragments = st.fragments().iterator();
for (Object value : st.values()) {
sql.append(fragments.next());
if (value instanceof QueryFragment fragment) {
if (fragment.include()) {
sql.append(fragment.sql());
params.addAll(fragment.parameters());
}
} else {
sql.append("?");
params.add(value);
}
}
sql.append(fragments.next());
return new DynamicQuery(sql.toString().replaceAll("\\s+", " ").trim(), params);
});
public interface QueryFragment {
String sql();
List<Object> parameters();
boolean include();
}
public record DynamicQuery(String sql, List<Object> parameters) {
@Override
public String toString() {
return "SQL: " + sql + "\nParams: " + parameters;
}
}
public static class ConditionalFragment implements QueryFragment {
private final String sql;
private final List<Object> parameters;
private final boolean include;
public ConditionalFragment(String sql, List<Object> parameters, boolean include) {
this.sql = sql;
this.parameters = parameters;
this.include = include;
}
@Override public String sql() { return sql; }
@Override public List<Object> parameters() { return parameters; }
@Override public boolean include() { return include; }
}
// Builder methods for conditional fragments
public static QueryFragment whereIf(boolean condition, String clause, Object... params) {
return new ConditionalFragment(condition ? " WHERE " + clause : "",
condition ? Arrays.asList(params) : List.of(),
condition);
}
public static QueryFragment andIf(boolean condition, String clause, Object... params) {
return new ConditionalFragment(condition ? " AND " + clause : "",
condition ? Arrays.asList(params) : List.of(),
condition);
}
public static QueryFragment orderByIf(boolean condition, String... columns) {
String clause = " ORDER BY " + String.join(", ", columns);
return new ConditionalFragment(condition ? clause : "", List.of(), condition);
}
public static QueryFragment limitIf(boolean condition, int limit) {
return new ConditionalFragment(condition ? " LIMIT ?" : "",
condition ? List.of(limit) : List.of(),
condition);
}
// Usage example with dynamic conditions
public static void main(String[] args) {
String searchName = "John";
Integer minAge = 25;
String sortBy = "name";
Integer limit = 10;
boolean hasNameFilter = searchName != null && !searchName.isEmpty();
boolean hasAgeFilter = minAge != null;
boolean hasSorting = sortBy != null;
boolean hasLimit = limit != null;
var query = SQL."""
SELECT id, name, email, age
FROM users
\{whereIf(hasNameFilter || hasAgeFilter, "1=1")}
\{andIf(hasNameFilter, "name LIKE ?", "%" + searchName + "%")}
\{andIf(hasAgeFilter, "age >= ?", minAge)}
\{orderByIf(hasSorting, sortBy)}
\{limitIf(hasLimit, limit)}
""";
System.out.println("Dynamic Query:\n" + query);
// Example with all conditions false
var emptyQuery = SQL."""
SELECT *
FROM users
\{whereIf(false, "id = ?", 1)}
\{andIf(false, "name = ?", "test")}
\{orderByIf(false, "id")}
\{limitIf(false, 10)}
""";
System.out.println("\nEmpty Conditions Query:\n" + emptyQuery);
}
}
Key Features and Benefits
Type Safety
- Compile-time checking of SQL structure
- Prevention of common SQL errors
- IDE support for auto-completion
SQL Injection Protection
- Automatic parameterization of user input
- Validation of identifiers and values
- Safe value types for trusted content
Flexibility
- Support for complex queries with joins and subqueries
- Dynamic query building based on conditions
- Fluent API for readable code
Performance
- Efficient string building
- Prepared statement compatibility
- Minimal runtime overhead
Best Practices
- Always Use Parameterization: Never concatenate user input directly into SQL
- Validate Identifiers: Ensure table and column names are safe
- Use Safe Types: Leverage SafeValue for trusted content
- Test Thoroughly: Verify generated SQL with your database
- Monitor Performance: Use EXPLAIN on complex generated queries
This SQL query builder approach combines the readability of string templates with the safety and flexibility of a proper query builder, making it an excellent choice for Java applications that need dynamic SQL generation without the complexity of full ORM frameworks.