JarCastingAbout FluentJdbc
FluentJdbc is a java library for convenient native SQL querying. Blends well with Java 8 / functional code, supports functionality many jdbc wrappers prevent / abstract away, and is lightweight (~80K, no dependencies).
FluentJdbc's key features:
- functional, fluent API
- execution of select/insert/update/delete/alter/... statements as one-liners
- parameter mapping (named, positional, supports java.time, enums, Optional, Collections, plugins for custom types)
- transactions
- access to generated keys of insert/update queries
- big data (scalable, streaming style of batch and select)
- automatic result to pojo mapping
- database inspection
- query listener (for logging, auditing, performance measurement, ...)
<dependency>
<groupId>org.codejargon</groupId>
<artifactId>fluentjdbc</artifactId>
<version>1.8.3</version>
</dependency>
Note: requires java 8
Full documentation on wiki
Latest javadoc
Code examples of common use cases
Setting up FluentJdbc
DataSource dataSource = ...
FluentJdbc fluentJdbc = new FluentJdbcBuilder()
.connectionProvider(dataSource)
.build();
Query query = fluentJdbc.query();
// ... use the Query interface for queries (thread-safe, reentrant)
Note: using a DataSource is the most common scenario, there are other alternatives documented on the wiki
Update or insert queries
query
.update("UPDATE CUSTOMER SET NAME = ?, ADDRESS = ?")
.params("John Doe", "Dallas")
.run();
Query for a list of results
List<Customer> customers = query.select("SELECT * FROM CUSTOMER WHERE NAME = ?")
.params("John Doe")
.listResult(customerMapper);
Mapping of results
Mapper can be implemented manually
resultSet -> new Customer(resultSet.getString("NAME"), resultSet.getString("ADDRESS"));
or mapping can be performed automatically to a java object
ObjectMappers objectMappers = ObjectMappers.builder().build(); //typically one instance per app
...
Mapper<Customer> customerMapper = objectMappers.forClass(Customer.class);
Query for single result
Long count = query.select("SELECT COUNT(*) FROM CUSTOMER WHERE NAME = ?")
.params("John Doe")
.singleResult(Mappers.singleLong);
Query for first result
Optional<Customer> customer = query.select("SELECT FROM CUSTOMER WHERE NAME = ?")
.params("John Doe")
.firstResult(customerMapper);
Batch insert or update
Stream<List<Object>> params = ...; // or Iterator/Iterable
query.batch("INSERT INTO CUSTOMER(NAME, ADDRESS) VALUES(?, ?)")
.params(params)
.run();
Named parameters
query.update("UPDATE CUSTOMER SET NAME = :name, ADDRESS = :address")
.namedParam("name", "John Doe")
.namedParam("address", "Dallas")
.run();
Note: or .namedParams(mapOfParams)
java.time support for query parameters
query.update("UPDATE CUSTOMER SET DEADLINE = ?, UPDATED = ?")
.params(LocalDate.of(2015, Month.MARCH, 5), Instant.now())
.run();
Note: support for any type can be implemented
java.util.Optional support
Optional<LocalData> deadline = ...
query.update("UPDATE CUSTOMER SET DEADLINE = ?")
.params(deadline)
.run();
Collection parameter support
Set<Long> ids = ...
List<Customer> customers = query.select("SELECT * FROM CUSTOMER WHERE ID IN (:ids)")
.namedParam("ids", ids)
.listResult(customerMapper);;
Note: supported for named parameters
Iterating a large resultset
query.select("SELECT * FROM CUSTOMER")
.iterateResult(rs -> {
// do something with the row
});
Query for a list of limited results
List<Customer> customers = query.select("SELECT * FROM CUSTOMER WHERE NAME = ?")
.params("John Doe")
.maxRows(345L)
.listResult(customerMapper);
Fetching generated key of an insert or updates
UpdateResultGenKeys<Long> result = query
.update("INSERT INTO CUSTOMER(NAME) VALUES(:name)")
.namedParams(namedParams)
.runFetchGenKeys(Mappers.singleLong());
Long id = result.generatedKeys().get(0);
Querying using a specific connection object
Connection connection = ...
Query query = fluentJdbc.queryOn(connection);
// do some querying...
Transactions
query.transaction().in(
() -> {
query
.update("UPDATE CUSTOMER SET NAME = ?, ADDRESS = ?")
.params("John Doe", "Dallas")
.run();
someOtherBusinessOperationAlsoNeedingTransactions();
}
)
All queries executed in the block will be part of the transaction - in the same thread, based on the same FluentJdbc/ConnectionProvider. Exceptions cause rollback. It is possible to use multiple transactions/datasources simultaneously.
Query listener
A listener provides a callback mechanism called on each FluentJdbc query operation. This allows things like SQL statement logging, performance measurement. The following example logs all successful SQL operations along with the time taken to execute them:
AfterQueryListener listener = execution -> {
if(execution.success()) {
log.debug(
String.format(
"Query took %s ms to execute: %s",
execution.executionTimeMs(),
execution.sql()
)
)
}
};
FluentJdbc fluentJdbc = new FluentJdbcBuilder()
// other configuration
.afterQueryListener(listener)
.build();
// run queries
Refer to the full documentation for more details and code examples.