org.opensubsystems.patterns.listdata.persist.db
Class ListDatabaseUtils

java.lang.Object
  org.opensubsystems.patterns.listdata.persist.db.ListDatabaseUtils

Direct Known Subclasses:

DB2ListDatabaseUtils, HsqlDBListDatabaseUtils, MSSQLListDatabaseUtils, MySQLListDatabaseUtils, OracleListDatabaseUtils, PostgreSQLListDatabaseUtils, SybaseListDatabaseUtils

public class ListDatabaseUtils
extends java.lang.Object

This class is used for common list retrieval and manipulation routines.

Version:

$Id: ListDatabaseUtils.java,v 1.56 2007/03/24 08:34:41 bastafidli Exp $

Author:

Julo Legeny

Code reviewer:

Miro Halas

Code reviewed:

1.47 2006/03/14 23:56:56 jlegeny

Field Summary

static java.lang.String

LIST_INDEX_PREFIX Prefix that will be used for explicitely defined index name on columns that can be used within the ORDER BY clause for retrieving data to the list.

Constructor Summary

ListDatabaseUtils()

Method Summary

protected java.util.List	allocateObjectList(ListOptions options, int iCount) Method for allocating of the object list
protected java.lang.String	convertValueToSQL(java.lang.Object objValue, int iValueType) Convert the value to coresponding SQL representation based on it's data type.
java.lang.String	getConstructListIndexName(java.lang.String strTableName, java.lang.String strColumnName) Method construct name of the index used for data retrieving into the list.
static ListDatabaseUtils	getInstance() Get database dependent instance of ListDatabaseUtils.
java.util.List	getObjectList(ListOptions options, java.lang.String query, ListDatabaseFactory factory, ListDatabaseSchema schema) Get list of data objects for specified parameters
protected java.util.List	getObjectListKeepSelected(ListDatabaseFactory factory, java.sql.Connection cntConnection, java.lang.String query, ListOptions options, java.util.List lstObjects, ListDatabaseSchema schema) Get list of data objects keeping the selected data object.
protected int	getSelectCount(java.sql.Connection cntConnection, java.lang.String query, ListDatabaseSchema schema, int iDomainId) Method for executing query for getting number of all items retrieved using count(x).
java.lang.String	getSelectList(java.lang.String strTableName, ListOptions options, ListDatabaseSchema schema) Generate select query to returns data based on the list option describing what data objects to load taking into account filter decribing security restrictions that prevent us from loading data that the user doesn't have rights to see and all other filtering conditions.
protected java.lang.String	getSQLOperation(DataCondition condition) Get SQL operation for operation and value of given data condition.
protected java.lang.String	getSQLValue(DataCondition condition) Get SQL value for operation and value of given data condition..
protected int	getTotalRecords(java.sql.ResultSet rsQueryResults, ListOptions options) Method for getting number of total records in result set using getRow() method.
java.lang.String	getWhereClause(java.lang.String strTableName, int[] arrIds, int[] arrDomainIds, SimpleRule securityFilter, ListDatabaseSchema schema) Get where clause to select specified ids while taking into account specified security filter therefore retrieving only those data objects for which the access is granted.
java.lang.String	getWhereClause(java.lang.String strTableName, int[] arrIds, SimpleRule securityFilter, ListDatabaseSchema schema) Get where clause to select specified ids while taking into account specified security filter therefore retrieving only those data objects for which the access is granted.
protected void	parseCondition(java.lang.StringBuffer strBuffer, DataCondition condition, ListDatabaseSchema schema) Parse DataCondition to SQL query expression.
protected java.lang.String	preprocessSelectQuery(java.lang.String inputQuery, ListOptions options, ListDatabaseSchema schema) Method that preprocess query (by adding specific SQL clauses for example LIMIT, ...) to improve performance of selecting items from the list at specific position.
protected java.lang.String	preprocessSelectQueryForCreationDate(java.lang.String inputQuery, ListOptions options, ListDatabaseSchema schema) Method that preprocess query (by adding specific SQL clauses after CREATION_DATE sort column) to fix ordering if DBS supports short timestamp value.
protected ListOptions	setOptionPositioning(int iCount, ListOptions options) Method set up begin position, end position and actual list size based on the total number of retrieved items.

Methods inherited from class java.lang.Object

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

Field Detail

LIST_INDEX_PREFIX

public static final java.lang.String LIST_INDEX_PREFIX

Prefix that will be used for explicitely defined index name on columns that can be used within the ORDER BY clause for retrieving data to the list.

See Also:

Constant Field Values

Constructor Detail

ListDatabaseUtils

public ListDatabaseUtils()

Method Detail

getInstance

public static ListDatabaseUtils getInstance()
                                     throws org.opensubsystems.core.error.OSSException

Get database dependent instance of ListDatabaseUtils.

Returns:

ListDatabaseUtils - the database dependent instance to use for this class

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getConstructListIndexName

public java.lang.String getConstructListIndexName(java.lang.String strTableName,
                                                  java.lang.String strColumnName)

Method construct name of the index used for data retrieving into the list. This index name should be common for all DB systems. But there is restriction for IBM DB2 database, the length of the index name shold be max. 18 characters. So there will be generated index name from: 1. index prefix = LST_ (4 characters) 2. Prefix BF_ from the table name will be removed first. Also all others occurences of the character '_' will be removed. max 7. characters from the table name: 3 characters from the begining of the table name and 4 characters from the end of the table name string. 3. All occurences of the character '_' will be removed. max 7. characters from the column name: 3 characters from the begining of the column name and 4 characters from the end of the column name string For example: table BF_INTERNAL_SESSION column CREATION_DATE index name = LST_INTSIONCREDATE

Parameters:

strTableName - - name of the table that will be useed for index name costruction

strColumnName - - name of the column that will be useed for index name costruction

Returns:

String - constructed index name

getObjectList

public java.util.List getObjectList(ListOptions options,
                                    java.lang.String query,
                                    ListDatabaseFactory factory,
                                    ListDatabaseSchema schema)
                             throws org.opensubsystems.core.error.OSSException

Get list of data objects for specified parameters

Parameters:

options - - list option describing the list to retrieve

query - - select SQL query

factory - - list factory for specific data object

schema - - list database schema

Returns:

List - list of data objects

Throws:

org.opensubsystems.core.error.OSSException - - an error getting object list

getObjectListKeepSelected

protected java.util.List getObjectListKeepSelected(ListDatabaseFactory factory,
                                                   java.sql.Connection cntConnection,
                                                   java.lang.String query,
                                                   ListOptions options,
                                                   java.util.List lstObjects,
                                                   ListDatabaseSchema schema)
                                            throws org.opensubsystems.core.error.OSSException,
                                                   java.sql.SQLException

Get list of data objects keeping the selected data object.

Parameters:

factory - - list factory for specific data object

cntConnection - - connection

query - - select SQL query

options - - list option describing the list to retrieve

lstObjects - - list of data objects

schema - - list database schema

Returns:

List - list of data objects

Throws:

org.opensubsystems.core.error.OSSException - - an error getting object list keeping selected

java.sql.SQLException - - an error getting object list keeping selected

getSelectList

public java.lang.String getSelectList(java.lang.String strTableName,
                                      ListOptions options,
                                      ListDatabaseSchema schema)
                               throws org.opensubsystems.core.error.OSSException

Generate select query to returns data based on the list option describing what data objects to load taking into account filter decribing security restrictions that prevent us from loading data that the user doesn't have rights to see and all other filtering conditions. Following portions of the list options are considered - list parent specification - list ignored list - security options - filter options - any extra conditions - ordering clause

Parameters:

strTableName - - name of the table from which the data should be loaded

options - - list options used to generate query to load list of data

schema - - list database schema implementation capable of providing more info about attributes included in the query

Returns:

String - select query for list options

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getWhereClause

public java.lang.String getWhereClause(java.lang.String strTableName,
                                       int[] arrIds,
                                       SimpleRule securityFilter,
                                       ListDatabaseSchema schema)
                                throws org.opensubsystems.core.error.OSSException

Get where clause to select specified ids while taking into account specified security filter therefore retrieving only those data objects for which the access is granted. The returned clause will start with "where".

Parameters:

strTableName - - name of the table in database

arrIds - - array of Ids which should be selected, may be null when no special IDs should be selected

securityFilter - - rule describing what data can user access

schema - - list database schema implementation capable of providing more info about attributes included in the query

Returns:

String - where query

Throws:

org.opensubsystems.core.error.OSSException - - error during parse

getWhereClause

public java.lang.String getWhereClause(java.lang.String strTableName,
                                       int[] arrIds,
                                       int[] arrDomainIds,
                                       SimpleRule securityFilter,
                                       ListDatabaseSchema schema)
                                throws org.opensubsystems.core.error.OSSException

Get where clause to select specified ids while taking into account specified security filter therefore retrieving only those data objects for which the access is granted. The returned clause will start with "where".

Parameters:

strTableName - - name of the table in database

arrIds - - array of Ids which should be selected, may be null when no special IDs should be selected

arrDomainIds - - array of domain Ids in which should be selected arrIDs, may be null and in that case will be used original method getWhereClause() above

securityFilter - - rule describing what data can user access

schema - - list database schema implementation capable of providing more info about attributes included in the query

Returns:

String - where query

Throws:

org.opensubsystems.core.error.OSSException - - error during parse

preprocessSelectQuery

protected java.lang.String preprocessSelectQuery(java.lang.String inputQuery,
                                                 ListOptions options,
                                                 ListDatabaseSchema schema)
                                          throws org.opensubsystems.core.error.OSSException

Method that preprocess query (by adding specific SQL clauses for example LIMIT, ...) to improve performance of selecting items from the list at specific position. This method can be overwritten by DB specific method.

Parameters:

inputQuery - - query that will be preprocessed

options - - initialized list options that will be used for retrieving begin position, page size, ...

schema - - list database schema

Returns:

String - preprocessed query

Throws:

org.opensubsystems.core.error.OSSException - - error during preprocessing select query

preprocessSelectQueryForCreationDate

protected java.lang.String preprocessSelectQueryForCreationDate(java.lang.String inputQuery,
                                                                ListOptions options,
                                                                ListDatabaseSchema schema)
                                                         throws org.opensubsystems.core.error.OSSException

Method that preprocess query (by adding specific SQL clauses after CREATION_DATE sort column) to fix ordering if DBS supports short timestamp value. This method can be overwritten by DB specific method.

Parameters:

inputQuery - - query that will be preprocessed

options - - initialized list options that will be used for retrieving begin position, page size, ...

schema - - list database schema

Returns:

String - preprocessed query

Throws:

org.opensubsystems.core.error.OSSException - - error during preprocessing select query

parseCondition

protected void parseCondition(java.lang.StringBuffer strBuffer,
                              DataCondition condition,
                              ListDatabaseSchema schema)
                       throws org.opensubsystems.core.error.OSSException

Parse DataCondition to SQL query expression. There result will be one or several search term: COLUMN OPERATION VALUE ....

Parameters:

strBuffer - - buffer to add SQL query expression to

condition - - condition to parse

schema - - list database schema implementation capable of providing more info about attributes included in the query

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

getSQLOperation

protected java.lang.String getSQLOperation(DataCondition condition)
                                    throws org.opensubsystems.core.error.OSSInvalidDataException

Get SQL operation for operation and value of given data condition.

Parameters:

condition - - data condition to process

Returns:

String - operation string

Throws:

org.opensubsystems.core.error.OSSInvalidDataException - - error occured during parsing data

convertValueToSQL

protected java.lang.String convertValueToSQL(java.lang.Object objValue,
                                             int iValueType)
                                      throws org.opensubsystems.core.error.OSSInvalidDataException

Convert the value to coresponding SQL representation based on it's data type.

Parameters:

objValue - - value to convert to SQL string representation.

iValueType - - one of the DataCondition.VALUE_TYPE_XXX constants

Returns:

String - string representing the value in valid SQL command

Throws:

org.opensubsystems.core.error.OSSInvalidDataException - - error occured during parsing data

getSQLValue

protected java.lang.String getSQLValue(DataCondition condition)
                                throws org.opensubsystems.core.error.OSSInvalidDataException

Get SQL value for operation and value of given data condition..

Parameters:

condition - - data condition to process

Returns:

String - value string

Throws:

org.opensubsystems.core.error.OSSInvalidDataException - - error occured during parsing data

setOptionPositioning

protected ListOptions setOptionPositioning(int iCount,
                                           ListOptions options)

Method set up begin position, end position and actual list size based on the total number of retrieved items.

Parameters:

iCount - - number of items

options - - options to be set up

Returns:

ListOptions - changed options

getSelectCount

protected int getSelectCount(java.sql.Connection cntConnection,
                             java.lang.String query,
                             ListDatabaseSchema schema,
                             int iDomainId)
                      throws org.opensubsystems.core.error.OSSException,
                             java.sql.SQLException

Method for executing query for getting number of all items retrieved using count(x).

Parameters:

cntConnection - - db connection

query - - original query to be executed to retrieve items

schema - - list database schema implementation capable of providing more info about attributes included in the query

iDomainId - - ID of the domain

Returns:

int - total number of specified records retrieved from count(x)

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

java.sql.SQLException - - error while executing query

allocateObjectList

protected java.util.List allocateObjectList(ListOptions options,
                                            int iCount)
                                     throws org.opensubsystems.core.error.OSSException

Method for allocating of the object list

Parameters:

options - - list options

iCount - - number of all items

Returns:

List - allocated object list, this will be null if options were not returned of if there are no items

Throws:

org.opensubsystems.core.error.OSSException - - error occured during allocating object list

getTotalRecords

protected int getTotalRecords(java.sql.ResultSet rsQueryResults,
                              ListOptions options)
                       throws org.opensubsystems.core.error.OSSException,
                              java.sql.SQLException

Method for getting number of total records in result set using getRow() method. There can be owerwritten this method by DB specific parts.

Parameters:

rsQueryResults - - result set

options - - list options

Returns:

int - number of records in resultset

Throws:

org.opensubsystems.core.error.OSSException - - an error has occured

java.sql.SQLException - - error while executing query