Has anyone used any alternatives to the DBCP with Tomcat because it seems
to get worse and worse as I use it more. For no apparent reason on one
server, if there is a failure of a query to clean up its resources
properly then when it it cleaned up by the finalizer method of our query
object it actually closes the connection and then DBCP doesnt open any
more even though it should, so any connection attempt ends up saying
"Connection is closed". This is obviously a bit of a problem. I can only
assume that the problem lies in the combination of DBCP and my code.
Obviously I know that we shouldnt ever use a finalizer to be cleaning
stuff up but this is is development so allows us to catch where this
occurs in code. The code for our Query object is included here.
The cleanUp method works fine normally it seems it is just when called by
a finalizer that it goes wrong (although I suppose it could be caused by a
SQL Exception in the back end of the system?). We're using INet
Software's Sprinta2000 SQL Server 2000 JDBC driver if that of any
importance.
cheers
Pete
/*
* Query.java
*
* Created on 27 May 2002, 15:20
*/
package com.ktdev.sql;
import javax.sql.*;
import java.sql.*;
import javax.naming.*;
import java.io.*;
import java.util.*;
import com.ktdev.errors.GetStackTrace;
import org.apache.log4j.Logger;
/**
*
* @author Dominic Bevacqua
* @version
*
* Wrapper class for java.sql.Statement and java.sql.CallableStatement
*
*/
public class Query {
static Logger log = Logger.getLogger(Query.class);
/** static variables **/
private static Hashtable JNDIEnv=null;
/** non-static variables **/
private Connection conn;
private Vector statements = new Vector();
private Vector resultSets = new Vector();
private Statement currentStmt;
private String callingMethodStackTrace = "";
private String dataSourceName;
private String commandText;
private boolean isUpdate=false;
private boolean isCallable=true;
private boolean useServerCursor=false;
private boolean returnsMultipleResultSets=false;
private int resultSetType = java.sql.ResultSet.TYPE_FORWARD_ONLY;
private int resultSetConcurrency =
java.sql.ResultSet.CONCUR_READ_ONLY;
/** Constructors **/
// should we provide no args constructor? no real need for it but...
public Query() {
// This simply gets the stack that called the query so if it is
not cleaned up properly then we can report it.
// Only run if it is debug mode.
if (log.isDebugEnabled())
callingMethodStackTrace =
GetStackTrace.getStackTraceAsString();
}
public Query(String dataSourceName) {
if (log.isDebugEnabled()) {
log.debug("Creating a Query connection to " + dataSourceName);
callingMethodStackTrace =
GetStackTrace.getStackTraceAsString();
}
this.setDataSourceName(dataSourceName);
this.openJNDIConnection();
}
/** Create new Query with specified commandText on specified
dataSourceName **/
public Query(String commandText, String dataSourceName) {
this(dataSourceName);
this.setCommandText(commandText);
this.createStatement();
this.execute();
log.debug("Query created and run with commandText = " +
commandText );
}
// In case someone does not want to use JNDI to get connection...
public Query(String url, String username, String password, String
driver) {
if (log.isDebugEnabled()) {
log.debug("Creating a Query using JDBC parameters.");
callingMethodStackTrace =
GetStackTrace.getStackTraceAsString();
}
try {
Class.forName(driver);
conn = DriverManager.getConnection(url , username, password);
} catch (ClassNotFoundException ce) {
// throw exception
throw new QueryException(ce.getMessage());
} catch (SQLException se) {
// throw exception
throw new QueryException( se );
}
}
// Better way of doing it...
private void openJNDIConnection(){
try {
// Define JNDI InitialContext object.
InitialContext ctx = new InitialContext(JNDIEnv);
// Look up data source in InitialContext.
DataSource ds = (DataSource)ctx.lookup(dataSourceName);
// get connection from pool
conn = ds.getConnection();
} catch (javax.naming.NamingException ne) {
log.error("Caught a NamingException trying to get a Connection
from JNDI.", ne);
throw new QueryException(ne.getMessage());
} catch (SQLException se) {
log.error("Caught a SQLException trying to get a Connection
from JNDI.", se);
throw new QueryException( se );
}
}
public void createStatement() {
// First clear the current statement as we are starting a new
statement
currentStmt = null;
try {
Statement stmt;
// is this a call for a stored procedure?
if(isCallable) {
// it is, so the statement should be a callable statement
log.debug("Statement is callable");
// Create the statement, add it to the Vector then set as
the currentStatement
stmt = conn.prepareCall(commandText, resultSetType,
resultSetConcurrency);
statements.add(stmt);
currentStmt = stmt;
} else {
// it isn't, so the statement should be a simple statement
log.debug("Statement is not callable");
stmt = conn.createStatement(resultSetType,
resultSetConcurrency);
statements.add(stmt);
currentStmt = stmt;
}
// does the cursor name have to be globally unique? or unique
for the connection?
if (useServerCursor) stmt.setCursorName("");
} catch (SQLException se) {
// Clean up then throw exception
log.error("SQLException thrown when creating a statement for "
+ commandText, se);
try {
this.cleanUp();
} catch (Exception cleanUpException) {
log.error("Exception thrown when cleaning up the query
after an exception was thrown in createstatement for " + commandText,
cleanUpException);
}
throw new QueryException( se );
}
}
public void execute() {
try {
// are there multiple result sets? is this an update?
if (returnsMultipleResultSets) {
log.debug("Executing, expecting multiple result sets");
currentStmt.execute(commandText);
} else if (isUpdate) {
log.debug("Executing, expecting an update");
currentStmt.executeUpdate(commandText);
} else {
log.debug("Execute the query");
currentStmt.executeQuery(commandText);
}
} catch (SQLException se) {
// Clean up then throw exception
log.error("SQLException thrown when executing " + commandText,
se);
try {
this.cleanUp();
} catch (Exception cleanUpException) {
log.error("Exception thrown when cleaning up the query
after an exception was thrown executing " + commandText,
cleanUpException);
}
throw new QueryException( se );
}
}
/**
* Close any open ResultSets and Statements, then return connection
to the pool
*/
public void cleanUp() {
log.debug("Cleaning up Query :" + this.toString() + " with
commandText = " + commandText );
cleanUpResultSets();
cleanUpStatements();
closeConn();
}
/**
* Close any open ResultSets
*/
public void cleanUpResultSets() {
Iterator i = resultSets.iterator();
while (i.hasNext()) {
try {
ResultSet rs = (ResultSet) i.next();
if (rs != null)
rs.close();
} catch (SQLException se) {
// throw exception
log.error("SQLException closing a resultset from the
vector",se);
}
}
resultSets = null;
}
/**
* Close any open ResultSets
*/
public void cleanUpStatements() {
Iterator i = statements.iterator();
while (i.hasNext()) {
try {
Statement stmt = (Statement) i.next();
if (stmt != null)
stmt.close();
} catch (SQLException se) {
// throw exception
log.error("SQLException closing a statement from the
vector",se);
}
}
statements = null;
}
/**
* Return connection to pool
**/
public void closeConn() {
log.debug("Closing the connection");
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
log.error("SQLException thrown closing a connection",se);
} finally {
conn = null;
}
}
/**
* Accessor methods
**/
/** get resultSet **/
public ResultSet getResultSet() {
try {
// Get the resultset, add to the vector then return.
ResultSet rs = currentStmt.getResultSet();
resultSets.add(rs);
return rs;
} catch (SQLException se) {
// throw exception
log.error("SQLException thrown getting the resultset from the
current statement",se);
throw new QueryException( se );
}
}
/** get statement **/
public Statement getStatement() {
return currentStmt;
}
/** get and set commandText (so-called because it is not necessarily
SQL) **/
public void setCommandText(String commandText){
this.commandText = commandText;
}
public String getCommandText() {
return this.commandText;
}
/** get and set dataSourceName **/
public void setDataSourceName(String dataSourceName){
this.dataSourceName = dataSourceName;
}
public String getDataSourceName() {
return this.dataSourceName;
}
/** get and set isCallable **/
public void setIsCallable(boolean isCallable){
this.isCallable = isCallable;
}
public boolean getIsCallable(){
return this.isCallable;
}
/** get and set isUpdate **/
public void setIsUpdate(boolean isUpdate){
this.isUpdate = isUpdate;
}
public boolean getIsUpdate(){
return this.isUpdate;
}
/** get and set useServerCursor **/
public void setUseServerCursor(boolean useServerCursor){
this.useServerCursor = useServerCursor;
}
public boolean getUseServerCursor(){
return this.useServerCursor;
}
/** get and set resultSetType **/
public void setResultSetType(int resultSetType){
this.resultSetType = resultSetType;
}
public int getResultSetType(){
return this.resultSetType;
}
/** get and set resultSetConcurrency **/
public void setResultSetConcurrency(int resultSetConcurrency){
this.resultSetConcurrency = resultSetConcurrency;
}
public int getResultSetConcurrency(){
return this.resultSetConcurrency;
}
/** get and set returnsMultipleResultSets **/
public void setReturnsMultipleResultSets(boolean
returnsMultipleResultSets){
this.returnsMultipleResultSets = returnsMultipleResultSets;
}
public boolean getReturnsMultipleResultSets(){
return this.returnsMultipleResultSets;
}
/** get and set methods for JNDIEnv **/
public Hashtable getJNDIEnv() {
return JNDIEnv;
}
public static void setJNDIEnv( Hashtable JNDIEnv ) {
Query.JNDIEnv = JNDIEnv;
}
/** If it is not cleaned up properly then clean up when being garbage
collected */
public void finalize() {
if (conn != null) {
String msg = ("".equals(callingMethodStackTrace)
? "Enable DEBUG logging for a stack trace of
the calling method. Last command was " + commandText
: "Last command was " + commandText + "\n
Executing stack was: \n" + callingMethodStackTrace);
log.fatal("Code error - Query was not cleaned up by the
calling code. " + msg);
this.cleanUp();
}
}
}
Kiss Technologies
http://www.kisstechnologies.co.uk/
Please note, we have moved!
4, Percy Street
London
W1T 1DF
New permanent phone numbers:
Phone 020 7692 9922
Fax 020 7692 9923
