com.ibm.commerce.foundation.server.services.dataaccess

Class JDBCQueryService

java.lang.Object

com.ibm.commerce.foundation.server.services.dataaccess.JDBCQueryService

public class JDBCQueryService
extends java.lang.Object

This class deals with complex SQL queries. These include select statements that use aggregate functions, like sum() or avg(), or delete/update statements to update a large number of records without reading them first. This class should only be used in the above situations. Use the PhysicalDataContainer for CRUD operations on the physical entities. Use the mediation classes that implement the BusinessObjectManager for CRUD operations on the nouns and noun parts.

Field Summary

Fields

Modifier and Type	Field and Description
static java.lang.String	COPYRIGHT IBM copyright notice field.

Constructor Summary

Constructors

Constructor and Description
JDBCQueryService(java.lang.String astrComponentId) Constructor for the JDBCQueryService.

Method Summary

Modifier and Type	Method and Description
int[]	executeBatchUpdate(java.lang.String astrQueryName, java.util.List alParametersForBatchedQueries) This method submits a batch of commands to the database.
int[]	executeBatchUpdate(java.lang.String astrQueryName, java.util.List alParametersForBatchedQueries, boolean updateInWorkspace) This method submits a batch of commands to the database.
int[]	executeBatchUpdateWithStatement(java.lang.String astrStatement, java.lang.String tableName, java.util.List alParametersForBatchedQueries, boolean updateInWorkspace) This method submits a batch of commands to the database.
java.util.List	executeDirectSQLQuery(java.lang.String sqlQuery, java.util.Map aQueryParameters) Executes the specified SQL statement defined in the query template and returns the result.
java.util.List	executeQuery(java.lang.String astrQueryName, java.util.Map aQueryParameters) Executes the specified SQL statement defined in the query template and returns the result.
java.util.List	executeQuery(java.lang.String astrQueryName, java.util.Map aQueryParameters, int startPageNumber, int numberOfPage) Executes the specified SQL statement defined in the query template and returns the result.
java.util.List	executeQuery(java.lang.String astrQueryName, java.util.Map aQueryParameters, int startPageNumber, int numberOfPage, int maxRows) Executes the specified SQL statement defined in the query template and returns the result.
int	executeSQLUpdate(java.lang.String aSQLQuery, java.util.Map aQueryParameters, java.lang.Long anOptCounter, java.lang.String[] arrTableNames) This method executes the insert, update or delete SQL statement defined in the SQL input parameter.
int	executeUpdate(java.lang.String astrQueryName, java.util.Map aQueryParameters) This method executes the insert, update or delete SQL statement defined in a query template file.
int	executeUpdate(java.lang.String astrQueryName, java.util.Map aQueryParameters, java.lang.Long anOptCounter) This method executes the insert, update or delete SQL statement defined in a query template file.
java.sql.Timestamp	getCurrentTimestamp() Gets the current timestamp according to the Commerce Server database.
java.util.List	myExecuteQuery() This method is intended to be used internally by this class.
void	setConnection(java.sql.Connection aConnection) Passes a database connection to be used by this class.

Methods inherited from class java.lang.Object

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

Field Detail

COPYRIGHT

public static final java.lang.String COPYRIGHT

IBM copyright notice field.

See Also:

Constant Field Values

Constructor Detail

JDBCQueryService

public JDBCQueryService(java.lang.String astrComponentId)

Constructor for the JDBCQueryService.

Parameters:

astrComponentId - - component identifier.

Method Detail

executeUpdate

public int executeUpdate(java.lang.String astrQueryName,
                         java.util.Map aQueryParameters)
                  throws java.sql.SQLException,
                         QueryServiceApplicationException

This method executes the insert, update or delete SQL statement defined in a query template file.

Parameters:

astrQueryName - the name of the query in the query template file

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

Returns:

either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing

Throws:

java.sql.SQLException - if a database access error occurs

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeUpdate

public int executeUpdate(java.lang.String astrQueryName,
                         java.util.Map aQueryParameters,
                         java.lang.Long anOptCounter)
                  throws java.sql.SQLException,
                         QueryServiceApplicationException

This method executes the insert, update or delete SQL statement defined in a query template file.

Parameters:

astrQueryName - the name of the query in the query template file

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

anOptCounter - the value of the optimistic collision column

Returns:

either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing

Throws:

java.sql.SQLException - if a database access error occurs

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeSQLUpdate

public int executeSQLUpdate(java.lang.String aSQLQuery,
                            java.util.Map aQueryParameters,
                            java.lang.Long anOptCounter,
                            java.lang.String[] arrTableNames)
                     throws java.sql.SQLException,
                            QueryServiceApplicationException

This method executes the insert, update or delete SQL statement defined in the SQL input parameter. This method is only executed by foundation code.

Parameters:

aSQLQuery - the SQL statement with parameter markers to execute in this method.

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

anOptCounter - the value of the optimistic collision column

arrTableNames - the string array of table name(s) associated to this SQL update for cache invalidation purposes

Returns:

either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for SQL statements that return nothing

Throws:

java.sql.SQLException - if a database access error occurs

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeBatchUpdate

public int[] executeBatchUpdate(java.lang.String astrQueryName,
                                java.util.List alParametersForBatchedQueries)
                         throws java.sql.SQLException,
                                java.sql.BatchUpdateException,
                                QueryServiceApplicationException

This method submits a batch of commands to the database. Each command executes a query in the query template with given parameters. If all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order of the elements in the parameters for batched queries as specified by the alParametersForBatchedQueries parameter. The elements in the array returned by the method executeBatchUpdate have the same values as the elements returned by the executeBatch()method of the Statement interface.

Parameters:

astrQueryName - the name of the query in the query template file

alParametersForBatchedQueries - List of NamedQueryParameters objects. The set of parameters in each object should be valid for a given query. Each object should have exactly the same set of parameter names.

Returns:

an array of update counts containing one element for each command in the batch.

Throws:

java.sql.SQLException - if a database access error occurs or the driver does not support batch statements.

java.sql.BatchUpdateException - (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeBatchUpdate

public int[] executeBatchUpdate(java.lang.String astrQueryName,
                                java.util.List alParametersForBatchedQueries,
                                boolean updateInWorkspace)
                         throws java.sql.SQLException,
                                java.sql.BatchUpdateException,
                                QueryServiceApplicationException

This method submits a batch of commands to the database. Each command executes a query in the query template with given parameters. If all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order of the elements in the parameters for batched queries as specified by the alParametersForBatchedQueries parameter. The elements in the array returned by the method executeBatchUpdate have the same values as the elements returned by the executeBatch()method of the Statement interface.

Parameters:

astrQueryName - the name of the query in the query template file

alParametersForBatchedQueries - List of NamedQueryParameters objects. The set of parameters in each object should be valid for a given query. Each object should have exactly the same set of parameter names.

updateInWorkspace - if set to true, then a check is performed to determine if the method is called in the context of workspace. If yes, then updates are performed one by one for each named query parameter. If the method is called in base, then a batch update is performed.

Returns:

an array of update counts containing one element for each command in the batch.

Throws:

java.sql.SQLException - if a database access error occurs or the driver does not support batch statements.

java.sql.BatchUpdateException - (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeBatchUpdateWithStatement

public int[] executeBatchUpdateWithStatement(java.lang.String astrStatement,
                                             java.lang.String tableName,
                                             java.util.List alParametersForBatchedQueries,
                                             boolean updateInWorkspace)
                                      throws java.sql.SQLException,
                                             java.sql.BatchUpdateException,
                                             QueryServiceApplicationException

This method submits a batch of commands to the database. Each command executes a query in the query template with given parameters. If all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order of the elements in the parameters for batched queries as specified by the alParametersForBatchedQueries parameter. The elements in the array returned by the method executeBatchUpdate have the same values as the elements returned by the executeBatch()method of the Statement interface.

Parameters:

astrStatement - the statement to be executed

tableName - the tablename to be executed on

alParametersForBatchedQueries - List of NamedQueryParameters objects. The set of parameters in each object should be valid for a given query. Each object should have exactly the same set of parameter names.

updateInWorkspace - if set to true, then a check is performed to determine if the method is called in the context of workspace. If yes, then updates are performed one by one for each named query parameter. If the method is called in base, then a batch update is performed.

Returns:

an array of update counts containing one element for each command in the batch.

Throws:

java.sql.SQLException - if a database access error occurs or the driver does not support batch statements.

java.sql.BatchUpdateException - (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeQuery

public java.util.List executeQuery(java.lang.String astrQueryName,
                                   java.util.Map aQueryParameters)
                            throws java.sql.SQLException,
                                   QueryServiceApplicationException

Executes the specified SQL statement defined in the query template and returns the result. The query specified must be a SELECT statement.

Parameters:

astrQueryName - the name of the query in the query template file

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

Returns:

A list representation of the records in the result set. Each element in the list is a record of the result set. Each record is a HashMap where the key is the column name and the value is the column value. An empty list is returned if no records are returned from the query.

Throws:

java.sql.SQLException - if there is a problem with the connection from the datasource or executing the SQL statement.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeDirectSQLQuery

public java.util.List executeDirectSQLQuery(java.lang.String sqlQuery,
                                            java.util.Map aQueryParameters)
                                     throws java.sql.SQLException,
                                            QueryServiceApplicationException

Executes the specified SQL statement defined in the query template and returns the result. The query specified must be a SELECT statement.

Parameters:

sqlQuery - the direct query string

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

Returns:

A list representation of the records in the result set. Each element in the list is a record of the result set. Each record is a HashMap where the key is the column name and the value is the column value. An empty list is returned if no records are returned from the query.

Throws:

java.sql.SQLException - if there is a problem with the connection from the datasource or executing the SQL statement.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeQuery

public java.util.List executeQuery(java.lang.String astrQueryName,
                                   java.util.Map aQueryParameters,
                                   int startPageNumber,
                                   int numberOfPage)
                            throws java.sql.SQLException,
                                   QueryServiceApplicationException

Executes the specified SQL statement defined in the query template and returns the result. The query specified must be a SELECT statement.

Parameters:

astrQueryName - the name of the query in the query template file

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

startPageNumber - the start page number for pagination.

numberOfPage - the number of rows in a page pagination.

Returns:

A list contains both the totalNumber of result and the records list in result set.

The totalNumber is a Integer value that should be got by list.get(0)

The records list is a List object that should be got by list.get(1), each element in the records list is a record of the result set. Each record is a HashMap where the key is the column name and the value is the column value. An empty list is returned if no records are returned from the query.

Throws:

java.sql.SQLException - if there is a problem with the connection from the datasource or executing the SQL statement.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

executeQuery

public java.util.List executeQuery(java.lang.String astrQueryName,
                                   java.util.Map aQueryParameters,
                                   int startPageNumber,
                                   int numberOfPage,
                                   int maxRows)
                            throws java.sql.SQLException,
                                   QueryServiceApplicationException

Executes the specified SQL statement defined in the query template and returns the result. The query specified must be a SELECT statement.

Parameters:

astrQueryName - the name of the query in the query template file

aQueryParameters - a map that contains the parameter name as a map key and a list of values. Multiple values are specified for queries that use the SQL 'IN' operator.

startPageNumber - the start page number for pagination.

numberOfPage - the number of rows in a page pagination.

maxRows - the max rows that should be returned.

Returns:

A list contains both the totalNumber of result and the records list in result set.

The totalNumber is a Integer value that should be got by list.get(0)

The records list is a List object that should be got by list.get(1), each element in the records list is a record of the result set. Each record is a HashMap where the key is the column name and the value is the column value. An empty list is returned if no records are returned from the query.

Throws:

java.sql.SQLException - if there is a problem with the connection from the datasource or executing the SQL statement.

QueryServiceApplicationException - if an error occurred during getting query template information based on the specified query name.

myExecuteQuery

public final java.util.List myExecuteQuery()
                                    throws java.sql.SQLException

This method is intended to be used internally by this class. Any other use is not supported.

Returns:

the query result. Each element is a Map from column label to column value.

Throws:

java.sql.SQLException

getCurrentTimestamp

public java.sql.Timestamp getCurrentTimestamp()
                                       throws java.sql.SQLException

Gets the current timestamp according to the Commerce Server database.

Returns:

The current time according to the Commerce Server database.

Throws:

java.sql.SQLException - if there is a problem with the connection from the datasource or executing the SQL statement.

setConnection

public void setConnection(java.sql.Connection aConnection)

Passes a database connection to be used by this class. This method should be used by the callers who would like to the manage the opening and closing of the database connection. This can be useful when the caller wants to re-use the same connection for executing multiple queries. If a database connection is not set, it will be instantiated by this class.

Parameters:

aConnection - - database connection