Package org.soulwing.jdbc

Interface JdbcQuery<T>

  • All Superinterfaces:
    AutoCloseable
    public interface JdbcQuery<T>
extends AutoCloseable
    An SQL query operation.

    This API uses the builder pattern to allow a query to be easily configured and executed. It supports queries that return many rows as well as those that return a single row. The API is designed to allow a fluent specification of the query configuration.

    An instance of this type is created using the query or queryForType method on the JdbcOperations interface. The user of this interface must configure at least two characteristics:

    1. the SQL statement to execute, specified via the using method
    2. the manner in which the results are to be handled; either mapping rows to objects, extracting a column value, or using a result set handler

    Additionally, the query can be configured for single execution (default) or repeated execution. When a query is configured for repeated execution (using repeatedly()), the underlying statement and connection objects remain open after a result is retrieved, allowing the query to be executed again with different parameter values. A query that is configured for repeated execution must be closed when it is no longer needed, by invoking the close() method explicitly or by enclosing it in a try-with-resources construct.

    After a query has been fully configured, a result can be retrieved using either

    Examples:

    Retrieving a list of objects that correspond to the rows returned by a query using a RowMapper: 

     
 // A mapper that maps column values of a person row to a Person object
 RowMapper<Person> personMapper = new RowMapper<Person>() { ... };

 List<Person> results = sqlTemplate.queryForType(Person.class)
     .using("SELECT * FROM person ORDER BY name")
     .mappingRowsWith(personMapper)
     .retrieveList();

    Retrieving a list of values for a given column for all matching rows: 

     
 List<String> names = sqlTemplate.queryForType(String.class)
     .using("SELECT * FROM person ORDER BY name")
     .extractingColumn("name")
     .retrieveList();

    Retrieving a single object using a RowMapper: 

     
 // A mapper that maps column values of a person row to a Person object
 RowMapper<Person> personMapper = new RowMapper<Person>() { ... };

 long id = 3;  // ID of the person to retrieve
 Person person = sqlTemplate.queryForType(Person.class)
     .using("SELECT * FROM person WHERE id = ?")
     .mappingRowsWith(personMapper)
     .retrieveValue(Parameter.with(id));

    Retrieving a single value of a column: 

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

    Repeatedly executing a query with different parameters: 

     
 try (JdbcQuery<String> query = sqlTemplate.queryForType(String.class)
     .using("SELECT name FROM person WHERE name LIKE ?")
     .extractingColumn()
     .repeatedly()) {
   System.out.format("matching names: %s",
       query.retrieveList("%en%"));
   System.out.format("matching names: %s",
       query.retrieveList("%sh%"));
 }

    Processing the returned result set using a ResultSetHandler: 

     
 sqlTemplate.query()
     .using("SELECT * FROM person ORDER BY id")
     .handlingResultWith(new ResultSetMapper<Void>() {
        public void handleResult(ResultSet rs) throws SQLException {
          while (rs.next()) {
            exporter.exportPerson(rs.getLong("id"), rs.getLong("name"));
          }
          return null;
        }
     })
     .retrieveValue();
    Author:
    Carl Harris

    • Method Detail

      • using

        JdbcQuery<T> using​(String sql)
        Configures this query to execute the given SQL statement.
        Parameters:
        sql - the SQL statement to execute
        Returns:
        this query

      • using

        JdbcQuery<T> using​(SQLSource source)
        Configures this query to execute the given SQL statement.
        Parameters:
        source - source for the SQL statement to execute
        Returns:
        this query

      • handlingResultWith

        JdbcQuery<T> handlingResultWith​(ResultSetHandler<T> handler)
        Configures this query to use the given result set handler.

        Invoking this method replaces any existing configured row mapper or column extractor with the given result set handler.

        Parameters:
        handler - result set extractor
        Returns:
        this query

      • extractingColumn

        JdbcQuery<T> extractingColumn()
        Configures this query to extract the value of the first column.

        Invoking this method replaces any existing configured result set extractor or row mapper with the given column extractor.

        Returns:
        this query

      • extractingColumn

        JdbcQuery<T> extractingColumn​(int index)
        Configures this query to extract the value of the given column.

        Invoking this method replaces any existing configured result set extractor or row mapper with the given column extractor.

        Parameters:
        index - column index (index values start at 1)
        Returns:
        this query

      • extractingColumn

        JdbcQuery<T> extractingColumn​(String label)
        Configures this query to extract the value of the given column.

        Invoking this method replaces any existing configured result set extractor or row mapper with the given column extractor.

        Parameters:
        label - column label
        Returns:
        this query

      • mappingRowsWith

        JdbcQuery<T> mappingRowsWith​(RowMapper<T> rowMapper)
        Configures this query to use the given row mapper.

        Invoking this method replaces any existing configured result set extractor or row mapper with the given column extractor.

        Parameters:
        rowMapper - row mapper
        Returns:
        this query

      • repeatedly

        JdbcQuery<T> repeatedly()
        Configures this query for repeated execution.

        A query that is configured as repeatable must be explicitly closed by the caller using close() when no longer needed.

        Returns:
        this query

      • retrieveList

        List<T> retrieveList​(Parameter... parameters)
        Executes the query, retrieving the list of values for all matching rows.
        Parameters:
        parameters - values for query placeholders
        Returns:
        list of values of type T that were extracted/mapped by this query

      • retrieveValue

        T retrieveValue​(Parameter... parameters)
        Executes the query, retrieving a value representing the matching row
        Parameters:
        parameters - values for query placeholders
        Returns:
        value of type T that was extracted/mapped by this query
        Throws:
        SQLNoResultException - if no row was matched by this query
        SQLNonUniqueResultException - if more than one row was matched by this query

      • execute

        void execute​(Parameter... parameters)
        Executes the query, processing the result set with the configured handler.

        This method is a synonym for retrieveValue(Parameter...), but does not return the result produced by the query. It is typically used when the query is configured with a ResultSetHandler that has a void return type.

        Parameters:
        parameters - values for query placeholders

      • close

        void close()
        Closes the JDBC resources associated with this query.

        After a query is closed, its retrieval methods may not be subsequently invoked.

        Specified by:
        close in interface AutoCloseable