package hirondelle.web4j.database;
import java.util.*;
import hirondelle.web4j.model.Id;
/**
<span class="highlight">Utility class for the most common database tasks.</span>
<P>This class allows many DAO methods to be implemented in one or two lines
of simple code.
<h3><a name="Parameters"></a>SQL Parameters</h3>
SQL statement parameters are passed to this class using an <tt>Object...</tt>
sequence parameter. The objects in these arrays must be one of the classes
supported by {@link hirondelle.web4j.database.ConvertColumn}.
<span class="highlight">The number and order of these parameter objects must match
the number and order of the '<tt>?</tt>' parameters in the underlying SQL
statement</span>.
<P>For <tt>Id</tt> objects, the underlying column must be modeled as text, not a number. If
the underlying column is numeric, then the caller must convert an {@link Id} into a numeric form
using {@link Id#asInteger} or {@link Id#asLong}.
<P><tt>Locale</tt> and <tt>TimeZone</tt> objects represent a bit of a special case.
Take for example a table storing user preferences, which stores locale and time zone.
How would you store that information? There are 2 basic styles:
<ul>
<li>just place the <tt>Locale</tt> and <tt>TimeZone</tt> identifiers (en_CA, America/Montreal) in their own
columns, as text; this style will repeat the identifiers, and will not be 'normalized data', in database terminology.
<li>define code tables for <tt>Locale</tt> and <tt>TimeZone</tt>, to define the accepted values, and link the user preferences
table to them, using a foreign key.
</ul>
<P>The second form is usually more robust, since it's normalized. However, when it is used, passing <tt>Locale</tt> and
<tt>TimeZone</tt> objects directly to an <tt>INSERT</tt> statement, for example, using this class, is not appropriate.
Instead, you'll need to treat them as any other code table, and translate the en_CA (for example) into
a corresponding foreign key identifier. In this case, the fact that <tt>Db</tt> supports <tt>Locale</tt> and <tt>TimeZone</tt>
becomes irrelevant, since you will translate them into an <tt>Id</tt> anyway.
<h3><a name="ConvertColumn"></a>Parsing Columns Into Objects</h3>
For operations involving a <tt>ResultSet</tt>, this class will always use the application's
{@link ConvertColumn} implementation to convert columns into various building block objects.
<P>In addition, it uses an ordering convention to map <tt>ResultSet</tt> columns to Model
Object constructor arguments. See the package overview for more information on this important
point.
<h3><a name="CompoundObjects"></a>Compound Objects</h3>
Occasionally, it is desirable to present a large amount of summary information to the user on a single page.
In this case, an application needs a single large Model Object (a parent) containing collections
of other Model Objects (children). Here, these are called <em>Compound Objects</em>.
<P>Constructing an arbitrary Compound Object can always be performed in multiple steps: first
fetch the children, and then construct the parent, by passing the children to
the parent's constructor.
<P><em>For the simplest cases</em>, this can be performed conveniently in a single step, using
the <tt>fetchCompound</tt> and <tt>listCompound</tt> methods of this class. These
methods process a <tt>ResultSet</tt> in a fundamentally different way : instead of translating a
<em>single</em> row into a single Model Object, they can translate <em>groups</em> of 1..N rows into
a single Model Object instead.
<P>Here is an illustration. The target Model Object constructor has the form (for example):
<PRE>
public UserRole (String aUserName, List<Id> aRoles) {
...
}
</PRE>
<span class="highlight">That is, the constructor takes a <em>single</em> {@link List} of Model Objects at
the <em>end</em> of its list of arguments.</span> Here, a <tt>List</tt> of {@link Id} objects appears at the end.
The <tt>List</tt> can be a <tt>List</tt> of Model Objects, or a <tt>List</tt> of Base Objects supported by
{@link hirondelle.web4j.model.ConvertParam}.
<P><span class="highlight">The underlying SELECT statement returns data across a <tt>0..N</tt> relation, with data
in the first N columns repeating the parent data, and with the remaining M columns containing the child data</span>.
For example:
<PRE>
SELECT Name, Role FROM UserRole ORDER BY Role
</PRE>
which has a <tt>ResultSet</tt> of the form :
<table border=1 cellpadding=3 cellspacing=1>
<tr><th>Name</th><th>Role</th></tr>
<tr><td>kenarnold</td><td>access-control</td></tr>
<tr><td>kenarnold</td><td>user-general</td></tr>
<tr><td>kenarnold</td><td>user-president</td></tr>
<tr><td>davidholmes</td><td>user-general</td></tr>
</table>
<P>That is, the repeated parent data (Name) comes first and is attached to the parent, while the
child data (Role) <em>appears only in the final columns</em>. <span class="highlight">In addition, changes to the
value in the <em>first</em> column must indicate that a new parent has started.</span>
<P>If the above requirements are satisfied, then a {@code List<UserRole>} is built using
{@link #listCompound(Class, Class, int, SqlId, Object[])}, as in:
<PRE>
Db.listCompound(UserRole.class, Id.class, 1, ROLES_LIST_SQL);
</PRE>
*/
public final class Db {
/**
<tt>SELECT</tt> operation which returns a single Model Object.
@param aClass class of the returned Model Object.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
@return <tt>null</tt> if no record is found.
*/
public static <T> T fetch(Class<T> aClass, SqlId aSqlId, Object... aParams) throws DAOException {
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
ModelFromRow<T> builder = new ModelFromRow<T>(aClass);
return fetcher.fetchObject(builder);
}
/**
<tt>SELECT</tt> operation which returns a single 'building block' value such as <tt>Integer</tt>, <tt>BigDecimal</tt>, and so on.
@param aSupportedTargetClass class supported by the configured
implementation of {@link ConvertColumn}.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
@return <tt>null</tt> if no record is found.
*/
public static <T> T fetchValue(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) throws DAOException {
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
ModelBuilder<T> builder = new ValueFromRow<T>(aSupportedTargetClass);
return fetcher.fetchObject(builder);
}
/**
<tt>SELECT</tt> operation which returns <tt>0..N</tt> Model Objects, one per row.
@param aClass class of the returned Model Objects.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
@return an unmodifiable {@link List} of Model Objects. The list may be empty.
*/
public static <T> List<T> list(Class<T> aClass, SqlId aSqlId, Object... aParams) throws DAOException {
List<T> result = new ArrayList<T>();
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
fetcher.fetchObjects(builder, result);
return Collections.unmodifiableList(result);
}
/**
<tt>SELECT</tt> operation which returns a <tt>List</tt> of 'building block' values such
as <tt>Integer</tt>, <tt>BigDecimal</tt>, and so on.
@param aSupportedTargetClass class supported by the configured
implementation of {@link ConvertColumn}.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
@return an unmodifiable {@link List} of building block objects. The list may be empty.
*/
public static <T> List<T> listValues(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) throws DAOException {
List<T> result = new ArrayList<T>();
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
ModelBuilder<T> builder = new ValueFromRow<T>(aSupportedTargetClass);
fetcher.fetchObjects(builder, result);
return Collections.unmodifiableList(result);
}
/**
<tt>SELECT</tt> operation that returns a <tt>List</tt> of Model Objects "subsetted" to
a particular range of rows.
<P>This method is intended for paging through long listings. When the underlying
<tt>SELECT</tt> returns many pages of items, the records can be "subsetted" by
calling this method.
<P>See {@link hirondelle.web4j.ui.tag.Pager}.
@param aClass class of the returned Model Objects.
@param aSqlId identifies the underlying SQL statement.
@param aStartIndex 1-based index indentifying the first row to be returned.
@param aPageSize number of records to be returned.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
@return an unmodifiable {@link List} of Model Objects. The list may be empty.
*/
public static <T> List<T> listRange(Class<T> aClass, SqlId aSqlId, Integer aStartIndex, Integer aPageSize, Object... aParams) throws DAOException {
List<T> result = new ArrayList<T>();
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
fetcher.limitRowsToRange(aStartIndex, aPageSize);
ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
fetcher.fetchObjects(builder, result);
return Collections.unmodifiableList(result);
}
/**
<tt>SELECT</tt> operation for listing the result of a user's search with the given {@link DynamicCriteria}
and corresponding parameter values.
<P>This method is called only if the exact underlying criteria are not known beforehand, but are rather
determined <em>dynamically</em> by user selections. See {@link DynamicCriteria} for more information.
@param aClass class of the returned Model Objects.
@param aSqlId identifies the underlying SQL statement.
@param aSearchCriteria criteria for the given search, containing <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement, corresponding to the given criteria.
@return an unmodifiable {@link List} of Model Objects, corresponding to the input criteria. The list may be empty.
*/
public static <T> List<T> search(Class<T> aClass, SqlId aSqlId, DynamicCriteria aSearchCriteria, Object... aParams) throws DAOException {
List<T> result = new ArrayList<T>();
SqlFetcher fetcher = SqlFetcher.forSearch(aSqlId, aSearchCriteria, aParams);
ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
fetcher.fetchObjects(builder, result);
return Collections.unmodifiableList(result);
}
/**
<tt>INSERT</tt>, <tt>UPDATE</tt>, or <tt>DELETE</tt> operations which take parameters.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
@return the number of records affected by this edit operation.
*/
public static int edit(SqlId aSqlId, Object... aParams) throws DAOException, DuplicateException {
SqlEditor change = SqlEditor.forSingleOp(aSqlId, aParams);
return change.editDatabase();
}
/**
<tt>INSERT</tt> operation which returns the database identifier of the added record.
<P>This operation is not supported by all databases. See
{@link java.sql.Statement} for more information.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> for the SQL statement.
*/
public static Id add(SqlId aSqlId, Object... aParams) throws DAOException, DuplicateException {
SqlEditor add = SqlEditor.forSingleOp(aSqlId, aParams);
return new Id(add.addRecord());
}
/**
<tt>DELETE</tt> operation which takes parameters.
@param aSqlId identifies the underlying SQL statement.
@param aParams identifies the item to be deleted. Often 1 or more {@link Id} objects.
@return the number of deleted records.
*/
public static int delete(SqlId aSqlId, Object... aParams) throws DAOException {
SqlEditor delete = SqlEditor.forSingleOp(aSqlId, aParams);
return delete.editDatabase();
}
/**
<tt>SELECT</tt> operation which typically returns a single item with a <tt>0..N</tt> relation.
<P>The <tt>ResultSet</tt> is parsed into a single parent Model Object having a <tt>List</tt> of
<tt>0..N</tt> child Model Objects.
See note on <a href="#CompundObjects">compound objects</a> for more information.
@param aClassParent class of the parent Model Object.
@param aClassChild class of the child Model Object.
@param aNumTrailingColsForChildList number of columns appearing at the end of the <tt>ResultSet</tt> which
are passed to the <em>child</em> constructor.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> to the underlying SQL statement.
*/
public static <T> T fetchCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) throws DAOException {
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
ModelBuilder<T> builder = new ModelFromRow<T>(aClassParent, aClassChild, aNumTrailingColsForChildList);
return fetcher.fetchObject(builder);
}
/**
<tt>SELECT</tt> operation which typically returns mutliple items item with a <tt>0..N</tt> relation.
<P>The <tt>ResultSet</tt> is parsed into a <tt>List</tt> of parent Model Objects, each having <tt>0..N</tt>
child Model Objects. See note on <a href="#CompoundObjects">compound objects</a> for more information.
@param aClassParent class of the parent Model Object.
@param aClassChild class of the child Model Object.
@param aNumTrailingColsForChildList number of columns appearing at the end of the <tt>ResultSet</tt> which
are passed to the <em>child</em> constructor.
@param aSqlId identifies the underlying SQL statement.
@param aParams <a href="#Parameters">parameters</a> to the underlying SQL statement.
*/
public static <T> List<T> listCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) throws DAOException {
List<T> result = new ArrayList<T>();
SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
ModelBuilder<T> builder = new ModelFromRow<T>(aClassParent, aClassChild, aNumTrailingColsForChildList);
fetcher.fetchObjects(builder, result);
return result;
}
/**
Add an <tt>Id</tt> to a list of parameters already extracted from a Model Object.
<P>This method exists to avoid repetition in your DAOs regarding the parameters
passed to <em>add</em> and <em>change</em> operations.
<P>Take the following example :
<PRE>
INSERT INTO Resto (Name, Location, Price, Comment) VALUES (?,?,?,?)
UPDATE Resto SET Name=?, Location=?, Price=?, Comment=? WHERE Id=?
</PRE>
In this case, the parameters are exactly the same, and appear in the same order,
<em>except</em> for the <tt>Id</tt> at the end of the <tt>UPDATE</tt> statement.
<P>In such cases, this method can be used to simply append the <tt>Id</tt> to an
already existing list of parameters.
@param aBaseParams all parameters used in an <tt>INSERT</tt> statement
@param aId the <tt>Id</tt> parameter to append to <tt>aBaseParams</tt>,
@return parameters needed for a <em>change</em> operation
*/
public static Object[] addIdTo(Object[] aBaseParams, Id aId){
List<Object> result = new ArrayList<Object>();
for(Object thing: aBaseParams){
result.add(thing);
}
result.add(aId);
return result.toArray();
}
// PRIVATE
private Db() {
//prevent construction by the caller
}
}