Core Concepts
Statements
Statements are used for general-purpose DML execution (INSERT, UPDATE, DELETE, functions, ...) They can also be expressions that return nothing, such as DDL operations (CREATE TABLE, ALTER TABLE, ...)
Basic Statements
Most statements follow this pattern - execute them and see how many rows were affected:
// Returns the number of affected rows
long updateCount = database.query("""
UPDATE car
SET color = :color
""")
.bind("color", Color.RED)
.execute();
// Returns the number of affected rows
long updateCount = database.query("""
UPDATE car
SET color = :color
""")
.bind("color", Color.RED)
.execute();
References:
Returning Statements
You may return single or multiple objects via database-native DML-returning syntax. PostgreSQL, SQLite, and DuckDB use SQL RETURNING for DML. MariaDB supports INSERT ... RETURNING. SQL Server uses OUTPUT, including OUTPUT ... INTO for trigger-enabled tables. For Oracle, prefer JDBC-generated keys with explicit key column names.
// Returns a single object - here, the sequence value used for the insert
Optional<BigInteger> insertedCarId = database.query("""
INSERT INTO car (id, color)
VALUES (nextval('car_seq'), :color)
RETURNING id
""")
.bind("color", Color.GREEN)
.executeForObject(BigInteger.class);
// Returns a list of objects - here, the rows that were updated
List<Car> repaintedCars = database.query("""
UPDATE car
SET color = :newColor
WHERE color = :oldColor
RETURNING *
""")
.bind("newColor", Color.GREEN)
.bind("oldColor", Color.BLUE)
.executeForList(Car.class);
// Returns a single object - here, the sequence value used for the insert
Optional<BigInteger> insertedCarId = database.query("""
INSERT INTO car (id, color)
VALUES (nextval('car_seq'), :color)
RETURNING id
""")
.bind("color", Color.GREEN)
.executeForObject(BigInteger.class);
// Returns a list of objects - here, the rows that were updated
List<Car> repaintedCars = database.query("""
UPDATE car
SET color = :newColor
WHERE color = :oldColor
RETURNING *
""")
.bind("newColor", Color.GREEN)
.bind("oldColor", Color.BLUE)
.executeForList(Car.class);
References:
Generated Keys
For identity/auto-increment columns, use JDBC-generated keys. This is useful for databases that do not support RETURNING for inserts. Oracle requires explicit generated-key column names. On PostgreSQL and SQLite, prefer explicit SQL RETURNING; on MariaDB, use INSERT ... RETURNING; and on SQL Server, use OUTPUT. DuckDB's JDBC driver has no generated-key support at all - Query::executeReturningGeneratedKey(...) fails fast there with a clear DatabaseException directing you to RETURNING. Map the returned rows with Query::executeForObject(Class<T>) or Query::executeForList(Class<T>).
Optional<BigInteger> generatedCarId = database.query("""
INSERT INTO car (color)
VALUES (:color)
""")
.bind("color", Color.GREEN)
.executeReturningGeneratedKey(BigInteger.class);
// Some drivers require the generated-key column names to be specified.
Optional<BigInteger> generatedCarIdByColumn = database.query("""
INSERT INTO car (color)
VALUES (:color)
""")
.bind("color", Color.BLUE)
.executeReturningGeneratedKey(BigInteger.class, "id");
Optional<BigInteger> generatedCarId = database.query("""
INSERT INTO car (color)
VALUES (:color)
""")
.bind("color", Color.GREEN)
.executeReturningGeneratedKey(BigInteger.class);
// Some drivers require the generated-key column names to be specified.
Optional<BigInteger> generatedCarIdByColumn = database.query("""
INSERT INTO car (color)
VALUES (:color)
""")
.bind("color", Color.BLUE)
.executeReturningGeneratedKey(BigInteger.class, "id");
Use Query::executeReturningGeneratedKeys(Class<T>) when one statement can produce multiple generated-key rows.
MariaDB Connector/J's default generated-key result for one multi-row INSERT contains only the first ID. The plural generated-key method and explicit key column names do not retrieve the missing IDs, and a successful single-key call does not mean only one row was inserted. To retrieve every inserted ID, use INSERT ... RETURNING with executeForList(...); see the MariaDB recipe. This concerns a multi-values SQL statement, not the update-count list returned by executeBatch(...).
For MySQL INSERT ... ON DUPLICATE KEY UPDATE, the generated-key rows can include IDs of rows that were not changed, even with id = LAST_INSERT_ID(id). The single-key method can throw after the DML has executed because the driver supplied more than one row; the multiple-key method does not validate those IDs against actual changed rows. Explicit key column names do not fix this. For a single-row upsert, use execute() followed immediately by SELECT LAST_INSERT_ID() in the same transaction, with the assignment in the update clause. See the MySQL upsert recipe.
References:
Query::executeReturningGeneratedKey(Class<T>)Query::executeReturningGeneratedKey(Class<T>, String...)Query::executeReturningGeneratedKeys(Class<T>)Query::executeReturningGeneratedKeys(Class<T>, String...)
Batch Operations
Batch operations can be more efficient than execution of discrete statements - they are useful for inserting a lot of data at once.
// Batch parameters are lists of maps keyed by named parameters
List<Map<String, Object>> parameterGroups = List.of(
Map.of("id", 123, "color", Color.BLUE),
Map.of("id", 456, "color", Color.RED)
);
// Insert both cars
List<Long> updateCounts = database.query("""
INSERT INTO car (id, color)
VALUES (:id, :color)
""")
.executeBatch(parameterGroups);
// Batch parameters are lists of maps keyed by named parameters
List<Map<String, Object>> parameterGroups = List.of(
Map.of("id", 123, "color", Color.BLUE),
Map.of("id", 456, "color", Color.RED)
);
// Insert both cars
List<Long> updateCounts = database.query("""
INSERT INTO car (id, color)
VALUES (:id, :color)
""")
.executeBatch(parameterGroups);
executeBatch returns update counts, not result rows. Batch statements must not contain RETURNING/OUTPUT result sets; use executeForList for returned rows, or a callback-scoped raw JDBC generated-key batch where the driver supports it. See Bulk insert IDs.
Each parameter group must provide a complete set of values after merging with any values bound on the query, and all groups must expand to the same number of JDBC parameters (for example, IN-list sizes must match).
Use Query::batchChunkSize(Integer) to bound how many parameter groups Pyranid sends in each JDBC batch execution:
List<Long> updateCounts = database.query("""
INSERT INTO car (id, color)
VALUES (:id, :color)
""")
.batchChunkSize(500)
.executeBatch(parameterGroups);
List<Long> updateCounts = database.query("""
INSERT INTO car (id, color)
VALUES (:id, :color)
""")
.batchChunkSize(500)
.executeBatch(parameterGroups);
Chunking is performed by Pyranid; each chunk is still executed by the JDBC driver as a normal batch. If unset, Pyranid preserves the default behavior of sending all parameter groups in one JDBC batch. Query::batchChunkSize(...) applies only to Query::executeBatch(...); using it with a non-batch terminal operation throws IllegalStateException. For all-or-nothing behavior when a later chunk fails, wrap chunked batches in Database::transaction(...); outside an explicit transaction, earlier chunks may already be committed depending on autocommit and driver behavior.
Parameter Resource Lifetime
When the driver supports PreparedStatement::clearParameters(), batchChunkSize(...) also bounds retained JDBC resources created for SqlArrayParameter, SqlStructParameter, and DuckDB VectorParameter values. It does not bound the input list's size or the driver's internal buffering.
If the driver reports that clearing parameters is unsupported, those resources remain allocated until later cleanup, so the resource-retention bound does not apply. Other parameter-reset failures stop later chunks from executing. Rollback, connection, and cancelation failures propagate even when the driver also reports an unsupported feature.
A parameter-release failure alone does not stop the remaining chunks. Pyranid reports the earliest release failure at exit; if statement execution also fails, the statement failure remains primary and cleanup failures are suppressed onto it. For streamed statements, parameter resources remain available until the cursor and statement close.
Large Update Counts
Pyranid will automatically determine if your JDBC driver supports "large" updates and batch operations and uses them if available. If the driver reports that large updates or large batches are unsupported, Pyranid falls back to the standard JDBC update APIs and caches that support decision for the current Database instance.

