Examples

This page contains several examples for the usage of the query builder. For in depth examples for the components have a look at the components page

Select

We always read a User object in that section, which looks like that:

public record User(int id, UUID uuid, String name) {
    public static RowMapping<User> map() {
        return row -> new User(row.getInt("id"), row.getUuidFromString("uuid"), row.getString("name"));
    }
}

This is an example for a RowMapping

Get All

This returns all matching entries for our query and maps it to a user.

List<User> users = query.query("SELECT * FROM users WHERE id = ? AND name ILIKE :name")
        .single(Call.of().bind(1).bind("name", "lilly"))
        .map(User.map())
        .all();

Get First

This returns the first row of our result and terminates after that.

If a result was returned the option won't be empty.

Optional<User> user = query.query("SELECT * FROM users where id = :id")
        .single(Call.of().bind("id", 1)))
        .map(User.map())
        .first();

Insert, Update and Delete

Inserts, updates and deletes are currently all handled nearly the same. Deletes and updates return an ManipulationResult, which allows access to the changed rows. Inserts return an InsertionResult, which provides additional access to generated keys.

Single

Insert a single value.

// Insert a single entry
InsertionResult change = query
        // Define the query
        .query("INSERT INTO users(uuid, name) VALUES(:uuid::uuid,?)")
        // Create a new call
        // First parameter is named and second indexed
        .single(Call.of().bind("uuid", UUID.randomUUID(), UUIDAdapter.AS_STRING).bind("someone"))
        // Insert the data
        .insert();

// Check that something changed
Assertions.assertTrue(change.changed());
// Check that one row was added
Assertions.assertEquals(change.rows(), 1);

Batch

Execute multiple inserts.

// Insert multiple entries at the same time
InsertionBatchResult<InsertionResult> change = query
        // Define the query
        .query("INSERT INTO users(uuid, name) VALUES(?::uuid,?)")
        // Create a new batch call
        .batch(
                // Define the first call
                Call.of().bind(UUID.randomUUID(), UUIDAdapter.AS_STRING).bind("someone"),
                // Define the second call
                Call.of().bind(UUID.randomUUID(), UUIDAdapter.AS_STRING).bind("someone else"))
        // Insert the data
        .insert();

// Check that something changed
Assertions.assertTrue(change.changed());
// Check that two rows were added
Assertions.assertEquals(2, change.rows());

// Check how many rows for each batch execution were changed
for (ManipulationResult result : change.results()) {
    Assertions.assertEquals(1, result.rows());
}

Single transaction mode

To execute queries in a single transaction you need to create a connected configuration. The connected configuration has to be used in a try with resources.

// atomic transaction
try (var conn = query.withSingleTransaction()) {
    // Retrieve the first user and store them it to use it again later
    // From here on another query could be issued that uses the results of this query
    ManipulationResult manipulation = conn.query("INSERT INTO users(uuid, name) VALUES (:uuid::uuid, :name) RETURNING id, uuid, name")
            .single(Call.of().bind("uuid", UUID.randomUUID(), AS_STRING).bind("name", "lilly")))
            .map(User::map)
            .storeOneAndAppend("user")
            .query("INSERT INTO birthdays(user_id, birth_date) VALUES (:id, :date)")
            // produce error
            .single(storage -> Call.of().bind("id", storage.getAs("user", User.class).get().id()).bind("date", "").asSingleCall())
            .insert();
}

List<User> users = query.query("SELECT * FROM users")
        .single()
        .map(User::map)
        .all();

// Make sure that the first insert was not commited
Assertions.assertEquals(0, users.size());
}

Query Storage

As you can see in the previous example we store the result of the first query and access it in the second one.

Another good example for that is this query:

// Retrieve the first user and store them it to use it again later
// From here on another query could be issued that uses the results of this query
ManipulationResult manipulation = query.query("INSERT INTO users(uuid, name) VALUES (:uuid::uuid, :name) RETURNING id, uuid, name")
        .single(Call.of().bind("uuid", UUID.randomUUID(), AS_STRING).bind("name", "lilly"))
        .map(User.map())
        .storeOneAndAppend("user")
        .query("INSERT INTO birthdays(user_id, birth_date) VALUES (:id, :date)")
        .single(storage -> Call.of().bind("id", storage.getAs("user", User.class).get().id()).bind("date", LocalDate.of(1990, 1, 1)).asSingleCall())
        .insert();

It inserts a user into the user table and use the returned id directly to insert something with it into another table.