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 following :

• String
• Integer
• Boolean
• Date
• BigDecimal
• Float
• Long
• Id
• SafeText (the parameter value is taken from SafeText.getRawString())

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().

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 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 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 int	delete(SqlId aSqlId, Id aId) DELETE operation which takes a single Id parameter.
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 <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, DynamicCriteria aSearchCriteria, Object... aParams) SELECT operation for listing the result of a user's search with the given DynamicCriteria 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,
                                 DynamicCriteria aSearchCriteria,
                                 Object... aParams)
                      throws DAOException

SELECT operation for listing the result of a user's search with the given DynamicCriteria 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 DynamicCriteria 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,
                         Id aId)
                  throws DAOException

DELETE operation which takes a single Id parameter.

Parameters:

aSqlId - identifies the underlying SQL statement.

aId - identifies the item to be deleted.

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