Class SafeSql
This class is intended to work with JDBC Connection
API with parameters set through
the setObject()
method.
The main use case though, is to be able to compose subqueries and leaf-level parameters with an
intuitive templating API.
The syntax to create a SQL with JDBC parameters, and potentially with dynamic SQL arguments (such as column names) is as simple and intuitive as the following example:
List<String> groupColumns = ...;
SafeSql sql = SafeSql.of(
"""
SELECT `{group_columns}`, SUM(revenue) AS revenue
FROM Sales
WHERE sku = {sku}
GROUP BY `{group_columns}`
""",
groupColumns, sku, groupColumns);
List<RevenueRecord> results = sql.query(dataSource, RevenueRecord.class);
By default, all placeholder values are passed as JDBC parameters, unless quoted by backticks (used by databases like BigQuery, Databricks) or double quotes (used by databases like Oracle, Microsoft SQL Server or PostgreSQL). These are validated and interpreted as identifiers.
In the above example, placeholder sku
will be passed as JDBC parameter,
whereas the backtick-quoted groupColumns
string list will be validated and then
used as identifiers.
Except the placeholders, everything outside the curly braces are strictly WYSIWYG (what you see is what you get), so you can copy paste them between the Java code and your SQL console for quick testing and debugging.
- The
IN
Operator
IN
SQL operator:
SafeSql sql = SafeSql.of(
"""
SELECT id FROM Users
WHERE firstName = {first_name} AND lastName IN ({last_names})
""",
firstName, lastNamesList);
List<Long> ids = sql.query(dataSource, Long.class);
In the above example if firstName
is "Emma" and lastNamesList
is
["Watson", "Lin"]
, the generated SQL will be:
SELECT id FROM Employees
WHERE firstName = ? AND lastName IN (?, ?)
And the parameters will be set as:
statement.setObject(1, "Emma");
statement.setObject(2, "Watson");
statement.setObject(3, "Lin");
- Compile-time Protection
The templating engine uses compile-time checks to guard against accidental use of untrusted strings in the SQL, ensuring that they can only be sent as parameters of PreparedStatement: try to use a dynamically generated String as the SQL template and you'll get a compilation error.
In addition, the same set of compile-time guardrails from the StringFormat
class
are in effect to make sure that you don't pass lastName
in the place of
first_name
, for example.
To enable the compile-time plugin, copy the <annotationProcessorPaths>
in the
"maven-compiler-plugin" section from the following pom.xml file snippet:
<build>
<pluginManagement>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<annotationProcessorPaths>
<path>
<groupId>com.google.errorprone</groupId>
<artifactId>error_prone_core</artifactId>
<version>2.23.0</version>
</path>
<path>
<groupId>com.google.mug</groupId>
<artifactId>mug-errorprone</artifactId>
<version>9.2</version>
</path>
</annotationProcessorPaths>
</configuration>
</plugin>
</plugins>
</pluginManagement>
</build>
- Conditional Subqueries
if-else
, nested if
, loops etc. should be performed
in Java and passed in as subqueries.
Starting from v8.4, simple conditional subqueries (e.g. selecting a column if a flag is
enabled) can use the guard operator ->
inside template placeholders:
SafeSql sql = SafeSql.of(
"SELECT {shows_email -> email,} name FROM Users", showsEmail());
The query text after the ->
operator is the conditional subquery that's only included if
showEmail()
returns true. The subquery can include arbitrary characters except curly
braces, so you can also have multi-line conditional subqueries.
The ->
guard operator can also be used for Optional
parameters such that
the right-hand-side SQL will only render if the optional value is present.
For example, the following code builds SQL to query the Users table with flexible
number of columns and a flexible WHERE clause depending on the UserCriteria
object's state:
class UserCriteria {
Optional<String> userId();
Optional<String> firstName();
List<String> aliases();
...
}
SafeSql usersQuery(UserCriteria criteria, @CompileTimeConstant String... columns) {
return SafeSql.of(
"""
SELECT `{columns}`
FROM Users
WHERE 1 = 1
{user_id? -> AND id = user_id?}
{first_name? -> AND firstName LIKE '%first_name?%'}
{aliases? -> AND name IN (aliases?)}
""",
asList(columns),
criteria.userId()),
criteria.firstName(),
criteria.aliases());
}
List<User> users = usersQuery(userCriteria, "email", "lastName")
.query(dataSource, User.class);
The special "{foo? -> ...}" guard syntax informs the template engine that the
right hand side query snippet is only rendered if the Optional
parameter corresponding
to the "foo?" placeholder is present, or the Collection
paameter corresponding to it
isn't empty, in which case the value of the Optional or Collection will be used in
the right hand side snippet as if it were a regular template argument.
If UserCriteria
has specified firstName()
but userId()
is
unspecified (empty), the resulting SQL will look like:
SELECT `email`, `lastName` FROM Users WHERE firstName LIKE ?
And when you call usersQuery.prepareStatement(connection)
or one of the similar
convenience methods, statement.setObject(1, "%" + criteria.firstName().get() + "%")
will be called to populate the PreparedStatement.
- Complex Dynamic Subqueries
Imagine if you need to translate a user-facing structured search expression like
location:US AND name:jing OR status:active
into SQL. And you already have the search
expression parser that turns the search expression into an AST (abstract syntax tree).
The following code uses SafeSql template to turn it into SQL where clause that can be used to
query the database for the results:
// The AST
interface Expression permits AndExpression, OrExpression, HasExpression {}
record AndExpression(Expression left, Expression right) implements Expression {}
record OrExpression(Expression left, Expression right) implements Expression {}
record HasExpression(String field, String text) implements Expression {}
// AST -> SafeSql
SafeSql toSqlFilter(Expression expression) {
return switch (expression) {
case HasExpression(String field, String text) ->
SafeSql.of("`{field}` LIKE '%{text}%'", field, text);
case AndExpression(Expression left, Expression right) ->
SafeSql.of("({left}) AND ({right})", toSqlFilter(left), toSqlFilter(right));
case OrExpression(Expression left, Expression right) ->
SafeSql.of("({left}) OR ({right})", toSqlFilter(left), toSqlFilter(right));
};
}
SafeSql query = SafeSql.of("SELECT * FROM Foos WHERE {filter}", toSqlFilter(expression));
- Parameterize by Column Names or Table Names
If the identifiers are compile-time string literals, you can wrap them using
SafeSql.of(COLUMN_NAME)
, which can then be composed as subqueries.
But what if the identifier string is loaded from a resource file, or is specified by a
request field?
Passing the string directly as a template parameter will only generate the JDBC
'?'
parameter in its place, which won't work (PreparedStatement can't parameterize
identifiers); SafeSql.of(theString)
will fail to compile because such strings are
inherently dynamic and untrusted.
The safe way to parameterize dynamic strings as identifiers is to backtick-quote their placeholders in the SQL template (if you use Oracle, PostgreSQL that use double quotes for identifier, use double quotes instead). For example:
SafeSql.of("SELECT `{columns}` FROM Users", request.getColumns())
The backticks tell SafeSql that the string is supposed to be an identifier (or a list of
identifiers). SafeSql will sanity-check the string(s) to ensure injection safety.
In the above example, if getColumns()
returns ["id", "age"]
, the genereated
SQL will be:
SELECT `id`, `age` FROM Users
That is, each individual string will be backtick-quoted and then joined by ", ".
- The
LIKE
Operator
Note that with straight JDBC API, if you try to use the LIKE operator to match a user-provided
substring, i.e. using LIKE '%foo%'
to search for "foo", this seemingly intuitive
syntax is actually incorect:
String searchTerm = ...;
PreparedStatement statement =
connection.prepareStatement("SELECT id FROM Users WHERE firstName LIKE '%?%'");
statement.setString(1, searchTerm);
JDBC PreparedStatement considers the quoted question mark as a literal so the setString()
call will fail. You'll need to use the following workaround:
PreparedStatement statement =
connection.prepareStatement("SELECT id FROM Users WHERE firstName LIKE ?");
statement.setString(1, "%" + searchTerm + "%");
And even then, if the searchTerm
includes special characters like '%' or backslash ('\'),
they'll be interepreted as wildcards and escape characters, opening it up to a form of minor
SQL injection despite already using the parameterized SQL.
The SafeSql template protects you from this caveat. The most intuitive syntax does exactly what you'd expect (and it escapes special characters too):
String searchTerm = ...;
SafeSql sql = SafeSql.of(
"SELECT id FROM Users WHERE firstName LIKE '%{search_term}%'", searchTerm);
List<Long> ids = sql.query(dataSource, Long.class);
Automatic Escaping: No Need for ESCAPE Clause
This means you do not need to (and in fact, must not) write SQL
using ESCAPE
clauses after LIKE '%{foo}%'
. Any such attempt, as in:
SELECT name FROM Users WHERE name LIKE '%{term}%' ESCAPE '\'
...will be rejected, because SafeSql already performs all necessary escaping internally
and automatically uses ESCAPE '^'
. In other words, LIKE just works.
This eliminates the need for developers to deal with brittle double-escaping
(like '\\'
), or any cross-dialect compatibility issues.
The template is also more readable.
If you find yourself wanting to use ESCAPE
, consider whether you are
manually escaping strings that could instead be safely passed as-is to SafeSql's
template system.
That said, this only applies when template placeholder is used in the LIKE string. You can use any valid SQL ESCAPE syntax if placeholder isn't used in the LIKE expression.
- Quote String Placeholders
LIKE
operator or the percent sign (%), it may still be
more readable to quote the string placeholders just so the SQL template explicitly tells readers
that the parameter is a string. The following template works with or without the quotes around
the {id}
placeholder:
// Reads more clearly that the {id} is a string
SafeSql sql = SafeSql.of("SELECT * FROM Users WHERE id = '{id}'", userId);
- Enforce Identical Parameter
The compile-time check tries to be helpful and checks that if you use the same parameter name more than once in the template, the same value must be used for it.
So for example, if you are trying to generate a SQL that looks like:
SELECT u.firstName, p.profileId
FROM (SELECT firstName FROM Users WHERE id = 'foo') u,
(SELECT profileId FROM Profiles WHERE userId = 'foo') p
It'll be important to use the same user id for both subqueries. And you can use the following
template to make sure of it at compile time:
SafeSql sql = SafeSql.of(
"""
SELECT u.firstName, p.profileId
FROM (SELECT firstName FROM Users WHERE id = '{user_id}') u,
(SELECT profileId FROM Profiles WHERE userId = '{user_id}') p
""",
userId, userId);
If someone mistakenly passes in inconsistent ids, they'll get a compilation error.
For Spring users: in order to participate in Spring declarative transaction
(methods annotated with @Transactional
),
you need to call one of the methods that accept a Connection
, such as the
update(Connection)
method. In a nutshell, it takes calling
DataSourceUtils.getConnection(dataSource)
to get the connection in the current transaction,
then passing it to update(connection)
.
Note that you will also need to catch SQLException
and turn it into a Spring
DataAccessException. For that you need Spring's SQLExceptionTranslator
, which
has some quirks to use.
At this point, it may be easier to create a small wrapper class to execute SafeSql from within a Spring transaction. And while you are there, might as well make it safer to also support calling from outside of a transaction, by using try-with-resources to close the connection:
// Use Java 16 record for brevity. You can use a regular class too.
@Component
public record SafeSqlBridge(DataSource dataSource, SQLExceptionTranslator translator) {
public int executeUpdate(SafeSql sql) {
try {
if (TransactionSynchronizationManager.isActualTransactionActive()) {
// in an active transaction, don't close or release the connection.
return sql.update(DataSourceUtils.getConnection(dataSource()));
} else {
// not in active transaction, should close the connection.
try (Connection connection = dataSource().getConnection()) {
return sql.update(connection);
}
}
} catch (SQLException e) {
DataAccessException dae =
translator().translate("executeUpdate(SafeSql)", sql.debugString(), e);
throw dae == null ? throw new UncheckedSqlException(e) : dae;
}
}
}
You can then dependency-inject SafeSqlBridge to execute SafeSql queries:
// Use Java 16 record for brevity. You can use a regular class too.
@Service
record MyService(SafeSqlBridge bridge) {
@Transactional void transferCredit(String fromAccount, String toAccount) {
SafeSql sql = SafeSql.of("INSERT INTO(...)...'{from}'...'{to}'", fromAccount(), toAccount());
bridge().executeUpdate(sql);
}
}
Immutable if the template parameters you pass to it are immutable.
Starting from v9.0, SafeSql is moved to the mug-safesql artifact, and no longer requires Guava as a dependency.
- Since:
- 8.2
-
Field Summary
Fields -
Method Summary
Modifier and TypeMethodDescriptionand()
A collector that joins boolean query snippet usingAND
operator.Returns a query string with the parameter values embedded for easier debugging (logging, testing, golden file etc.).boolean
int
hashCode()
Returns a collector that joins SafeSql elements usingdelimiter
.static SafeSql
nonNegativeLiteral
(int number) Deprecated.static SafeSql
static SafeSql
optionally
(String query, Optional<?> param) Deprecated.UseSafeSql.of("{foo? -> OR foo?}", optionalFoo)
instead ofoptionally("or {foo}", optionalFoo)
because the former allows you to referencefoo?
multiple times in the right hand side snippet.or()
A collector that joins boolean query snippet usingOR
operator.Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns thefallback
query.Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the SafeSql produced from thefallback
template andargs
.Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the query produced by thefallback
supplier.prepareStatement
(Connection connection) Returns aPreparedStatement
with the encapsulated sql and parameters.static <T> StringFormat.Template
<List<T>> prepareToQuery
(Connection connection, String template, SqlFunction<? super ResultSet, ? extends T> rowMapper) Returns a query template that will reuse the same cachedPreparedStatement
for repeated calls ofStringFormat.Template.with(java.lang.Object...)
using different parameters.static <T> StringFormat.Template
<List<T>> prepareToQuery
(Connection connection, String template, Class<? extends T> resultType) Returns a query template that will reuse the same cachedPreparedStatement
for repeated calls ofStringFormat.Template.with(java.lang.Object...)
using different parameters.static StringFormat.Template
<Integer> prepareToUpdate
(Connection connection, String template) Returns a DML (create, update, delete) template that will reuse the same cachedPreparedStatement
for repeated calls ofStringFormat.Template.with(java.lang.Object...)
using different parameters.<T> List
<T> query
(Connection connection, SqlFunction<? super ResultSet, ? extends T> rowMapper) Similar toquery(DataSource, SqlFunction)
, but uses an existing connection.<T> List
<T> query
(Connection connection, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) Similar toquery(DataSource, StatementSettings, SqlFunction)
, but uses an existing connection.<T> List
<T> query
(Connection connection, StatementSettings settings, Class<? extends T> resultType) Similar toquery(DataSource, StatementSettings, Class)
, but uses an existing connection.<T> List
<T> query
(Connection connection, Class<? extends T> resultType) Similar toquery(DataSource, Class)
, but uses an existing connection.<T> List
<T> query
(DataSource dataSource, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstdataSource.getConnection()
.<T> List
<T> query
(DataSource dataSource, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstdataSource.getConnection()
, usingsettings
(can be set via lambda likestmt -> stmt.setMaxRows(100)
).<T> List
<T> query
(DataSource dataSource, StatementSettings settings, Class<? extends T> resultType) Similar toquery(DataSource, Class)
, but withsettings
(can be set via lambda likestmt -> stmt.setMaxRows(100)
) to allow customization.<T> List
<T> query
(DataSource dataSource, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstdataSource.getConnection()
.<T> Optional
<T> queryForOne
(Connection connection, SqlFunction<? super ResultSet, ? extends T> rowMapper) Similar toquery(Connection, SqlFunction)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.<T> Optional
<T> queryForOne
(Connection connection, Class<? extends T> resultType) Similar toquery(Connection, Class)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.<T> Optional
<T> queryForOne
(DataSource dataSource, SqlFunction<? super ResultSet, ? extends T> rowMapper) Similar toquery(DataSource, SqlFunction)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.<T> Optional
<T> queryForOne
(DataSource dataSource, Class<? extends T> resultType) Similar toquery(DataSource, Class)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.<T> Stream
<T> queryLazily
(Connection connection, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(Connection connection, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstconnection
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
, and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(Connection connection, StatementSettings settings, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstconnection
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
), and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(Connection connection, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(DataSource dataSource, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(DataSource dataSource, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstdataSource
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
, and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(DataSource dataSource, StatementSettings settings, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstconnection
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
), and then fetches the results lazily in a stream.<T> Stream
<T> queryLazily
(DataSource dataSource, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.static StringFormat.Template
<SafeSql> toString()
Returns the SQL text with the template parameters translated to the JDBC'?'
placeholders.int
update
(Connection connection) Executes the encapsulated DML (create, update, delete statements) againstconnection
and returns the number of affected rows.int
update
(Connection connection, StatementSettings settings) Similar toupdate(Connection)
, but withsettings
(can be set via lambda likestmt -> stmt.setQueryTimeout(100)
) to allow customization.int
update
(DataSource dataSource) Executes the encapsulated DML (create, update, delete statements) againstdataSource
and returns the number of affected rows.int
update
(DataSource dataSource, StatementSettings settings) Similar toupdate(DataSource)
, but withsettings
(can be set via lambda likestmt -> stmt.setQueryTimeout(100)
) to allow customization.when
(boolean condition) Returns this SafeSql ifcondition
is true; otherwise returnsEMPTY
.static SafeSql
An optional query that's only rendered ifcondition
is true; otherwise returnsEMPTY
.
-
Field Details
-
EMPTY
An empty SQL
-
-
Method Details
-
of
@TemplateFormatMethod public static SafeSql of(@TemplateString @CompileTimeConstant String template, Object... params) ReturnsSafeSql
usingtemplate
andparams
.For example:
List<Long> jobIds = SafeSql.of( "SELECT id FROM Jobs WHERE timestamp BETWEEN {start} AND {end}", startTime, endTime) .query(dataSource, Long.class);
Note that if you plan to create a
PreparedStatement
and use it multiple times with different sets of parameters, it's more efficient to useprepareToQuery()
orprepareToUpdate()
, which will reuse the same PreparedStatement for multiple calls. The returnedStringFormat.Template
s are protected at compile-time against incorrect varargs.- Parameters:
template
- the sql templateparams
- The template parameters. Parameters that are themselvesSafeSql
are considered trusted subqueries and are appended directly. Other types are passed through JDBCPreparedStatement.setObject(int, java.lang.Object, int)
, with one exception: when the corresponding placeholder is quoted by backticks like`{columns}`
, its string parameter value (orIterable<String>
parameter value) are directly appended (quotes, backticks, backslash and other special characters are disallowed). This makes it easy to parameterize by table names, column names etc.
-
nonNegativeLiteral
Deprecated.PreferOFFSET-FETCH
clause, which is parameterizableWraps non-negativenumber
as a literal SQL snippet in a SafeSql object.For example, the following SQL Server query allows parameterization by the TOP n number:
SafeSql.of("SELECT TOP {page_size} UserId FROM Users", nonNegativeLiteral(pageSize))
Needed because the SQL Server JDBC driver doesn't support parameterizing the TOP number through
PreparedStatement
API.Although, you should generally use the OFFSET-FETCH syntax, which supports parameterization.
- Throws:
IllegalArgumentException
- ifnumber
is negative
-
optionally
@TemplateFormatMethod @Deprecated public static SafeSql optionally(@TemplateString @CompileTimeConstant String query, Optional<?> param) Deprecated.UseSafeSql.of("{foo? -> OR foo?}", optionalFoo)
instead ofoptionally("or {foo}", optionalFoo)
because the former allows you to referencefoo?
multiple times in the right hand side snippet.An optional query that's only rendered ifparam
is present; otherwise returnsEMPTY
. It's for use cases where a subquery is only added when present, for example the following query will add the WHERE clause if the filter is present:SafeSql query = SafeSql.of( "SELECT * FROM jobs {where}", SafeSql.optionally("WHERE {filter}", getOptionalWhereClause()));
-
when
@TemplateFormatMethod public static SafeSql when(boolean condition, @TemplateString @CompileTimeConstant String template, Object... params) An optional query that's only rendered ifcondition
is true; otherwise returnsEMPTY
. It's for use cases where a subquery is only conditionally added, for example the following query will only include the userEmail column under super user mode:SafeSql query = SafeSql.of( "SELECT job_id, start_timestamp {user_email} FROM jobs", SafeSql.when(isSuperUser, ", user_email"));
- Parameters:
condition
- the guard condition to determine iftemplate
should be renderdtemplate
- the template to render ifcondition
is trueparams
- seeof(String, Object...)
for discussion on the template arguments
-
when
-
template
Returns aStringFormat.Template
ofSafeSql
based on thetemplate
string. Useful for creating a constant to be reused with different parameters.For example:
private static final Template<SafeSql> FIND_USERS_BY_NAME = SafeSql.template("SELECT `{columns}` FROM Users WHERE name LIKE '%{name}%'"); String searchBy = ...; List<User> userIds = FIND_USERS_BY_NAME.with(asList("id", "name"), searchBy) .query(dataSource, User.class);
If you don't need a reusable template, consider using
of(java.lang.String, java.lang.Object...)
instead, which is simpler.The template arguments follow the same rules as discussed in
of(String, Object...)
and receives the same compile-time protection against mismatch or out-of-order human mistakes, so it's safe to use the template as a constant.The returned template is immutable and thread safe.
-
and
A collector that joins boolean query snippet usingAND
operator. The AND'ed sub-queries will be enclosed in pairs of parenthesis to avoid ambiguity. If the input is empty, the result will be "(1 = 1)".Empty SafeSql elements are ignored and not joined.
-
or
-
joining
-
orElse
@TemplateFormatMethod public SafeSql orElse(@TemplateString @CompileTimeConstant String fallback, Object... params) Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the SafeSql produced from thefallback
template andargs
.Using this method, you can create a chain of optional queries like:
SafeSql.of( """ CREATE TABLE ... {cluster_by} """, when(enableCluster, "CLUSTER BY (`{cluster_columns}`)", clusterColumns) .orElse("-- no cluster"));
- Since:
- 8.3
-
orElse
Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns thefallback
query.- Since:
- 8.3
-
orElse
Ifthis
query is empty (likely from a call tooptionally(java.lang.String, java.util.Optional<?>)
orwhen(boolean, java.lang.String, java.lang.Object...)
), returns the query produced by thefallback
supplier.- Since:
- 8.3
-
query
Executes the encapsulated SQL as a query againstdataSource.getConnection()
. TheResultSet
will be consumed, transformed to a list ofT
and then closed before returning.For example:
List<User> users = SafeSql.of("SELECT id, name FROM Users WHERE name LIKE '%{name}%'", name) .query(dataSource, User.class); record User(long id, String name) {...}
The class of
resultType
must define a non-private constructor that accepts the same number of parameters as returned by the query. The parameter order doesn't matter but the parameter names and types must match.Note that if you've enabled the
-parameters
javac flag, the above example code will just work. If you can't enable-parameters
, consider explicitly annotating the constructor parameters as in:record User(@SqlName("id") long id, @SqlName("name") String name) {...}
Alternatively, if your query only selects one column, you could also use this method to read the results:
List<String> names = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name) .query(dataSource, String.class);
You can also map the result rows to Java Beans, for example:
List<UserBean> users = SafeSql.of("SELECT id, name FROM Users WHERE name LIKE '%{name}%'", name) .query(dataSource, UserBean.class); public class UserBean { public void setId(long id) {...} public void setName(String name) {...} }
The rules of mapping query columns to Java Bean properties are:
- Case doesn't matter.
job_id
will matchjobId
orJOB_ID
. - The column names can be a superset, or a subset of the bean property names. This allows you to use "select *" in the query (when performance isn't a concern), or use a generic Java bean that may have more properties than some individual queries.
- If a bean property is of primitive type, and the corresponding query column value is null, the property will be left as is.
- If you can't make a bean property match a query column, consider annotating the setter
method with the
@SqlName
annotation to customize the column name. - Exception will be thrown if a column doesn't map to a settable property, and the columns aren't a superset. For example, you may have renamed a property but forgot to rename the corresponding query column. In such case, failing loudly and clearly is safer than letting the program silently run with corrupted state.
- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.0
- Case doesn't matter.
-
query
Similar toquery(DataSource, Class)
, but uses an existing connection.It's usually more convenient to use the
DataSource
overload because you won't have to manage the JDBC resources. Use this method if you need to reuse a connection (mostly for multi-statement transactions).- Throws:
SQLException
- Since:
- 8.7
-
query
public <T> List<T> query(DataSource dataSource, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstdataSource.getConnection()
. TheResultSet
will be consumed, transformed byrowMapper
and then closed before returning.For example:
List<Long> ids = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name) .query(dataSource, row -> row.getLong("id"));
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.0
-
query
public <T> List<T> query(DataSource dataSource, StatementSettings settings, Class<? extends T> resultType) Similar toquery(DataSource, Class)
, but withsettings
(can be set via lambda likestmt -> stmt.setMaxRows(100)
) to allow customization.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.0
-
query
public <T> List<T> query(Connection connection, StatementSettings settings, Class<? extends T> resultType) throws SQLException Similar toquery(DataSource, StatementSettings, Class)
, but uses an existing connection.It's usually more convenient to use the
DataSource
overload because you won't have to manage the JDBC resources. Use this method if you need to reuse a connection (mostly for multi-statement transactions).- Throws:
SQLException
- Since:
- 9.0
-
query
public <T> List<T> query(DataSource dataSource, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstdataSource.getConnection()
, usingsettings
(can be set via lambda likestmt -> stmt.setMaxRows(100)
). TheResultSet
will be consumed, transformed byrowMapper
and then closed before returning.For example:
List<Long> ids = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name) .query(dataSource, stmt -> stmt.setMaxRows(100000), row -> row.getLong("id"));
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.0
-
query
public <T> List<T> query(Connection connection, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) throws SQLException Similar toquery(DataSource, StatementSettings, SqlFunction)
, but uses an existing connection.It's usually more convenient to use the
DataSource
overload because you won't have to manage the JDBC resources. Use this method if you need to reuse a connection (mostly for multi-statement transactions).For example:
List<Long> ids = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name) .query(connection, stmt -> stmt.setMaxRows(100000), row -> row.getLong("id"));
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
SQLException
- Since:
- 9.0
-
query
public <T> List<T> query(Connection connection, SqlFunction<? super ResultSet, ? extends T> rowMapper) throws SQLException Similar toquery(DataSource, SqlFunction)
, but uses an existing connection.It's usually more convenient to use the
DataSource
overload because you won't have to manage the JDBC resources. Use this method if you need to reuse a connection (mostly for multi-statement transactions).For example:
List<Long> ids = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name) .query(connection, row -> row.getLong("id"));
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
SQLException
-
queryForOne
Similar toquery(DataSource, Class)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.Suitable for queries that search by the primary key, for example:
Optional<User> user = SafeSql.of("select id, name from Users where id = {id}", userId) .queryForOne(dataSource, User.class);
- Throws:
UncheckedSqlException
- wrapsSQLException
if failedNullPointerException
- if the first column value is null, andresultType
isn't a record or Java Bean.- Since:
- 9.2
-
queryForOne
public <T> Optional<T> queryForOne(Connection connection, Class<? extends T> resultType) throws SQLException Similar toquery(Connection, Class)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.Suitable for queries that search by the primary key, for example:
Optional<User> user = SafeSql.of("select id, name from Users where id = {id}", userId) .queryForOne(connection, User.class);
- Throws:
NullPointerException
- if the first column value is null, andresultType
isn't a record or Java Bean.SQLException
- Since:
- 9.2
-
queryForOne
public <T> Optional<T> queryForOne(DataSource dataSource, SqlFunction<? super ResultSet, ? extends T> rowMapper) Similar toquery(DataSource, SqlFunction)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.Suitable for queries that search by the primary key.
- Throws:
UncheckedSqlException
- wrapsSQLException
if failedNullPointerException
- ifrowMapper
returns null- Since:
- 9.2
-
queryForOne
public <T> Optional<T> queryForOne(Connection connection, SqlFunction<? super ResultSet, ? extends T> rowMapper) throws SQLException Similar toquery(Connection, SqlFunction)
, but only fetches one row if the query result includes at least one rows, or else returnsOptional.empty()
.Suitable for queries that search by the primary key.
- Throws:
NullPointerException
- ifrowMapper
returns nullSQLException
- Since:
- 9.2
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(DataSource dataSource, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.Each result row is transformed into
resultType
.The caller must close it using try-with-resources idiom, which will close the associated
Statement
andResultSet
.For example:
SafeSql sql = SafeSql.of("SELECT id, name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<User> users = sql.queryLazily(connection, User.class)) { return users.findFirst(); } record User(long id, String name) {...}
The class of
resultType
must define a non-private constructor that accepts the same number of parameters as returned by the query. The parameter order doesn't matter but the parameter names and types must match.Note that if you've enabled the
-parameters
javac flag, the above example code will just work. If you can't enable-parameters
, consider explicitly annotating the constructor parameters as in:record User(@SqlName("id") long id, @SqlName("name") String name) {...}
Alternatively, if your query only selects one column, you could also use this method to read the results:
SafeSql sql = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<Long> ids = sql.queryLazily(dataSource, Long.class)) { return ids.findFirst(); }
You can also map the result rows to Java Beans, similar to
query(Connection, Class)
.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.2
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(DataSource dataSource, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.The returned
Stream
includes results transformed byrowMapper
. The caller must close it using try-with-resources idiom, which will close the associatedConnection
,Statement
andResultSet
.For example:
SafeSql sql = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<String> names = sql.queryLazily(dataSource, row -> row.getString("name"))) { return names.findFirst(); }
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.2
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(DataSource dataSource, StatementSettings settings, Class<? extends T> resultType) Executes the encapsulated SQL as a query againstconnection
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
), and then fetches the results lazily in a stream.Each result row is transformed into
resultType
.For example:
SafeSql sql = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<String> names = sql.queryLazily( dataSource, stmt -> stmt.setFetchSize(100), String.class)) { return names.findFirst(); }
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.2
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(DataSource dataSource, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) Executes the encapsulated SQL as a query againstdataSource
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
, and then fetches the results lazily in a stream.The returned
Stream
includes results transformed byrowMapper
. The caller must close it using try-with-resources idiom, which will close the associatedConnection
,Statement
andResultSet
.For example:
SafeSql sql = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<String> names = sql.queryLazily( dataSource, stmt -> stmt.setFetchSize(100), row -> row.getString("name"))) { return names.findFirst(); }
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.2
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(Connection connection, Class<? extends T> resultType) throws SQLException Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.Each result row is transformed into
resultType
.The caller must close it using try-with-resources idiom, which will close the associated
Statement
andResultSet
.For example:
SafeSql sql = SafeSql.of("SELECT id, name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<User> users = sql.queryLazily(connection, User.class)) { return users.findFirst(); } record User(long id, String name) {...}
The class of
resultType
must define a non-private constructor that accepts the same number of parameters as returned by the query. The parameter order doesn't matter but the parameter names and types must match.Note that if you've enabled the
-parameters
javac flag, the above example code will just work. If you can't enable-parameters
, consider explicitly annotating the constructor parameters as in:record User(@SqlName("id") long id, @SqlName("name") String name) {...}
Alternatively, if your query only selects one column, you could also use this method to read the results:
SafeSql sql = SafeSql.of("SELECT id FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<Long> ids = sql.queryLazily(connection, Long.class)) { return ids.findFirst(); }
You can also map the result rows to Java Beans, similar to
query(Connection, Class)
.- Throws:
SQLException
- Since:
- 8.7
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(Connection connection, SqlFunction<? super ResultSet, ? extends T> rowMapper) throws SQLException Executes the encapsulated SQL as a query againstconnection
, and then fetches the results lazily in a stream.The returned
Stream
includes results transformed byrowMapper
. The caller must close it using try-with-resources idiom, which will close the associatedStatement
andResultSet
.For example:
SafeSql sql = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<String> names = sql.queryLazily(connection, row -> row.getString("name"))) { return names.findFirst(); }
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
SQLException
- Since:
- 8.4
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(Connection connection, StatementSettings settings, Class<? extends T> resultType) throws SQLException Executes the encapsulated SQL as a query againstconnection
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
), and then fetches the results lazily in a stream.Each result row is transformed into
resultType
.For example:
SafeSql sql = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<String> names = sql.queryLazily( connection, stmt -> stmt.setFetchSize(100), String.class)) { return names.findFirst(); }
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
SQLException
- Since:
- 9.0
-
queryLazily
@MustBeClosed public <T> Stream<T> queryLazily(Connection connection, StatementSettings settings, SqlFunction<? super ResultSet, ? extends T> rowMapper) throws SQLException Executes the encapsulated SQL as a query againstconnection
, withsettings
(can be set via lambda likestmt -> stmt.setFetchSize(100)
, and then fetches the results lazily in a stream.The returned
Stream
includes results transformed byrowMapper
. The caller must close it using try-with-resources idiom, which will close the associatedStatement
andResultSet
.For example:
SafeSql sql = SafeSql.of("SELECT name FROM Users WHERE name LIKE '%{name}%'", name); try (Stream<String> names = sql.queryLazily( connection, stmt -> stmt.setFetchSize(100), row -> row.getString("name"))) { return names.findFirst(); }
Internally it delegates to
PreparedStatement.executeQuery()
orStatement.executeQuery(java.lang.String)
if this sql contains no JDBC binding parameters.- Throws:
SQLException
- Since:
- 9.0
-
update
Executes the encapsulated DML (create, update, delete statements) againstdataSource
and returns the number of affected rows.For example:
SafeSql.of("INSERT INTO Users(id, name) VALUES({id}, '{name}')", id, name) .update(dataSource);
Internally it delegates to
PreparedStatement.executeUpdate()
.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.2
-
update
Similar toupdate(DataSource)
, but withsettings
(can be set via lambda likestmt -> stmt.setQueryTimeout(100)
) to allow customization.- Throws:
UncheckedSqlException
- wrapsSQLException
if failed- Since:
- 9.2
-
update
Executes the encapsulated DML (create, update, delete statements) againstconnection
and returns the number of affected rows.For example:
SafeSql.of("INSERT INTO Users(id, name) VALUES({id}, '{name}')", id, name) .update(connection);
Internally it delegates to
PreparedStatement.executeUpdate()
.- Throws:
SQLException
-
update
@CanIgnoreReturnValue public int update(Connection connection, StatementSettings settings) throws SQLException Similar toupdate(Connection)
, but withsettings
(can be set via lambda likestmt -> stmt.setQueryTimeout(100)
) to allow customization.- Throws:
SQLException
- Since:
- 9.0
-
prepareStatement
Returns aPreparedStatement
with the encapsulated sql and parameters.It's often more convenient to use
query(javax.sql.DataSource, java.lang.Class<? extends T>)
orupdate(javax.sql.DataSource)
unless you need to directly operate on the PreparedStatement.- Throws:
SQLException
-
prepareToQuery
public static <T> StringFormat.Template<List<T>> prepareToQuery(Connection connection, @CompileTimeConstant String template, Class<? extends T> resultType) Returns a query template that will reuse the same cachedPreparedStatement
for repeated calls ofStringFormat.Template.with(java.lang.Object...)
using different parameters.Allows callers to take advantage of the performance benefit of PreparedStatement without having to re-create the statement for each call. For example in:
Each timetry (var connection = ...) { var queryByName = SafeSql.prepareToQuery( connection, "SELECT id, name FROM Users WHERE name LIKE '%{name}%'", User.class); for (String name : names) { for (User user : queryByName.with(name))) { ... } } } record User(long id, String name) {...}
queryByName.with(name)
is called, it executes the same query template against the connection, but with a differentname
parameter. Internally it reuses the cached PreparedStatement object and just callsPreparedStatement.setObject(int, Object)
with the new set of parameters before callingPreparedStatement.executeQuery()
.The template arguments follow the same rules as discussed in
of(String, Object...)
and receives the same compile-time protection against mismatch or out-of-order human mistakes.The returned Template is not thread safe.
The caller is expected to close the
connection
after done, which will close the cached PreparedStatement. TheResultSet
objects are guaranteed to be closed after each use before the PreparedStatement is closed.- Since:
- 8.7
-
prepareToQuery
public static <T> StringFormat.Template<List<T>> prepareToQuery(Connection connection, @CompileTimeConstant String template, SqlFunction<? super ResultSet, ? extends T> rowMapper) Returns a query template that will reuse the same cachedPreparedStatement
for repeated calls ofStringFormat.Template.with(java.lang.Object...)
using different parameters.Allows callers to take advantage of the performance benefit of PreparedStatement without having to re-create the statement for each call. For example in:
Each timetry (var connection = ...) { var queryByName = SafeSql.prepareToQuery( connection, "SELECT id FROM Users WHERE name LIKE '%{name}%'", row -> row.getLong("id")); for (String name : names) { for (long id : queryByName.with(name))) { ... } } }
queryByName.with(name)
is called, it executes the same query template against the connection, but with a differentname
parameter. Internally it reuses the cached PreparedStatement object and just callsPreparedStatement.setObject(int, Object)
with the new set of parameters before callingPreparedStatement.executeQuery()
.The template arguments follow the same rules as discussed in
of(String, Object...)
and receives the same compile-time protection against mismatch or out-of-order human mistakes.The returned Template is not thread safe.
The caller is expected to close the
connection
after done, which will close the cached PreparedStatement. TheResultSet
objects are guaranteed to be closed after each use before the PreparedStatement is closed. -
prepareToUpdate
public static StringFormat.Template<Integer> prepareToUpdate(Connection connection, @CompileTimeConstant String template) Returns a DML (create, update, delete) template that will reuse the same cachedPreparedStatement
for repeated calls ofStringFormat.Template.with(java.lang.Object...)
using different parameters.Allows callers to take advantage of the performance benefit of PreparedStatement without having to re-create the statement for each call. For example in:
Each timetry (var connection = ...) { var insertUser = SafeSql.prepareToUpdate( connection, "INSERT INTO Users(id, name) VALUES({id}, '{name}')"); int totalRowsAffected = insertUser.with(1, "Tom") + insertUser.with(2, "Emma"); }
insertUser.with(...)
is called, it executes the same DML template against the connection, but with differentid
andname
parameters. Internally it reuses the cached PreparedStatement object and just callsPreparedStatement.setObject(int, Object)
with the new set of parameters before callingPreparedStatement.executeUpdate()
.The template arguments follow the same rules as discussed in
of(String, Object...)
and receives the same compile-time protection against mismatch or out-of-order human mistakes.The returned Template is not thread safe because the cached
PreparedStatement
objects aren't.The caller is expected to close the
connection
after done, which will close the cached PreparedStatement. -
debugString
Returns a query string with the parameter values embedded for easier debugging (logging, testing, golden file etc.). DO NOT use it as the production SQL query because embedding the parameter values isn't safe from SQL injection.- Since:
- 9.0
-
toString
-
hashCode
-
equals
-
OFFSET-FETCH
clause, which is parameterizable