FluentJdbc Query API

Introduction

FluentJdbc provides a fluent API for executing native SQL queries. It is best suited for projects that require fine control over SQL queries and operations in a convenient, declarative way. Can be used standalone or complement higher level abstractions like JPA or other.

It provides features like: support for custom parameter types (like java.time), named query parameters, automatic ResultSet to POJO mapping, etc. It also avoids inconveniences of JDBC API, like: checked exceptions, explicit resource management / leaks, clutter. FluentJdbc is light-weight, has no 3^rd party dependencies.

Main advantages over plain JDBC:

• A flexible, functional API making the most common JDBC operations trivial one-liners
• Implicit resource management, avoiding leaks of Connections, PreparedStatements, ResultSets
• Out of the box support for java.time, Extension API for more custom types
• Support for automatic mapping of results to Java beans
• Named query parameters

Easy integration to Spring, Guice, JEE, ... applications including transaction management

Using the Code

Add FluentJdbc to your project:

<code><dependency>
    <groupId>org.codejargon</groupId>
    <artifactId>fluentjdbc</artifactId>
    <version>0.9</version>
</dependency>

The following examples are taken from the documentation and sources/javadocs at github.

Update

query
    .update("UPDATE CUSTOMER SET NAME = ?, ADDRESS = ?")
    .params("John Doe", "Dallas")
    .run();

Custom parameter types (java.time out of the box)

query
 .update("UPDATE CUSTOMER SET DEADLINE = ?, UPDATED = ?")
 .params(LocalDate.of(2015, Month.MARCH, 5), Instant.now())
 .run();

Named parameters

Map<String, Object> namedParams = new HashMap<>();
namedParams.put("name", "John Doe");
namedParams.put("address", "Dallas");
query
 .batch("UPDATE CUSTOMER SET NAME = :name, ADDRESS = :address")
 .namedParams(namedParams)
 .run();

Select

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

Convenience methods

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

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

Iterating a large result

query
.select("SELECT * FROM CUSTOMER")
    .iterateResult(customerMapper, (customer) -> {
        if(customer.isExpired()) {
          ...
        }
    });

Batch operations

Iterator<List<Object>> params = ...;
query
 .batch("INSERT INTO CUSTOMER(NAME, ADDRESS) VALUES(?, ?)")
 .params(params)
 .run();

Initializing with a DataSource

DataSource dataSource = ...
FluentJdbc fluentJdbc = new FluentJdbcBuilder()
    .connectionProvider(new DataSourceConnectionProvider(dataSource)
        .build();
Query query = fluentJdbc.query();

Querying on a specific Connection

Connection connection = ...
Query query = fluentJdbc.queryOn(connection);

Custom ConnectionProvider

ConnectionProvider provider = query -> {
    Connection connection = ... // get a connection instance
    query.receive(connection);  // pass the connection to the query
    ... // release the connection
}

History

• 18^th January, 2015: Initial version