/**
* (c) 2001 - 2006 eBworx Malaysia Sdn Bhd. All rights reserved.
* 7th Floor, Menara Merais,
* No. 1, Jalan 19/3,
* 46300 Petaling Jaya,
* Selangor Darul Ehsan,
* Malaysia.
*
* This software is the intellectual property of eBworx. The program
* may be used only in accordance with the terms of the license agreement you
* entered into with eBworx.
*/
// DBUtils.java
package com.ebworx.kernel.syscore;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
import com.ebworx.kernel.adapters.exception.AdapterException;
import com.ebworx.kernel.helper.KernelConstant;
import com.ebworx.kernel.servicedirector.ServiceDirector;
import com.ebworx.kernel.servicedirector.helper.ServiceDirectorException;
import com.ebworx.kernel.syscore.exception.SysCoreException;
import com.ebworx.kernel.transactiondirector.metadata.TCB;
import com.ebworx.services.common.server.helper.ExceptionDefinitionConstant;
import com.ebworx.services.common.server.utils.ConfigurationManager;
import com.ebworx.services.common.server.utils.ConfigurationManagerException;
import com.ebworx.services.mapper.IOracleMapper;
import com.ebworx.services.vo.metadata.IValueObject;
/**
* Database Utilities.
*
* @author Nicholas
* @version $Id:
*/
public class DB {
private final static String OBJECT_CODE = "DBU";
private final static String DATA_SOURCE_PROP_NAME = "jndiName";
private final static String DATASOURCE_NODE_PATH="//datasource";
private static Logger logger = Logger.getLogger(DB.class.getName());
private static String DATA_SOURCE_NAME = "";
static {
try {
ConfigurationManager cfgMgr = ConfigurationManager.getInstance(KernelConstant.CONFIGURATION_KEY);
Object tcbPoolNode= cfgMgr.setNode(DATASOURCE_NODE_PATH);
DATA_SOURCE_NAME = cfgMgr.getChildValue(tcbPoolNode, DATA_SOURCE_PROP_NAME);
}
catch (ConfigurationManagerException e) {
logger.fatal("Unable to load TCBPool Configuration ",e);
}
}
/**
* Get a valid connection from the datasource specify by the datasource name passed in.
*
* @param dataSourceName Data source name setup in application server.
* @return Connection object.
* @throws SysCoreException If an error occurs
*/
public static Connection getConnection(String contextFactory, String provideURL, String dataSourceName)
throws SysCoreException {
// TODO: Change to Service Director search
Connection connection = null;
DataSource dataSource = null;
try {
Hashtable evn = new Hashtable();
evn.put(InitialContext.INITIAL_CONTEXT_FACTORY, contextFactory);
evn.put(InitialContext.PROVIDER_URL, provideURL);
Context ic = new InitialContext(evn);
dataSource = (DataSource)ic.lookup(dataSourceName);
connection = dataSource.getConnection();
}
catch (Exception e) {
throw new SysCoreException(e);
}
return connection;
}
/**
* Get a valid connection from the default datasource configured in properties file
*
* @param dataSourceName Data source name setup in application server.
* @return Connection object.
* @throws
*/
public static Connection getConnection()
throws SysCoreException {
try {
return ((DataSource)ServiceDirector.getInstance().getJNDISync(DATA_SOURCE_NAME)).getConnection();
}
catch (ServiceDirectorException e) {
throw new SysCoreException(e);
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new SysCoreException(e);
}
}
/**
* Close database objects in the correct sequence (resultset>statement>connection)
*
* @param rs <code>Resultset</code> object (optional).
* @param st <code>Statement</code> object (optional).
* @param conn <code>Connection</code> object.
* @return
* @throws
*/
public static void close(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
}
catch (SQLException sqlEx) {
logger.error("Encounter error when closing database objects: " + sqlEx.getMessage());
}
}
/**
* Close connection object
*
* @param conn <code>Connection</code> object.
* @return
* @throws
*/
public static void close(Connection conn) {
try {
if (conn != null) {
conn.close();
}
}
catch (SQLException sqlEx) {
logger.error("Encounter error when closing connection object: " + sqlEx.getMessage());
}
}
/**
* Close statement object
*
* @param conn <code>Statement</code> object.
* @return
* @throws
*/
public static void close(Statement st) {
try {
if (st != null) {
st.close();
}
}
catch (SQLException sqlEx) {
logger.error("Encounter error when closing statement object: " + sqlEx.getMessage());
}
}
/**
* Close resultset object
*
* @param conn <code>ResultSet</code> object.
* @return
* @throws
*/
public static void close(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
}
catch (SQLException sqlEx) {
logger.error("Encounter error when closing resultset object: " + sqlEx.getMessage());
}
}
/**
* Perform ACD (Add, Change, Delete) operations.
*
* @param tcb Transaction Control Block containing the necessary operation parameters/data.
* @param connection <code>Connection</code> object used to perform the ACD.
* @return
* @throws SysCoreException If an error occurs
*/
public static void ACD(TCB tcb, Connection connection)
throws SysCoreException {
if (logger.isDebugEnabled()) {
logger.debug("Enter SysCore DB ACD.");
}
IValueObject vo = null;
CallableStatement callableStatement = null;
IOracleMapper mapper = null;
String previousStatement = "";
int outParameterIndex = 0;
int updatedRowCount = 0;
long startTime=0;
long dbTime=0;
long requestMappingTime=0;
long responseMappingTime=0;
try {
Iterator it = tcb.requestVOs.iterator();
while (it.hasNext()) {
//Extracting VO from request
vo = (IValueObject) it.next();
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Action Id = " + vo.getActionId());
}
mapper = (IOracleMapper) vo.getMapper();
// Check if the statement to execute is the same as the previous one
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To get prepare call using " + mapper.getStatement(vo.getActionId()));
}
if (!mapper.getStatement(vo.getActionId()).equals(previousStatement)) {
close(callableStatement);
previousStatement = mapper.getStatement(vo.getActionId());
callableStatement = connection.prepareCall(previousStatement);
}
// To bind request
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To bind request");
}
if (logger.isInfoEnabled() )
startTime= System.currentTimeMillis();
outParameterIndex = mapper.bindRequest(vo, callableStatement);
if (logger.isInfoEnabled() )
requestMappingTime = System.currentTimeMillis() - startTime;
callableStatement.setString(outParameterIndex, tcb.transactionId);
if (logger.isInfoEnabled() )
startTime= System.currentTimeMillis();
// To execute callable statement
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To execute callable statement");
}
updatedRowCount = callableStatement.executeUpdate();
if (logger.isInfoEnabled() ) {
dbTime = System.currentTimeMillis() - startTime;
profileExecuteTimeStamp(dbTime,requestMappingTime,responseMappingTime,mapper.getStatement(vo.getActionId()),updatedRowCount);
}
}
}
catch (SQLException sqlEx) {
throw new SysCoreException(sqlEx);
}
catch (Exception e) {
throw new SysCoreException(e);
}
finally {
close(callableStatement);
}
}
/**
* Perform ACD (Add, Change, Delete) operations using the default connection.
*
* @param tcb Transaction Control Block containing the necessary operation parameters/data.
* @return
* @throws SysCoreException If an error occurs
*/
public static void ACD(TCB tcb)
throws SysCoreException {
Connection connection = null;
try {
connection = getConnection();
ACD(tcb, connection);
}
finally {
close(connection);
}
}
/**
* Perform add operation with one or more Lob (Clob/Blob).
*
* @param tcb Transaction Control Block containing the necessary operation parameters/data.
* @param connection <code>Connection<c/ode> object used to perform the add.
* @return
* @throws SysCoreException If an error occurs
*/
public static void lobACD(TCB tcb, Connection connection)
throws SysCoreException {
if (logger.isDebugEnabled()) {
logger.debug("Enter SysCore DB lobACD.");
}
IValueObject vo = null;
ResultSet resultSet = null;
CallableStatement callableStatement = null;
IOracleMapper mapper = null;
String previousStatement = "";
int outParameterIndex = 0;
int updatedRowCount = 0;
long startTime=0;
long dbTime=0;
long requestMappingTime=0;
long responseMappingTime=0;
try {
Iterator it = tcb.requestVOs.iterator();
while(it.hasNext()) {
// Extracting VO from request
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Action Id = " + vo.getActionId());
}
vo = (IValueObject) it.next();
mapper = (IOracleMapper) vo.getMapper();
// Check if the statement to execute is the same as the previous one
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To get prepare call using " + mapper.getStatement(vo.getActionId()));
}
if (!mapper.getStatement(vo.getActionId()).equals(previousStatement)) {
close(callableStatement);
previousStatement = mapper.getStatement(vo.getActionId());
callableStatement = connection.prepareCall(previousStatement);
}
// To bind request
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To bind request");
}
outParameterIndex = mapper.bindRequest(vo, callableStatement);
if (logger.isInfoEnabled() )
requestMappingTime = System.currentTimeMillis() - startTime;
callableStatement.registerOutParameter(outParameterIndex, -10);
callableStatement.setString(outParameterIndex+1, tcb.transactionId);
// To execute callable statement
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To execute callable statement");
}
if (logger.isInfoEnabled() )
startTime = System.currentTimeMillis();
callableStatement.execute();
if (logger.isInfoEnabled() )
dbTime = System.currentTimeMillis() - startTime;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Statement executed");
}
if (logger.isInfoEnabled() )
startTime = System.currentTimeMillis();
// To obtain resultset
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To obtain ResultSet with LOBs");
}
resultSet = (ResultSet) callableStatement.getObject(outParameterIndex);
if (resultSet.next()) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To bind LOBs");
}
mapper.bindLobRequest(vo, resultSet);
updatedRowCount = 1;
}
else {
logger.error("No record with Lobs retrieved from database");
throw new SysCoreException(ExceptionDefinitionConstant.ERROR,
KernelConstant.SYSCORE_MODULE_CODE, OBJECT_CODE, "6001");
}
close(resultSet);
if (logger.isInfoEnabled() ) {
responseMappingTime = System.currentTimeMillis() - startTime;
profileExecuteTimeStamp(dbTime,requestMappingTime,responseMappingTime,mapper.getStatement(vo.getActionId()),updatedRowCount);
}
}
}
catch (SQLException sqlEx) {
throw new SysCoreException(sqlEx);
}
catch (Exception e) {
throw new SysCoreException(e);
}
finally {
close(resultSet);
close(callableStatement);
}
}
/**
* Perform add operation with one or more Lob (Clob/Blob) using the default connection.
*
* @param tcb Transaction Control Block containing the necessary operation parameters/data.
* @return
* @throws SysCoreException If an error occurs
*/
public static void lobACD(TCB tcb)
throws SysCoreException {
Connection connection = null;
try {
connection = getConnection();
lobACD(tcb, connection);
}
finally {
close(connection);
}
}
/**
* Perform search operations.
*
* @param tcb Transaction Control Block containing the necessary operation parameters/data.
* @param connection Connection object used to perform the search.
* @param pagingMode Pagination approach. Currently supports NO_PAGING, ADAPTER_PAGING and PROCEDURE_PAGING.
* @return
* @throws SysCoreException If an error occurs
*/
public static void select(TCB tcb, int pagingMode, Connection connection)
throws SysCoreException {
if (logger.isDebugEnabled()) {
logger.debug("Enter SysCore DB select.");
}
int outParameterIndex;
int rowIndex = 0;
IValueObject vo;
CallableStatement callableStatement = null;
IOracleMapper mapper;
ResultSet resultSet = null;
long startTime=0;
long dbTime=0;
long requestMappingTime=0;
long responseMappingTime=0;
try {
// Extracting VO from request
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To extract value object from TCB");
}
vo = (IValueObject) tcb.requestVOs.get(0);
// Extract Mapper object from request VO
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To extract mapper");
}
mapper = (IOracleMapper) vo.getMapper();
// Preparing callable statement
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To get prepare call using " + mapper.getStatement(vo.getActionId()));
}
callableStatement = connection.prepareCall(mapper.getStatement(vo.getActionId()));
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To bind request");
}
// To bind request
if (logger.isInfoEnabled() )
startTime= System.currentTimeMillis();
outParameterIndex = mapper.bindRequest(vo, callableStatement);
if (logger.isInfoEnabled() )
requestMappingTime= System.currentTimeMillis() - startTime;
// To bind output parameter for callable statement
bindParameterForReadOnlyAccess(callableStatement, tcb, pagingMode,outParameterIndex);
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To execute statement");
}
if (logger.isInfoEnabled() )
startTime = System.currentTimeMillis();
callableStatement.execute();
if (logger.isInfoEnabled() )
dbTime = System.currentTimeMillis() - startTime;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Statement executed");
}
resultSet = (ResultSet) callableStatement.getObject(outParameterIndex);
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", ResultSet obtained");
}
if (logger.isInfoEnabled() )
startTime= System.currentTimeMillis();
if (pagingMode == KernelConstant.MODE_NO_PAGING) {
if (resultSet != null) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To invoke Mapper object to extract data from ResultSet");
}
// Initialize response list object for storing returning data
tcb.responseVOs = new ArrayList();
// Loop through ResultSet to extract data
while (resultSet.next()) {
tcb.responseVOs.add(mapper.bindResponse(vo.getActionId(), resultSet));
}
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", ResultSet processing completed");
}
} else {
logger.warn(tcb.transactionId + ", ResultSet object is null");
}
}
else {
// Determine if any of the row in ResultSet can be skipped. For eg, when retrieving record
// for third page, a portion of beginning rows can be skipped
int currentRow = skipToBeginningRow(resultSet, tcb, pagingMode);
// Abort processing if end of ResultSet is reached after skipping of row
if (!resultSet.isAfterLast()) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To invoke Mapper object to extract data from ResultSet");
}
tcb.responseVOs = new ArrayList();
// Loop through ResultSet to extract (only) a total number of rows requested by caller
do {
tcb.responseVOs.add(mapper.bindResponse(vo.getActionId(), resultSet));
rowIndex++;
}
// **** DO NOT CHANGE THE SEQUENCE OF CONDITION ELSE PAGING PARAMETER CALCULATION WOULD GO WRONG **** //
while (rowIndex < tcb.rowPerPage && resultSet.next());
// Keeping track of current row in ResultSet by adding number of iteration through ResultSet with
// the current row value. The value is substracted by 1 given that the above iteration is a
// do...while() loop whereby rowIndex is incremented by 1 before the while condition is evaluated
currentRow += rowIndex - 1;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", ResultSet processing completed");
}
// Determine the paging parameters
determinePagingParameters(resultSet, tcb, pagingMode, currentRow);
} else {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Processing aborted as end of ResultSet is reached");
}
}
}
if (logger.isInfoEnabled() ) {
responseMappingTime = System.currentTimeMillis() - startTime;
profileExecuteTimeStamp(dbTime,requestMappingTime,responseMappingTime,mapper.getStatement(vo.getActionId()),tcb.totalRow);
}
}
catch (SQLException sqlEx) {
throw new SysCoreException(sqlEx);
}
catch (Exception e) {
throw new SysCoreException(e);
}
finally {
close(resultSet);
close(callableStatement);
}
}
/**
* Perform search operations using the default connection.
*
* @param tcb Transaction Control Block containing the necessary operation parameters/data.
* @param pagingMode Pagination approach. Currently supports NO_PAGING, ADAPTER_PAGING and PROCEDURE_PAGING.
* @return
* @throws SysCoreException If an error occurs
*/
public static void select(TCB tcb, int pagingMode)
throws SysCoreException {
Connection connection = null;
try {
connection = getConnection();
select(tcb, pagingMode, connection);
}
finally {
close(connection);
}
}
/**
* Calculates the total page count for setting this value into the Transaction Context object.
*
* @param totalRecords Total number of records returned from database access
* @param recordsPerPage Number of record to display on each page
* @return Total page count
*/
private static int calculatePageCount(int totalRecords, int recordsPerPage) {
int totalPageCount;
double d;
try {
d = ((double) totalRecords / recordsPerPage);
d = Math.ceil(d);
totalPageCount = (int) d;
} catch (NumberFormatException nfe) {
return 1;
}
// Page count returned is always 1 or larger
return (totalPageCount <= 0) ? 1 : totalPageCount;
}
/**
* Determines values for the paging parameters. This essentially includes:<list>
* 1. Checking if there is any trailing record in <code>ResultSet</code> object after data is extracted from it.
* If there is, the <code>MorePagesAvailable</code> attribute in Transcation Context object would be set to true.
* Else, it would be set to false. This attribute is to be used by front-end for controlling the behavior related
* to pagination. For eg, toggle the next page button on or off.
* 2. Determining total number of record returned from the database access. This value is determined differently
* depending on the paging approach used:
* <list>
* i. Adapter paginatin - total number of records is determine by iterating through the <code>ResultSet</code>
* object until its end position. Note that this is an expensive process when the data size returned is very large.
* ii. Procedure pagination - total number of records is to be determined at procedure level and is to be returned
* at <bold>FIRST</code> column position (that is the first column). This method expects the first column to contain
* the total number of records when procedure pagination is used.
* </list>
* </list>
*
* @param resultSet ResultSet object returned from database access
* @param transactionContext Transaction Context object
* @param pagingMode Pagination approach. Currently supports NO_PAGING, ADAPTER_PAGING and PROCEDURE_PAGING.
* @param currentRow Current row number in ResultSet object
* @throws SQLException If a database access error occurs
*/
private static void determinePagingParameters(ResultSet resultSet, TCB tcb, int pagingMode, int currentRow)
throws SQLException {
// Total number of row is made equivalent of current row in ResultSet before further iteration
int totalRow = currentRow;
// First determine if there is trailing record
if (resultSet.next()) {
// Since ResultSet has been move next again, increase total row value by 1 to keep track of total row
totalRow += 1;
// Set indicator to false to indicate there is trailing record
tcb.isMorePage = true;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", More record(s) available in ResultSet");
}
// For adapter pagination, keep iterating until the end position is reached to get the total row count.
// For procedure pagination, the total row count is retrieved from the first column of a row in ResultSet
// in the skipToBeginningRow method
if (pagingMode == KernelConstant.MODE_ADAPTER_PAGING) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId +
", To iterate till end of ResultSet to determine total row count as Adapter Pagination is used.");
}
// Iterates through ResultSet object to the end of it
while (resultSet.next()) {
totalRow++;
}
}
} else {
// Set indicator to false to indicate there is no trailing record and skip further iteration through the
// ResultSet since there is no more record available
tcb.isMorePage = false;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", No more record available in ResultSet");
}
}
// Set total row for Adapter Pagination and only Adapater Pagination since the total row of Procedure Pagination
// has been set in earlier process and is therefore, not to be overwritten here
if (pagingMode == KernelConstant.MODE_ADAPTER_PAGING) {
// For adapter pagination, keep iterating until the end position is reached to get the total row count.
// For procedure pagination, the total row count is retrieved from the first column of a row in ResultSet
// in the skipToBeginningRow method
// Set total row count value
tcb.totalRow = totalRow;
}
// If total number of record is more than row per page, calculate total page value
if (tcb.totalRow > tcb.rowPerPage) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Total row is more than row per page. To calculate total page.");
}
// Calculate total page by dividing total number of row over number of row (display) per page if the total row
// is more than the row per page
tcb.totalPage = calculatePageCount(tcb.totalRow,tcb.rowPerPage);
}
// If total number of record is less than row per page, set total page value as 1
else {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Total row is less than row per page. Set total page as 1.");
}
tcb.totalPage = 1;
}
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Total row = " + tcb.totalRow);
logger.debug(tcb.transactionId + ", Total page = " + tcb.totalPage);
// todo: to confirm exact implementation of this warning
// A custom message to prompt for attention of exploring procedure pagination
if (pagingMode == KernelConstant.MODE_PROCEDURE_PAGING &&
tcb.totalRow > 100) {
logger.warn(tcb.transactionId + ", Total record retrieved is more than 100 rows. " +
"Explore if Procedure Pagination could provide performance gain for this access.");
}
}
}
/**
* Skips row in <code>ResultSet</code> object returned from database access to move to the correct starting
* row for extracting data. The correct starting row is relative to the current page index supplied in
* the Transaction Context object.
*
* @param resultSet ResultSet object returned from database access
* @param transactionContext Transaction Context object
* @param pagingMode Pagination approach. Currently supports NO_PAGING, ADAPTER_PAGING and PROCEDURE_PAGING.
* @return Current row of ResultSet object
* @throws SQLException If a database access error occurs
*/
private static int skipToBeginningRow(ResultSet resultSet, TCB tcb, int pagingMode) throws SQLException {
int currentRow = 0;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Current page index = " + tcb.pageIndex);
logger.debug(tcb.transactionId + ", Row per page = " + tcb.rowPerPage);
}
// Thress conditions anticipated in the processing here:
// 1. Procedure Pagination is used - procedure has performed pagination and no skipping is required
// 2. Adapter Pagination is used and page count is 1 - indicating record 1 of page 1 is required
// 3. Adapter Pagination is used and page count is > 1 - indicating records in first N page(s) is to be skipped
// For 1st and 2nd cases, ResultSet is to be moved next once. For 3rd case, ResultSet is to be moved for
// calculated number of times to skip the unused records at beginning position.
if (pagingMode == KernelConstant.MODE_PROCEDURE_PAGING) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", No skipping required as Procedure Pagination is used");
}
// Under normal condition, moving next in ResultSet is expected to be successful
if (resultSet.next()) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To retrieve total row count from first row of record");
}
// Keep track of total row as ResultSet is moved to next position
currentRow = 1;
try {
// todo: to consider moving the column to first position to reduce processing overhead
// Retrieve the first column from ResultSet object (i.e. the total row)
tcb.totalRow = resultSet.getInt(resultSet.getMetaData().getColumnCount());
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Total row count = " +
tcb.totalRow);
}
}
catch (SQLException e) {
logger.error(tcb.transactionId +
", Exception encountered when retrieving total row count for Procedure Pagination. Ensure " +
"that total row count is indeed included as last column in procedure before further " +
"investigation.", e);
throw e;
}
}
// If for any reason the moving next in ResultSet fails, leave current row value as 0
else {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", No record retrieved from database");
}
}
}
// Not checking for Adapter Pagination since arriving here implies that Adapter Pagination is used
else if (tcb.pageIndex == 1) {
logger.debug(tcb.transactionId + ", No skipping required as page index is 1");
// Under normal condition, moving next in ResultSet is expected to be successful
if (resultSet.next()) {
// Keep track of total row as ResultSet is moved to next position
currentRow = 1;
}
// If for any reason the moving next in ResultSet fails, leave current row value as 0
else {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", No record retrieved from database");
}
}
}
// Not checking for Adapter Pagination since arriving here implies that Adapter Pagination is used
else {
// Determine the number of row to skip
int skipRow = (tcb.pageIndex - 1) * tcb.rowPerPage + 1;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Number of row to skip by calculation = " + skipRow);
}
// Adjust skipRow to 0 if negative value is found (this happens when current page is set incorrectly)
skipRow = (skipRow > 0) ? skipRow : 0;
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Absolute number of row to skip = " + skipRow);
}
for (int i = 1; i <= skipRow; i++) {
// Keep track of total row as ResultSet is moved to next position which is equivalent value of i
currentRow = i;
// Break the loop if ResultSet has gone beyond the last row. This may happen if invalid paging
// parameters were used or that paging parameters became invalid due to concurrent access
// (For eg, user 1 requesting for page 10 while user 2 is deleting all the records)
if (!resultSet.next()) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId +
", Iteration is discontinued as ResultSet is exhausted. This could be due to: " +
"1. Paging parameters are invalid. 2. Data has been deleted.");
}
break;
}
}
}
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", ResultSet processing to start at row = " + resultSet.getRow());
}
return currentRow;
}
/**
* Binds additional parameter(s) to <code>CallableStatement</code> for read-only access. Also performs a checking
* if procedure pagination is used. If so, automatically binds two extra paging parameters required by procedure.
* These parameters and their binding positions are:
* <list>
* 1. Requested page index - bound after the output parameter (cursor) position (i.e. outParameterIndex + 1)
* 2. Total row of record displayed per page - bound after requested page index, that is the last position of
* procedure input parameter list (i.e. outParameterIndex + 2)
* </list>
* For procedure pagination implementation, the procedure is expected to accept the above two parameters at
* specified position.
*
* @param callableStatement Callablestatement object constructed for database access.
* @param transactionContext Transaction Context object.
* @param pagingMode Pagination approach. Currently supports NO_PAGING, ADAPTER_PAGING and PROCEDURE_PAGING.
* @param outParameterIndex Index of output parameter at which the parameter is bound.
* @throws AdapterException If adapter specific error occurs in processing.
* @throws SQLException If a database access error occurs.
*/
private static void bindParameterForReadOnlyAccess(CallableStatement callableStatement, TCB tcb, int pagingMode, int outParameterIndex)
throws SysCoreException, SQLException {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", To bind out parameter at position = " + outParameterIndex);
}
// For procedure paging, the 2 mandatory extra input are to be automatically set to callablestatement:
// 1. Requested page index
// 2. Total row of record displayed per page
// 3. Request Id for tracing purposes
if (pagingMode == KernelConstant.MODE_NO_PAGING) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Adapter Pagination detected. No paging parameters to bind.");
logger.debug(tcb.transactionId + ", Request Id position = " + (outParameterIndex + 1));
}
callableStatement.registerOutParameter(outParameterIndex, -10);
callableStatement.setString(outParameterIndex + 1, tcb.transactionId);
} else
if (pagingMode == KernelConstant.MODE_ADAPTER_PAGING) {
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Adapter Pagination detected. No paging parameters to bind.");
logger.debug(tcb.transactionId + ", Request Id position = " + (outParameterIndex + 1));
}
callableStatement.registerOutParameter(outParameterIndex, -10);
callableStatement.setString(outParameterIndex + 1, tcb.transactionId);
} else if (pagingMode == KernelConstant.MODE_PROCEDURE_PAGING) {
// TODO: Raised exception if pageIndex and rowPerPage is not set
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Procedure Pagination detected. To bind paging parameters.");
logger.debug(tcb.transactionId + ", Page index to bind = " + tcb.pageIndex);
logger.debug(tcb.transactionId + ", Row per page to bind = " + tcb.rowPerPage);
logger.debug(tcb.transactionId + ", Request Id position = " + (outParameterIndex + 3));
}
callableStatement.registerOutParameter(outParameterIndex, -10);
callableStatement.setInt(outParameterIndex + 1, tcb.pageIndex);
callableStatement.setInt(outParameterIndex + 2, tcb.rowPerPage);
callableStatement.setString(outParameterIndex + 3, tcb.transactionId);
} else {
logger.error(tcb.transactionId + ", No pagination specified. Please specify pagination approach.");
throw new SysCoreException(ExceptionDefinitionConstant.ERROR,
KernelConstant.ORACLEADAPTER_MODULE_CODE, OBJECT_CODE,
"6004");
}
if (logger.isDebugEnabled()) {
logger.debug(tcb.transactionId + ", Registering of additional parameter(s) completed");
}
}
private static void profileExecuteTimeStamp(long dbTime, long requestMappingTime, long responseMappingTime , String spName, long rowAffected) {
if (logger.isInfoEnabled()) {
logger.info(
new StringBuffer().append('/t')
.append(spName)
.append('/t')
.append(dbTime)
.append('/t')
.append(requestMappingTime)
.append('/t')
.append(responseMappingTime)
.append('/t')
.append(rowAffected)
.toString()
);
}
}
} // DB.java
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
