hirondelle.web4j.database
Class DynamicCriteria

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

• sorting result sets dynamically
• filtering and sorting result sets dynamically, with many possible filter criteria

Items closely related to this class are :

• Db.search(Class, SqlId, DynamicCriteria, Object[]) - performs the SELECT using DynamicCriteria.
• ActionImpl.getOrderBy(RequestParameter, RequestParameter, String) - convenience method for constructing an ORDER BY clause from request parameters.
• the Report class.

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:

• if forces you to place static items in your .sql file (where they should be, since they are static)
• it allows this class to perform aggressive checking to ward off SQL injection attacks

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:

• this class ensures that all criteria it creates are dynamic, not static. Thus, even if the caller makes an egregious error, and mistakenly constructs a criterion by placing request data where a ? should actually be used, then that improper injection will be detected, and it will not execute.
• the output of this class is always used to first build a PreparedStatement. Then, user input from the request is passed to the ? placeholders in the usual way.

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