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