Pyranid | 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:

Query::execute()

Returning Statements

You may return single or multiple objects via database-native DML-returning syntax. PostgreSQL, SQLite, and MariaDB use SQL 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, SQLite, MariaDB, and SQL Server, prefer explicit SQL RETURNING/OUTPUT with Query::executeForObject(Class<T>) or Query::executeForList(Class<T>) when you want database-returned rows.

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.

References:

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);

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.

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.