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<Id> 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 }