001    package hirondelle.web4j.database;
002    
003    import hirondelle.web4j.BuildImpl;
004    import hirondelle.web4j.model.AppException;
005    import hirondelle.web4j.model.Id;
006    import hirondelle.web4j.readconfig.Config;
007    import hirondelle.web4j.readconfig.ConfigReader;
008    
009    import java.util.ArrayList;
010    import java.util.Collections;
011    import java.util.List;
012    import java.util.Map;
013    
014    /**
015     <span class="highlight">Utility class for the most common database tasks.</span>
016     
017     <P>This class allows many DAO methods to be implemented in one or two lines 
018     of simple code.
019    
020    <h3><a name="Parameters"></a>SQL Parameters</h3>
021     SQL statement parameters are passed to this class using an <tt>Object...</tt> 
022     sequence parameter. The objects in these arrays must be one of the classes 
023     supported by {@link hirondelle.web4j.database.ConvertColumn}.
024      
025     <span class="highlight">The number and order of these parameter objects must match 
026     the number and order of the '<tt>?</tt>' parameters in the underlying SQL 
027     statement</span>.
028    
029     <P>For <tt>Id</tt> objects, the underlying column must be modeled as text, not a number. If
030     the underlying column is numeric, then the caller must convert an {@link Id} into a numeric form
031     using {@link Id#asInteger} or {@link Id#asLong}. 
032    
033     <P><tt>Locale</tt> and <tt>TimeZone</tt> objects represent a bit of a special case.
034     Take for example a table storing user preferences, which stores locale and time zone. 
035     How would you store that information? There are 2 basic styles: 
036     <ul>
037      <li>just place the <tt>Locale</tt> and <tt>TimeZone</tt> identifiers (en_CA, America/Montreal) in their own 
038      columns, as text; this style will repeat the identifiers, and will not be 'normalized data', in database terminology.
039      <li>define code tables for <tt>Locale</tt> and <tt>TimeZone</tt>, to define the accepted values, and link the user preferences
040      table to them, using a foreign key. 
041     </ul> 
042     
043     <P>The second form is usually more robust, since it's normalized. However, when it is used, passing <tt>Locale</tt> and 
044     <tt>TimeZone</tt> objects directly to an <tt>INSERT</tt> statement, for example, using this class, is not appropriate.
045     Instead, you'll need to treat them as any other code table, and translate the en_CA (for example) into 
046     a corresponding foreign key identifier. In this case, the fact that <tt>Db</tt> supports  <tt>Locale</tt> and <tt>TimeZone</tt>
047     becomes irrelevant, since you will translate them into an <tt>Id</tt> anyway.
048      
049    <h3><a name="ConvertColumn"></a>Parsing Columns Into Objects</h3>
050     For operations involving a <tt>ResultSet</tt>, this class will always use the application's 
051     {@link ConvertColumn} implementation to convert columns into various building block objects. 
052     
053     <P>In addition, it uses an ordering convention to map <tt>ResultSet</tt> columns to Model 
054     Object constructor arguments. See the package overview for more information on this important 
055     point. 
056    
057     <h3><a name="CompoundObjects"></a>Compound Objects</h3>
058     Occasionally, it is desirable to present a large amount of summary information to the user on a single page.
059     In this case, an application needs a single large Model Object (a parent) containing collections 
060     of other Model Objects (children). Here, these are called <em>Compound Objects</em>.   
061     
062     <P>Constructing an arbitrary Compound Object can always be performed in multiple steps: first 
063     fetch the children, and then construct the parent, by passing the children to 
064     the parent's constructor.
065     
066     <P><em>For the simplest cases</em>, this can be performed conveniently in a single step, using 
067     the <tt>fetchCompound</tt> and <tt>listCompound</tt> methods of this class. These 
068     methods process a <tt>ResultSet</tt> in a fundamentally different way : instead of translating a 
069     <em>single</em> row into a single Model Object, they can translate <em>groups</em> of 1..N rows into 
070     a single Model Object instead.
071       
072     <P>Here is an illustration. The target Model Object constructor has the form (for example):
073     <PRE>
074     public UserRole (String aUserName, List&lt;Id&gt; aRoles)  {
075       ...
076     }
077     </PRE>
078     <span class="highlight">That is, the constructor takes a <em>single</em> {@link List} of Model Objects at 
079     the <em>end</em> of its list of arguments.</span> Here, a <tt>List</tt> of {@link Id} objects appears at the end.
080     The <tt>List</tt> can be a <tt>List</tt> of Model Objects, or a <tt>List</tt> of Base Objects supported by 
081     {@link hirondelle.web4j.model.ConvertParam}. 
082      
083     <P><span class="highlight">The underlying SELECT statement returns data across a <tt>0..N</tt> relation, with data 
084     in the first N columns repeating the parent data, and with the remaining M columns containing the child data</span>. 
085     For example:
086     <PRE>
087     SELECT Name, Role FROM UserRole ORDER BY Role
088     </PRE>
089     which has a <tt>ResultSet</tt> of the form :
090     <table border=1 cellpadding=3 cellspacing=1>
091      <tr><th>Name</th><th>Role</th></tr>
092      <tr><td>kenarnold</td><td>access-control</td></tr>
093      <tr><td>kenarnold</td><td>user-general</td></tr>
094      <tr><td>kenarnold</td><td>user-president</td></tr>
095      <tr><td>davidholmes</td><td>user-general</td></tr>
096     </table>
097     
098     <P>That is, the repeated parent data (Name) comes first and is attached to the parent, while the 
099     child data (Role) <em>appears only in the final columns</em>. <span class="highlight">In addition, changes to the 
100     value in the <em>first</em> column must indicate that a new parent has started.</span>
101    
102     <P>If the above requirements are satisfied, then a {@code List<UserRole>} is built using 
103     {@link #listCompound(Class, Class, int, SqlId, Object[])}, as in: 
104     <PRE>
105     Db.listCompound(UserRole.class, Id.class, 1, ROLES_LIST_SQL);
106     </PRE>
107    */ 
108    public final class Db {
109    
110      /**
111       <tt>SELECT</tt> operation which returns a single Model Object. 
112        
113       @param aClass class of the returned Model Object.
114       @param aSqlId identifies the underlying SQL statement.
115       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
116       @return <tt>null</tt> if no record is found.
117      */
118      public static <T> T fetch(Class<T> aClass, SqlId aSqlId, Object... aParams) throws DAOException {
119        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
120        ModelFromRow<T> builder = new ModelFromRow<T>(aClass);
121        return fetcher.fetchObject(builder);
122      }
123    
124      /**
125       <tt>SELECT</tt> operation which returns a single 'building block' value such as <tt>Integer</tt>, <tt>BigDecimal</tt>, and so on.
126      
127       @param aSupportedTargetClass class supported by the configured 
128       implementation of {@link ConvertColumn}.
129       @param aSqlId identifies the underlying SQL statement.
130       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
131       @return <tt>null</tt> if no record is found.
132      */
133      public static <T> T fetchValue(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) throws DAOException {
134        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
135        ModelBuilder<T> builder = new ValueFromRow<T>(aSupportedTargetClass); 
136        return fetcher.fetchObject(builder);
137      }
138      
139      /**
140       <tt>SELECT</tt> operation which returns <tt>0..N</tt> Model Objects, one per row.  
141        
142       @param aClass class of the returned Model Objects.
143       @param aSqlId identifies the underlying SQL statement.
144       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
145       @return an unmodifiable {@link List} of Model Objects. The list may be empty.
146      */
147      public static <T> List<T> list(Class<T> aClass, SqlId aSqlId, Object... aParams) throws DAOException {
148        List<T> result = new ArrayList<T>();
149        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
150        ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
151        fetcher.fetchObjects(builder, result);
152        return Collections.unmodifiableList(result);
153      }
154      
155      /**
156       <tt>SELECT</tt> operation which returns a <tt>List</tt> of 'building block' values such 
157       as <tt>Integer</tt>, <tt>BigDecimal</tt>, and so on.
158      
159       @param aSupportedTargetClass class supported by the configured 
160       implementation of {@link ConvertColumn}.
161       @param aSqlId identifies the underlying SQL statement.
162       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
163       @return an unmodifiable {@link List} of building block objects. The list may be empty.
164      */
165      public static <T> List<T> listValues(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) throws DAOException {
166        List<T> result = new ArrayList<T>();
167        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
168        ModelBuilder<T> builder = new ValueFromRow<T>(aSupportedTargetClass); 
169        fetcher.fetchObjects(builder, result);
170        return Collections.unmodifiableList(result);
171      }
172      
173      /**
174       <tt>SELECT</tt> operation that returns a <tt>List</tt> of Model Objects "subsetted" to 
175       a particular range of rows.
176       
177       <P>This method is intended for paging through long listings. When the underlying 
178       <tt>SELECT</tt> returns many pages of items, the records can be "subsetted" by 
179       calling this method.
180       
181       <P>See {@link hirondelle.web4j.ui.tag.Pager}.
182       @param aClass class of the returned Model Objects.
183       @param aSqlId identifies the underlying SQL statement.
184       @param aStartIndex 1-based index indentifying the first row to be returned.
185       @param aPageSize number of records to be returned.
186       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
187       @return an unmodifiable {@link List} of Model Objects. The list may be empty.
188      */
189      public static <T> List<T> listRange(Class<T> aClass, SqlId aSqlId, Integer aStartIndex, Integer aPageSize, Object... aParams) throws DAOException  {
190        List<T> result = new ArrayList<T>();
191        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
192        fetcher.limitRowsToRange(aStartIndex, aPageSize);
193        ModelBuilder<T> builder = new ModelFromRow<T>(aClass); 
194        fetcher.fetchObjects(builder, result);
195        return Collections.unmodifiableList(result);
196      }
197    
198      /**
199       <tt>SELECT</tt> operation for listing the result of a user's search with the given {@link DynamicSql} 
200       and corresponding parameter values.
201       
202       <P>This method is called only if the exact underlying criteria are not known beforehand, but are rather
203       determined <em>dynamically</em> by user selections. See {@link DynamicSql} for more information.
204        
205       @param aClass class of the returned Model Objects.
206       @param aSqlId identifies the underlying SQL statement.
207       @param aSearchCriteria criteria for the given search, containing <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses.
208       @param aParams <a href="#Parameters">parameters</a> for the SQL statement, corresponding to the given criteria.
209       @return an unmodifiable {@link List} of Model Objects, corresponding to the input criteria. The list may be empty.
210      */
211      public static <T> List<T> search(Class<T> aClass, SqlId aSqlId, DynamicSql aSearchCriteria, Object... aParams) throws DAOException {
212        List<T> result = new ArrayList<T>();
213        SqlFetcher fetcher = SqlFetcher.forSearch(aSqlId, aSearchCriteria, aParams);
214        ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
215        fetcher.fetchObjects(builder, result);
216        return Collections.unmodifiableList(result);
217      }
218       
219      /**
220       <tt>INSERT</tt>, <tt>UPDATE</tt>, or <tt>DELETE</tt> operations which take parameters.
221        
222       @param aSqlId identifies the underlying SQL statement.
223       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
224       @return the number of records affected by this edit operation.
225      */
226      public static int edit(SqlId aSqlId, Object... aParams) throws DAOException, DuplicateException {
227        SqlEditor change = SqlEditor.forSingleOp(aSqlId, aParams);
228        return change.editDatabase();
229      }
230      
231      /**
232       <tt>INSERT</tt> operation which returns the database identifier of the added record.
233      
234       <P>This operation is not supported by all databases. See 
235       {@link java.sql.Statement} for more information.
236       
237       @param aSqlId identifies the underlying SQL statement.
238       @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
239      */
240      public static Id add(SqlId aSqlId, Object... aParams) throws DAOException, DuplicateException  {
241        SqlEditor add = SqlEditor.forSingleOp(aSqlId, aParams);
242        return new Id(add.addRecord());
243      }
244    
245      /**
246       <tt>DELETE</tt> operation which takes parameters.
247        
248       @param aSqlId identifies the underlying SQL statement.
249       @param aParams identifies the item to be deleted. Often 1 or more {@link Id} objects.
250       @return the number of deleted records.
251      */
252      public static int delete(SqlId aSqlId, Object... aParams) throws DAOException {
253        SqlEditor delete = SqlEditor.forSingleOp(aSqlId, aParams);
254        return delete.editDatabase();
255      }
256      
257      /**
258       <tt>SELECT</tt> operation which typically returns a single item with a <tt>0..N</tt> relation. 
259        
260       <P>The <tt>ResultSet</tt> is parsed into a single parent Model Object having a <tt>List</tt> of 
261       <tt>0..N</tt> child Model Objects. 
262       See note on <a href="#CompundObjects">compound objects</a> for more information.
263         
264       @param aClassParent class of the parent Model Object.
265       @param aClassChild class of the child Model Object.
266       @param aNumTrailingColsForChildList number of columns appearing at the end of the <tt>ResultSet</tt> which 
267       are passed to the <em>child</em> constructor.
268       @param aSqlId identifies the underlying SQL statement.
269       @param aParams <a href="#Parameters">parameters</a> to the underlying SQL statement.
270      */
271      public static <T> T fetchCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) throws DAOException {
272        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
273        ModelBuilder<T> builder = new ModelFromRow<T>(aClassParent, aClassChild, aNumTrailingColsForChildList);
274        return fetcher.fetchObject(builder);
275      }
276    
277      /**
278       <tt>SELECT</tt> operation which typically returns mutliple items item with a <tt>0..N</tt> relation. 
279        
280       <P>The <tt>ResultSet</tt> is parsed into a <tt>List</tt> of parent Model Objects, each having <tt>0..N</tt> 
281       child Model Objects. See note on <a href="#CompoundObjects">compound objects</a> for more information.
282         
283       @param aClassParent class of the parent Model Object.
284       @param aClassChild class of the child Model Object.
285       @param aNumTrailingColsForChildList number of columns appearing at the end of the <tt>ResultSet</tt> which 
286       are passed to the <em>child</em> constructor.
287       @param aSqlId identifies the underlying SQL statement.
288       @param aParams <a href="#Parameters">parameters</a> to the underlying SQL statement.
289      */
290      public static <T> List<T> listCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) throws DAOException {
291        List<T> result = new ArrayList<T>();
292        SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
293        ModelBuilder<T> builder = new ModelFromRow<T>(aClassParent, aClassChild, aNumTrailingColsForChildList);
294        fetcher.fetchObjects(builder, result);
295        return result;
296      }
297    
298      /**
299       Add an <tt>Id</tt> to a list of parameters already extracted from a Model Object. 
300       
301       <P>This method exists to avoid repetition in your DAOs regarding the parameters
302       passed to <em>add</em> and <em>change</em> operations. 
303       
304       <P>Take the following example :
305       <PRE>
306      INSERT INTO Resto (Name, Location, Price, Comment) VALUES (?,?,?,?)
307      UPDATE Resto SET Name=?, Location=?, Price=?, Comment=? WHERE Id=?
308      </PRE>
309       In this case, the parameters are exactly the same, and appear in the same order, 
310       <em>except</em> for the <tt>Id</tt> at the end of the <tt>UPDATE</tt> statement.
311       
312       <P>In such cases, this method can be used to simply append the <tt>Id</tt> to an 
313       already existing list of parameters.
314       
315       @param aBaseParams all parameters used in an <tt>INSERT</tt> statement
316       @param aId the <tt>Id</tt> parameter to append to <tt>aBaseParams</tt>,
317       @return parameters needed for a <em>change</em> operation 
318      */
319      public static Object[] addIdTo(Object[] aBaseParams, Id aId){
320        List<Object> result = new ArrayList<Object>();
321        for(Object thing: aBaseParams){
322          result.add(thing);
323        }
324        result.add(aId);
325        return result.toArray(); 
326      }
327      
328      /**
329       Initialize the web4j database layer only.
330       This method is intended for using the web4j database layer outside of a servlet container (for example, 
331       in a command-line application).
332        
333       <P>The idea is that the services of the web4j data layer can be used in any application, not just web applications.
334       Callers will also need to supply an implementation of {@link ConnectionSource}, in the usual way, by either 
335       making use of the standard package name and class name ('hirondelle.web4j.config.ConnectionSrc'), or by specifying 
336       the class name in the provided settings parameter. Callers that make use of the <tt>Reports</tt> class may also need to 
337       supply an implementation of {@link hirondelle.web4j.request.DateConverter}. 
338       
339       @param aSettings correspond to the same name-value pairs defined in <tt>web.xml</tt>, and take the same values.
340       Callers should pay particular attention to the settings related to the database. 
341       See the <a href='http://www.web4j.com/UserGuide.jsp'>User Guide</a> for more information.
342       @param aRawSql each String in this list corresponds to the contents of a single <tt>.sql</tt> file. 
343      */
344      public static void initStandalone(Map<String, String> aSettings, List<String> aRawSql) throws AppException{
345        Config.init(aSettings); 
346        BuildImpl.initDatabaseLayer(aSettings);  //not all interfaces init-ed
347        Map<String, String> processedSql = ConfigReader.processRawSql(aRawSql);
348        SqlStatement.initSqlStatementsManually(processedSql); 
349      }
350    
351      // PRIVATE 
352      
353      private Db() {
354        //prevent construction by the caller
355      }
356    }