1 package hirondelle.web4j.database;

     2 

     3 import hirondelle.web4j.BuildImpl;

     4 import hirondelle.web4j.readconfig.Config;

     5 import hirondelle.web4j.util.Args;

     6 import hirondelle.web4j.util.Consts;

     7 import hirondelle.web4j.util.Util;

     8 

     9 import java.sql.Connection;

    10 import java.sql.PreparedStatement;

    11 import java.sql.ResultSet;

    12 import java.sql.SQLException;

    13 import java.util.ArrayList;

    14 import java.util.Collection;

    15 import java.util.List;

    16 import java.util.logging.Logger;

    17 

    18 /**

    19  Perform a single SELECT command.

    20 

    21 <P>To perform single INSERT, DELETE, or UPDATE operation, use {@link SqlEditor} instead. 

    22 

    23 <P><span class="highlight">The utilities in the {@link Db} class should always 

    24  be considered as a simpler alternative to this class.</span> 

    25  

    26 <P>The user of this class is able to fetch records in a very 

    27  compact style, without concern for details regarding 

    28 <ul>

    29  <li>database connections

    30  <li>manipulating raw SQL text

    31  <li>inserting parameters into SQL statements

    32  <li>error handling 

    33  <li>logging of warnings

    34  <li>closing statements and connections

    35 </ul>

    36 <P>Example use case in a Data Access Object(DAO), where a <tt>Person</tt> object 

    37 is fetched using <tt>aName</tt> as a simple business identifier : 

    38 <PRE>

    39 Person fetch(String aName) throws DAOException {

    40   Args.checkForContent(aName);

    41   SqlFetcher3 fetcher = SqlFetcher3.forSingleOp(FETCH_PERSON, Util.asList(aName));

    42   ModelBuilder3 builder = new ModelFromRow(Person.class);

    43   return (Person)fetcher.fetchObject( builder );

    44 }

    45 </PRE>

    46  <P>Note that the above feature is implemented using only

    47 <ul>

    48  <li>one entry in a <tt>.sql</tt> text file, which stores the underlying SQL

    49  statement as text, outside compiled code

    50  <li>one <tt>public static final</tt> {@link SqlId} field declared in some class 

    51  (usually the {@link hirondelle.web4j.action.Action} of the DAO itself) 

    52  <li>a short method in a DAO. (In addition, if this method uses 

    53  {@link ModelFromRow}, then the length of this method is almost always 3 or 4 lines, 

    54  <em>regardless of the number of fields in the underlying Model Object</em>)

    55 </ul>

    56  Given these database utility classes, many DAO implementations 

    57  become very compact. (The {@link Db} class can reduce the size of  

    58  implementations even further.)

    59 

    60 <P> Almost all callers of <tt>SqlFetcher</tt> will also use a {@link ModelBuilder},

    61  which is closely related to this class.

    62 

    63  <P><tt>SqlFetcher</tt> can participate in a transaction by passing a

    64  {@link Connection} to one of its static factory methods. Otherwise, an internal 

    65  <tt>Connection</tt> is obtained from {@link hirondelle.web4j.database.ConnectionSource}, 

    66  using {@link SqlId#getDatabaseName()} to identify the database. 

    67 */

    68 final class SqlFetcher {

    69 

    70   /**

    71    Factory method for single <tt>SELECT</tt> operation which takes parameters.

    72   */

    73   static SqlFetcher forSingleOp(SqlId aSqlId, Object... aParams){

    74     return new SqlFetcher(aSqlId, INTERNAL_CONNECTION, NO_SEARCH_CRITERIA, aParams);

    75   }

    76 

    77   /**

    78    Factory method for <tt>SELECT</tt> operation which takes parameters and participates in 

    79    a transaction.

    80   

    81    @param aConnection used by all operations participating in the given transaction; 

    82    this connection is owned by the caller.

    83   */

    84   static SqlFetcher forTx(SqlId aSqlId, Connection aConnection, Object... aParams){

    85     return new SqlFetcher(aSqlId, aConnection, NO_SEARCH_CRITERIA, aParams);

    86   }

    87   

    88   /**

    89    Factory method for a <tt>SELECT</tt> operation which takes parameters, and whose criteria are dynamic.

    90   */

    91   static SqlFetcher forSearch(SqlId aSqlId, DynamicSql aSearchCriteria, Object... aParams) {

    92     return new SqlFetcher(aSqlId, INTERNAL_CONNECTION, aSearchCriteria, aParams);

    93   }

    94   

    95   /**

    96    Perform a SELECT and return the first row as a Model Object.

    97     

    98    <P>Intended for use with SELECT statements which usually return one row.<br>

    99    If <tt>1..N</tt> rows are returned, parse the first row into a Model Object and 

   100    return it. Ignore any other rows.<br> If no rows are returned, return <tt>null</tt>.

   101    (This behavior can occasionally be used to limit the result set to a single record. 

   102    It is a convenient alternative when the database has no robust or simple method of

   103    limiting the number of returned rows. However, this technique should be used 

   104    only if the number of returned rows is not excessively large.)

   105   

   106    @param aModelBuilder parses a row into a corresponding Model Object.

   107   */

   108   <T> T fetchObject(ModelBuilder<T> aModelBuilder) throws DAOException {

   109     List<T> list = new ArrayList<T>();

   110     fetchObjects(aModelBuilder, list);

   111     return list.isEmpty()? null : list.get(0);

   112   }

   113   

   114   /**

   115    Perform a SELECT and return a corresponding <tt>Collection</tt> of Model Objects.

   116   

   117    <P>Intended for use with SELECT statements which usually return multiple rows.<br>

   118    If <tt>1..N</tt> rows are found, parse each row into a Model Object and 

   119    place it in <tt>aResult</tt>.<br> Items are added to <tt>aResult</tt>

   120    in the order returned from the underlying <tt>ResultSet</tt>.<br>

   121    If <tt>0</tt> rows are found, then <tt>aResult</tt> will remain empty.

   122   

   123    @param aModelBuilder parses a row into a corresponding Model Object.

   124    @param aResult acts as an "out" parameter, and is initially empty.

   125   */

   126   <T> void fetchObjects(ModelBuilder<T> aModelBuilder, Collection<T> aResult) throws DAOException {

   127     checkIsEmpty(aResult);

   128     

   129     ResultSet resultSet = fetchRows();

   130     RowCycler cycler = new CollectionRowCycler(aResult);

   131     cycler.cycleRows(resultSet, aModelBuilder);

   132     if (aResult.size() == 0){

   133       fLogger.fine("No Results Found for query: " + fSql);

   134     }

   135   }

   136 

   137   /**

   138     Set parameters for limiting returned items to a specific block of rows.

   139    

   140     <P><em>This method is not usually called</em>. It is intended only for those 

   141     cases in which only a subset of the underlying <tt>ResultSet</tt> is to be 

   142     returned. Usually, all rows of the underlying result set are of interest, 

   143     and none should be eliminated from processing.

   144    

   145     @param aStartIdx 1 or more

   146     @param aPageSize 1 or more, the number of items returned to the caller

   147    */

   148    void limitRowsToRange(Integer aStartIdx, Integer aPageSize){

   149      Args.checkForPositive(aStartIdx.intValue());

   150      Args.checkForPositive(aPageSize.intValue());

   151      fStartIdx = aStartIdx;

   152      fEndIdx = getEndIdx(aStartIdx, aPageSize);

   153    }

   154 

   155   /**

   156    Override the default transaction isolation level specified in <tt>web.xml</tt>.

   157   

   158    <P>This setting is applied only if this class is using its own internal connection, and 

   159    has not received a connection from the caller.

   160    

   161    <P><span class="highlight">If the user passed an external 

   162    <tt>Connection</tt> to a constructor of this class, then this method 

   163    must not be called.</span> (Changing the isolation level after a transaction 

   164    has started is not permitted - see {@link Connection}).

   165   

   166    @see TxIsolationLevel

   167   */ 

   168   void setTxIsolationLevel(TxIsolationLevel aTxIsolationLevel) {

   169     if ( ! fConnectionIsInternal ) {

   170       throw new IllegalStateException (

   171         "Cannot set transaction isolation level after transaction has started."

   172       );

   173     }

   174     fLogger.fine("Setting transaction isolation level to " + aTxIsolationLevel);

   175     fExplicitTxIsolationLevel = aTxIsolationLevel;

   176   }

   177   

   178   // PRIVATE 

   179   

   180   private SqlStatement fSql;

   181   private PreparedStatement fStatement;

   182   

   183   private Connection fConnection;

   184   private boolean fConnectionIsInternal;

   185   /** 

   186     Explicit Isolation level, for internal connections only.

   187     If null, then the isolation level defined by {@link Config} for the given db name will be used. 

   188   */

   189   private TxIsolationLevel fExplicitTxIsolationLevel;

   190   

   191   private Integer fStartIdx;

   192   private Integer fEndIdx;

   193   

   194   private static final Connection INTERNAL_CONNECTION = null;

   195   private static final DynamicSql NO_SEARCH_CRITERIA = null;

   196   

   197   private static final Logger fLogger = Util.getLogger(SqlFetcher.class);  

   198 

   199   private SqlFetcher(SqlId aSqlId, Connection aConnection, DynamicSql aSearchCriteria, Object... aParams){

   200     fSql = new SqlStatement(aSqlId, aSearchCriteria, aParams);

   201     if ( aConnection != null ) {

   202       fConnectionIsInternal = false;

   203       fConnection = aConnection;

   204     }

   205     else {

   206       fConnectionIsInternal = true;

   207     }

   208   }

   209   

   210   private ResultSet fetchRows() throws DAOException {

   211     ResultSet result = null;

   212     Connection connection = null;

   213     try {

   214       connection = getConnection();

   215       fStatement = fSql.getPreparedStatement(connection);

   216       result = fStatement.executeQuery();

   217       DbUtil.logWarnings(fStatement);

   218     }

   219     catch (SQLException rootCause){

   220       String message = 

   221         "Cannot execute fetch: " + rootCause + Consts.SPACE +  rootCause.getMessage() + 

   222         Consts.SPACE + fSql

   223       ;

   224       fLogger.severe(message);

   225       //unusual: the close is not in a finally block, since it must only be closed if there's a problem

   226       close(fStatement, connection);

   227       throw new DAOException(message, rootCause);

   228     }

   229     return result;

   230   }

   231 

   232   /**

   233    Cycles over a <tt>ResultSet</tt>, but what is done to each row is left 

   234    up to subclasses.

   235   

   236    (This private class exists in order to eliminate code repetition between the 

   237    Collection and Map styles used above.) 

   238   */

   239   private abstract class RowCycler<T> {

   240     void cycleRows(ResultSet aResultSet, ModelBuilder<T> aModelBuilder) throws DAOException {

   241       try {

   242         while (aResultSet.next()){

   243           if ( ! isLimitingRows() ) {

   244             addItemToResult(aModelBuilder, aResultSet);

   245           }

   246           else {

   247             if ( isAfterStart(aResultSet) ){

   248               if ( ! isAfterEnd(aResultSet) ) {

   249                 addItemToResult(aModelBuilder, aResultSet);

   250               }

   251               else {

   252                 fLogger.fine(

   253                   "Finished processing ResultSet. This row is passed the end :  " + 

   254                   aResultSet.getRow()

   255                 );

   256                 break;

   257               }

   258             }

   259             else {

   260               fLogger.fine(

   261                 "Skipping row. Index not yet high enough to start processing : " + 

   262                 aResultSet.getRow()

   263               );

   264             }

   265           }

   266         }

   267       }

   268       catch (SQLException rootCause){

   269         String message = 

   270           "Cannot execute fetch: " + rootCause + Consts.SPACE + 

   271           rootCause.getErrorCode() + Consts.SPACE + fSql

   272         ;

   273         throw new DAOException(message, rootCause);

   274       }

   275       finally {

   276         close(fStatement, fConnection);

   277       }

   278     }

   279     abstract void addItemToResult(ModelBuilder<T> aModelBuilder, ResultSet aResultSet) throws DAOException, SQLException;

   280   }

   281   

   282   private final class CollectionRowCycler<T> extends RowCycler<T> {

   283     CollectionRowCycler(Collection<T> aCollection){

   284       fCollection = aCollection;

   285     }

   286     void addItemToResult(ModelBuilder<T> aModelBuilder, ResultSet aResultSet) throws DAOException {

   287       T item = aModelBuilder.buildObject(aResultSet);

   288       fCollection.add( item );

   289     }

   290     private Collection<T> fCollection;

   291   }

   292   

   293   private void checkIsEmpty(Collection aOutParam){

   294     if ( ! aOutParam.isEmpty() ) {

   295       throw new IllegalArgumentException("Out parameter is not initially empty.");

   296     }

   297   }

   298   

   299   /**

   300    Return either the external connection, passed by the caller, or an internal one with 

   301    the appropriate isolation level.

   302   */

   303   private Connection getConnection() throws DAOException {

   304     if ( fConnectionIsInternal ) {

   305       String dbName = fSql.getSqlId().getDatabaseName();

   306       if( Util.textHasContent(dbName) ){

   307         fConnection = BuildImpl.forConnectionSource().getConnection(dbName);

   308       }

   309       else{ 

   310         fConnection = BuildImpl.forConnectionSource().getConnection();

   311       }

   312       TxIsolationLevel.set(getIsolationLevel(dbName), fConnection);

   313     }

   314     else {

   315       //use the external connection passed to the constructor

   316     }

   317     return fConnection;

   318   }

   319   

   320   private TxIsolationLevel getIsolationLevel(String aDbName){

   321     TxIsolationLevel result = null;

   322     if(fExplicitTxIsolationLevel != null) {

   323       result = fExplicitTxIsolationLevel;

   324     }

   325     else {

   326       result = new Config().getSqlFetcherDefaultTxIsolationLevel(aDbName);

   327     }

   328     return result;

   329   }

   330   

   331   private void close(

   332     PreparedStatement aStatement, Connection aConnection

   333   ) throws DAOException {

   334     if ( fConnectionIsInternal ) {

   335       DbUtil.close(aStatement, aConnection);

   336     }

   337     else {

   338       //do not close the connection, since it is owned by the caller, and is

   339       //needed by other statements in the transaction

   340       DbUtil.close(aStatement);

   341     }

   342   }

   343   

   344   private Integer getEndIdx(Integer aStartIdx, Integer aPageSize){

   345     return new Integer( aStartIdx.intValue() + aPageSize.intValue() - 1);

   346   }

   347   

   348   private boolean isLimitingRows() {

   349     return fStartIdx != null;

   350   }

   351   

   352   private boolean isAfterStart(ResultSet aResultSet) throws SQLException {

   353     return aResultSet.getRow() >= fStartIdx.intValue();

   354   }

   355 

   356   private boolean isAfterEnd(ResultSet aResultSet) throws SQLException {

   357     return aResultSet.getRow() > fEndIdx.intValue();

   358   }

   359 }