Class ParameterizedQuery
Instances of this class are created from a compile-time template
.
Template arguments are protected by the same set of compile-time checks that protect StringFormat
.
For simple use cases, a one-liner is enough to construct a parameterized query. For example:
ParameterizedQuery query = ParameterizedQuery.of(
"SELECT name FROM Students WHERE id = {id} and status = {status}",
studentId, Status.ENROLLED);
TableResult result = query.run();
If you need to reuse the same query for different parameters, or to get a long query "out of the way", you can define the query template as a class constant:
private static final Template<ParameterizedQuery> GET_STUDENT = ParameterizedQuery.template(
"SELECT name FROM Students WHERE id = {id} and status = {status}");
// 200 lines later
TableResult enrolled = GET_STUDENT.with(studentId, Status.ENROLLED).run();
TableResult graduated = GET_STUDENT.with(alumniId, Status.GRADUATED).run();
Compared to building the QueryJobConfiguration
object manually, you get the following benefits:
- Automatic type conversion. Particularly,
Instant
andLocalDate
are formatted and converted toTIMESTAMP
andDATE
parameters respectively. - Concise API for common use cases.
- Compile-time safety for defining the template as a class constant.
In addition to parameterizing by values, you can also parameterize by columns, table names or sub-queries. The following example allows you to use the same query on different datasets:
private static final Template<ParameterizedQuery> GET_TABLES = ParameterizedQuery.template(
"SELECT table_name FROM `{dataset}.INFORMATION_SCHEMA.TABLES`");
TableResult marketingTables = GET_TABLES.with(ParameterizedQuery.of("marketing")).run();
TableResult humanResourceTables = GET_TABLES.with(ParameterizedQuery.of("human-resource")).run();
Non-value string parameters must be wrapped inside ParameterizedQuery
to ensure safety.- Since:
- 7.1
-
Method Summary
Modifier and TypeMethodDescriptionstatic Stream
<ParameterizedQuery> enumConstants
(Class<? extends Enum<?>> enumClass) Returns the stream of enum constants defined byenumClass
, with the names wrapped in ParameterizedQuery}.boolean
int
hashCode()
com.google.cloud.bigquery.QueryJobConfiguration
Returns theQueryJobConfiguration
that can be sent to BigQuery.static Collector
<ParameterizedQuery, ?, ParameterizedQuery> Returns a collector that joins ParameterizedQuery elements usingdelimiter
.static ParameterizedQuery
Convenience method when you need to create theParameterizedQuery
inline, with both the query template and the arguments.com.google.cloud.bigquery.TableResult
run
(com.google.cloud.bigquery.BigQuery.JobOption... options) Sends this query to BigQuery using the default client configuration withoptions
to control BigQuery jobs.Returns a template ofQueryJobConfiguration
based on thetemplate
string.toString()
-
Method Details
-
of
@TemplateFormatMethod public static ParameterizedQuery of(@CompileTimeConstant @TemplateString String query, Object... args) Convenience method when you need to create theParameterizedQuery
inline, with both the query template and the arguments.For example:
TableResult result = ParameterizedQuery.of("select * from JOBS where id = {id}", jobId).run();
-
template
public static StringFormat.Template<ParameterizedQuery> template(@CompileTimeConstant String template) Returns a template ofQueryJobConfiguration
based on thetemplate
string.For example:
private static final Template<QueryJobConfiguration> GET_JOB_IDS_BY_QUERY = ParameterizedQuery.template( """ SELECT job_id from INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE configuration.query LIKE '%{keyword}%' """); TableResult result = GET_JOB_IDS_BY_QUERY.with("sensitive word").run();
Except
ParameterizedQuery
itself, which are directly substituted into the query, all other placeholder arguments are passed into the QueryJobConfiguration as query parameters.Placeholder types supported:
- CharSequence
- Enum
- java.time.Instant (translated to TIMESTAMP)
- java.time.LocalDate (translated to DATE)
- Integer
- Long
- BigDecimal
- Double
- Float
- arrays
QueryParameterValue
. -
enumConstants
Returns the stream of enum constants defined byenumClass
, with the names wrapped in ParameterizedQuery}. -
joining
public static Collector<ParameterizedQuery,?, joiningParameterizedQuery> (@CompileTimeConstant String delimiter) Returns a collector that joins ParameterizedQuery elements usingdelimiter
.Useful if you need to parameterize by a set of columns to select. Say, you might need to query the table names only, or read the project, dataset and table names:
private static final Template<ParameterizedQuery> QUERY_TABLES = ParameterizedQuery.template("SELECT {columns} FROM {dataset}.INFORMATION_SCHEMA.TABLES"); ParameterizedQuery getTableNames = QUERY_TABLES.with(ParameterizedQuery.of("table_name")); ParameterizedQuery getFullyQualified = QUERY_TABLES.with( Stream.of("table_catalog", "table_schema", "table_name") .map(ParameterizedQuery::of) .collect(ParameterizedQuery.joining(", ")), ParameterizedQuery.of("my-dataset"));
-
run
public com.google.cloud.bigquery.TableResult run(com.google.cloud.bigquery.BigQuery.JobOption... options) throws com.google.cloud.bigquery.JobException, InterruptedException Sends this query to BigQuery using the default client configuration withoptions
to control BigQuery jobs.To use alternative configuration, pass the return value of
jobConfiguration()
to theBigQuery
object of your choice.- Throws:
com.google.cloud.bigquery.JobException
InterruptedException
-
jobConfiguration
public com.google.cloud.bigquery.QueryJobConfiguration jobConfiguration()Returns theQueryJobConfiguration
that can be sent to BigQuery. -
hashCode
public int hashCode() -
equals
-
toString
-