Class ParameterizedQuery

java.lang.Object
com.google.mu.spanner.ParameterizedQuery
@Immutable public final class ParameterizedQuery extends Object
An injection-safe SQL template for Google Cloud Spanner.

Spanner SQL is constructed using compile-time enforced templates, with support for dynamic SQL and flexible subquery composition. The library manages the parameterized query and the query parameters automatically, across all subqueries.

Use this class to create Spanner SQL template with parameters, to compose subqueries, and even to parameterize by identifiers (table names, column names), without risking injection. For example: 


 List<String> groupColumns = ...;
 ParameterizedQuery query = ParameterizedQuery.of(
     """
     SELECT `{group_columns}`, SUM(revenue) AS revenue
     FROM Sales
     WHERE sku = '{sku}'
     GROUP BY `{group_columns}`
     """,
     groupColumns, sku, groupColumns);
 try (ResultSet resultSet = dbClient.singleUse().executeQuery(query.statement())) {
   ...
 }

If a placeholder is quoted by backticks (`) or double quotes, it's interpreted and validated as an identifier; all other placeholder values (except `ParameterizedQuery` objects, which are subqueries) are passed through Spanner's parameterization query.

To be explicit, a compile-time check is in place to require all non-identifier string placeholders to be single-quoted: this makes the template more self-evident, and helps to avoid the mistake of forgetting the quotes and then sending the column name as a query parameter.

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.

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 Spanner query parameters: 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
ParameterizedQuery's template syntax is designed to avoid control flows that could obfuscate SQL. Instead, complex control flow such as if-else, nested if, loops etc. should be performed in Java and passed in as subqueries.

Simple conditional subqueries (e.g. selecting a column if a flag is enabled) can use the guard operator -> inside template placeholders: 


   ParameterizedQuery sql = ParameterizedQuery.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();
     ...
   }

   ParameterizedQuery usersQuery(UserCriteria criteria, @CompileTimeConstant String... columns) {
     return ParameterizedQuery.of(
         """
         SELECT `{columns}`
         FROM Users
         WHERE 1 = 1
             {user_id? -> AND id = user_id?}
             {first_name? -> AND firstName = 'first_name?'}
             {alises? -> AND name IN (aliases?)}
         """,
         asList(columns),
         criteria.userId()),
         criteria.firstName(),
         criteria.aliases());
   }

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 = @first_name
Complex Dynamic Subqueries
By composing ParameterizedQuery objects that encapsulate subqueries, you can also parameterize by arbitrary sub-queries that are computed dynamically.

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 ParameterizedQuery template to turn it into a spanner SQL filter that can be used in a where clause to query Spanner for the results: 



 // The AST
 interface Expression permits AndExpression, OrExpression, MatchExpression {}

 record AndExpression(Expression left, Expression right) implements Expression {}
 record OrExpression(Expression left, Expression right) implements Expression {}
 record MatchExpression(String field, String text) implements Expression {}

 // AST -> ParameterizedQuery
 ParameterizedQuery toSqlFilter(Expression expression) {
   return switch (expression) {
     case MatchExpression(String field, String text) ->
         ParameterizedQuery.of("`{field}` = '{text}'", field, text);
     case AndExpression(Expression left, Expression right) ->
         ParameterizedQuery.of("({left}) AND ({right})", toSqlFilter(left), toSqlFilter(right));
     case OrExpression(Expression left, Expression right) ->
         ParameterizedQuery.of("({left}) OR ({right})", toSqlFilter(left), toSqlFilter(right));
   };
 }

 ParameterizedQuery query = ParameterizedQuery.of(
     "SELECT * FROM Foos WHERE {filter}", toSqlFilter(expression));
Parameterize by Column Names or Table Names
Sometimes you may wish to parameterize by table names, column names etc. for which Spanner parameterization has no support.

The safe way to parameterize dynamic strings as identifiers is to backtick-quote their placeholders in the SQL template. For example: 


   ParameterizedQuery.of("SELECT `{columns}` FROM Users", request.getColumns())
The backticks tell ParameterizedQuery that the string is supposed to be an identifier (or a list of identifiers). ParameterizedQuery 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 ", ".

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: 

   ParameterizedQuery sql = ParameterizedQuery.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.

That said, placeholder names used in different subqueries are completely independent. There is no risk of name clash. The template will ensure the final parameter name uniqueness.

Since:
9.0

  • Field Details

  • Method Details

    • of

      @TemplateFormatMethod @TemplateStringArgsMustBeQuoted public static ParameterizedQuery of(@TemplateString @CompileTimeConstant String template, Object... params)
      Returns ParameterizedQuery using template and params.

      For example: 

      
 List<Long> jobIds = ParameterizedQuery.of(
         "SELECT id FROM Jobs WHERE timestamp BETWEEN {start} AND {end}",
         startTime, endTime)
     .query(dataSource, Long.class);
      Parameters:
      template - the sql template
      params - The template parameters. Parameters that are themselves ParameterizedQuery are considered trusted subqueries and are appended directly. Other types are passed through as Spanner query parameters, with one exception: when the corresponding placeholder is quoted by backticks like `{columns}`, its string parameter value (or Iterable<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.

    • when

      @TemplateFormatMethod @TemplateStringArgsMustBeQuoted public static ParameterizedQuery when(boolean condition, @TemplateString @CompileTimeConstant String template, Object... params)
      An optional query that's only rendered if condition is true; otherwise returns EMPTY. 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: 
      
 ParameterizedQuery query = ParameterizedQuery.of(
     "SELECT job_id, start_timestamp {user_email} FROM jobs",
     ParameterizedQuery.when(isSuperUser, ", user_email"));
      Parameters:
      condition - the guard condition to determine if template should be renderd
      template - the template to render if condition is true
      params - see of(String, Object...) for discussion on the template arguments

    • when

      public ParameterizedQuery when(boolean condition)
      Returns this ParameterizedQuery if condition is true; otherwise returns EMPTY.

    • and

      public static Collector<ParameterizedQuery, ?, ParameterizedQuery> and()
      A collector that joins boolean query snippet using AND 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 ParameterizedQuery elements are ignored and not joined.

    • or

      public static Collector<ParameterizedQuery, ?, ParameterizedQuery> or()
      A collector that joins boolean query snippet using OR operator. The OR'ed sub-queries will be enclosed in pairs of parenthesis to avoid ambiguity. If the input is empty, the result will be "(1 = 0)".

      Empty ParameterizedQuery elements are ignored and not joined.

    • joining

      public static Collector<ParameterizedQuery, ?, ParameterizedQuery> joining(@CompileTimeConstant String delimiter)
      Returns a collector that joins ParameterizedQuery elements using delimiter.

      Empty ParameterizedQuery elements are ignored and not joined.

    • orElse

      @TemplateFormatMethod @TemplateStringArgsMustBeQuoted public ParameterizedQuery orElse(@TemplateString @CompileTimeConstant String fallback, Object... params)
      If this query is empty (likely from a call to when(boolean, java.lang.String, java.lang.Object...)), returns the ParameterizedQuery produced from the fallback template and args.

      Using this method, you can create a chain of optional queries like: 

      
 ParameterizedQuery.of(
     """
     CREATE TABLE ...
     {cluster_by}
     """,
     when(enableCluster, "CLUSTER BY (`{cluster_columns}`)", clusterColumns)
         .orElse("-- no cluster"));

    • orElse

      public ParameterizedQuery orElse(ParameterizedQuery fallback)
      If this query is empty (likely from a call to when(boolean, java.lang.String, java.lang.Object...)), returns the fallback query.

    • orElse

      public ParameterizedQuery orElse(Supplier<ParameterizedQuery> fallback)
      If this query is empty (likely from a call to when(boolean, java.lang.String, java.lang.Object...)), returns the query produced by the fallback supplier.

    • statement

      public Statement statement()
      Creates an equivalent Statement to be passed to Spanner.

      This is how ParameterizedQuery is eventually consumed.

    • toString

      public String toString()
      Returns the SQL text with the template parameters translated to named Spanner parameters, annotated with parameter values.
      Overrides:
      toString in class Object

    • hashCode

      public int hashCode()
      Overrides:
      hashCode in class Object

    • equals

      public boolean equals(Object obj)
      Overrides:
      equals in class Object