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

java.lang.Object
  extended by 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


Copyright © 2003 - 2006 OpenSubsystems s.r.o.