Skip to content

Performing Queries and Updates

Jump to bottom Edit New page
Carl Harris edited this page Apr 27, 2017 · 6 revisions

Fluent JDBC provides an API that is based on the builder and command patterns for executing queries and updates.

Performing Queries

Fluent JDBC provides support for multi-row and single-row queries, with many conveniences for common use cases.

Retrieving and Mapping Rows

When invoking a query that returns multiple rows, Fluent JDBC allows you to easily map column values to create a list of Java objects that correspond to each row of data returned by the query. The RowMapper interface allows you to define how row contents should be used to create instances of almost any object type:

int minAge = 21;
List<Person> person = jdbc.queryForType(Person.class)
    .using("SELECT * FROM person WHERE age >= ? ORDER BY age")
    .mappingRowsWith(new RowMapper<Person>() { 
                         public void mapRow(ResultSet rs, int rowNum) throws SQLException {
                           Person person = new Person();
                           person.setId(rs.getLong("id"));
                           person.setName(rs.getString("name"));
                           person.setAge(rs.getInt("age"));
                           return person;
                         }
                     })
    .retrieveList(Parameter.with(minAge));

The RowMapper is invoked once for each row, and is provided with a JDBC ResultSet positioned on the current row and the index of the row (starting at 1).

Note that query parameters are specified using the Parameter class. This class has with factory methods to create instances that specify a value and (optionally) an SQL type.

You can also extract all values of a given column:

int minAge = 21;
List<Integer> person = jdbc.queryForType(Integer.class)
    .using("SELECT * FROM person WHERE age >= ?")
    .extractingColumn("age")
    .retrieveList(Parameter.with(minAge));

In addition to specifying a column label (name), you can also specify its index (starting at 1). For the common case of single column queries, you can omit the label or index to retrieve the first column.

Single Row Queries

For queries that return a single row, the query builder provides the retrieveValue method for getting a Java object from a single row.

You can use a RowMapper to turn the resulting row of a single row query into an object:

Person person = jdbc.queryForType(Person.class)
    .using("SELECT * FROM person WHERE id = ?")
    .mappingRowsWith(new RowMapper<Person>() { 
        public void mapRow(ResultSet rs, int rowNum) throws SQLException {
          Person person = new Person();
          person.setId(rs.getLong("id"));
          person.setName(rs.getString("name"));
          person.setAge(rs.getInt("age"));
          return person;
        }})
    .retrieveValue(Parameter.with(2));

Often, you want to query and get a single column value. The query builder's extractingColumn method can be used to accomplish this easily:

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

When the query returns multiple columns, you can identify the column value of interest by name/label:

int min = jdbc.queryForType(int.class)
    .using("SELECT MAX(age) max_age, MIN(age) min_age FROM person")
    .extractingColumn("min_age")
    .retrieveValue();

Or by column position (column indexes start at 1):

int min = jdbc.queryForType(int.class)
    .using("SELECT MAX(age) max_age, MIN(age) min_age FROM person")
    .extractingColumn(2)
    .retrieveValue();

NOTE: You should use retrieveValue only when you expect exactly one row. If the query returns no rows, the SQLNoResultException will be thrown. If the query returns more than one row, the SQLNonUniqueResultException will be thrown.

Manipulating Result Sets Using a ResultSetHandler

If you want to do your own manipulation of the ResultSet returned by a query you can use the ResultSetHandler interface. Suppose we wanted to export some data:

jdbc.query().
  .using("SELECT * FROM person")
  .handlingResultWith(new ResultSetExtractor()<Void> {
      public Void extract(ResultSet rs) throws SQLException {
        while (rs.next()) {
          exporter.exportPerson(rs.getLong("id"), rs.getString("name"), 
              rs.getInt("age")); 
        }
        return null;
      })
  .execute();

By using ResultSetHandler you get the benefit of a closure based design in your code, in addition to not having to handle SQLException yourself.

Performing Updates

Executing statements that insert, update, or delete rows can be done with the update method. It returns an update builder that can be configured with the SQL statement that you want to execute.

For example:

jdbc.update()
    .using("UPDATE person SET age = age + 1 WHERE id = ?")
    .execute(Parameter.with(2));

The update method returns the number of rows affected by the given statement.

Executing Queries and Updates Repeatedly

Normally, Fluent JDBC closes the underlying JDBC statement and database connection after the statement has been executed (using execute, retrieveList, or retrieveValue) and the result has been retrieved.

However, sometimes you want to query or update the database repeatedly, using the same statement, with different parameters. For these situations, the query and update builders provide a repeatedly method which is used to configure the query or update so that it can be executed as many times as needed.

When configured for repeated execution, the SQL is parsed once, and the resulting JDBC statement object and associated connection are cached until the query or update is closed. This allows you to repeatedly execute the prepared SQL statement, while passing different parameters to be bound before each execution.

Suppose you are importing information from a CSV file. For each line in the CSV file, you want to execute the same INSERT statement, with different values. This could be accomplished as follows:

final File csvFile = new File("people.csv");
final String sql = "";

try (CSVReader reader = new CSVReader(csvFile);
    JdbcUpdate updater = jdbc.update().
        .using("INSERT INTO person(id, name, age) VALUES(?, ?, ?)")
        .repeatedly()) {  
  while (reader.hasNext()) {
    CSV csv = reader.next();
    updater.execute(
        Parameter.with(Long.valueOf(csv.get(0))),
        Parameter.with(csv.get(1)), 
        Parameter.with(Integer.valueOf(csv.get(2)));
  }  
}

When a query or updater is configured for repeated execution, you must close it when it is no longer needed, so that the database connection and other JDBC objects it holds can be released. As shown here, a convenient way to make certain it gets closed is to use the Java 7 try-with-resources construct. Of course, you could also accomplish the same thing using a try-finally construct, in which you explicitly call the close method.

Using Files for SQL Queries and Updates

Just as you can with the execute method, you can put your SQL query or update statements in files and use an SQLSource to access them. This makes your code cleaner and easier to understand. Also, with some careful organization of your resources, you can easily write code that handles multiple database dialects.

Using SQLSource with the query and update methods is easy:

long id = 2;
int age = jdbc.queryForType(int.class)
    .using(ResourceSQLSource.with("classpath:sql/queries/findPersonById.sql"))
    .extractingColumn("age")
    .retrieveValue(Parameter.with(id));

age = age + 1;

jdbc.update()
    .using(ResourceSQLSource.with("classpath:sql/updates/updatePersonAgeById.sql"))
    .execute(Parameter.with(age), Parameter.with(id));

Each passed SQLSource is used to read a single statement, and is then closed.

Add a custom footer
Clone this wiki locally