Version 3.10.0

hirondelle.web4j.database
Class DynamicCriteria

Object
  extended by hirondelle.web4j.database.DynamicCriteria

public final class DynamicCriteria
extends Object

Dynamic WHERE and ORDER BY clauses for SELECT operations.

This class is used to dynamically create WHERE and ORDER BY clauses, which are then appended to an SQL statement defined (as usual), in an .sql file. Specifically, the return value of toString() is appended. The framework then uses the resulting text to construct a PreparedStatement internally.

The ORDER BY clause is required by this class, but the WHERE clause is optional. This is to support cases where you are only interested in changing the sort order of a result, but not in dynamically changing criteria.

There are two common use cases for this class :

Items closely related to this class are :

Distinguishing static versus dynamic criteria in WHERE clause

A WHERE clause can hold either static or dynamic criteria. The static criteria are fixed, while the dynamic criteria always accept one or more parameters. All static criteria must appear in the underlying .sql file, while all dynamic criteria are added by this class.

This class always assumes that the WHERE predicates it creates represent dynamic criteria that require parameters. This has two benefits:

When possible, you should use JOIN clauses to remove some static items from the WHERE clause altogether.

Dynamic Sort Only

Allowing the end user to sort columns dynamically is a common requirement. In this specific use case, the base SQL statement in the .sql file has the form
SELECT a,b,c 
FROM d
[ORDER BY added here]
or
SELECT a,b,c 
FROM d
WHERE e=f [static criteria only]
[ORDER BY added here]
That is, the base SQL statement has no ORDER BY clause. The ORDER BY clause is added by this class at the end, in the location indicated above in italics. If the base SQL statement contains a WHERE clause, then the WHERE clause must be both fully formed and static, in the sense of having no dynamic parameters. (For the case of dynamic WHERE clauses, see below.)

Example of typical code in an Action which creates the ORDER BY clause dynamically from request parameters :

//Declare request params related to sorting
public static final RequestParameter SORT_ON = RequestParameter.withRegexCheck("SortOn", "(1|2|3)");
public static final RequestParameter ORDER = RequestParameter.withRegexCheck("Order", "(ASC|DESC)");
//Default ORDER BY, used if the above params are not present
private static final String DEFAULT = "  ORDER BY 2 DESC";
...
//Build the criteria using above request parameters, in the body of an Action method 
DynamicCriteria customSort = new DynamicCriteria(getOrderBy(SORT_ON, ORDER, DEFAULT));
The customSort object is then passed to Db.search(Class, SqlId, DynamicCriteria, Object[]).

Dynamic Search and Sort

A search operation is the user's attempt to find a specific piece of information. Whereas a simple report or listing usually has either no criteria, or a few relatively well-defined criteria, a search can often have a much wider variety of possible criteria. For example, a search for a person might use first name, last name, account number, phone number, address, and so on. Those criteria may be combined in many different ways.

If a search has relatively simple criteria, then it can usually be implemented using one (or several) ordinary statements placed in an .sql file. However, sometimes the number of possible combinations of different criteria prohibits the enumeration of all possible SQL statements in an .sql file. This class exists to assist in such cases, where search criteria are more complex.

In this specific use case, the base SQL statement in the .sql file has the form

SELECT a,b,c 
FROM d
WHERE 
[dynamic WHERE predicates added here]
[ORDER BY added here]
or
SELECT a,b,c 
FROM d
WHERE
 e=f  [static criteria only]
[dynamic WHERE predicates added here]
[ORDER BY added here]
Note that in the first case, the WHERE appears in the .sql file to dangle at the end, with nothing following it. That's OK, since this class will complete the statement.

Example of building criteria dynamically :

DynamicCriteria criteria = new DynamicCriteria("ORDER BY Name");
criteria.addWhere("Name Like ?");
criteria.addWhere("Price >= ? AND Price <= ?");
criteria.addWhere("Salary <= ? OR Salary >= ?"); 
These criteria are AND-ed together in the final WHERE clause.

Items Appearing After ORDER BY

Some databases allow items to appear after the ORDER BY clause. In MySQL, for example, the following style limits the number of returned records:
 
 SELECT Name, Price 
 FROM Resto
 ORDER BY Name 
 LIMIT 100
 
If any items after ORDER BY are desired, they may be included in a DynamicCriteria by simply appending them to the the ORDER BY clause itself.

SQL Injection Attacks

When constructing SQL statements dynamically in code, one must always exercise care to guard against SQL Injection attacks. This class is not vulnerable to such attacks from the end user, for these reasons:


Field Summary
static String ASC
          Value - " ASC ", convenience value for building an ORDER BY clause.
static String DESC
          Value - " DESC ", convenience value for building an ORDER BY clause.
static DynamicCriteria NONE
          Represents the absence of any criteria.
static String ORDER_BY
          Value - "ORDER BY ", convenience value for building an ORDER BY clause.
 
Constructor Summary
DynamicCriteria(String aOrderByClause)
          Constructor taking only an ORDER BY clause.
DynamicCriteria(String aWherePredicates, String aOrderByClause)
          Constructor taking complete WHERE and ORDER BY clauses.
 
Method Summary
 void addWhere(String aWherePredicate)
          Add a new dynamic criterion to the WHERE clause.
 boolean equals(Object aThat)
           
 int hashCode()
           
 String toString()
          Return the dynamic WHERE predicates (if any) followed by the ORDER BY clause, separated by a newline.
 
Methods inherited from class Object
clone, finalize, getClass, notify, notifyAll, wait, wait, wait
 

Field Detail

NONE

public static DynamicCriteria NONE
Represents the absence of any criteria. The value of this item is simply null.

If a method allows a null DynamicCriteria object to indicate the absence of any criteria, then it is recommended that this reference be used instead of null.


ORDER_BY

public static final String ORDER_BY
Value - "ORDER BY ", convenience value for building an ORDER BY clause.

See Also:
Constant Field Values

ASC

public static final String ASC
Value - " ASC ", convenience value for building an ORDER BY clause.

See Also:
Constant Field Values

DESC

public static final String DESC
Value - " DESC ", convenience value for building an ORDER BY clause.

See Also:
Constant Field Values
Constructor Detail

DynamicCriteria

public DynamicCriteria(String aWherePredicates,
                       String aOrderByClause)
                throws ModelCtorException
Constructor taking complete WHERE and ORDER BY clauses.

This constructor is meant for cases where AND-ing of the various WHERE criteria is not desired. If possible, one should use the other constructor instead, to ease the burden on the caller for defining logic regarding AND-ing together various criteria.

Example :

 DynamicCriteria criteria = DynamicCriteria("Name Like ?", "ORDER BY Name DESC, Price");
 
Note that the text 'WHERE' doesn't appear here. Instead, it must be attached to the underlying SQL statement in the .sql file.

Parameters:
aWherePredicates - (required), complete and valid set of WHERE predicates, without the initial 'WHERE ', and contains only dynamic criteria taking parameters. The 'WHERE' text must be placed in the .sql file. In the usual way, the '?' placeholders will be replaced by the framework with user input data, using an internal PreparedStatement. May contain more than one criterion (and often does).
aOrderByClause - (required) complete and valid ORDER BY clause, starts with 'ORDER BY', minimum 10 characters. May have other items appended - usually a 'LIMIT', as in 'ORDER BY Price LIMIT 100'.
Throws:
ModelCtorException

DynamicCriteria

public DynamicCriteria(String aOrderByClause)
                throws ModelCtorException
Constructor taking only an ORDER BY clause.

This is the recommended constructor, intended for the common case of building a WHERE clause using multiple items AND-ed together. Such a style likely represents the majority of user search operations.

This constructor is also used when you are interested solely in dynamically changing sort order, and not in dynamic WHERE criteria.

After calling this constructor, you can add dynamic WHERE criteria taking parameters by calling addWhere(String).

Example :

DynamicCriteria criteria = new DynamicCriteria("ORDER BY Name");
criteria.addWhere("Name Like ?");
criteria.addWhere("Price >= ? AND Price <= ?");
criteria.addWhere("Salary <= ? OR Salary >= ?"); 
 

Parameters:
aOrderByClause - (required) complete and valid ORDER BY clause, starts with 'ORDER BY', minimum 10 characters. May have other items appended - usually a 'LIMIT', as in 'ORDER BY Price LIMIT 100'.
Throws:
ModelCtorException
Method Detail

addWhere

public void addWhere(String aWherePredicate)
Add a new dynamic criterion to the WHERE clause.

Each criterion is added in a kind of 'stand-alone' manner, without regard to the possible presence or absence of any other criteria. For example,

 DynamicCriteria criteria = new DynamicCriteria("ORDER BY Name");
 criteria.addWhere("Name Like ?");
 criteria.addWhere("Price >= ? AND Price <= ?");
 criteria.addWhere("Salary <= ? OR Salary >= ?"); 
 

The toString() method will always place an AND between criteria added with this method. (never an OR or NOT). For the example above, the result of toString is :

 WHERE
 (Name Like ?) AND
 (Price >= ? AND Price <= ?) AND
 (Salary <= ? OR Salary >= ?)
 ORDER BY Name 

This method can only be called when DynamicCriteria(String) was called to construct this object.

Parameters:
aWherePredicate - part of a WHERE clause, taking dynamic parameters for all data. Cannot start with 'WHERE', which must appear in the .sql file itself.

toString

public String toString()
Return the dynamic WHERE predicates (if any) followed by the ORDER BY clause, separated by a newline.

Here, no provision is made for any clauses appearing between the two, such as GROUP BY and HAVING. (Those items usually appear only in summary reports, and are not used in typical search operations.)

If the WHERE predicates were passed directly to the constructor, then they are calculated.

See addWhere(String) for an example return value.

Overrides:
toString in class Object

equals

public boolean equals(Object aThat)
Overrides:
equals in class Object

hashCode

public int hashCode()
Overrides:
hashCode in class Object

Version 3.10.0

Copyright Hirondelle Systems. Published September 20, 2008 - User Guide - All Docs.