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 the SQL RETURNING clause for DML statements (Postgres and Oracle). SQL Server uses a special OUTPUT clause for this scenario.
// 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:
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).
Pyranid will automatically determine if your JDBC driver supports "large" updates and batch operations and uses them if available.