org.hdpagination.dataaccess.support.jdbc
Class JdbcQueryCallbackWrapper

java.lang.Object
  org.hdpagination.dataaccess.support.QueryBuilder
      org.hdpagination.dataaccess.support.jdbc.JdbcQueryCallbackWrapper

All Implemented Interfaces:

java.io.Serializable, QueryCallback, JdbcQueryCallback

public class JdbcQueryCallbackWrapper
extends QueryBuilder
implements JdbcQueryCallback

This class is used as a convenient wrapper class for JdbcQueryCallback. It simplifies the way to generate an instance of JdbcQueryCallback based on the searching criteria fields.

The way to use JdbcQueryCallbackWrapper usually follows the following steps:

(1) Call one of its static factory methods getInstance to get an initial instance of JdbcQueryCallbackWrapper.

(2) Call addRestriction methods from the instance.

(3) Call setStatementBeforeOrderBy method if necessary.

(4) Call setOrderBy method if you want to sort the search result.

A sample code is as follows:

JdbcQueryCallbackWrapper callback = JdbcQueryCallbackWrapper.newInstance("select productId,prodNo,price,madeIn,description from product p", ProductVO.class)
.addRestriction( Restrictions.eq("prodNo", crit.getProdNo()) )
.addRestriction( Restrictions.in("madeIn", crit.getMadeIn()) )
.addRestriction( Restrictions.contains("description", crit.getDescription()) )
.addRestriction( Restrictions.range("price", priceFrom, priceTo))
.setOrderBy("p.madeIn");

Behind the scene, the way JdbcQueryCallbackWrapper generates the full SQL query statement can be described as:

It uses the value of 'selectFrom' (the first argument from static factory methods newInstance) to construct an initial SQL statement.

It goes through all the restrictions (added by calling the methods addRestriction). For each restriction:

(1) It appends the keyword 'WHERE' or 'AND' to the SQL statement first. If it is the first restriction, 'WHERE' is added; otherwise 'AND' is added.

(2) Then it appends the restriction's clause statement (e.g. p.description LIKE ?) to the SQL statement.

If the property statemetnBeforeOrderBy is set, then append its value to the SQL statement.

If orderBy is set, then append the generated ORDER BY clause to the SQL statement.

Since:

1.3.1

Author:

Liangfeng Ren

See Also:

Serialized Form

Field Summary

Fields inherited from class org.hdpagination.dataaccess.support.QueryBuilder

asending, countStatement, orderBy, selectFrom

Constructor Summary

JdbcQueryCallbackWrapper(java.lang.String selectFrom)
Please note that the factory methods newInstance are encouraged to use to create an object of JdbcQueryCallbackWrapper rather than instantiating it directly.

Method Summary

JdbcQueryCallbackWrapper	addRestriction(Restriction restriction) Add restriction applied to the query SELECT statement.
JdbcQueryCallbackWrapper	addRestriction(java.lang.String clause) Add restriction applied to the query SELECT statement.
JdbcQueryCallbackWrapper	addRestriction(java.lang.String clause, java.util.List params) Add restriction applied to the query SELECT statement.
JdbcQueryCallbackWrapper	addRestriction(java.lang.String clause, java.lang.Object param) Add restriction applied to the query SELECT statement.
JdbcQueryCallbackWrapper	addRestriction(java.lang.String clause, java.lang.Object[] params) Add restriction applied to the query SELECT statement.
JdbcQueryCallbackWrapper	addRestriction(java.lang.String clause, java.lang.Object[] params, int[] sqlTypes) Add restriction applied to the query SELECT statement.
JdbcQueryCallbackWrapper	addRestriction(java.lang.String clause, java.lang.Object param, int sqlType) Add restriction applied to the query SELECT statement.
java.lang.String	getQueryStatement() Query statement(e.g.
static JdbcQueryCallbackWrapper	newInstance(java.lang.String selectFrom) As a convenient (or shortcut) factory method to get an initial JdbcQueryCallbackWrapper object.
static JdbcQueryCallbackWrapper	newInstance(java.lang.String selectFrom, java.lang.Class rowMappedClass) As a convenient (or shortcut) factory method to get an initial JdbcQueryCallbackWrapper object.
static JdbcQueryCallbackWrapper	newInstance(java.lang.String selectFrom, RowMapper rowMapper) As a convenient (or shortcut) factory method to get an initial JdbcQueryCallbackWrapper object.
java.lang.Object	processRow(java.sql.ResultSet rs) Implementations implement this method to fetch each row of data in the ResultSet and map the fetched data to an object of any type you wish and finally return it.
JdbcQueryCallbackWrapper	setOrderBy(java.lang.String orderBy) Set order by
JdbcQueryCallbackWrapper	setOrderBy(java.lang.String orderBy, boolean ascending) Set order by
void	setRowMapper(RowMapper rowMapper) Set the property (type of RowMapper) for mapping rows of a ResultSet on a per-row basis when query is executed.
JdbcQueryCallbackWrapper	setStatementBeforeOrderBy(java.lang.String statementBeforeOrderBy) Set the statement before "ORDER BY" clause and after "WHERE" clause.
void	setValues(java.sql.PreparedStatement ps) set values on a PreparedStatement provided by JdbcQueryTemplate

Methods inherited from class org.hdpagination.dataaccess.support.QueryBuilder

getCountRecordsQueryStatement, getQueryOrder, setCountRecordsQueryStatement, setQueryOrder

Methods inherited from class java.lang.Object

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

Methods inherited from interface org.hdpagination.core.QueryCallback

getCountRecordsQueryStatement, getQueryOrder, setQueryOrder

Constructor Detail

JdbcQueryCallbackWrapper

public JdbcQueryCallbackWrapper(java.lang.String selectFrom)

Please note that the factory methods newInstance are encouraged to use to create an object of JdbcQueryCallbackWrapper rather than instantiating it directly.

Parameters:

selectFrom - the "SELECT ... FROM .." statement for the query. Ideally, it should not contain the "WHERE" clause, otherwise when restrictions are added later, the full generated query statement will not be correct (it contains more than one "WHERE" key words). The methods addRestriction are used to specify the "WHERE" clause. It also should not contain the "ORDER BY" clause, the methods setOrderBy are for this purpose.

Method Detail

setStatementBeforeOrderBy

public JdbcQueryCallbackWrapper setStatementBeforeOrderBy(java.lang.String statementBeforeOrderBy)

Set the statement before "ORDER BY" clause and after "WHERE" clause. A good example of this is "GROUP BY" clause.

Parameters:

statementBeforeOrderBy -

Returns:

this, to enable chaining

setRowMapper

public void setRowMapper(RowMapper rowMapper)

Set the property (type of RowMapper) for mapping rows of a ResultSet on a per-row basis when query is executed.

Parameters:

rowMapper - object that will map one object per row

setOrderBy

public JdbcQueryCallbackWrapper setOrderBy(java.lang.String orderBy)

Set order by

Parameters:

orderBy - the column after the key word ORDER BY in 'ORDER BY' clause

Returns:

this, to enable chaining

setOrderBy

public JdbcQueryCallbackWrapper setOrderBy(java.lang.String orderBy,
                                           boolean ascending)

Set order by

Parameters:

orderBy - the column after the key word ORDER BY in 'ORDER BY' clause

ascending - if sorted in ascending order

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(Restriction restriction)

Add restriction applied to the query SELECT statement.

If the argument 'restriction' is not null, the restriction will be appended to the WHERE clause of the SELECT statement to filter the search result. Otherwise, the restriction will not be appended.

Typically, this method is used together with one of static factory methods from Restrictions. Restrictions provides some static factory methods to generate object of Restriction conditionally based on the parameter value.

A general rule for some of its simple factory methods (eq, gt, ge, lt, le, contains) is:

If the parameter is null or blank (if the type of parameter is java.lang.String), the method returns null. As a result,the restriction will not be added.

In a typical search scenario, only if the search criteria field is provided (e.g. its value is not null and empty), the restriction should be applied. This method particularly suits this scenario. You can avoid coding the annoying IF .. ELSE checking by doing this.

Parameters:

restriction - If the argument is null, the restriction is not applied to the query.

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(java.lang.String clause)

Add restriction applied to the query SELECT statement. The restriction will be appended to the WHERE clause of the SELECT statement to filter the search result.

Parameters:

clause - The WHERE clause fragment (after prefixing "WHERE" or "AND" sql keywords) will be appended to the SELECT statement. Please note that the SQL key words "WHERE" or "AND" should be excluded from this clause fragment. When generating the full query statement, the framework knows when to prefix "WHERE" or "AND". The clause fragment should not contain '?' IN parameter placeholder. This argument can not be null or blank.

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(java.lang.String clause,
                                               java.lang.Object param)

Add restriction applied to the query SELECT statement. The restriction will be appended to the WHERE clause of the SELECT statement to filter the search result.

Parameters:

clause - The WHERE clause fragment (after prefixing "WHERE" or "AND" sql keywords) will be appended to the SELECT statement. Please note that the SQL key words "WHERE" or "AND" should be excluded from this clause fragment. When generating the full query statement, the framework knows when to prefix "WHERE" or "AND". The clause fragment should contain ONE and ONLY ONE '?' IN parameter placeholder. This argument can not be null or blank.

param - parameter to bind to the query (leaving it to the java.sql.PreparedStatement to guess the corresponding SQL type); It is to bind to the '?' IN parameter placeholder in the clause argument. It should be a valid type to call the method java.sql.PreparedStatement.setObject(int parameterIndex, Object x) as the second argument. This argument can not be null.

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(java.lang.String clause,
                                               java.lang.Object param,
                                               int sqlType)

Add restriction applied to the query SELECT statement. The restriction will be appended to the WHERE clause of the SELECT statement to filter the search result.

Parameters:

clause - The WHERE clause fragment (after prefixing "WHERE" or "AND" sql keywords) will be appended to the SELECT statement. Please note that the SQL key words "WHERE" or "AND" should be excluded from this clause fragment. When generating the full query statement, the framework knows when to prefix "WHERE" or "AND". The clause fragment should contain ONE and ONLY ONE '?' IN parameter placeholder. This argument can not be null or blank.

param - the parameter value binding to the '?' IN parameter placeholder in the clause argument. It should be a valid type to call the method java.sql.PreparedStatement.setObject(int parameterIndex, Object x, int targetSqlType) as the second argument. This argument can not be null.

sqlType - SQL type of the binding parameter (constants from java.sql.Types)

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(java.lang.String clause,
                                               java.lang.Object[] params)

Add restriction applied to the query SELECT statement. The restriction will be appended to the WHERE clause of the SELECT statement to filter the search result.

Parameters:

clause - The WHERE clause fragment (after prefixing "WHERE" or "AND" sql keywords) will be appended to the SELECT statement. Please note that the SQL key words "WHERE" or "AND" should be excluded from this clause fragment. When generating the full query statement, the framework knows when to prefix "WHERE" or "AND". The clause fragment can contain multiple '?' IN parameter placeholders, and the number of placeholders must match the number of elements in the params argument. This argument can not be null or blank.

params - the parameter values binding to the '?' IN parameter placeholders in the clause argument (leaving it to the java.sql.PreparedStatement to guess the corresponding SQL type). Its elements should be a valid type to call the method java.sql.PreparedStatement.setObject(int parameterIndex, Object x) as the second argument. This argument can not be null and should contain at least one element.

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(java.lang.String clause,
                                               java.util.List params)

Add restriction applied to the query SELECT statement. The restriction will be appended to the WHERE clause of the SELECT statement to filter the search result.

Parameters:

clause - The WHERE clause fragment (after prefixing "WHERE" or "AND" sql keywords) will be appended to the SELECT statement. Please note that the SQL key words "WHERE" or "AND" should be excluded from this clause fragment. When generating the full query statement, the framework knows when to prefix "WHERE" or "AND". The clause fragment can contain multiple '?' IN parameter placeholders, and the number of placeholders must match the number of elements in the params argument. This argument can not be null or blank.

params - the parameter values binding to the '?' IN parameter placeholders in the clause argument (leaving it to the java.sql.PreparedStatement to guess the corresponding SQL type). Its elements should be a valid type to call the method java.sql.PreparedStatement.setObject(int parameterIndex, Object x) as the second argument. This argument can not be null and should contain at least one element.

Returns:

this, to enable chaining

addRestriction

public JdbcQueryCallbackWrapper addRestriction(java.lang.String clause,
                                               java.lang.Object[] params,
                                               int[] sqlTypes)

Add restriction applied to the query SELECT statement. The restriction will be appended to the WHERE clause of the SELECT statement to filter the search result.

Parameters:

clause - The WHERE clause fragment (after prefixing "WHERE" or "AND" sql keywords) will be appended to the SELECT statement. Please note that the SQL key words "WHERE" or "AND" should be excluded from this clause fragment. When generating the full query statement, the framework knows when to prefix "WHERE" or "AND". The clause fragment can contain multiple '?' IN parameter placeholders, and the number of placeholders must match the number of elements in the params argument. This argument can not be null or blank.

params - the parameter values binding to the '?' IN parameter placeholders in the clause argument. Its elements should be a valid type to call the method java.sql.PreparedStatement.setObject(int parameterIndex, Object x, int targetSqlType) as the second argument. This argument can not be null and should contain at least one element.

sqlTypes - SQL types of the binding parameters (constants from java.sql.Types) This argument can not be null and should contain at least one element.

Returns:

this, to enable chaining

getQueryStatement

public java.lang.String getQueryStatement()

Description copied from interface: QueryCallback

Query statement(e.g. sql for JDBC and queryString for Hibernate)

Specified by:

getQueryStatement in interface QueryCallback

Returns:

setValues

public void setValues(java.sql.PreparedStatement ps)
               throws java.sql.SQLException

Description copied from interface: JdbcQueryCallback

set values on a PreparedStatement provided by JdbcQueryTemplate

Specified by:

setValues in interface JdbcQueryCallback

Throws:

java.sql.SQLException

processRow

public java.lang.Object processRow(java.sql.ResultSet rs)
                            throws java.sql.SQLException

Description copied from interface: JdbcQueryCallback

Implementations implement this method to fetch each row of data in the ResultSet and map the fetched data to an object of any type you wish and finally return it. Thus it provides the way of adding the fetched data in the type of any class you prefer to the search result(java.util.List).

Specified by:

processRow in interface JdbcQueryCallback

Returns:

Throws:

java.sql.SQLException

newInstance

public static JdbcQueryCallbackWrapper newInstance(java.lang.String selectFrom)

As a convenient (or shortcut) factory method to get an initial JdbcQueryCallbackWrapper object. A default implementation ColumnMapRowMapper of RowMapper is used for mapping rows of a ResultSet on a per-row basis. As a result, the elements in the search result(java.util.List) will have the type of java.util.Map, representing all columns as key-value pairs: one entry for each column, with the column name (low case) as the key, column value as the value.

Please note that the factory methods are encouraged to use to create an object of JdbcQueryCallbackWrapper rather than instantiating it directly.

Parameters:

selectFrom - the "SELECT ... FROM .." statement for the query. Ideally, it should not contain the "WHERE" clause, otherwise when restrictions are added later, the full generated query statement will not be correct (it contains more than one "WHERE" key words). The methods addRestriction are used to specify the "WHERE" clause. It also should not contain the "ORDER BY" clause, the methods setOrderBy are for this purpose. This argument can not be null or empty.

Returns:

newInstance

public static JdbcQueryCallbackWrapper newInstance(java.lang.String selectFrom,
                                                   RowMapper rowMapper)

As a convenient (or shortcut) factory method to get an initial JdbcQueryCallbackWrapper object. A specified instance of RowMapper is used for mapping rows of a ResultSet on a per-row basis.

Please note that the factory methods are encouraged to use to create an object of JdbcQueryCallbackWrapper rather than instantiating it directly.

Parameters:

selectFrom - the "SELECT ... FROM .." statement for the query. Ideally, it should not contain the "WHERE" clause, otherwise when restrictions are added later, the full generated query statement will not be correct (it contains more than one "WHERE" key words). The methods addRestriction are used to specify the "WHERE" clause. It also should not contain the "ORDER BY" clause, the methods setOrderBy are for this purpose. This argument can not be null or empty.

rowMapper - An instance of RowMapper is used for mapping rows of a ResultSet on a per-row basis. To customise the rows mapping of a ResultSet, you only need to implement a class of RowMapper in your own way and pass an object of that class.

Returns:

newInstance

public static JdbcQueryCallbackWrapper newInstance(java.lang.String selectFrom,
                                                   java.lang.Class rowMappedClass)

As a convenient (or shortcut) factory method to get an initial JdbcQueryCallbackWrapper object. Internally, the implementation BeanPropertyRowMapper of RowMapper is used for mapping rows of a ResultSet on a per-row basis.

Briefly, column values are mapped based on matching the column name as obtained from result set metadata to public setters for the corresponding properties (of the bean with type of the specified class - the second argument 'rowMappedClass'). The names are matched either directly or by transforming a name separating the parts with underscores to the same name using "camel" case.

Please note that the factory methods are encouraged to use to create an object of JdbcQueryCallbackWrapper rather than instantiating it directly.

Also very importantly, because BeanPropertyRowMapper internally uses some classes available only since Spring framework 2.5.2. So this method can only be used if Spring framework 2.5.2 or later version is used.

Parameters:

selectFrom - the "SELECT ... FROM .." statement for the query. Ideally, it should not contain the "WHERE" clause, otherwise when restrictions are added later, the full generated query statement will not be correct (it contains more than one "WHERE" key words). The methods addRestriction are used to specify the "WHERE" clause. It also should not contain the "ORDER BY" clause, the methods setOrderBy are for this purpose. This argument can not be null or empty.

rowMappedClass - the class is used by the underlying RowMapper implementation BeanPropertyRowMapper to convert a row into a new instance of the specified mapped target class. This argument can not be null.

Returns: