hirondelle.web4j.database
Class Db

Object
  hirondelle.web4j.database.Db

public final class Db
extends Object

Utility class for the most common database tasks.

This class allows many DAO methods to be implemented in one or two lines of simple code.

SQL Parameters

SQL statement parameters are passed to this class using an Object... sequence parameter. The objects in these arrays must be one of the classes supported by ConvertColumn. The number and order of these parameter objects must match the number and order of the '?' parameters in the underlying SQL statement.

For Id objects, the underlying column must be modeled as text, not a number. If the underlying column is numeric, then the caller must convert an Id into a numeric form using Id.asInteger() or Id.asLong().

Locale and TimeZone 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:

• just place the Locale and TimeZone 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.
• define code tables for Locale and TimeZone, to define the accepted values, and link the user preferences table to them, using a foreign key.

The second form is usually more robust, since it's normalized. However, when it is used, passing Locale and TimeZone objects directly to an INSERT 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 Db supports Locale and TimeZone becomes irrelevant, since you will translate them into an Id anyway.

Parsing Columns Into Objects

For operations involving a ResultSet, this class will always use the application's ConvertColumn implementation to convert columns into various building block objects.

In addition, it uses an ordering convention to map ResultSet columns to Model Object constructor arguments. See the package overview for more information on this important point.

Compound Objects

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 Compound Objects.

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.

For the simplest cases, this can be performed conveniently in a single step, using the fetchCompound and listCompound methods of this class. These methods process a ResultSet in a fundamentally different way : instead of translating a single row into a single Model Object, they can translate groups of 1..N rows into a single Model Object instead.

Here is an illustration. The target Model Object constructor has the form (for example):

 public UserRole (String aUserName, List<Id> aRoles)  {
   ...
 }
 

That is, the constructor takes a single List of Model Objects at the end of its list of arguments. Here, a List of Id objects appears at the end. The List can be a List of Model Objects, or a List of Base Objects supported by ConvertParam.

The underlying SELECT statement returns data across a 0..N relation, with data in the first N columns repeating the parent data, and with the remaining M columns containing the child data. For example:

 SELECT Name, Role FROM UserRole ORDER BY Role
 

which has a ResultSet of the form :

Name	Role
kenarnold	access-control
kenarnold	user-general
kenarnold	user-president
davidholmes	user-general

That is, the repeated parent data (Name) comes first and is attached to the parent, while the child data (Role) appears only in the final columns. In addition, changes to the value in the first column must indicate that a new parent has started.

If the above requirements are satisfied, then a List<UserRole> is built using listCompound(Class, Class, int, SqlId, Object[]), as in:

 Db.listCompound(UserRole.class, Id.class, 1, ROLES_LIST_SQL);
 

Method Summary

static Id	add(SqlId aSqlId, Object... aParams) INSERT operation which returns the database identifier of the added record.
static Object[]	addIdTo(Object[] aBaseParams, Id aId) Add an Id to a list of parameters already extracted from a Model Object.
static int	delete(SqlId aSqlId, Object... aParams) DELETE operation which takes parameters.
static int	edit(SqlId aSqlId, Object... aParams) INSERT, UPDATE, or DELETE operations which take parameters.
static <T> T	fetch(Class<T> aClass, SqlId aSqlId, Object... aParams) SELECT operation which returns a single Model Object.
static <T> T	fetchCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) SELECT operation which typically returns a single item with a 0..N relation.
static <T> T	fetchValue(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) SELECT operation which returns a single 'building block' value such as Integer, BigDecimal, and so on.
static void	initStandalone(Map<String,String> aSettings, List<String> aRawSql) Initialize the web4j database layer only.
static <T> List<T>	list(Class<T> aClass, SqlId aSqlId, Object... aParams) SELECT operation which returns 0..N Model Objects, one per row.
static <T> List<T>	listCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) SELECT operation which typically returns mutliple items item with a 0..N relation.
static <T> List<T>	listRange(Class<T> aClass, SqlId aSqlId, Integer aStartIndex, Integer aPageSize, Object... aParams) SELECT operation that returns a List of Model Objects "subsetted" to a particular range of rows.
static <T> List<T>	listValues(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) SELECT operation which returns a List of 'building block' values such as Integer, BigDecimal, and so on.
static <T> List<T>	search(Class<T> aClass, SqlId aSqlId, DynamicSql aSearchCriteria, Object... aParams) SELECT operation for listing the result of a user's search with the given DynamicSql and corresponding parameter values.

Methods inherited from class Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Detail

fetch

public static <T> T fetch(Class<T> aClass,
                          SqlId aSqlId,
                          Object... aParams)
               throws DAOException

SELECT operation which returns a single Model Object.

Parameters:

aClass - class of the returned Model Object.

aSqlId - identifies the underlying SQL statement.

aParams - parameters for the SQL statement.

Returns:

null if no record is found.

Throws:

DAOException

fetchValue

public static <T> T fetchValue(Class<T> aSupportedTargetClass,
                               SqlId aSqlId,
                               Object... aParams)
                    throws DAOException

SELECT operation which returns a single 'building block' value such as Integer, BigDecimal, and so on.

Parameters:

aSupportedTargetClass - class supported by the configured implementation of ConvertColumn.

aSqlId - identifies the underlying SQL statement.

aParams - parameters for the SQL statement.

Returns:

null if no record is found.

Throws:

DAOException

list

public static <T> List<T> list(Class<T> aClass,
                               SqlId aSqlId,
                               Object... aParams)
                    throws DAOException

SELECT operation which returns 0..N Model Objects, one per row.

Parameters:

aClass - class of the returned Model Objects.

aSqlId - identifies the underlying SQL statement.

aParams - parameters for the SQL statement.

Returns:

an unmodifiable List of Model Objects. The list may be empty.

Throws:

DAOException

listValues

public static <T> List<T> listValues(Class<T> aSupportedTargetClass,
                                     SqlId aSqlId,
                                     Object... aParams)
                          throws DAOException

SELECT operation which returns a List of 'building block' values such as Integer, BigDecimal, and so on.

Parameters:

aSupportedTargetClass - class supported by the configured implementation of ConvertColumn.

aSqlId - identifies the underlying SQL statement.

aParams - parameters for the SQL statement.

Returns:

an unmodifiable List of building block objects. The list may be empty.

Throws:

DAOException

listRange

public static <T> List<T> listRange(Class<T> aClass,
                                    SqlId aSqlId,
                                    Integer aStartIndex,
                                    Integer aPageSize,
                                    Object... aParams)
                         throws DAOException

SELECT operation that returns a List of Model Objects "subsetted" to a particular range of rows.

This method is intended for paging through long listings. When the underlying SELECT returns many pages of items, the records can be "subsetted" by calling this method.

See Pager.

Parameters:

aClass - class of the returned Model Objects.

aSqlId - identifies the underlying SQL statement.

aStartIndex - 1-based index indentifying the first row to be returned.

aPageSize - number of records to be returned.

aParams - parameters for the SQL statement.

Returns:

an unmodifiable List of Model Objects. The list may be empty.

Throws:

DAOException

search

public static <T> List<T> search(Class<T> aClass,
                                 SqlId aSqlId,
                                 DynamicSql aSearchCriteria,
                                 Object... aParams)
                      throws DAOException

SELECT operation for listing the result of a user's search with the given DynamicSql and corresponding parameter values.

This method is called only if the exact underlying criteria are not known beforehand, but are rather determined dynamically by user selections. See DynamicSql for more information.

Parameters:

aClass - class of the returned Model Objects.

aSqlId - identifies the underlying SQL statement.

aSearchCriteria - criteria for the given search, containing WHERE and ORDER BY clauses.

aParams - parameters for the SQL statement, corresponding to the given criteria.

Returns:

an unmodifiable List of Model Objects, corresponding to the input criteria. The list may be empty.

Throws:

DAOException

edit

public static int edit(SqlId aSqlId,
                       Object... aParams)
                throws DAOException,
                       DuplicateException

INSERT, UPDATE, or DELETE operations which take parameters.

Parameters:

aSqlId - identifies the underlying SQL statement.

aParams - parameters for the SQL statement.

Returns:

the number of records affected by this edit operation.

Throws:

DAOException

DuplicateException

add

public static Id add(SqlId aSqlId,
                     Object... aParams)
              throws DAOException,
                     DuplicateException

INSERT operation which returns the database identifier of the added record.

This operation is not supported by all databases. See Statement for more information.

Parameters:

aSqlId - identifies the underlying SQL statement.

aParams - parameters for the SQL statement.

Throws:

DAOException

DuplicateException

delete

public static int delete(SqlId aSqlId,
                         Object... aParams)
                  throws DAOException

DELETE operation which takes parameters.

Parameters:

aSqlId - identifies the underlying SQL statement.

aParams - identifies the item to be deleted. Often 1 or more Id objects.

Returns:

the number of deleted records.

Throws:

DAOException

fetchCompound

public static <T> T fetchCompound(Class<T> aClassParent,
                                  Class<?> aClassChild,
                                  int aNumTrailingColsForChildList,
                                  SqlId aSqlId,
                                  Object... aParams)
                       throws DAOException

SELECT operation which typically returns a single item with a 0..N relation.

The ResultSet is parsed into a single parent Model Object having a List of 0..N child Model Objects. See note on compound objects for more information.

Parameters:

aClassParent - class of the parent Model Object.

aClassChild - class of the child Model Object.

aNumTrailingColsForChildList - number of columns appearing at the end of the ResultSet which are passed to the child constructor.

aSqlId - identifies the underlying SQL statement.

aParams - parameters to the underlying SQL statement.

Throws:

DAOException

listCompound

public static <T> List<T> listCompound(Class<T> aClassParent,
                                       Class<?> aClassChild,
                                       int aNumTrailingColsForChildList,
                                       SqlId aSqlId,
                                       Object... aParams)
                            throws DAOException

SELECT operation which typically returns mutliple items item with a 0..N relation.

The ResultSet is parsed into a List of parent Model Objects, each having 0..N child Model Objects. See note on compound objects for more information.

Parameters:

aClassParent - class of the parent Model Object.

aClassChild - class of the child Model Object.

aNumTrailingColsForChildList - number of columns appearing at the end of the ResultSet which are passed to the child constructor.

aSqlId - identifies the underlying SQL statement.

aParams - parameters to the underlying SQL statement.

Throws:

DAOException

addIdTo

public static Object[] addIdTo(Object[] aBaseParams,
                               Id aId)

Add an Id to a list of parameters already extracted from a Model Object.

This method exists to avoid repetition in your DAOs regarding the parameters passed to add and change operations.

Take the following example :

  INSERT INTO Resto (Name, Location, Price, Comment) VALUES (?,?,?,?)
  UPDATE Resto SET Name=?, Location=?, Price=?, Comment=? WHERE Id=?
  

In this case, the parameters are exactly the same, and appear in the same order, except for the Id at the end of the UPDATE statement.

In such cases, this method can be used to simply append the Id to an already existing list of parameters.

Parameters:

aBaseParams - all parameters used in an INSERT statement

aId - the Id parameter to append to aBaseParams,

Returns:

parameters needed for a change operation

initStandalone

public static void initStandalone(Map<String,String> aSettings,
                                  List<String> aRawSql)
                           throws AppException

Initialize the web4j database layer only. This method is intended for using the web4j database layer outside of a servlet container (for example, in a command-line application).

The idea is that the services of the web4j data layer can be used in any application, not just web applications. Callers will also need to supply an implementation of ConnectionSource, in the usual way, by either making use of the standard package name and class name ('hirondelle.web4j.config.ConnectionSrc'), or by specifying the class name in the provided settings parameter. Callers that make use of the Reports class may also need to supply an implementation of DateConverter.

Parameters:

aSettings - correspond to the same name-value pairs defined in web.xml, and take the same values. Callers should pay particular attention to the settings related to the database. See the User Guide for more information.

aRawSql - each String in this list corresponds to the contents of a single .sql file.

Throws:

AppException