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 }