To perform single INSERT, DELETE, or UPDATE operation, use {@link SqlEditor} instead. + +
The utilities in the {@link Db} class should always + be considered as a simpler alternative to this class. + +
The user of this class is able to fetch records in a very + compact style, without concern for details regarding +
Example use case in a Data Access Object(DAO), where a Person object +is fetched using aName as a simple business identifier : +
+Person fetch(String aName) throws DAOException {
+ Args.checkForContent(aName);
+ SqlFetcher3 fetcher = SqlFetcher3.forSingleOp(FETCH_PERSON, Util.asList(aName));
+ ModelBuilder3 builder = new ModelFromRow(Person.class);
+ return (Person)fetcher.fetchObject( builder );
+}
+
+ Note that the above feature is implemented using only +
Almost all callers of SqlFetcher will also use a {@link ModelBuilder}, + which is closely related to this class. + +
SqlFetcher can participate in a transaction by passing a + {@link Connection} to one of its static factory methods. Otherwise, an internal + Connection is obtained from {@link hirondelle.web4j.database.ConnectionSource}, + using {@link SqlId#getDatabaseName()} to identify the database. +*/ +final class SqlFetcher { + + /** + Factory method for single SELECT operation which takes parameters. + */ + static SqlFetcher forSingleOp(SqlId aSqlId, Object... aParams){ + return new SqlFetcher(aSqlId, INTERNAL_CONNECTION, NO_SEARCH_CRITERIA, aParams); + } + + /** + Factory method for SELECT operation which takes parameters and participates in + a transaction. + + @param aConnection used by all operations participating in the given transaction; + this connection is owned by the caller. + */ + static SqlFetcher forTx(SqlId aSqlId, Connection aConnection, Object... aParams){ + return new SqlFetcher(aSqlId, aConnection, NO_SEARCH_CRITERIA, aParams); + } + + /** + Factory method for a SELECT operation which takes parameters, and whose criteria are dynamic. + */ + static SqlFetcher forSearch(SqlId aSqlId, DynamicSql aSearchCriteria, Object... aParams) { + return new SqlFetcher(aSqlId, INTERNAL_CONNECTION, aSearchCriteria, aParams); + } + + /** + Perform a SELECT and return the first row as a Model Object. + +
Intended for use with SELECT statements which usually return one row. Intended for use with SELECT statements which usually return multiple rows. This method is not usually called. It is intended only for those
+ cases in which only a subset of the underlying ResultSet is to be
+ returned. Usually, all rows of the underlying result set are of interest,
+ and none should be eliminated from processing.
+
+ @param aStartIdx 1 or more
+ @param aPageSize 1 or more, the number of items returned to the caller
+ */
+ void limitRowsToRange(Integer aStartIdx, Integer aPageSize){
+ Args.checkForPositive(aStartIdx.intValue());
+ Args.checkForPositive(aPageSize.intValue());
+ fStartIdx = aStartIdx;
+ fEndIdx = getEndIdx(aStartIdx, aPageSize);
+ }
+
+ /**
+ Override the default transaction isolation level specified in web.xml.
+
+ This setting is applied only if this class is using its own internal connection, and
+ has not received a connection from the caller.
+
+ If the user passed an external
+ Connection to a constructor of this class, then this method
+ must not be called. (Changing the isolation level after a transaction
+ has started is not permitted - see {@link Connection}).
+
+ @see TxIsolationLevel
+ */
+ void setTxIsolationLevel(TxIsolationLevel aTxIsolationLevel) {
+ if ( ! fConnectionIsInternal ) {
+ throw new IllegalStateException (
+ "Cannot set transaction isolation level after transaction has started."
+ );
+ }
+ fLogger.fine("Setting transaction isolation level to " + aTxIsolationLevel);
+ fExplicitTxIsolationLevel = aTxIsolationLevel;
+ }
+
+ // PRIVATE
+
+ private SqlStatement fSql;
+ private PreparedStatement fStatement;
+
+ private Connection fConnection;
+ private boolean fConnectionIsInternal;
+ /**
+ Explicit Isolation level, for internal connections only.
+ If null, then the isolation level defined by {@link Config} for the given db name will be used.
+ */
+ private TxIsolationLevel fExplicitTxIsolationLevel;
+
+ private Integer fStartIdx;
+ private Integer fEndIdx;
+
+ private static final Connection INTERNAL_CONNECTION = null;
+ private static final DynamicSql NO_SEARCH_CRITERIA = null;
+
+ private static final Logger fLogger = Util.getLogger(SqlFetcher.class);
+
+ private SqlFetcher(SqlId aSqlId, Connection aConnection, DynamicSql aSearchCriteria, Object... aParams){
+ fSql = new SqlStatement(aSqlId, aSearchCriteria, aParams);
+ if ( aConnection != null ) {
+ fConnectionIsInternal = false;
+ fConnection = aConnection;
+ }
+ else {
+ fConnectionIsInternal = true;
+ }
+ }
+
+ private ResultSet fetchRows() throws DAOException {
+ ResultSet result = null;
+ Connection connection = null;
+ try {
+ connection = getConnection();
+ fStatement = fSql.getPreparedStatement(connection);
+ result = fStatement.executeQuery();
+ DbUtil.logWarnings(fStatement);
+ }
+ catch (SQLException rootCause){
+ String message =
+ "Cannot execute fetch: " + rootCause + Consts.SPACE + rootCause.getMessage() +
+ Consts.SPACE + fSql
+ ;
+ fLogger.severe(message);
+ //unusual: the close is not in a finally block, since it must only be closed if there's a problem
+ close(fStatement, connection);
+ throw new DAOException(message, rootCause);
+ }
+ return result;
+ }
+
+ /**
+ Cycles over a ResultSet, but what is done to each row is left
+ up to subclasses.
+
+ (This private class exists in order to eliminate code repetition between the
+ Collection and Map styles used above.)
+ */
+ private abstract class RowCycler
+ If 1..N rows are returned, parse the first row into a Model Object and
+ return it. Ignore any other rows.
If no rows are returned, return null.
+ (This behavior can occasionally be used to limit the result set to a single record.
+ It is a convenient alternative when the database has no robust or simple method of
+ limiting the number of returned rows. However, this technique should be used
+ only if the number of returned rows is not excessively large.)
+
+ @param aModelBuilder parses a row into a corresponding Model Object.
+ */
+
+ If 1..N rows are found, parse each row into a Model Object and
+ place it in aResult.
Items are added to aResult
+ in the order returned from the underlying ResultSet.
+ If 0 rows are found, then aResult will remain empty.
+
+ @param aModelBuilder parses a row into a corresponding Model Object.
+ @param aResult acts as an "out" parameter, and is initially empty.
+ */
+