hirondelle.web4j.database
Class DynamicSql

Object
  hirondelle.web4j.database.DynamicSql

public class DynamicSql
extends Object

Dynamic SQL statement created in code. The SQL statement can be either:

• a complete statement
• a fragment of a statement, to be appended to the end of a static base statement

This class is intended for two use cases:

• creating a statement entirely in code
• creating WHERE and ORDER BY clauses dynamically, by building sort and filter criteria from user input

The creation of SQL in code is dangerous. You have to exercise care that your code will not be subject to SQL Injection attacks. If you don't know what such attacks are all about, then you are in danger of creating very large, dangerous security flaws in your application.

The main means of protecting yourself from these attacks is to ensure that the sql strings you pass to this class never contain data that has come directly from the user, in an unescaped form. You achieve this by parameterizing user input, and proceeding in 2 steps:

1. create an SQL statement that always uses a ? placeholder for data entered by the user
2. pass all user-entered data as parameters to the above statement

The above corresponds to the correct use of a PreparedStatement. After you have built your dynamic SQL, you will usually pass it to Db.search(Class, SqlId, DynamicSql, Object[]) to retrieve the data.

Entries in .sql Files

The SQL string you pass to this class is always appended (using toString()) to a (possibly-empty) base SQL statement already defined (as usual), in your .sql file. That entry can take several forms. The criteria on the entry are:

• it can be precompiled by WEB4J upon startup, if desired.
• it contains only static elements of the final SQL statement

It's important to note that the static base SQL can be completely empty. For example, the entry in your .sql file can look something like this:

MY_DYNAMIC_REPORT {
    -- this sql is generated in code
}

As you can see, there's only a comment here; there's no real SQL. In this case, you will need to build the entire SQL statement in code. (Even though the above entry is empty, it's still necessary, since it's where you specify any non-default target database name. It also ensures that the same mechanism web4j applies to processing SqlId objects will remain in effect, which is useful.)

You are encouraged to implement joins between tables using the JOIN syntax. The alternative is to implement joins using expressions in the WHERE clause. This usually isn't desirable, since it mixes up two distinct items - joins and actual criteria. Using JOIN allows these items to remain separate and distinct.

See Also

Other items closely related to this class are :

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

Constants

The WHERE, AND, and other constants are included in this class as a simple convenience. Note that each value includes a leading a trailing space, to avoid trivial spacing errors.

This class is non-final, and can be overridden, if desired. The reason is that some applications may wish to try to validate that the SQL passed to this class has been properly parameterized.

Field Summary

static String	AND Value - " AND ", convenience value for building a WHERE clause.
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 DynamicSql	NONE Represents the absence of any criteria.
static String	OR Value - " OR ", convenience value for building a WHERE clause.
static String	ORDER_BY Value - " ORDER BY ", convenience value for building an ORDER BY clause.
static String	WHERE Value - " WHERE ", convenience value for building a WHERE clause.

Constructor Summary

DynamicSql(String aSql)
Constructor.

DynamicSql(StringBuilder aSql)
Convenience constructor, forwards to DynamicSql(String).

Method Summary

String

toString() Return the String passed to the constructor, trimmed.

Methods inherited from class Object

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

Field Detail

WHERE

public static final String WHERE

Value - " WHERE ", convenience value for building a WHERE clause.

See Also:

Constant Field Values

AND

public static final String AND

Value - " AND ", convenience value for building a WHERE clause.

See Also:

Constant Field Values

OR

public static final String OR

Value - " OR ", convenience value for building a WHERE clause.

See Also:

Constant Field Values

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

NONE

public static final DynamicSql NONE

Represents the absence of any criteria. The value of this item is simply null.

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

Constructor Detail

DynamicSql

public DynamicSql(String aSql)

Constructor.

This constructor will slightly modify the given parameter: it will trim it, and prepend a new line to the result.

Parameters:

aSql - must have content; it will be trimmed by this method.

DynamicSql

public DynamicSql(StringBuilder aSql)

Convenience constructor, forwards to DynamicSql(String).

Method Detail

toString

public final String toString()

Return the String passed to the constructor, trimmed.

The returned value is appended by the framework to an existing (possibly empty) entry in an .sql file.

Overrides:

toString in class Object