Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / HTML

FluentJdbc Query API

5.00/5 (2 votes)
18 Jan 2015MIT1 min read 15.2K   2  
FluentJdbc Query API for more convenient native SQL querying

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 3rd 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:

XML
<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

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

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

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

Named parameters

Java
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

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

Convenience methods

Java
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

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

Batch operations

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

Initializing with a DataSource

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

Querying on a specific Connection

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

Custom ConnectionProvider

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

History

  • 18th January, 2015: Initial version

License

This article, along with any associated source code and files, is licensed under The MIT License