Getting Started

Getting Started

The FluentJdbc class provides the central API you use to perform SQL operations. See the [Javadocs] (http://soulwing.github.io/fluent-jdbc/maven-site/apidocs/) for full details of the API.

It is a plain old Java object, with a constructor that takes a JDBC DataSource. An instance of FluentJdbc is thread safe and thus can be used by any number of application components concurrently.

import javax.sql.DataSource;
import org.soulwing.jdbc.FluentJdbc;

DataSource dataSource = ... // typically injected by your container
FluentJdbc jdbc = new FluentJdbc(dataSource);

Using Fluent JDBC you can easily execute any combination of queries and updates as well as SQL DDL statements. The following simple example creates a table, inserts some values into it, and then queries and prints some results.

src/test/java/FluentJdbcDemo.java:

1   DataSource dataSource = ... // typically injected by the container
2   FluentJdbc jdbc = new FluentJdbc(dataSource);

3   jdbc.execute("CREATE TABLE person ( " + 
       "id BIGINT PRIMARY KEY, name VARCHAR(50), age INTEGER )");

4   jdbc.executeScript(new StringSQLSource(
       "INSERT INTO PERSON(id, name, age) VALUES(1, 'Jennifer Wilson', 29);" +
       "INSERT INTO PERSON(id, name, age) VALUES(2, 'Nadine Bennett', 31);" +
       "INSERT INTO PERSON(id, name, age) VALUES(3, 'Megan Marshall', 27);"
    ));

5   List<Map> people = jdbc.queryForType(Map.class)
        .using("SELECT * FROM person")
        .mappingRowsWith(new RowMapper<Map>() {
            public Map mapRow(ResultSet rs, int rowNum)
                throws SQLException {
              Map<String, Object> person = new HashMap<>();
              person.put("id", rs.getLong("id"));
              person.put("name", rs.getString("name"));
              person.put("age", rs.getInt("age"));
              return person;
            }
        })
        .retrieveList();

6   System.out.format("people: %s\n", people);

7   try (JdbcUpdate updater = jdbc.update()
            .using("UPDATE person SET age = age + 1 WHERE id = ?")
            .repeatedly()) {
8     updater.execute(Parameter.with(2));
9     updater.execute(Parameter.with(3));
    }

10  int averageAge = jdbc.queryForType(int.class)
        .using("SELECT AVG(age) FROM person")
        .extractingColumn()
        .retrieveValue();

11  System.out.format("average age: %d\n", averageAge);

12  int count = jdbc.update()
        .using("DELETE FROM person")
        .execute();

13  System.out.format("deleted %d people\n", count);

The example demonstrates many of the salient features of Fluent JDBC.

• Line 2 -- FluentJdbc is a POJO that you construct with a DataSource
• Line 3 -- Execute arbitrary SQL DDL or DML statements using execute
• Line 4 -- Execute DDL or DML scripts using an SQLSource with executeScript; here we use StringSQLSource, but there are useful implementations that allow the use of files or classpath resources
• Line 5 -- Execute a query to retrieve a list of objects of arbitrary type using the queryByType method; the query builder's using method specifies the SQL statement, and the builder can be configured with a row mapper or column extractor.
• Line 7 -- Prepare an update statement for efficient repeated use using repeatedly on the query builder; note the use of the try-with-resources construct -- queries and updates that are configured for repeated use must be closed when no longer needed.
• Line 8-9 -- Execute an update using the prepared statement and arbitrary parameter values specified using Parameter.with
• Line 10 -- Execute a single row query, extract a column value and return it using the query builder's retrieveValue method. Since we didn't specify a column for extractingColumn, the first column value will be extracted, we but we can also specify a column by label or index
• Line 12 -- Executing an update returns the number of affected rows