org.araneaframework.backend.list.helper
Class BaseListSqlHelper

java.lang.Object
  org.araneaframework.backend.list.helper.BaseListSqlHelper

Direct Known Subclasses:

ListSqlHelper

public abstract class BaseListSqlHelper

extends Object

This class provides an SQL based implementation of the list. It takes care of the filtering, ordering and returning data to the web components. Implementations should override abstract methods noted in those methods.

Note, that all operations on items are made on the list of "processed", that is ordered and filtered items.

Since:

1.1

Author:

Jevgeni Kabanov (ekabanov at araneaframework dot org), Rein Raudjärv

Nested Class Summary

class	BaseListSqlHelper.BeanResultReader Resultset reader that uses beanToResultSetMapping in ListSqlHelper to construct a given type of Bean list.
class	BaseListSqlHelper.CountSqlCallback The total count query callback.
class	BaseListSqlHelper.ItemRangeSqlCallback The itme range query callback that returns List of items.
static class	BaseListSqlHelper.ListItemsDataCallback The item range and total count querites callback that returns ListItemsData object.

Field Summary

protected static Long	DEFAULT_RANGE_START
protected DataSource	ds
protected Fields	fields
protected Expression	filterExpr
protected SqlExpression	filterSqlExpr
protected Long	itemRangeCount
protected Long	itemRangeStart
protected NamingStrategy	namingStrategy
protected ComparatorExpression	orderExpr
protected SqlExpression	orderSqlExpr
protected ResultSetColumnReader	resultSetColumnReader
protected ValueConverter	valueConverter
protected VariableResolver	variableResolver

Constructor Summary

BaseListSqlHelper()
Creates ListSqlHelper without initializing any fields.

BaseListSqlHelper(DataSource dataSource)
Creates ListSqlHelper and provides it with the DataSource.

BaseListSqlHelper(DataSource dataSource, ListQuery query)
Creates ListSqlHelper initializing the appropriate fields and providing it with the DataSource.

BaseListSqlHelper(ListQuery query)
Creates ListSqlHelper initializing the appropriate fields.

Method Summary

abstract void	addNullParam(int valueType) Adds a NULL PreparedStatement parameter for later setting.
abstract void	addStatementParam(Object param) Adds a PreparedStatement parameter for later setting.
abstract void	addStatementParams(List params) Adds PreparedStatement parameters for later setting.
ResultReader	createBeanResultReader(Class itemClass) Returns Bean ResultSet reader.
protected VariableResolver	createExpressionBuilderResolver() Creates the VariableResolver for SqlExpressionBuilder that converts Variable names to their Database Field names according to the naming strategy.
protected StandardExpressionToSqlExprBuilder	createFilterSqlExpressionBuilder() Creates new filtering SQL Expression builder.
protected StandardCompExprToSqlExprBuilder	createOrderSqlExpressionBuilder() Creates new ordering SQL Expression builder.
protected static RuntimeException	createQueryFailedException(String QueryString, List queryParams, SQLException nestedException) Returns query failed Exception that contains query String and params.
ListItemsData	execute(Class itemClass) Executes SQL queries that should retrieve 1) the total count of items in the list and 2) a range of items from the list ListSqlHelper's BeanResultReader is used to convert the ResultSet into a List.
Object	execute(ConnectionCallback action) Execute a JDBC data access operation, implemented as callback action working on a JDBC Connection.
ListItemsData	execute(ResultReader reader) Executes SQL queries that should retrieve 1) the total count of items in the list and 2) a range of items from the list Provided ResultReader is used to convert the ResultSet into a List.
Long	executeCountSql() Executes a SQL query that should retrieve the total count of items in the list.
List	executeItemRangeSql(Class itemClass) Executes a SQL query that should retrieve a range of items from the list.
List	executeItemRangeSql(ResultReader reader) Executes a SQL query that should retrieve a range of items from the list.
ConnectionCallback	getCountSqlCallback() Returns the total count query callback.
protected abstract SqlStatement	getCountSqlStatement() Returns the total count SQL query String and parameters.
String	getDatabaseFields() Returns the database fields list seperated by commas, which can be used in "SELECT" clause.
String	getDatabaseFilter() Returns the filter database condition, which can be used in "WHERE" clause.
List	getDatabaseFilterParams() Returns the List of parameters that should be set in the PreparedStatement that belong to the filter database conditions.
String	getDatabaseFilterWith(String prefix, String suffix) Returns the database filter query with prefix added before and suffix after it if the query is not empty.
String	getDatabaseOrder() Returns the order database representation, which can be used in "ORDER BY" clause.
List	getDatabaseOrderParams() Returns the List of parameters that should be set in the PreparedStatement that belong to the order database representation.
String	getDatabaseOrderWith(String prefix, String suffix) Returns the database order query with prefix added before and suffix after it if the query is not empty.
Fields	getFields()
protected SqlExpression	getFieldsSqlExpression() Returns the fields SqlExpression, which can be used in "SELECT" clause.
protected SqlExpression	getFilterSqlExpression() Returns the filter SqlExpression, which can be used in "WHERE" clause.
ConnectionCallback	getItemRangeSqlCallback(ResultReader reader) Returns the item range query callback.
ConnectionCallback	getListItemsDataCallback(ResultReader reader) Returns the total count and item ragne queries callback.
NamingStrategy	getNamingStrategy()
protected SqlExpression	getOrderSqlExpression() Returns the order SqlExpression, which can be used in "ORDER BY" clause.
protected abstract SqlStatement	getRangeSqlStatement() Returns the itme range SQL query String and parameters.
ResultSetColumnReader	getResultSetColumnReader()
ValueConverter	getValueConverter()
protected void	init()
abstract void	setCountSqlQuery(String countSqlQuery) Sets the SQL query used to count the items in the database.
void	setDataSource(DataSource ds) Stores the DataSource.
void	setFields(Fields fields)
void	setFilterExpression(Expression filterExpr) Sets the filter expression saving it for later automatic SQL query creation.
void	setItemRangeCount(Long itemRangeCount) Sets the count of items in the range.
void	setItemRangeStart(Long itemRangeStart) Sets the (0-based) starting index of the item range.
void	setListQuery(ListQuery query) Sets the starting index and count of items in the range and filtering and ordering expressions.
void	setNamingStrategy(NamingStrategy namingStrategy)
void	setOrderExpression(ComparatorExpression orderExpr) Sets the order expression saving it for later automatic SQL query creation.
void	setResultSetColumnReader(ResultSetColumnReader resultSetColumnReader)
void	setSimpleSqlQuery(String fromSql) Sets the SQL query (with arguments) that will be used to retrieve the item range from the list and count the items.
void	setSimpleSqlQuery(String fromSql, String customWhereSql, Object[] customWhereArgs) Sets the SQL query (with arguments) that will be used to retrieve the item range from the list and count the items.
void	setSimpleSqlQuery(String fromSql, String customWhereSql, Object[] customWhereArgs, String customOrderbySql, Object[] customOrderbyArgs) Sets the SQL query (with arguments) that will be used to retrieve the item range from the list and count the items.
abstract void	setSqlQuery(String sqlQuery) Sets the SQL query that will be used to retrieve the item range from the list and count the items.
void	setValueConverter(ValueConverter valueConverter)

Methods inherited from class java.lang.Object

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

Field Detail

DEFAULT_RANGE_START

protected static final Long DEFAULT_RANGE_START

fields

protected Fields fields

namingStrategy

protected NamingStrategy namingStrategy

valueConverter

protected ValueConverter valueConverter

resultSetColumnReader

protected ResultSetColumnReader resultSetColumnReader

filterExpr

protected Expression filterExpr

orderExpr

protected ComparatorExpression orderExpr

variableResolver

protected VariableResolver variableResolver

filterSqlExpr

protected SqlExpression filterSqlExpr

orderSqlExpr

protected SqlExpression orderSqlExpr

itemRangeStart

protected Long itemRangeStart

itemRangeCount

protected Long itemRangeCount

ds

protected DataSource ds

Constructor Detail

BaseListSqlHelper

public BaseListSqlHelper()

Creates ListSqlHelper without initializing any fields.

BaseListSqlHelper

public BaseListSqlHelper(DataSource dataSource)

Creates ListSqlHelper and provides it with the DataSource.

BaseListSqlHelper

public BaseListSqlHelper(ListQuery query)

Creates ListSqlHelper initializing the appropriate fields.

BaseListSqlHelper

public BaseListSqlHelper(DataSource dataSource,
                         ListQuery query)

Creates ListSqlHelper initializing the appropriate fields and providing it with the DataSource.

Method Detail

init

protected void init()

setListQuery

public void setListQuery(ListQuery query)

Sets the starting index and count of items in the range and filtering and ordering expressions.

setOrderExpression

public void setOrderExpression(ComparatorExpression orderExpr)

Sets the order expression saving it for later automatic SQL query creation.

See Also:

getDatabaseOrder(), getDatabaseOrderWith(String, String), getDatabaseOrderParams()

setFilterExpression

public void setFilterExpression(Expression filterExpr)

Sets the filter expression saving it for later automatic SQL query creation.

See Also:

getDatabaseFilter(), getDatabaseFilterWith(String, String), getDatabaseFilterParams()

setItemRangeStart

public void setItemRangeStart(Long itemRangeStart)

Sets the (0-based) starting index of the item range.

setItemRangeCount

public void setItemRangeCount(Long itemRangeCount)

Sets the count of items in the range.

getFields

public Fields getFields()

setFields

public void setFields(Fields fields)

getNamingStrategy

public NamingStrategy getNamingStrategy()

setNamingStrategy

public void setNamingStrategy(NamingStrategy namingStrategy)

setValueConverter

public void setValueConverter(ValueConverter valueConverter)

getValueConverter

public ValueConverter getValueConverter()

setResultSetColumnReader

public void setResultSetColumnReader(ResultSetColumnReader resultSetColumnReader)

getResultSetColumnReader

public ResultSetColumnReader getResultSetColumnReader()

getFieldsSqlExpression

protected SqlExpression getFieldsSqlExpression()

Returns the fields SqlExpression, which can be used in "SELECT" clause.

Returns:

the fields SqlExpression, which can be used in "SELECT" clause.

getOrderSqlExpression

protected SqlExpression getOrderSqlExpression()

Returns the order SqlExpression, which can be used in "ORDER BY" clause.

Returns:

the order SqlExpression, which can be used in "ORDER BY" clause.

getFilterSqlExpression

protected SqlExpression getFilterSqlExpression()

Returns the filter SqlExpression, which can be used in "WHERE" clause.

Returns:

the filter SqlExpression, which can be used in "WHERE" clause.

createOrderSqlExpressionBuilder

protected StandardCompExprToSqlExprBuilder createOrderSqlExpressionBuilder()

Creates new ordering SQL Expression builder.

createFilterSqlExpressionBuilder

protected StandardExpressionToSqlExprBuilder createFilterSqlExpressionBuilder()

Creates new filtering SQL Expression builder.

getDatabaseFields

public String getDatabaseFields()

Returns the database fields list seperated by commas, which can be used in "SELECT" clause.

Returns:

the database fields list seperated by commas, which can be used in "SELECT" clause.

getDatabaseFilter

public String getDatabaseFilter()

Returns the filter database condition, which can be used in "WHERE" clause.

Returns:

the filter database condition, which can be used in "WHERE" clause.

See Also:

getDatabaseFilterWith(String, String), getDatabaseFilterParams()

getDatabaseFilterWith

public String getDatabaseFilterWith(String prefix,
                                    String suffix)

Returns the database filter query with prefix added before and suffix after it if the query is not empty.

Parameters:

prefix - Prefix added before the expression.

suffix - Suffix added after the expression.

Returns:

the database filter query with prefix added before and suffix after it if the query is not empty.

See Also:

getDatabaseFilter(), getDatabaseFilterParams()

getDatabaseFilterParams

public List getDatabaseFilterParams()

Returns the List of parameters that should be set in the PreparedStatement that belong to the filter database conditions.

Returns:

the List of parameters that should be set in the PreparedStatement that belong to the filter database conditions.

See Also:

getDatabaseFilter(), getDatabaseFilterWith(String, String)

getDatabaseOrder

public String getDatabaseOrder()

Returns the order database representation, which can be used in "ORDER BY" clause.

Returns:

the order database representation, which can be used in "ORDER BY" clause.

See Also:

getDatabaseOrderWith(String, String), getDatabaseOrderParams()

getDatabaseOrderWith

public String getDatabaseOrderWith(String prefix,
                                   String suffix)

Returns the database order query with prefix added before and suffix after it if the query is not empty.

Parameters:

prefix - Prefix added before the expression.

suffix - Suffix added after the expression.

Returns:

the database order query with prefix added before and suffix after it if the query is not empty.

See Also:

getDatabaseOrder(), getDatabaseOrderParams()

getDatabaseOrderParams

public List getDatabaseOrderParams()

Returns the List of parameters that should be set in the PreparedStatement that belong to the order database representation.

Returns:

the List of parameters that should be set in the PreparedStatement that belong to the order database representation.

See Also:

getDatabaseOrder(), getDatabaseOrderWith(String, String)

setSimpleSqlQuery

public void setSimpleSqlQuery(String fromSql)

Sets the SQL query (with arguments) that will be used to retrieve the item range from the list and count the items.

ListQuery filter and order conditions are used automatically.

To use additional custom filter (and order) conditions, use setSimpleSqlQuery(String, String, Object[]) or setSimpleSqlQuery(String, String, Object[], String, Object[]) method. To use more complex query, use setSqlQuery(String) method.

The constrcuted SQL query format is following (LQ = ListQuery):
SELECT (fromSql) [WHERE (LQ filter conditions)] [ORDER BY (LQ order conditions)]

Query arguments are automatically added in the appropriate order.

Parameters:

fromSql - FROM clause String.

setSimpleSqlQuery

public void setSimpleSqlQuery(String fromSql,
                              String customWhereSql,
                              Object[] customWhereArgs)

Sets the SQL query (with arguments) that will be used to retrieve the item range from the list and count the items.

ListQuery filter and order conditions are used automatically and they must not be added to this metohd's arguments. This method's Where arguments are only for additional conditions that are not contained in ListQuery already.

In simpler cases, use setSimpleSqlQuery(String) method. To use also custom order by conditions, use setSimpleSqlQuery(String, String, Object[], String, Object[]) method. To use more complex query, use setSqlQuery(String) method.

The constrcuted SQL query format is following (LQ = ListQuery):
SELECT (fromSql) [WHERE (customWhereSql) AND (LQ filter conditions)] [ORDER BY (customOrderbySql), (LQ order conditions)]

Query arguments are automatically added in the appropriate order.

Parameters:

fromSql - FROM clause String.

customWhereSql - custom WHERE clause String.

customWhereArgs - custom WHERE clause arguments.

setSimpleSqlQuery

public void setSimpleSqlQuery(String fromSql,
                              String customWhereSql,
                              Object[] customWhereArgs,
                              String customOrderbySql,
                              Object[] customOrderbyArgs)

Sets the SQL query (with arguments) that will be used to retrieve the item range from the list and count the items.

ListQuery filter and order conditions are used automatically and they must not be added to this metohd's arguments. This method's Where and Order by arguments are only for additional conditions that are not contained in ListQuery already.

In simpler cases, use setSimpleSqlQuery(String) or setSimpleSqlQuery(String, String, Object[]) method. To use more complex query, use setSqlQuery(String) method.

The constrcuted SQL query format is following (LQ = ListQuery):
SELECT (fromSql) [WHERE (customWhereSql) AND (LQ filter conditions)] [ORDER BY (customOrderbySql), (LQ order conditions)]

Query arguments are automatically added in the appropriate order.

Parameters:

fromSql - FROM clause String.

customWhereSql - custom WHERE clause String.

customWhereArgs - custom WHERE clause arguments.

customOrderbySql - custom ORDER BY clause String.

customOrderbyArgs - custom ORDER BY clause arguments.

setSqlQuery

public abstract void setSqlQuery(String sqlQuery)

Sets the SQL query that will be used to retrieve the item range from the list and count the items. SQL query must start with SELECT. All query arguments must be added additionally.

ListQuery filter and order conditions are not added automatically. To add them, use getDatabaseFilter* and getDatabaseOrder* methods.

For simpler cases, use one of the setSimpleSqlQuery methods instead.

Parameters:

sqlQuery - the SQL query that will be used to retrieve the item range from the list and count the items.

setCountSqlQuery

public abstract void setCountSqlQuery(String countSqlQuery)

Sets the SQL query used to count the items in the database. SQL query must start with SELECT.

By default, total items count and items range queries are constructed automatically based on the original query. This method should only be used, if it can considerably boost the perfomacne of count query.

Parameters:

countSqlQuery - the SQL query used to count the items in the database.

addNullParam

public abstract void addNullParam(int valueType)

Adds a NULL PreparedStatement parameter for later setting.

This method should not be used with one of the setSimpleSqlQuery methods.

Parameters:

valueType - the type of the NULL value.

addStatementParam

public abstract void addStatementParam(Object param)

Adds a PreparedStatement parameter for later setting.

This method should not be used with one of the setSimpleSqlQuery methods.

Parameters:

param - a PreparedStatement parameter.

addStatementParams

public abstract void addStatementParams(List params)

Adds PreparedStatement parameters for later setting.

This method should not be used with one of the setSimpleSqlQuery methods.

Parameters:

params - PreparedStatement parameters.

getCountSqlStatement

protected abstract SqlStatement getCountSqlStatement()

Returns the total count SQL query String and parameters.

getRangeSqlStatement

protected abstract SqlStatement getRangeSqlStatement()

Returns the itme range SQL query String and parameters.

setDataSource

public void setDataSource(DataSource ds)

Stores the DataSource.

execute

public Object execute(ConnectionCallback action)

Execute a JDBC data access operation, implemented as callback action working on a JDBC Connection. The stored DataSource is used to provide JDBC connection for the action. The connection is always closed after the action. This method is used by all other execute methods in ListSqlHelper. To override getting the connection, you have to use one of the ConnectionCallback returning methods and use your own implementation to execute it.

Parameters:

action - callback object that specifies the action.

Returns:

a result object returned by the action, or null.

execute

public ListItemsData execute(ResultReader reader)

Executes SQL queries that should retrieve 1) the total count of items in the list and 2) a range of items from the list Provided ResultReader is used to convert the ResultSet into a List. The stored DataSource is used to provide JDBC connection for the action. The connection will be closed automatically.

Parameters:

reader - ResultSet reader.

Returns:

ListItemsData containing the item range and total count.

execute

public ListItemsData execute(Class itemClass)

Executes SQL queries that should retrieve 1) the total count of items in the list and 2) a range of items from the list ListSqlHelper's BeanResultReader is used to convert the ResultSet into a List. The stored DataSource is used to provide JDBC connection for the action. The connection will be closed automatically.

Parameters:

itemClass - Bean class.

Returns:

ListItemsData containing the item range and total count.

executeCountSql

public Long executeCountSql()

Executes a SQL query that should retrieve the total count of items in the list. The stored DataSource is used to provide JDBC connection for the action. The connection will be closed automatically.

Returns:

the total count of items in the list.

executeItemRangeSql

public List executeItemRangeSql(ResultReader reader)

Executes a SQL query that should retrieve a range of items from the list. Provided ResultReader is used to convert the ResultSet into a List. The stored DataSource is used to provide JDBC connection for the action. The connection will be closed automatically.

Parameters:

reader - ResultSet reader.

Returns:

List containing the item range.

executeItemRangeSql

public List executeItemRangeSql(Class itemClass)

Executes a SQL query that should retrieve a range of items from the list. ListSqlHelper's BeanResultReader is used to convert the ResultSet into a List. The stored DataSource is used to provide JDBC connection for the action. The connection will be closed automatically.

Parameters:

itemClass - Bean class.

Returns:

List containing the item range.

getListItemsDataCallback

public ConnectionCallback getListItemsDataCallback(ResultReader reader)

Returns the total count and item ragne queries callback. In most cases, you should not use this method directly, instead using one of the execute methods is recommended.

getCountSqlCallback

public ConnectionCallback getCountSqlCallback()

Returns the total count query callback. In most cases, you should not use this method directly, instead using one of the execute methods is recommended.

getItemRangeSqlCallback

public ConnectionCallback getItemRangeSqlCallback(ResultReader reader)

Returns the item range query callback. In most cases, you should not use this method directly, instead using one of the execute methods is recommended.

createBeanResultReader

public ResultReader createBeanResultReader(Class itemClass)

Returns Bean ResultSet reader. In most cases, you should not use this method directly, instead using one of the execute methods is recommended.

createExpressionBuilderResolver

protected VariableResolver createExpressionBuilderResolver()

Creates the VariableResolver for SqlExpressionBuilder that converts Variable names to their Database Field names according to the naming strategy.

Returns:

the VariableResolver for SqlExpressionBuilder that converts Variable names to their Database Field names according to the naming strategy.

createQueryFailedException

protected static RuntimeException createQueryFailedException(String QueryString,
                                                             List queryParams,
                                                             SQLException nestedException)

Returns query failed Exception that contains query String and params.