SQL Query Builder with String Templates in Java

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.