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:
<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 = ...
query.receive(connection);
...
}
History
- 18th January, 2015: Initial version