Interface Query
This is not a script runner. Driver behavior for multi-statement strings differs; use
Database.useRawConnection(RawConnectionOperation) with complete, dialect-appropriate commands
or a database migration tool for scripts.
Obtain instances via Database.query(String).
Positional parameters via ? are not supported; use named parameters (e.g. :id) and bind(String, Object).
Parameter-looking text inside SQL string literals, quoted identifiers, comments, PostgreSQL dollar-quoted strings,
and SQL Server-style bracket-quoted identifiers is ignored. For DuckDB, brackets are parsed as list/subscript syntax,
so list literals can contain named parameters. PostgreSQL JSONB/hstore ?, ?|, and ?&
operators are supported and are escaped automatically for pgjdbc when the Database is configured
as PostgreSQL.
Example usage:
// Query returning one row
Optional<Employee> employee = database.query("SELECT * FROM employee WHERE id = :id")
.bind("id", 42)
.fetchObject(Employee.class);
// Query returning multiple rows
List<Employee> employees = database.query("SELECT * FROM employee WHERE dept = :dept")
.bind("dept", "Engineering")
.fetchList(Employee.class);
// DML with no result
long rowsAffected = database.query("UPDATE employee SET active = :active WHERE id = :id")
.bind("id", 42)
.bind("active", false)
.execute();
// DML with RETURNING clause
Optional<Employee> updated = database.query("UPDATE employee SET salary = :salary WHERE id = :id RETURNING *")
.bind("id", 42)
.bind("salary", new BigDecimal("150000"))
.executeForObject(Employee.class);
Implementations of this interface are intended for use by a single thread.
- Since:
- 4.0.0
- Author:
- Mark Allen
- See Also:
-
Method Summary
Modifier and TypeMethodDescriptionbatchChunkSize(@Nullable Integer batchChunkSize) Configures the maximum number of parameter groups to send in each JDBC batch execution.Binds a named parameter to a value.Binds all entries from the given map as named parameters.customize(@NonNull PreparedStatementCustomizer preparedStatementCustomizer) Customizes thePreparedStatementbefore execution.execute()Executes a DML statement (INSERT, UPDATE, DELETE) with no resultset.Executes a DML statement in batch over groups of named parameters.executeForList(@NonNull Class<T> resultType) Executes a DML statement that returns multiple rows (for example, with PostgreSQL/SQLiteRETURNING, MariaDBINSERT ... RETURNING, or SQL ServerOUTPUT).executeForObject(@NonNull Class<T> resultType) Executes a DML statement that returns a single row (for example, with PostgreSQL/SQLiteRETURNING, MariaDBINSERT ... RETURNING, or SQL ServerOUTPUT).executeReturningGeneratedKey(@NonNull Class<T> resultType) Executes a DML statement and maps a single JDBC-generated key row.executeReturningGeneratedKey(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames) Executes a DML statement and maps a single JDBC-generated key row.executeReturningGeneratedKeys(@NonNull Class<T> resultType) Executes a DML statement and maps all JDBC-generated key rows.executeReturningGeneratedKeys(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames) Executes a DML statement and maps all JDBC-generated key rows.Executes the query and returns all results as a list.fetchObject(@NonNull Class<T> resultType) Executes the query and returns a single result.Configures the JDBC fetch size for this query.<T,R> @Nullable R Associates an identifier with this query for logging/diagnostics.Acquires a result type token for fetching rows as insertion-orderedMap<String, Object>instances, usable anywhere a result type token is accepted.Configures the JDBC maximum row count for this query.preparedStatementBinder(@Nullable PreparedStatementBinder preparedStatementBinder) Overrides theDatabase-widePreparedStatementBinderfor this query only.queryTimeout(@Nullable Duration queryTimeout) Configures the JDBC query timeout for this query.resultSetMapper(@Nullable ResultSetMapper resultSetMapper) Overrides theDatabase-wideResultSetMapperfor this query only.
-
Method Details
-
bind
Binds a named parameter to a value.- Parameters:
name- the parameter name (without the leading:)value- the value to bind (may benull). RawCollectionand array values are not expanded; useParameters.inList(java.util.Collection),Parameters.sqlArrayOf(String, Object[]), orParameters.arrayOf(Class, Object)as appropriate.- Returns:
- this builder, for chaining
-
bindAll
-
id
-
queryTimeout
Configures the JDBC query timeout for this query.This maps to
Statement.setQueryTimeout(int).nullleaves the timeout unset so anyDatabase.Builder.queryTimeout(Duration)default applies.Duration.ZEROdisables the JDBC timeout. Positive sub-second durations are rounded up to one second because JDBC accepts whole seconds.- Parameters:
queryTimeout- timeout to apply, ornullto inherit the database default- Returns:
- this builder, for chaining
- Since:
- 4.2.0
-
fetchSize
Configures the JDBC fetch size for this query.This maps to
Statement.setFetchSize(int).nullleaves the fetch size unset so anyDatabase.Builder.fetchSize(Integer)default applies. A value of0uses the driver's default fetch-size behavior. Streaming queries also apply dialect rules: PostgreSQL and MariaDB default to 256 when no fetch size is configured; MySQL preserves positive hints only when Connector/J cursor fetching is positively enabled, otherwise retaining row streaming. An explicit per-query zero opts into driver-default behavior, which may buffer.- Parameters:
fetchSize- fetch size to apply, ornullto inherit the database default- Returns:
- this builder, for chaining
- Since:
- 4.2.0
-
maxRows
Configures the JDBC maximum row count for this query.This maps to
Statement.setMaxRows(int).nullleaves the maximum row count unset so anyDatabase.Builder.maxRows(Integer)default applies. A value of0disables the JDBC row limit.- Parameters:
maxRows- maximum rows to apply, ornullto inherit the database default- Returns:
- this builder, for chaining
- Since:
- 4.2.0
-
batchChunkSize
Configures the maximum number of parameter groups to send in each JDBC batch execution.This setting applies only to
executeBatch(List).nullleaves batch execution unchunked, preserving the default behavior of sending all parameter groups in one JDBC batch. A positive value causes Pyranid to execute multiple JDBC batches as needed and flatten the returned update counts in input order. If this setting is specified and a non-batch terminal operation is invoked, Pyranid throwsIllegalStateException.Chunking is performed by Pyranid. JDBC drivers still execute each chunk as a normal JDBC batch. If a later chunk fails outside an explicit transaction, earlier chunks may already be committed depending on autocommit and driver behavior. Wrap chunked batches in
Database.transaction(TransactionalOperation)when all-or-nothing behavior is required.- Parameters:
batchChunkSize- maximum parameter groups per JDBC batch execution, ornullto execute one batch- Returns:
- this builder, for chaining
- Throws:
IllegalArgumentException- ifbatchChunkSizeis less than or equal to0- Since:
- 4.2.0
-
resultSetMapper
Overrides theDatabase-wideResultSetMapperfor this query only.This enables per-query inline mapping - for example, projecting an ad-hoc join or computed columns - without configuring a database-wide mapper. Its generic mapping method requires an implementation rather than a lambda:
The override applies to every row this query maps (includingdatabase.query("SELECT name, COUNT(*) AS total FROM employee GROUP BY name") .resultSetMapper(new ResultSetMapper() { @Override public <T> Optional<T> map(StatementContext<T> ctx, ResultSet rs, Class<T> type, InstanceProvider ip) throws SQLException { return Optional.of(type.cast(new NameCount(rs.getString(1), rs.getLong(2)))); } }) .fetchList(NameCount.class);fetchStream(Class, Function)rows and DML-returning results). Other queries on the sameDatabaseare unaffected. Metrics, statement logging, and exception diagnostics behave identically with an override present.- Parameters:
resultSetMapper- the mapper to use for this query, ornullto inherit the database-wide mapper- Returns:
- this builder, for chaining
- Since:
- 4.5.0
-
preparedStatementBinder
default @NonNull Query preparedStatementBinder(@Nullable PreparedStatementBinder preparedStatementBinder) Overrides theDatabase-widePreparedStatementBinderfor this query only.The override receives every non-null parameter for this query - including expanded IN-list elements and each batch group's parameters. As with the database-wide SPI,
SecureParameterandOptionalwrappers are unwrapped by Pyranid before the binder is invoked, so custom binders receive bound-ready raw values and need no unwrap logic.nullparameters never reach the binder; Pyranid binds them viaPreparedStatement.setNull(int, int)even when an override is present.Other queries on the same
Databaseare unaffected.- Parameters:
preparedStatementBinder- the binder to use for this query, ornullto inherit the database-wide binder- Returns:
- this builder, for chaining
- Since:
- 4.5.0
-
customize
Customizes thePreparedStatementbefore execution.If called multiple times, the most recent customizer wins. The customizer runs after database-wide statement settings and this query's
queryTimeout(Duration),fetchSize(Integer), andmaxRows(Integer)settings, so it can override them when needed. It runs before Pyranid binds parameters.For dialect-managed streams, Pyranid may apply driver-specific stream settings after this callback unless this query explicitly configured a fetch size. MySQL may still replace a positive hint with its row-streaming sentinel unless Connector/J cursor fetching is positively enabled.
For driver-specific cancellation beyond
queryTimeout(Duration), application code may capture thePreparedStatementhere and callStatement.cancel()from its own cancellation path. Cancellation behavior is JDBC-driver-specific.- Parameters:
preparedStatementCustomizer- customization callback- Returns:
- this builder, for chaining
-
mapRowType
Acquires a result type token for fetching rows as insertion-orderedMap<String, Object>instances, usable anywhere a result type token is accepted.Java's type erasure means there is no
Map<String, Object>.classliteral - a rawMap.classtoken can only ever infer the rawMaptype at fetch sites. This method returns the same runtimeMap.classtoken, statically typed asClass<Map<String, Object>>so results are properly parameterized without caller-side casts:
This is safe because Pyranid's default mapping producesList<Map<String, Object>> rows = database.query("SELECT * FROM car") .fetchList(Query.mapRowType());LinkedHashMap<String, Object>rows for this token. Note that a customResultSetMapperreceiving this token observes the rawMap.class.- Returns:
- a
Map<String, Object>result type token - Since:
- 4.5.0
-
fetchObject
Executes the query and returns a single result.- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal each row to- Returns:
- the single result, or empty if no rows
- Throws:
DatabaseException- if more than one row is returned
-
fetchList
-
fetchStream
<T,R> @Nullable R fetchStream(@NonNull Class<T> resultType, @NonNull Function<Stream<@Nullable T>, R> streamFunction) Executes the query and provides aStreambacked by the underlyingResultSet.This approach is useful for processing very large resultsets (e.g. millions of rows), where it's impractical to load all rows into memory at once.
JDBC resources are closed automatically when
streamFunctionreturns (or throws), so the stream must be fully consumed within that callback. Do not escape the stream from the function.The stream must be consumed within the scope of the transaction or connection that created it. If the stream participates in a Pyranid transaction, it must also be closed by the thread that opened it.
PostgreSQL streams default to a fetch size of 256 inside and outside Pyranid transactions. Outside a transaction, Pyranid temporarily disables autocommit to enable cursor fetching. MariaDB uses forward-only/read-only statements and a default fetch size of 256. MySQL uses row streaming unless a positive hint and Connector/J's
useCursorFetch=trueenable cursor fetching. Opaque wrappers retain row streaming when cursor mode cannot be established. Explicit per-queryfetchSize(0)requests the driver default, which may buffer. A database-wide zero still receives the dialect streaming default. Fully consume or close a MySQL/MariaDB stream before another statement uses the same connection; early close may drain remaining rows. Streams bound driver buffering, but collecting the entire result still retains all values.- Type Parameters:
T- the result typeR- the return type- Parameters:
resultType- the type to marshal each row tostreamFunction- function that consumes the stream and returns a result- Returns:
- the value returned by
streamFunction
-
execute
-
executeReturningGeneratedKey
Executes a DML statement and maps a single JDBC-generated key row.This uses JDBC
Statement.RETURN_GENERATED_KEYS. It is intended for database-generated values such as identity/auto-increment primary keys. If your SQL returns rows directly via database syntax such as PostgreSQLRETURNINGor SQL ServerOUTPUT, useexecuteForObject(Class)instead.With its default settings, MariaDB Connector/J returns only the first generated ID for one multi-row
INSERT. Receiving one generated-key row therefore does not establish that only one row was inserted. To retrieve every inserted ID on MariaDB, useINSERT ... RETURNINGwithexecuteForList(Class). Explicit key column names do not change this default driver behavior.For SQL Server multi-row identity inserts, use
OUTPUTwithexecuteForList(Class)instead of JDBC generated keys. MySQLINSERT ... ON DUPLICATE KEY UPDATEcan produce extra JDBC-generated key rows derived from the affected-row count, even withid = LAST_INSERT_ID(id). A single-key call can therefore throw after the update has executed, while a multiple-key call can include IDs of rows that were not changed. For a single-row upsert, useexecute()withid = LAST_INSERT_ID(id)in the update clause, then immediately querySELECT LAST_INSERT_ID()in the sameDatabase.transaction(ReturningTransactionalOperation)callback so both statements use one connection. See the MySQL upsert recipe.Oracle requires explicit generated-key column names; use
executeReturningGeneratedKey(Class, String...)instead.- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal the generated-key row to- Returns:
- the single generated key row, or empty if the driver returns no generated keys
- Throws:
DatabaseException- if more than one generated-key row is returned- Since:
- 4.2.0
-
executeReturningGeneratedKey
default <T> @NonNull Optional<T> executeReturningGeneratedKey(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames) Executes a DML statement and maps a single JDBC-generated key row.This uses JDBC
Connection.prepareStatement(String, String[])with the supplied key column names. Some drivers require column names to return generated keys for specific columns, especially when more than one generated value is available. IfkeyColumnNamesis empty, this behaves likeexecuteReturningGeneratedKey(Class), except for dialects such as Oracle which require explicit names.With its default settings, MariaDB Connector/J returns only the first generated ID for one multi-row
INSERT. Receiving one generated-key row therefore does not establish that only one row was inserted. To retrieve every inserted ID on MariaDB, useINSERT ... RETURNINGwithexecuteForList(Class). Explicit key column names do not change this default driver behavior.For SQL Server multi-row identity inserts, use
OUTPUTwithexecuteForList(Class)instead of JDBC generated keys. MySQLINSERT ... ON DUPLICATE KEY UPDATEcan produce extra JDBC-generated key rows derived from the affected-row count, even withid = LAST_INSERT_ID(id). A single-key call can therefore throw after the update has executed, while a multiple-key call can include IDs of rows that were not changed. For a single-row upsert, useexecute()withid = LAST_INSERT_ID(id)in the update clause, then immediately querySELECT LAST_INSERT_ID()in the sameDatabase.transaction(ReturningTransactionalOperation)callback so both statements use one connection. See the MySQL upsert recipe.- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal the generated-key row tokeyColumnNames- generated-key column names requested from the driver- Returns:
- the single generated key row, or empty if the driver returns no generated keys
- Throws:
DatabaseException- if more than one generated-key row is returned- Since:
- 4.2.0
-
executeReturningGeneratedKeys
Executes a DML statement and maps all JDBC-generated key rows.This uses JDBC
Statement.RETURN_GENERATED_KEYS. It is intended for database-generated values such as identity/auto-increment primary keys. If your SQL returns rows directly via database syntax such as PostgreSQLRETURNINGor SQL ServerOUTPUT, useexecuteForList(Class)instead.With its default settings, MariaDB Connector/J returns only the first generated ID for one multi-row
INSERT. Receiving one generated-key row therefore does not establish that only one row was inserted. To retrieve every inserted ID on MariaDB, useINSERT ... RETURNINGwithexecuteForList(Class). Explicit key column names do not change this default driver behavior.For SQL Server multi-row identity inserts, use
OUTPUTwithexecuteForList(Class)instead of JDBC generated keys. MySQLINSERT ... ON DUPLICATE KEY UPDATEcan produce extra JDBC-generated key rows derived from the affected-row count, even withid = LAST_INSERT_ID(id). A single-key call can therefore throw after the update has executed, while a multiple-key call can include IDs of rows that were not changed. For a single-row upsert, useexecute()withid = LAST_INSERT_ID(id)in the update clause, then immediately querySELECT LAST_INSERT_ID()in the sameDatabase.transaction(ReturningTransactionalOperation)callback so both statements use one connection. See the MySQL upsert recipe.Oracle requires explicit generated-key column names; use
executeReturningGeneratedKeys(Class, String...)instead.- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal each generated-key row to- Returns:
- list of generated key rows
- Since:
- 4.2.0
-
executeReturningGeneratedKeys
default <T> @NonNull List<@Nullable T> executeReturningGeneratedKeys(@NonNull Class<T> resultType, @NonNull String @NonNull ... keyColumnNames) Executes a DML statement and maps all JDBC-generated key rows.This uses JDBC
Connection.prepareStatement(String, String[])with the supplied key column names. Some drivers require column names to return generated keys for specific columns, especially when more than one generated value is available. IfkeyColumnNamesis empty, this behaves likeexecuteReturningGeneratedKeys(Class), except for dialects such as Oracle which require explicit names.With its default settings, MariaDB Connector/J returns only the first generated ID for one multi-row
INSERT. Receiving one generated-key row therefore does not establish that only one row was inserted. To retrieve every inserted ID on MariaDB, useINSERT ... RETURNINGwithexecuteForList(Class). Explicit key column names do not change this default driver behavior.For SQL Server multi-row identity inserts, use
OUTPUTwithexecuteForList(Class)instead of JDBC generated keys. MySQLINSERT ... ON DUPLICATE KEY UPDATEcan produce extra JDBC-generated key rows derived from the affected-row count, even withid = LAST_INSERT_ID(id). A single-key call can therefore throw after the update has executed, while a multiple-key call can include IDs of rows that were not changed. For a single-row upsert, useexecute()withid = LAST_INSERT_ID(id)in the update clause, then immediately querySELECT LAST_INSERT_ID()in the sameDatabase.transaction(ReturningTransactionalOperation)callback so both statements use one connection. See the MySQL upsert recipe.- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal each generated-key row tokeyColumnNames- generated-key column names requested from the driver- Returns:
- list of generated key rows
- Since:
- 4.2.0
-
executeBatch
@NonNull List<Long> executeBatch(@NonNull List<@NonNull Map<@NonNull String, @Nullable Object>> parameterGroups) Executes a DML statement in batch over groups of named parameters.Any parameters already bound on this
Queryapply to all groups; group values override them. Each group must provide a complete set of parameter values after merging; groups must be non-null and expand to the same number of JDBC parameters (for example, IN-list sizes must match). Batch statements must not return result rows, including throughRETURNINGorOUTPUT. To collect IDs from a multi-row insert, use a single returning statement withexecuteForList(Class), or perform a JDBC generated-key batch insideDatabase.useRawConnection(RawConnectionOperation). This method returns update counts, not generated keys.- Parameters:
parameterGroups- groups of named parameter values (without the leading:)- Returns:
- the number of rows affected by the SQL statement per-group
-
executeForObject
Executes a DML statement that returns a single row (for example, with PostgreSQL/SQLiteRETURNING, MariaDBINSERT ... RETURNING, or SQL ServerOUTPUT).- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal the row to- Returns:
- the single result, or empty if no rows
- Throws:
DatabaseException- if more than one row is returned
-
executeForList
Executes a DML statement that returns multiple rows (for example, with PostgreSQL/SQLiteRETURNING, MariaDBINSERT ... RETURNING, or SQL ServerOUTPUT).- Type Parameters:
T- the result type- Parameters:
resultType- the type to marshal each row to- Returns:
- list of results
-