FluentJdbc

FluentJdbc provides built-in support for several types:

• java.time: Supports LocalDate, Instant, etc.
• java.util.Optional: Automatically handles nullability.
• Collections: Supports passing a Set or List to a named parameter for IN clauses (e.g., WHERE ID IN (:ids)).

// java.time
query.update("UPDATE CUSTOMER SET DEADLINE = ?")
	.params(LocalDate.of(2015, Month.MARCH, 5))
	.run();

// Optional
Optional<LocalData> deadline = ...;
query.update("UPDATE CUSTOMER SET DEADLINE = ?")
	.params(deadline)
	.run();

// Collections (with named parameters)
Set<Long> ids = ...;
query.select("SELECT * FROM CUSTOMER WHERE ID IN (:ids)")
	.namedParam("ids", ids)
	.listResult(customerMapper);

You can map results manually using a lambda expression or automatically using ObjectMappers. For automatic mapping, create an ObjectMappers instance (typically once per application) and use .forClass(Class) to generate a mapper.

// Manual mapping
Mapper<Customer> manualMapper = resultSet -> new Customer(resultSet.getString("NAME"), resultSet.getString("ADDRESS"));

// Automatic mapping
ObjectMappers objectMappers = ObjectMappers.builder().build();
Mapper<Customer> customerMapper = objectMappers.forClass(Customer.class);

To use FluentJdbc in your Java 8+ project, add the following dependency to your pom.xml. Note that the library requires Java 8.

<dependency>
    <groupId>org.codejargon</groupId>
    <artifactId>fluentjdbc</artifactId>
    <version>1.8.3</version>
</dependency>

The most common way to initialize FluentJdbc is by providing a DataSource to the FluentJdbcBuilder. Once built, you can obtain a Query instance, which is thread-safe and reentrant, to execute your SQL statements.

DataSource dataSource = ...
FluentJdbc fluentJdbc = new FluentJdbcBuilder()
	.connectionProvider(dataSource)
	.build();
Query query = fluentJdbc.query();
// ... use the Query interface for queries (thread-safe, reentrant)

Transactions are managed via the .transaction().in(Runnable) method. All queries executed within the provided block run in the same thread and use the same connection. If an exception is thrown within the block, the transaction is automatically rolled back.

query.transaction().in(
	() -> {
		query
        	.update("UPDATE CUSTOMER SET NAME = ?, ADDRESS = ?")
        	.params("John Doe", "Dallas")
        	.run();
		someOtherBusinessOperationAlsoNeedingTransactions();
	}
);

To retrieve auto-generated keys (like IDs) after an INSERT or UPDATE, use .runFetchGenKeys(mapper). This returns an UpdateResultGenKeys object.

UpdateResultGenKeys<Long> result = query
	.update("INSERT INTO CUSTOMER(NAME) VALUES(:name)")
	.namedParams(namedParams)
    .runFetchGenKeys(Mappers.singleLong());

Long id = result.generatedKeys().get(0);

Use the .update() method to execute UPDATE, INSERT, DELETE, or ALTER statements. You can pass parameters using positional parameters with .params().

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

For high-performance batch operations, use the .batch() method. It accepts a Stream<List<Object>>, Iterator, or Iterable of parameter sets.

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

You can restrict the number of rows returned by a query using the .maxRows(long) method.

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

Use .singleResult(mapper) when you expect exactly one row (e.g., for counts) and .firstResult(mapper) when you want an Optional containing the first row found.

// Single result (e.g., Long) using built-in Mappers
Long count = query.select("SELECT COUNT(*) FROM CUSTOMER WHERE NAME = ?")
	.params("John Doe")
	.singleResult(Mappers.singleLong);

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

Instead of positional ? placeholders, you can use named parameters (e.g., :name). Use .namedParam(name, value) for individual parameters or .namedParams(map) to pass a map of parameters.

// Individual named parameters
query.update("UPDATE CUSTOMER SET NAME = :name, ADDRESS = :address")
	.namedParam("name", "John Doe")
	.namedParam("address", "Dallas")
	.run();

// Using a map
query.update("UPDATE CUSTOMER SET NAME = :name")
	.namedParams(mapOfParams)
	.run();

To retrieve multiple rows, use .select() followed by .listResult(mapper). You must provide a Mapper to transform the ResultSet into your desired objects.

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