org.soulwing.jdbc.JdbcQuery Maven / Gradle / Ivy
Show all versions of fluent-jdbc Show documentation
/*
* File created on Aug 9, 2015
*
* Copyright (c) 2015 Carl Harris, Jr
* and others as noted
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.soulwing.jdbc;
import java.util.List;
import org.soulwing.jdbc.source.SQLSource;
/**
* 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 {@link JdbcOperations#query query}
* or {@link JdbcOperations#queryForType(Class) queryForType} method on the
* {@link JdbcOperations} interface. The user of this interface must configure
* at least two characteristics:
*
* - the SQL statement to execute, specified via the {@link #using(String) using}
* method
*
- the manner in which the results are to be handled; either
* {@linkplain #mappingRowsWith(RowMapper) mapping rows to objects},
* {@linkplain #extractingColumn(int) extracting a column value}, or using a
* {@linkplain #handlingResultWith(ResultSetHandler) 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 {@link #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 {@link #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
*
* - {@link #retrieveList(Parameter...)} for a query that returns multiple
* rows, OR
*
- {@link #retrieveValue(Parameter...)} for a query that returns exactly
* one row
*
*
* Examples:
*
* Retrieving a list of objects that correspond to the rows returned by
* a query using a {@link RowMapper}:
*
* {@code
* // A mapper that maps column values of a person row to a Person object
* RowMapper personMapper = new RowMapper() { ... };
*
* List 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:
*
* {@code
* List names = sqlTemplate.queryForType(String.class)
* .using("SELECT * FROM person ORDER BY name")
* .extractingColumn("name")
* .retrieveList();
* }
*
* Retrieving a single object using a {@link RowMapper}:
*
* {@code
* // A mapper that maps column values of a person row to a Person object
* RowMapper personMapper = new RowMapper() { ... };
*
* 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:
*
* {@code
* int averageAge = sqlTemplate.queryForType(int.class)
* .using("SELECT AVG(age) FROM person")
* .extractingColumn()
* .retrieveValue();
* }
*
* Repeatedly executing a query with different parameters:
*
* {@code
* try (JdbcQuery 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 {@link ResultSetHandler}:
*
* {@code
* sqlTemplate.query()
* .using("SELECT * FROM person ORDER BY id")
* .handlingResultWith(new ResultSetMapper() {
* public void handleResult(ResultSet rs) throws SQLException {
* while (rs.next()) {
* exporter.exportPerson(rs.getLong("id"), rs.getLong("name"));
* }
* return null;
* }
* })
* .retrieveValue();
* }
*
* @author Carl Harris
*/
public interface JdbcQuery extends AutoCloseable {
/**
* Configures this query to execute the given SQL statement.
* @param sql the SQL statement to execute
* @return this query
*/
JdbcQuery using(String sql);
/**
* Configures this query to execute the given SQL statement.
* @param source source for the SQL statement to execute
* @return this query
*/
JdbcQuery using(SQLSource source);
/**
* 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.
*
* @param handler result set extractor
* @return this query
*/
JdbcQuery handlingResultWith(ResultSetHandler handler);
/**
* 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.
*
* @return this query
*/
JdbcQuery extractingColumn();
/**
* 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.
*
* @param index column index (index values start at 1)
* @return this query
*/
JdbcQuery 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.
*
* @param label column label
* @return this query
*/
JdbcQuery extractingColumn(String label);
/**
* 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.
*
* @param rowMapper row mapper
* @return this query
*/
JdbcQuery mappingRowsWith(RowMapper rowMapper);
/**
* Configures this query for repeated execution.
*
* A query that is configured as repeatable must be explicitly closed by
* the caller using {@link #close()} when no longer needed.
*
* @return this query
*/
JdbcQuery repeatedly();
/**
* Executes the query, retrieving the list of values for all matching rows.
* @param parameters values for query placeholders
* @return list of values of type {@code T} that were extracted/mapped by
* this query
*/
List retrieveList(Parameter... parameters);
/**
* Executes the query, retrieving a value representing the matching row
* @param parameters values for query placeholders
* @return value of type {@code T} that was extracted/mapped by this query
* @throws SQLNoResultException if no row was matched by this query
* @throws SQLNonUniqueResultException if more than one row was matched by
* this query
*/
T retrieveValue(Parameter... parameters);
/**
* Executes the query, processing the result set with the configured handler.
*
* This method is a synonym for {@link #retrieveValue(Parameter...)}, but
* does not return the result produced by the query. It is typically used
* when the query is configured with a {@link ResultSetHandler} that has a
* void return type.
*
* @param parameters values for query placeholders
*/
void execute(Parameter... parameters);
/**
* Closes the JDBC resources associated with this query.
*
* After a query is closed, its retrieval methods may not be subsequently
* invoked.
*/
void close();
}