/*
Copyright (c) 2003-2009 ITerative Consulting Pty Ltd. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted
provided that the following conditions are met:
o Redistributions of source code must retain the above copyright notice, this list of conditions and
the following disclaimer.
o Redistributions in binary form must reproduce the above copyright notice, this list of conditions
and the following disclaimer in the documentation and/or other materials provided with the distribution.
o This jcTOOL Helper Class software, whether in binary or source form may not be used within,
or to derive, any other product without the specific prior written permission of the copyright holder
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package net.helipilot50.stocktrade.genericdbms;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Map;
import java.util.Set;
import java.util.WeakHashMap;
import javax.sql.DataSource;
import net.helipilot50.stocktrade.framework.Array_Of_NamedElement;
import net.helipilot50.stocktrade.framework.Array_Of_TextData;
import net.helipilot50.stocktrade.framework.FrameworkUtils;
import net.helipilot50.stocktrade.framework.NamedElement;
import net.helipilot50.stocktrade.framework.ParameterHolder;
import net.helipilot50.stocktrade.framework.ServiceObjectProxy;
import net.helipilot50.stocktrade.framework.ServiceObjectRegistry;
import net.helipilot50.stocktrade.framework.Task;
import net.helipilot50.stocktrade.framework.TextData;
import net.helipilot50.stocktrade.framework.UsageException;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.ConnectionProxy;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.transaction.PlatformTransactionManager;
/**
* This class wraps up the management of a single JDBC data source and controls
* database connections obtained from it.
*
* @author Tim
*/
public class DBConnectionManager implements Constants, net.helipilot50.stocktrade.framework.Constants {
/** Container data source to get connection from database */
private static Map<String, DBConnectionManager> dbConnectionManagers = new Hashtable<String, DBConnectionManager>();
private static Logger log = Logger.getLogger(DBConnectionManager.class);
private static boolean isDebugEnabled = log.isDebugEnabled();
private DataSource ds;
private JdbcTemplate template;
private PlatformTransactionManager transaction;
//private Connection connection;
// private List statements = new ArrayList();
private String dbName;
private String dataSourceName;
/**
* It is possible to obtain a callback whenever a connection is created
*/
private ConnectionInitializer connectionInitialiser;
/**
* A list of the connections that are known by this DBConnectionManager. Note that
* this really should be a weak hash set, but none exists. This map will only be
* used if there is a sessionInitialiser present and <tt>useSingleDataConnection</tt>
* is <tt>false</tt>.
* <p>
* Note that nothing is ever explicitly removed from this hash table. The removal
* of items depends upon the connection pool releasing them and the garbage collector
* realsing that the weak reference associated with the key is now no longer needed
* and automatically removing the item from the map.
*/
private Map<Connection, String> sessionList = new WeakHashMap<Connection, String>();
/**
* In some cases we need to force the use of a single data connection per DBConnection manager. This is instead of the
* normal java pattern of using a connection pool, and more closely resembles the Forte way of doing things. This may
* be needed if the data access pattern creates a bunch of statements at partition startup time and keeps these statements
* around for long periods of time. Using a connection pool would result in the statements being closed when the connection
* is released back to the pool.<p>
* <p>
* If this pattern is used, this attribute holds the single database connection used to access the database. If this
* pattern is not used, then the dataConnection field will remain at null.
*/
private Connection dataConnection = null;
/**
* Determine the data access pattern to use. If this field is true, a DBConnectionManager will always use the same
* connection. If this field is false, the normal (pooled) connections will be used.
*/
private boolean useSingleDataConnection = false;
public String getDataSourceName() {
return dataSourceName;
}
public void setDataSourceName(String dataSourceName) {
this.dataSourceName = dataSourceName;
}
/**
*
* @param dataSource a string containing the name of the
* data source this connection manager should use.
* @return a instance of DBConnectionManager using the specified data
* source.
*/
public synchronized static DBConnectionManager getInstance(String dataSourceName) {
DBConnectionManager result = null;
// TF:07/07/2009:First see if there's an existing connection that we're forced to use, for example if a Forte
// load balanced service wrappers a connection, we must stick with the same connection.
result = ServiceObjectProxy.getExistingInstance(dataSourceName, DBConnectionManager.class);
if (result == null) {
if (dbConnectionManagers.containsKey(dataSourceName)) {
result = (DBConnectionManager) dbConnectionManagers.get(dataSourceName);
} else {
// There's one other possibility -- it just hasn't been injected yet. (eg if
// the client accesses the database directly)
result = (DBConnectionManager)ServiceObjectRegistry.getService(dataSourceName, DBConnectionManager.class);
result.setDataSourceName(dataSourceName);
if (result == null) {
throw new UsageException("Data source " + dataSourceName + " has not been injected into the DBConnectionMgr");
}
}
}
return result;
}
/**
* Create a new DBConnectionManager using the passed connection properties. The returned instance will be wrappered in its
* own transaction manager (and hence be transactional, but outside the transaction scope of other services) and will not
* be managed in any way. To close the connection and dispose of the associated resources, {@link #destroy()} must be
* called on this DBConnectionManager instance.
* @param dataSourceName
* @param properties
* @return
*/
public static DBConnectionManager createInstance(String dataSourceName, ConnectionProperties properties) {
DataSource ds = new SingleConnectionDataSource(
properties.getDriverClassName(),
properties.getUrl(),
properties.getUsername(),
properties.getPassword(),
true);
PlatformTransactionManager txnMgr = new DataSourceTransactionManager(ds);
DBConnectionManager conn = new DBConnectionManager(dataSourceName, ds, txnMgr, null, false);
return conn;
}
public DBConnectionManager(String name, DataSource ds) {
this(name, ds, new DataSourceTransactionManager(ds));
}
public DBConnectionManager(String name, DataSource ds, PlatformTransactionManager txn) {
this(name, ds, txn, null, true);
}
public DBConnectionManager(String name, DataSource ds, PlatformTransactionManager txn, ConnectionInitializer initializer) {
this(name, ds, txn, initializer, true);
}
private DBConnectionManager(String name, DataSource ds, PlatformTransactionManager txn, ConnectionInitializer initializer, boolean addToManagerMap) {
this.ds = ds;
//this.template = new JdbcTemplate(ds);
this.template = new JdbcTemplateWithNulls(new DelegatingDataSource(ds));
this.transaction = txn;
this.dbName = name;
this.dataSourceName = name;
this.connectionInitialiser = initializer;
if (addToManagerMap) {
dbConnectionManagers.put(name, this);
TransactionMgr.statistics.incrementDataSourcesManaged();
}
}
/**
* The delegating datasource allows us to implement calls to getConnection
* and replace them with our own getConnection method so we can keep track
* of them. This class is used only for the datasource passed to the JdbcTemplate
* -- all other calls in this class use the true (original) data source to
* prevent infinitely recursive callback loops
* @author tFaulkes
*
*/
private class DelegatingDataSource implements DataSource {
DataSource original;
public DelegatingDataSource(DataSource original) {
this.original = original;
}
public Connection getConnection() throws SQLException {
// Do not monitor the connections here, otherwise we will never be able
// to release them.
// TF:22/06/2009:Do NOT call through to getConnection, as this will call
// DataSourceUtils.getConnection(). The problem is that this method will
// be called from getJdbcTemplate, which calls DataSourceUtils.getConnection()
// which calls doGetConnection -- which calls this method. Calling getConnection()
// here will really screw things up.
// return DBConnectionManager.this.getConnection(false);
Connection result = original.getConnection();
initialiseConnection(result, false);
return result;
}
public Connection getConnection(String username, String password)
throws SQLException {
return original.getConnection(username, password);
}
public PrintWriter getLogWriter() throws SQLException {
return original.getLogWriter();
}
public int getLoginTimeout() throws SQLException {
return original.getLoginTimeout();
}
public void setLogWriter(PrintWriter out) throws SQLException {
original.setLogWriter(out);
}
public void setLoginTimeout(int seconds) throws SQLException {
original.setLoginTimeout(seconds);
}
// TF:02/07/2009:We must override the equals and hash code methods to return
// the same values as the originals, so that looking this up in a non-identity
// map will result in the same object. Otherwise a transaction can be started using
// dynamic SQL or straight JDBC and the connection looked up in the Spring
// TransactionSynchronisationManager.getResource, and it will identify the delegating
// data source as a difference data source so think it's not in a transaction.
@Override
public boolean equals(Object obj) {
return original.equals(obj);
}
@Override
public int hashCode() {
return original.hashCode();
}
/* JVM 1.6 methods */
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return original.isWrapperFor(iface);
}
public <T> T unwrap(Class<T> iface) throws SQLException {
return original.unwrap(iface);
}
}
/**
* This constructor allows the selection of the data access pattern to use, as well as the normal parameters
* @param name
* @param ds
* @param txn
* @param pUseSingleDatabaseConnection
*/
public DBConnectionManager(String name, DataSource ds, PlatformTransactionManager txn, boolean pUseSingleDatabaseConnection) {
this(name, ds, txn);
this.useSingleDataConnection = pUseSingleDatabaseConnection;
}
/**
* Obtain the platform transaction manager associated with this data source.
* @return the transaction manager for this data source
*/
public PlatformTransactionManager getTransactionManager() {
return this.transaction;
}
/**
* Call this method to get a new CallableStatement from the DB.
*
* @param call - a String containing the callable statement to execute.
* @return - a CallableStatement.
* @throws SQLException - if there is an error creating the CallableStatement.
*/
public CallableStatement getCallableStatement(String call)
throws SQLException {
TransactionMgr.setTransactionalDataSource(this);
// TF:22/06/2009:Added in proper error handling in order for the conneciton to be released.
// CallableStatement cs = getConnection().prepareCall(call);
Connection conn = this.getConnection();
CallableStatement cs;
try {
cs = conn.prepareCall(call);
}
catch (SQLException sqlE) {
this.releaseConnection(conn);
throw sqlE;
}
return cs;
}
/**
* Call this method to get a new PreparedStatement from the DB.
*
* @param call - a String containing the prepared statement to execute.
* @return - a PreparedStatement.
* @throws SQLException - if there is an error creating the PreparedStatement.
*/
public PreparedStatement getPreparedStatement(String call)
throws SQLException {
return getPreparedStatement(call, false);
}
/**
* Call this method to get a new PreparedStatement from the DB.
*
* @param call - a String containing the prepared statement to execute.
* @param pForUpdate - if this parameter is true, the result set will be scrollable and updateable, otherwise
* it will be scrollable in the forwards direction only and read only. It is more efficient to specify false for this parameter
* @return - a PreparedStatement.
* @throws SQLException - if there is an error creating the PreparedStatement.
*/
public PreparedStatement getPreparedStatement(String call, boolean pForUpdate)
throws SQLException {
TransactionMgr.setTransactionalDataSource(this);
PreparedStatement ps;
// TF:16/07/2008:If we're using a single connection, we need to ensure this is synchronised
// as a single connection is not thread-safe in java
if (useSingleDataConnection) {
synchronized (this) {
CloseTolerantStatement dps = new CloseTolerantStatement(call, this, pForUpdate);
ps = dps.getStatement();
}
}
else {
// TF:03/03/2009:Ensure that if an exception is thrown that the connection is released properly
Connection conn = this.getConnection();
try {
ps = conn.prepareStatement(call);
// TF:13/10/2009:Set the default fetch size for efficiency.
ps.setFetchSize(1000);
}
catch (SQLException sqlE) {
this.releaseConnection(conn);
throw sqlE;
}
}
if (log.isDebugEnabled()) {
log.debug("in getPreparedStatement, the preparedStatement is: " + call);
}
return ps;
}
/**
* Get a PreparedStatement for use with the dynamic SQL. This method will
* translate the SQL from ANSI SQL syntax into the java syntax (eg by
* replacing the placeholder characters with ?), determine the call type
* and the column list.
* <p>
* Callable statements are proxied to intercept the calls. This is needed only
* on dynamic SQL translated from Forte because on these calls to stored
* procedures or functions the output parameters need to be set. However, we don't
* explicitly ever have a chance to do this in the user code, as it was not
* required in Forte. Hence we kludge this by assuming any parameters that are
* not explicitly set in the input statement are output parameters.
* @param pCall
* @param pCmdType
* @param pColList
* @return
* @throws SQLException
*/
public PreparedStatement getDynamicStatement(String pCall, ParameterHolder pCmdType, ParameterHolder pColList) {
return this.getDynamicStatement(pCall, pCmdType, pColList, false);
}
/**
* The PreparedStatementWithResults class is used to allow multiple calls to getOutputDataSet to
* return the same instance of the result data set. This is needed because in JDBC you can call
* getResultSet() only once per executing a query. In order to be able to ensure the same result
* DBDataSet can be returned, this class then needs to cache this information.
* @author Tim
*
*/
private class PreparedStatementWithResults extends PreparedStatementMap {
private DBDataSet results;
public PreparedStatementWithResults(PreparedStatement delegate) {
super(delegate);
}
public DBDataSet getOutputDataSet() {
if (results == null) {
try {
results = new DBDataSet(this, super.getResultSet());
} catch (SQLException e) {
throw DBUtilities.translateSQLException(Constants.DB_VT_UNKNOWN, e);
}
}
return results;
}
@Override
public boolean execute() throws SQLException {
results = null;
return super.execute();
}
@Override
public boolean execute(String sql, int autoGeneratedKeys)
throws SQLException {
results = null;
return super.execute(sql, autoGeneratedKeys);
}
@Override
public boolean execute(String sql, int[] columnIndexes)
throws SQLException {
results = null;
return super.execute(sql, columnIndexes);
}
@Override
public boolean execute(String sql, String[] columnNames)
throws SQLException {
results = null;
return super.execute(sql, columnNames);
}
@Override
public boolean execute(String sql) throws SQLException {
results = null;
return super.execute(sql);
}
@Override
public int[] executeBatch() throws SQLException {
results = null;
return super.executeBatch();
}
@Override
public ResultSet executeQuery() throws SQLException {
results = null;
return super.executeQuery();
}
@Override
public ResultSet executeQuery(String sql) throws SQLException {
results = null;
return super.executeQuery(sql);
}
@Override
public int executeUpdate() throws SQLException {
results = null;
return super.executeUpdate();
}
@Override
public int executeUpdate(String sql, int autoGeneratedKeys)
throws SQLException {
results = null;
return super.executeUpdate(sql, autoGeneratedKeys);
}
@Override
public int executeUpdate(String sql, int[] columnIndexes)
throws SQLException {
results = null;
return super.executeUpdate(sql, columnIndexes);
}
@Override
public int executeUpdate(String sql, String[] columnNames)
throws SQLException {
results = null;
return super.executeUpdate(sql, columnNames);
}
@Override
public int executeUpdate(String sql) throws SQLException {
results = null;
return super.executeUpdate(sql);
}
@Override
public boolean getMoreResults() throws SQLException {
results = null;
return super.getMoreResults();
}
@Override
public boolean getMoreResults(int current) throws SQLException {
results = null;
return super.getMoreResults(current);
}
@Override
public ResultSet getResultSet() throws SQLException {
ResultSetHelper helper = getOutputDataSet().getResultSet();
if (helper != null) {
return helper.getResultSet();
}
return null;
}
}
/**
* Get a PreparedStatement for use with the dynamic SQL. This method will
* translate the SQL from ANSI SQL syntax into the java syntax (eg by
* replacing the placeholder characters with ?), determine the call type
* and the column list.
* <p>
* Callable statements are proxied to intercept the calls. This is needed only
* on dynamic SQL translated from Forte because on these calls to stored
* procedures or functions the output parameters need to be set. However, we don't
* explicitly ever have a chance to do this in the user code, as it was not
* required in Forte. Hence we kludge this by assuming any parameters that are
* not explicitly set in the input statement are output parameters.
* @param pCall
* @param pCmdType
* @param pColList
* @param pForUpdate If this parameter is true, the result set created will be scrollable and updatable, otherwise it will
* be scrollable forwards only and not updatable
* @return
* @throws SQLException
*/
public PreparedStatement getDynamicStatement(String pCall, ParameterHolder pCmdType, ParameterHolder pColList, boolean pForUpdate) {
try {
String sql = DBUtilities.translateSQL(pCall, pCmdType, pColList);
if (sql.startsWith("{? = call ")) {
CallableStatement cs = getCallableStatement(sql);
cs = (CallableStatement) Proxy.newProxyInstance(
ConnectionProxy.class.getClassLoader(),
new Class[] {CallableStatement.class},
new CallableStatementDelegate(cs));
cs.registerOutParameter(1, Types.OTHER);
return new PreparedStatementWithResults(cs);
}
else if (sql.startsWith("{call ")) {
CallableStatement cs = getCallableStatement(sql);
cs = (CallableStatement) Proxy.newProxyInstance(
ConnectionProxy.class.getClassLoader(),
new Class[] {CallableStatement.class},
new CallableStatementDelegate(cs));
return new PreparedStatementWithResults(cs);
}
else {
return new PreparedStatementWithResults(getPreparedStatement(sql, pForUpdate));
}
} catch (SQLException e) {
throw DBUtilities.translateSQLException(Constants.DB_VT_UNKNOWN, e);
}
}
public void cleanup(Statement statement) {
try {
if (statement != null) {
Connection connection = statement.getConnection();
try {
statement.close();
}
catch (Exception e) {}
if (connection != null && !connection.isClosed()) {
// TF:19/9/07:Changed this to use
// DataSourceUtils.releaseConnection();
// connection.close();
this.releaseConnection(connection);
}
}
} catch (SQLException e) {
log.error("Failed to retrieve the connection to close", e);
}
}
/**
* Call this method from a finally block to cleanup all statements, results
* sets and connections.
*
* @param rs -
* an array of results set to clean up.
*/
public void cleanup(ResultSet[] rs) {
for (ResultSet resultSet : rs) {
if (resultSet == null)
continue;
try {
cleanup(resultSet.getStatement());
} catch (SQLException e) {
log.error("Failed to retrieve the connection to close", e);
}
}
}
/**
* Obtain the datasource associated with this connection
* @return the datasource
*/
public DataSource getDataSource() {
return this.ds;
}
/**
* Obtain a JdbcTemplate reference associated with this datasource
* @return a JdbcTemplate that can be used to access the database
*/
public JdbcTemplate getTemplate() {
TransactionMgr.setTransactionalDataSource(this);
return this.template;
}
private Map<Connection, String> openConnections = new HashMap<Connection, String>();
/**
* Get a connection to the database from the connection pool represented by this
* DBConnectionManager. This method may return an existing connection, especially if
* <tt>useSingleDataConnection</tt> is set to true.
* <p>
* This method allows the DBConnectionManager to keep track of the sessions that have been
* returned from the connection pool and initialise them appropriately. For this reason,
* it should be used in preference to <tt>DataSourceUtils.getConnection</tt> (which it
* calls itself to get the actual connection).
* <p>
* If <tt>monitorConnection</tt> is set to true, the passed connection will be monitored to
* see if it's open or not; that is it will be added to a set of connections that are
* maintained which allows easy determination of when the connection was created. Note that
* not all connections can be monitored in this way -- in particular, connections that are
* used by <tt>JdbcTemplate</tt>s are not able to be monitored because there is no easy way
* to be able to intercept their release. The only connections that should be monitored are
* the ones that will use DBConnectionManager.releaseConnection to remove their connections.
* @param monitorConnection
* @return the newly created connection
* @throws CannotGetJdbcConnectionException
*/
private Connection getConnection(boolean monitorConnection) throws CannotGetJdbcConnectionException {
// Check to see if the task has been cancelled
Task.checkTaskCancelled();
// TF:16/07/2008:We need to ensure that a single data manager only ever returns the same
// database connection, and thus load balance the beans in the same way Forte did. This is
// because AXA cache the statements and the standard data access pattern will not work.
if (useSingleDataConnection) {
boolean requireNewConnection = (dataConnection == null);
if (!requireNewConnection) {
// Check to see if we need a new connection due to the connection being closed. This is
// possible as some calls in java such as committing a transaction will automatically close
// the connection and hence invalidate it.
try {
requireNewConnection = dataConnection.isClosed();
}
catch (Exception e) {
// Yep, need a new connection
requireNewConnection = true;
}
}
if (requireNewConnection) {
dataConnection = DataSourceUtils.getConnection(ds);
// TF:23 sept. 2008:Added a check for the session initialiser
if (this.connectionInitialiser != null) {
this.connectionInitialiser.initialise(dataConnection);
}
}
return dataConnection;
}
else {
try {
Connection result = DataSourceUtils.getConnection(ds);
this.initialiseConnection(result, monitorConnection);
return result;
}
catch (RuntimeException e) {
log.error("Error getting a connection to datasource " + this.dataSourceName);
log.error("The exception thrown was: " + e.getMessage(), e);
synchronized (this) {
Set<Connection> keys = openConnections.keySet();
int i = 0;
for (Connection key : keys) {
log.error("Connection[" + (i++) + "] " + key.toString() + " set from " + openConnections.get(key));
}
}
throw e;
}
}
}
/**
* Initialise a new connection. This method will log the connection into the openConnections table if the
* monitorConnection parameter is set to true and the debug is enabled on the log. Additionally, this method
* will see if there are any connection initialisers, and if there are it will invoke the connection
* initialiser on the real connection.
* @param newConnection
* @param monitorConnection
*/
private synchronized void initialiseConnection(Connection newConnection, boolean monitorConnection) {
Connection realConnection = null;
// TF:18/05/2009:Connection optimisation -- only do debugging of connections if the log flag is on
if (monitorConnection && isDebugEnabled) {
boolean debug = false;
// AD:Aug 14, 2008 - Only debug if the new connection and the openConnections size (plus the new connection) is divisible by 10
// TF:26/05/2009:Changed the key to use the real connection, and not the returned connection
realConnection = DataSourceUtils.getTargetConnection(newConnection);
if (openConnections.get(realConnection) == null && ((openConnections.size() + 1) % 10) == 0) {
debug = true;
}
openConnections.put(realConnection, FrameworkUtils.traceBack());
if (debug) {
Set<Connection> keys = openConnections.keySet();
int i = 0;
for (Connection key : keys) {
log.debug("Connection[" + (i++) + "] " + key.toString() + " set from " + openConnections.get(key));
}
}
}
// TF:23 sept. 2008:Added a check for the session initialiser
if (this.connectionInitialiser != null) {
// We must use the real connection and not any proxies to a connection.
if (realConnection == null) {
realConnection = DataSourceUtils.getTargetConnection(newConnection);
}
// Only invoke the initialiser if we haven't seen this connection before
if (!this.sessionList.containsKey(realConnection)) {
this.sessionList.put(realConnection, "");
this.connectionInitialiser.initialise(realConnection);
}
}
}
/**
* Get a connection to the database from the connection pool represented by this
* DBConnectionManager. This method may return an existing connection, especially if
* <tt>useSingleDataConnection</tt> is set to true.
* <p>
* This method allows the DBConnectionManager to keep track of the sessions that have been
* returned from the connection pool and initialise them appropriately. For this reason,
* it should be used in preference to <tt>DataSourceUtils.getConnection</tt> (which it
* calls itself to get the actual connection).
* @return the newly created connection
* @throws CannotGetJdbcConnectionException
*/
public Connection getConnection() throws CannotGetJdbcConnectionException {
return getConnection(true);
}
/**
* Release a connection back to the connection pool (or disconnect it from the database if
* it didn't come from a connection pool). Favour this method over <tt>DataSourceUtils.releaseConnection</tt>
* (which is called behind the scenes anyway) to allow the monitoring of connections. In
* particular, any connection obtained with a call to DBConnectionManager.getConnection
* should be released with this method call.
* @param connection the connection to be released
*/
public void releaseConnection(Connection connection) {
// TF:16/07/2008:If we're using a single data connection, never release the connection back to the pool
if (connection != null && !useSingleDataConnection) {
// TF:18/05/2009:If we're not debugging then the openConnections is not used, so we can skip this.
// Note that we cannot dynamically check the flag, otherwise the user might change the value on the
// fly, and unfortunate timing could result in a memory leak.
if (isDebugEnabled) {
synchronized(this) {
// TF:26/05/2009:Changed this to use the target connection, otherwise it's possible for the
// connection used to store the stack trace and the connection used to remove the stack trace
// to be different to one another, as one could be a proxy.
this.openConnections.remove(DataSourceUtils.getTargetConnection(connection));
}
}
DataSourceUtils.releaseConnection(connection, this.ds);
}
}
/**
* The DatabaseName attribute is a read-only attribute that contains the
* name of the database to which the session is connected. This is the
* unique name of the database, not the name of the Resource Manager.
*
* @return the database name
*/
public String getDatabaseName() {
return this.dbName;
}
/**
* The UserName attribute is a read-only attribute that contains
* the user name under which this session is operating.
*
* @return the userName
*/
public String getUserName() {
Connection c = this.getConnection();
try {
return c.getMetaData().getUserName();
}
catch (Exception e) {
log.error("Could not determine database user", e);
}
finally {
releaseConnection(c);
}
return "";
}
// TF:16/07/2008:Cache the database vendor type
private int databaseType = -1;
/**
* The DBVendorType attribute is a read-only attribute that contains a
* constant indicating the type of database interface to which the session
* is connected. This attribute is useful for writing portable code with
* conditional sections.
*
* @return an int with on the of the following constant values:
* <UL>
* <LI><B>DB_VT_DB2</B>
* <LI><B>DB_VT_INFORMIX</B>
* <LI><B>DB_VT_ODBC</B>
* <LI><B>DB_VT_ORACLE</B>
* <LI><B>DB_VT_RDB</B>
* <LI><B>DB_VT_SYBASE</B>
* </UL>
*/
public int getDBVendorType(){
if (databaseType >= 0) {
return databaseType;
}
Connection connection = null;
try {
int type = -1;
connection = getConnection();
// TF:09/12/2009:DET-142:Made this case insensitive
String product = connection.getMetaData().getDatabaseProductName().toLowerCase();
if (product.indexOf("sybase")!= -1)
type = Constants.DB_VT_SYBASE;
else if (product.indexOf("db2")!= -1)
type = Constants.DB_VT_DB2;
else if (product.indexOf("informix")!= -1)
type = Constants.DB_VT_INFORMIX;
else if (product.indexOf("ingress")!= -1)
type = Constants.DB_VT_INGRES;
else if (product.indexOf("rdb")!= -1)
type = Constants.DB_VT_RDB;
else if (product.indexOf("sqlserver")!= -1)
type = Constants.DB_VT_ODBC;
else if (product.indexOf("oracle")!= -1)
type = Constants.DB_VT_ORACLE;
databaseType = type;
return type;
} catch (SQLException e) {
throw new DataAccessResourceFailureException("Cannot get Vendor Type", e);
} finally {
releaseConnection(connection);
}
}
public void reconnect(){
// this method is a stub only as J2EE will get connections as required
}
public boolean isConnected(){
// this method is a stub only as J2EE will get connections as required
return true;
}
public void setConnected(boolean value){
// this method is a stub only as J2EE will get connections as required
}
/**
* The GetDatabaseDesc method returns a DBDatabaseDesc object which
* describes a collection of database tables. Gets only columns by default.
*
*
* @param tableNamePattern
* The tableNamePattern parameter specifies the search pattern
* that is used to select the table names. You can use any
* standard SQL search pattern that can be used in a LIKE clause
* of a SQL select statement. If a pattern is not specified or is
* empty, then all tables accessible by the session in the
* current database, schema, and catalog are returned.
* @return a DBDatabaseDesc object which describes a collection of database
* tables.
*/
public DBDatabaseDesc getDatabaseDesc(String tableNamePattern){
return getDatabaseDesc(tableNamePattern, Constants.DB_IN_COLUMNINFO + Constants.DB_IN_KEYINFO + Constants.DB_IN_FOREIGNKEYINFO);
}
/**
* The GetDatabaseDesc method returns a DBDatabaseDesc object which
* describes a collection of database tables.
*
*
* @param tableNamePattern
* The tableNamePattern parameter specifies the search pattern
* that is used to select the table names. You can use any
* standard SQL search pattern that can be used in a LIKE clause
* of a SQL select statement. If a pattern is not specified or is
* empty, then all tables accessible by the session in the
* current database, schema, and catalog are returned.
* @param info
* The info parameter specifies what type of information to
* include. The following legal values form a bitmask and can be
* added together:
* <UL>
* <LI><B>DB_IN_COLUMNINFO</B> returns information about each
* column for each table
* <LI><B>DB_IN_KEYINFO</B> returns information about each key
* of each table
* <LI><B>DB_IN_FOREIGNKEYINFO</B> returns information about
* each foreign key of each table
* </UL>
* @return a DBDatabaseDesc object which describes a collection of database
* tables.
*/
public DBDatabaseDesc getDatabaseDesc(String tableNamePattern, int info){
if ((info & Constants.DB_IN_COLUMNINFO) == 0) {
throw new UsageException("You must specify DB_IN_COLUMNINFO in the info parameter if you specify either DB_IN_KEYINFO or DB_IN_FOREIGNKEYINFO.",
SP_ER_PARAMETERERROR, SP_ER_USER, UsageException.qq_Resolver.cMESSAGE_REASONCODE_SEVERITY);
}
Connection connection = null;
try {
connection = this.getConnection();
DatabaseMetaData meta = connection.getMetaData();
DBDatabaseDesc dbd = new DBDatabaseDesc( meta.getURL(), getDBVendorType(), this.dbName);
ResultSet rs = meta.getTables(null, null, tableNamePattern, new String[] {"TABLE"});
while(rs.next()){
DBTableDesc tabdesc = new DBTableDesc(
rs.getString("TABLE_CAT"),
rs.getString("TABLE_NAME"),
rs.getString("TABLE_SCHEM"),
(rs.getString("TABLE_TYPE").equals("VIEW")) ? true : false);
// Get Columns
ResultSet cols = meta.getColumns(tabdesc.getCatalogName(), tabdesc.getSchemaName(), tabdesc.Name, null);
while (cols.next()){
DBColumnDesc aCol = new DBColumnDesc(
cols.getInt("COLUMN_SIZE"),
0,
cols.getShort("DATA_TYPE"),
cols.getString("COLUMN_NAME"),
(cols.getInt("NULLABLE") == 0) ? false : true,
cols.getInt("COLUMN_SIZE")
);
tabdesc.Columns.add(aCol);
}
cols.close();
// Get Primary Keys if requested
if ((info & Constants.DB_IN_KEYINFO) != 0) {
// initialize the keys
tabdesc.setKeys(new Array_Of_DBKeyDesc<DBKeyDesc>());
// get the columns for the primary key
ResultSet primaryKeys = meta.getPrimaryKeys(tabdesc.getCatalogName(), tabdesc.getSchemaName(), tabdesc.Name);
DBKeyDesc aKey = null;
while (primaryKeys.next()) {
if (aKey == null) {
// Create and add the key if it doesn't exist
aKey = new DBKeyDesc(primaryKeys.getString("PK_NAME"), true, true);
tabdesc.getKeys().add(aKey);
}
aKey.columns.add(tabdesc.findColumn(primaryKeys.getString("COLUMN_NAME")));
}
primaryKeys.close();
}
dbd.Tables.add(tabdesc);
}
rs.close();
// Get Foreign Keys if requested
// This section of code is in a separate loop because all the tables
// need to be populated first.
if ((info & Constants.DB_IN_FOREIGNKEYINFO) != 0) {
for (DBTableDesc tabdesc : dbd.Tables) {
// initialize the keys
tabdesc.setForeignKeys(new Array_Of_DBForeignKeyDesc<DBForeignKeyDesc>());
// get the keys
ResultSet foreignKeys = meta.getImportedKeys(tabdesc.catalogName, tabdesc.schemaName, tabdesc.Name);
DBForeignKeyDesc aKey = null;
DBTableDesc foreignTableDesc = null;
while (foreignKeys.next()) {
if (aKey == null || !aKey.name.equals(foreignKeys.getString("FK_NAME")) ) {
// Create and add the key if it doesn't exist or is different from the previous one
foreignTableDesc = dbd.findTable(foreignKeys.getString("PKTABLE_NAME"));
aKey = new DBForeignKeyDesc(foreignKeys.getString("FK_NAME"), false, false, foreignTableDesc);
tabdesc.ForeignKeys.add(aKey);
}
aKey.columns.add(tabdesc.findColumn(foreignKeys.getString("FKCOLUMN_NAME")));
if (foreignTableDesc != null) {
// Only add the Foreign Columns if they the foreign table was found.
// This is the same as Forte
aKey.foreignColumns.add(foreignTableDesc.findColumn(foreignKeys.getString("PKCOLUMN_NAME")));
}
}
foreignKeys.close();
}
}
return dbd;
} catch (SQLException e) {
throw new DataAccessResourceFailureException("Error getting Database description", e);
} catch (RuntimeException e1){
throw new DataAccessResourceFailureException("Error getting Database description", e1);
} finally {
releaseConnection(connection);
}
}
public String getServerName(){
return FrameworkUtils.getNodeName().toString();
}
/**
* Ends the database session.
*
*/
public void disconnect(){
}
/**
* Destroy the connection, closeing the underlying data source. This method only has effect on connections
* created using {@link #createInstance(String, ConnectionProperties)}
*/
public void destroy() {
if (this.ds instanceof SingleConnectionDataSource) {
SingleConnectionDataSource scds = (SingleConnectionDataSource)this.ds;
scds.destroy();
}
}
/**
* Establish a connection to a database.
* @param databaseName
* @param userName
* @param userPassword
*/
public DBConnectionManager connect(TextData databaseName, String userName, String userPassword){
return this.connect(databaseName.toString(), userName, userPassword);
}
/**
* The ConnectDB method starts a new database session.
* @param databaseName
* @param userName
* @param userPassword
*/
public DBConnectionManager connectDB(String databaseName, String userName, String userPassword){
return this.connect(databaseName, userName, userPassword);
}
public DBConnectionManager connectDB(TextData databaseName, TextData userName, TextData userPassword){
return this.connect(databaseName.asString(), userName.asString(), userPassword.asString());
}
public DBConnectionManager connectDB(TextData database, TextData userName, TextData userPassword, Array_Of_NamedElement<NamedElement> options) {
return this.connect(database.toString(), userName.toString(), userPassword.toString());
}
/**
* Establish a connection to a database. This is a stub method only as
* connection pools are used.
* @param databaseName
* @param userName
* @param userPassword
*/
public DBConnectionManager connect(String databaseName, String userName, String userPassword){
return this;
}
public DBConnectionManager connect(String databaseName, String userName, String userPassword, Array_Of_NamedElement<NamedElement> options){
return this;
}
/**
* this method has no implementation
* @param statementHandle
* @param resultDataSet
*/
public void setResultSizes(PreparedStatement statementHandle, DBDataSet resultDataSet){
}
/**
* The DescribeTable method returns a DBDataSet object that describes the specified table.
* The table description, provided by your DBMS, is useful for preparing to display or store data that will be selected from a database.
* For example, you can use the table description to create a form on which to display data from the database.
* @param TableName
* @return
*/
public DBDataSet describeTable(TextData TableName){
return this.describeTable(TableName.asString());
}
public DBDataSet describeTable(String TableName){
DBDataSet set;
Connection connection = this.getConnection();
try {
set = new DBDataSet(TableName, connection.getMetaData());
return set;
} catch (SQLException e) {
throw new DataAccessResourceFailureException("Error getting database Table description on table: " + TableName, e);
}
finally {
releaseConnection(connection);
}
}
public Array_Of_TextData<TextData> getTableList(TextData string) {
return getTableList(string.toString());
}
/**
* Get a list of the tables which matches the passed name
* @param string
* @return
*/
public Array_Of_TextData<TextData> getTableList(String pName) {
Array_Of_TextData<TextData> tableNames = new Array_Of_TextData<TextData>();
Connection connection = null;
try {
connection = this.getConnection();
ResultSet rs = connection.getMetaData().getTables(null, null, pName, null);
while (rs.next()) {
tableNames.add(new TextData(rs.getString(3)));
}
} catch (SQLException e) {
throw new UsageException(e);
} finally {
releaseConnection(connection);
}
return tableNames;
}
/**
* For callable statements (ie stored procedures) if we're accessing them dynamically instead of
* statically we have a problem: Java requires that the output parameters are marked as such
* prior to executing the call, however there is no information in Forte which describes which
* parameters are input and which are output parameters.<p>
* <p>
* To solve this, we create a proxy that delegates through to a normal callable statement. When the
* execute method is called, we see which parameters have had their values set and assume that any
* values that have not been set are output parameters. Hence, we go through and set these values
* as outputs.<p>
* <p>
* This method currently makes no attempt to process the return value for stored functions or optional
* parameters.
* @author Tim
*/
public class CallableStatementDelegate implements InvocationHandler {
/**
* the statement we will delgate through to
*/
private CallableStatement callableStmt;
/**
* The meta data associated with the statement
*/
private ParameterMetaData paramMetaData = null;
/**
* An array to indicate whether the param is set.
*/
private boolean[] paramsSet = null;
public CallableStatementDelegate(CallableStatement cs) {
this.callableStmt = cs;
try {
this.paramMetaData = cs.getParameterMetaData();
this.paramsSet = new boolean[this.paramMetaData.getParameterCount()];
Arrays.fill(this.paramsSet, false);
}
catch (Exception e) {}
// If there's any problems, we just ignore them because we have no choice.
// Methods later on in this class can handle if these have not been set properly
}
/**
* Process the methods, mostly delegating through to the real statement
*/
public Object invoke(Object proxy, Method method, Object[] args) throws SQLException, Throwable {
if (method.getName().equals("execute")) {
// for any parameters that have not been set, register them as output parameters, type unknown
if (this.paramMetaData != null) {
int count = this.paramMetaData.getParameterCount();
for (int i = 1; i <= count; i++) {
switch (this.paramMetaData.getParameterMode(i)) {
case ParameterMetaData.parameterModeIn:
break;
case ParameterMetaData.parameterModeOut:
case ParameterMetaData.parameterModeInOut:
this.callableStmt.registerOutParameter(i, this.paramMetaData.getParameterType(i));
break;
default:
// We don't know what the mode of the parameter is, so we need to use the boolean
// set; if it hasn't been set, we assume it's an output parameter
if (!paramsSet[i-1]) {
if (log.isDebugEnabled()) {
log.debug("Marking parameter " + i + " as an output parameter");
}
this.callableStmt.registerOutParameter(i, this.paramMetaData.getParameterType(i));
}
}
}
}
boolean result = ((Boolean)method.invoke(callableStmt, args)).booleanValue();
if (!result) {
// Stored procedures can return result sets or update counters, we only want to
// return results sets, not update counters
do {
if (callableStmt.getMoreResults()) {
// Ok, there really are results sets, return true
return new Boolean(true);
}
} while (callableStmt.getUpdateCount() != -1);
return Boolean.valueOf(false);
}
return Boolean.valueOf(true);
}
else if (method.getName().equals("getMoreResults")) {
// We cannot return update counters, only results sets or false
while (true) {
try {
if (this.callableStmt.getMoreResults()) {
return true;
} else if (callableStmt.getUpdateCount() == -1) {
return false;
}
}
catch (IllegalStateException ise) {
// We're in an illegal state (probably called getMoreResults too many times)
// so just return false
return false;
}
}
}
else if (method.getName().startsWith("set") && args != null && args.length == 2 && args[0].getClass().equals(Integer.class)) {
// It's one of the set statements to set one of the input values. We
// need to mark this parameter as set.
paramsSet[((Integer)args[0]).intValue()-1] = true;
}
return method.invoke(callableStmt, args);
}
}
// TF:16/07/2008:Cache the database product name
private String databaseName = null;
/**
* Returns the Database Management Systems Name
* @return String The DBMS Name
*/
public String getDatabaseProductName(){
// TF:19/9/07:Added this method
if (databaseName != null) {
return databaseName;
}
Connection connection = getConnection();
try {
String result = connection.getMetaData().getDatabaseProductName();
databaseName = result;
return result;
} catch (SQLException e) {
throw new DataAccessResourceFailureException("Error getting database product name", e);
}
finally {
releaseConnection(connection);
}
}
/**
* The GetDatabaseDesc method returns a DBDatabaseDesc object which
* describes a collection of database tables.
*
*
* @param tableNamePattern
* The tableNamePattern parameter specifies the search pattern
* that is used to select the table names. You can use any
* standard SQL search pattern that can be used in a LIKE clause
* of a SQL select statement. If a pattern is not specified or is
* empty, then all tables accessible by the session in the
* current database, schema, and catalog are returned.
* @param info
* The info parameter specifies what type of information to
* include. The following legal values form a bitmask and can be
* added together:
* <UL>
* <LI><B>DB_IN_COLUMNINFO</B> returns information about each
* column for each table
* <LI><B>DB_IN_KEYINFO</B> returns information about each key
* of each table
* <LI><B>DB_IN_FOREIGNKEYINFO</B> returns information about
* each foreign key of each table
* </UL>
* @return a DBDatabaseDesc object which describes a collection of database
* tables.
*/
public DBDatabaseDesc getDatabaseDesc(TextData tablename, int dbInColumninfo) {
return this.getDatabaseDesc(TextData.valueOf(tablename), dbInColumninfo);
}
/**
* The GetDatabaseDesc method returns a DBDatabaseDesc object which
* describes a collection of database tables.
*
*
* @param tableNamePattern
* The tableNamePattern parameter specifies the search pattern
* that is used to select the table names. You can use any
* standard SQL search pattern that can be used in a LIKE clause
* of a SQL select statement. If a pattern is not specified or is
* empty, then all tables accessible by the session in the
* current database, schema, and catalog are returned.
* @param info
* The info parameter specifies what type of information to
* include. The following legal values form a bitmask and can be
* added together:
* <UL>
* <LI><B>DB_IN_COLUMNINFO</B> returns information about each
* column for each table
* <LI><B>DB_IN_KEYINFO</B> returns information about each key
* of each table
* <LI><B>DB_IN_FOREIGNKEYINFO</B> returns information about
* each foreign key of each table
* </UL>
* @return a DBDatabaseDesc object which describes a collection of database
* tables.
*/
public DBDatabaseDesc getDatabaseDesc(TextData tablename) {
return this.getDatabaseDesc(TextData.valueOf(tablename), DB_IN_COLUMNINFO | DB_IN_FOREIGNKEYINFO | DB_IN_KEYINFO);
}
/**
* The GetDatabaseDesc method returns a DBDatabaseDesc object which
* describes a collection of database tables.
*
*
* @return a DBDatabaseDesc object which describes a collection of database
* tables.
*/
public DBDatabaseDesc getDatabaseDesc() {
return this.getDatabaseDesc((String)null);
}
/**
* The DBMSName attribute that contains the name of the vendor for the DBMS
* connected to this session. Examples are Microsoft SQL Server or ACCESS.
*
* @return DMBSName
*/
public String getDBMSName() {
return this.getDatabaseProductName();
}
/**
* The openCursor method executes the prepared select or execute procedure statement and positions the cursor before the
* first row in the result set.
* <p>
* @return The return value of the OpenCursor method indicates the result type of the select or execute procedure statement.
* The values of the result type are the following:
* <table>
* <tr><th>Value</th><th>Description</th></tr>
* <tr><td>DB_RS_ROW</td><td>The result set is rows from a select statement or first result set from a procedure.</td></tr>
* <tr><td>DB_RS_NONE</td><td>There are no result sets.</td></tr>
* </table>
* @param statement - The statement parameter identifies the SQL statement you prepared with the <tt>prepare</tt> method,
* which is also the <tt>PreparedStatement</tt> object returned from the <tt>prepare</tt> method. Note that this must be a
* statement of type DB_CV_SELECT or DB_CV_EXECUTE.
*
* @param inputDataSet - The inputDataSet parameter specifies a set of values to use for placeholder substitution. See the
* <tt>prepare</tt> method for information about the inputDataSet parameter. See DBDataSet for information about the methods
* for setting the values of the DBDataSet object.
*
* @param resultDataSet - The resultDataSet parameter (output) is a holder to the DBDataSet object that describes the data
* that will be returned from the database. This is useful for dynamically displaying the data to the end user. The data
* itself is returned by the FetchCursor method.
*/
public int openCursor(PreparedStatement statement, DBDataSet inputDataSet, ParameterHolder resultDataSet) {
try {
if (statement.execute()) {
DBDataSet resultSet;
if (statement instanceof PreparedStatementWithResults) {
resultSet =((PreparedStatementWithResults)statement).getOutputDataSet();
}
else {
resultSet = new DBDataSet(statement, statement.getResultSet(), true);
}
resultDataSet.setObject(resultSet);
return Constants.DB_RS_ROW;
}
else {
resultDataSet.setObject(null);
return Constants.DB_RS_NONE;
}
} catch (SQLException e) {
throw DBUtilities.translateSQLException(Constants.DB_VT_UNKNOWN, e);
}
}
public int fetchCursor(PreparedStatement statement, ParameterHolder resultDataSet, int maxRows) {
// Set a hint to the database about how many rows to return
if (maxRows > 1) {
try {
statement.setFetchSize(maxRows);
}
catch (Exception e) {
// Do nothing, it's just a hint...
}
}
// Now we need to get the output set. If it's a PreparedStatementWithResults, use this to get the results
DBDataSet resultSet;
if (statement instanceof PreparedStatementWithResults) {
resultSet =((PreparedStatementWithResults)statement).getOutputDataSet();
}
else {
try {
resultSet = new DBDataSet(statement, statement.getResultSet(), true);
} catch (SQLException e) {
throw DBUtilities.translateSQLException(Constants.DB_VT_UNKNOWN, e);
}
}
resultDataSet.setObject(resultSet);
return resultSet.fetch(maxRows);
}
public int fetchCursor(PreparedStatement statement, ParameterHolder resultDataSet) {
return this.fetchCursor(statement, resultDataSet, 1);
}
}