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, ...)
Pyranid will automatically determine if your JDBC driver supports "large" updates and batch operations and uses them if available.
Statement Types
Basic
Most statements follow this pattern - execute them and see how many rows were affected.
// Returns the number of affected rows
long updateCount = database.execute("UPDATE car SET color=?", Color.RED);
// Returns the number of affected rows
long updateCount = database.execute("UPDATE car SET color=?", Color.RED);
References:
Returning
You may return single or multiple objects via the SQL RETURNING clause for DML statements (Postgres and Oracle). SQL Sever uses a special OUTPUT clause for this scenario.
// Returns a single object - here, the sequence value used for the insert
Optional<BigInteger> insertedCarId = database.executeForObject("""
INSERT INTO car (id, color)
VALUES (nextval('car_seq'), ?)
RETURNING id
""", BigInteger.class, Color.GREEN);
// Returns a list of objects - here, the rows that were updated
List<Car> repaintedCars = database.executeForList("""
UPDATE car
SET color=?
WHERE color=?
RETURNING *
""", Car.class, Color.GREEN, Color.BLUE);
// Returns a single object - here, the sequence value used for the insert
Optional<BigInteger> insertedCarId = database.executeForObject("""
INSERT INTO car (id, color)
VALUES (nextval('car_seq'), ?)
RETURNING id
""", BigInteger.class, Color.GREEN);
// Returns a list of objects - here, the rows that were updated
List<Car> repaintedCars = database.executeForList("""
UPDATE car
SET color=?
WHERE color=?
RETURNING *
""", Car.class, Color.GREEN, Color.BLUE);
References:
Database::executeForObject(String, Class<T>, Object...)Database::executeForList(String, Class<T>, Object...)
Batch
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 lists
List<List<Object>> parameterGroups = List.of(
List.of(123, Color.BLUE),
List.of(456, Color.RED)
);
// Insert both cars
List<Long> updateCounts = database.executeBatch(
"INSERT INTO car VALUES (?,?)", parameterGroups);
// Batch parameters are lists of lists
List<List<Object>> parameterGroups = List.of(
List.of(123, Color.BLUE),
List.of(456, Color.RED)
);
// Insert both cars
List<Long> updateCounts = database.executeBatch(
"INSERT INTO car VALUES (?,?)", parameterGroups);