Get started guide

Quick start guide

This guides shows how to use FluentJdbc in a project. ###Requirements### Java 8 ###Include FluentJdbc library###

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

note: it won't add any additional transitive dependencies. ###Set up FluentJdbc instance### A FluentJdbc instance needs to be configured for the project. It provides access to the Query API. Query

API ultimately needs access to JDBC Connections. This can be either achieved by a ConnectionProvider

that feeds it with Connections (recommended), alternatively the API can be instantiated for "one

session" use using a specific JDBC Connection instance. ####ConnectionProvider#### Typically the ConnectionProvider will fetch Connections from a DataSource, the library comes with an

implementation for that.

DataSource dataSource = ...
ConnectionProvider provider = new DataSourceConnectionProvider(dataSource);

Other implementations are also possible, eg getting the Connection from a JPA session or a Connection

callback, etc. See documentations for details. ####Create a FluentJdbc instance#### A typical use:

FluentJdbc fluentJdbc = new FluentJdbcBuilder()
	.connectionProvider(provider) // optional
	.build();

FluentJdbc is thread-safe, no need to create it more than once. ####Create a Query instance#### With the configured ConnectionProvider (thread-safe)

Query query = fluentJdbc.query() ...

Alternatively with a given Connection instance (not thread-safe)

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

###Querying### The Query interface can then be used to execute SQL queries. Some examples: ####Update or insert queries####

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

Or with named parameters

Map<String, Object> namedParams = new HashMap<>();
namedParams.put("name", "John Doe");
namedParams.put("address", "Dallas");

UpdateResult result = query
	.update("UPDATE CUSTOMER SET NAME = :name, ADDRESS = :address")
	.namedParams(namedParams)
	.run();

####Select queries##### #####Mapping results##### A row of in the ResultSet will be mapped to a single object by a Mapper. Can be implemented manually:

Mapper<Customer> manualCustomerMapper = resultSet -> {
	return new Customer(resultSet.getString("NAME"));
}

Or can be mapped automatically to POJOs, based on matching object field names vs db column names. Name matching is case-insensitive and ignores '_' characters.

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

#####List of results#####

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

#####First result#####

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

#####Convenience mappers##### (and named parameters usage)

Map<String, Object> namedParams = 
Long count = query
	.select("SELECT COUNT(*) FROM CUSTOMER WHERE NAME = :name")
	.namedParams(namedParams)
	.singleResult(Mappers.singleLong());

#####Iterating large resultsets##### query .select("SELECT * FROM CUSTOMER") .iterateResult(customerMapper, (customer) -> { // do something with the customer }); ####Batch inserts or updates#### Using positional parameters:

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

Using named parameters:

Iterator<Map<String, Object>> params = ...;
query
	.batch("INSERT INTO CUSTOMER(NAME, ADDRESS) VALUES(:name, :address)")
	.namedParams(params)
	.run();

####Custom parameter types#### Parameters can be normal JDBC types (Integer, Long, String, BigDecimal, java.sql.Date, ...) or

java.time types - supported out of the box.

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

Support for more types can be registered to FluentJdbc

Map<Class, ParamSetter> paramSetters = ...
FluentJdbc fluentJdbc = new FluentJdbcBuilder()
	.connectionProvider(provider)
	.paramSetters(paramSetters)
	.build();

####Transactions#### Transaction-managed connections can be provided by the ConnectionProvider implementation. This is possible in a number of ways.

• Using a transaction-aware DataSource (eg JEE DataSources, Spring's TransactionAwareDataSourceProxy)
• Getting the underlying connection from a JPA session (eg Guice Persist with JPA)
• Getting transaction-managed connection from a Connection callback.
• There is an extension library for Guice Persist: fluentjdbc-guice-persist, which supports standalone transaction management (without JPA or other tech)