Select

Select queries

query.select(sqlQuery)...

Creates a select query that can be parameterized, customized and executed.

Parameters

Both positional and named parameters are supported

query
    .select("SELECT * FROM CUSTOMER WHERE NAME = ?")
    .params("John Doe")
    ...

Map<String, Object> params = ...
query
    .select("SELECT * FROM CUSTOMER WHERE NAME = :name")
    .namedParams(params)
    ...

Query customization

Select queries can be customized, eg

• fetchSize - number of rows fetched from the db per round-trip, overrides default set in FluentJdbc (if any) or db vendor default
• maxRows - limit max number of rows returned by the db

query
    .select("SELECT * FROM CUSTOMER WHERE NAME = :name")
    .namedParams(params)
    .fetchSize(50)
    .maxRows(200)
    ...

Results

Mappers

All result rows of a select query will be mapped to single java object by a Mapper. Mappers can be implemented manually

Mapper<Customer> manualCustomerMapper = resultSet -> {
	return new Customer(resultSet.getString("NAME"));
}

Or generated based on object field name / column name matching (case-insensitive, ignoring '_')

ObjectMappers objectMappers = ObjectMappers.builder().build();
...
Mapper<Customer> generatedCustomerMapper = objectMappers.forClass(Customer.class);

Check Auto POJO mapping page for more details.

List

List<Customer> customers = query
    .select("SELECT * FROM CUSTOMER WHERE NAME = ?")
    .params("John Doe")
    .listResult(customerMapper);

Set

Set<Customer> customers = query
    .select("SELECT * FROM CUSTOMER WHERE NAME = ?")
    .params("John Doe")
    .setResult(customerMapper);

Single result

There must be a result.

Long count = query
    .select("SELECT COUNT(*) FROM CUSTOMER WHERE NAME = ?")
    .params("John Doe")
    .singleResult(Mappers.singleLong());

First result

May not have a result

Optional<Customer> customer = query
    .select("SELECT * FROM CUSTOMER WHERE NAME = ?")
    .params("John Doe")
    .firstResult(customerMapper);

Consuming large results

query
    .select("SELECT * FROM CUSTOMER")
    .iterateResult(customerMapper, (customer) -> {
        // do something with the customer
    });

Filter results in java

List<Customer> customer = query
    .select("SELECT * FROM CUSTOMER WHERE NAME = ?")
    .params("John Doe")
    .filter(customer::isActive)
    .listResult(customerMapper);

Note: since all results will be fetched from the DB, this should be used conciously.