Get started guide

This guide shows how to get started with FluentJdbc ###Requirements### Java 8 ###Include FluentJdbc library###

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

###Set up FluentJdbc instance### Generally a single fluentJdbc instance is created for an application. It holds configuration, and creates the Query API instances (based on a DataSource or on a specific Connection object). The following example shows this based on a JDBC DataSource.

DataSource dataSource = ...
FluentJdbc fluentJdbc = new FluentJdbcBuilder()
	.connectionProvider(dataSource)
        // optionally other configuration
	.build();

Note: There are other ways of providing connections to FluentJdbc. See further details at the Integration / ConnectionProvider section in wiki the sidebar. ####Create a Query API instance#### Based on the configured ConnectionProvider (thread-safe, re-entrant)

Query query = fluentJdbc.query();

Or alternatively based on a given Connection instance (re-entrant)

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

The Query interface can then be used to execute SQL queries. ###Querying### Some examples: ####Update or insert queries#### Check Insert / Update page for detailed usage instructions. Some examples:

With positional parameters

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

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##### Check the Select page for detailed usage details on select querying and result mapping. Some examples: #####List of results#####

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

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

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

#####Single result, convenience mappers#####

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#### Check Batch page for more usage details. Some examples:

With positional parameters:

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

With named parameters:

Iterator<Map<String, Object>> params = ...; // or stream
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 configured. See Query parameter types page for more details ####java.util.Optional support####

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

####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);

####Transactions#### #####Programmatic 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. #####Transaction-aware DataSource###### Transaction may be managed outside FluentJdbc, when its based on a transaction-aware datasource ####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