In the context of a java application using SQLIte to persist data I am using the Zentus JDBC driver. Thus I am using the java.sql package to acces my database.
I am facing some strange (in a an environment with several Connection objects on the same database) issues and I am pretty sure my problems come from non closed ResultSet.
Is there any tool or technique allowing me to spot where to look in my source code to find these non closed objects ?
Edit May be using AspectJ ??
It seems like an aspect may be helpful.
How about wrapping the methods which return a result set in an aspect. Something like:
execution(public java.sql.ResultSet+ java.sql.Statement+.*(..))
Another aspect can monitor the close method on ResultSets. Perhaps:
execution(public * java.sql.ResultSet.close())
The first aspect would, on the return of every ResultSet, create a new Exception object and store it in a static Map somewhere using the hash of the ResultSet as the key. The second aspect, on the closing of the result set, would remove the Exception from the Map using the same hashcode as a key. At any time, the map should have one exception instance for every open ResultSet. From the exception you can obtain a stack trace to see where the ResultSet was opened.
You could perhaps store a larger object which includes an exception and some other contextual information; time that the ResultSet was created, etc.
A practical suggestion is to add some debug code and "log" creation and closing of resultsets to a csv file. Later on you could examine this file and check, if there's a "close" entry for each "create".
So, assuming you have a utility class with static methods that allows writing Strings to a file, you can do it like this:
ResultSet rs = stmt.executeQuery(query);
Util.writeln(rs.hashcode() + ";create"); // add this line whenever a
// new ResultSet is created
and
rs.close();
Util.writeln(rs.hashcode() + ";closed"); // add this line whenever a
// ResultSet is closed
Open the csv file with Excel or any other spread sheet program, sort the table and look if result sets are not closed. If this is the case, add more debug information to clearly identify the open sets.
BTW - Wrapping the interfaces (like JAMon) is pretty easy, if you have eclipse or something else, its coded in less then 15 Minutes. You'd need to wrap Connection, Statement (and PreparedStatement?) and ResultSet, the ResultSet wrapper could be instrumented to track and monitor creation and closing of result sets:
public MonitoredConnection implements Connection {
Connection wrappedConnection = null;
public MonitoredConnection(Connection wrappedConnection) {
this.wrappedConnection = wrappedConnection;
}
// ... implement interface methods and delegate to the wrappedConnection
@Override
public Statement createStatement() {
// we need MonitoredStatements because later we want MonitoredResultSets
return new MonitoredStatement(wrappedConnection.createStatemet());
}
// ...
}
The same for MonitoredStatement and MonitoredResultSet (MonitoredStatement will return wrapped ResultSets):
public MonitoredStatement implements Statement {
private Statement wrappedStatement = null;
@Override
public ResultSet executeQuery(String sql) throws SQLException
MonitoredResultSet rs = wrappedStatement.executeQuery(sql);
ResultSetMonitor.create(rs.getWrappedResultSet()); // some static utility class/method
return rs;
}
// ...
}
and
public MonitoredResultSet implements ResultSet {
private ResultSet wrappedResultSet;
@Override
public void close() {
wrappedResultSet.close();
ResultSetMonitor.close(wrappedResultSet); // some static utility class/method
}
// ...
}
At the end, you should only need to modify a single line in your code:
Connection con = DriverManager.getConnection(ur);
to
Connection con = new MonitoredConnection(DriverManager.getConnection(ur));
A Google Search pointed me directly to JAMon. It allows you to also monitor JDBC connections and cursors.
Personally, I would check the code and make sure that all Statement, PreparedStatement and ResultSet are closed when not needed. Even when using Connection Pooling, only JDBC Connection are returned into the pool and statements and ResultSet are closed.
This example shows how I achieve closing ResultSet and PreparedStatement in the finally close (for guarantee):
PreparedStatement ps = null;
ResultSet rs = null;
UserRequest request = null;
try {
ps = getConnection().prepareStatement(SQL_RETRIEVE);
ps.setLong(1, id);
rs = ps.executeQuery();
if (rs != null && rs.next()) {
request = mapEntity(rs);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new DAOException(e);
} finally {
try {
close(rs, ps);
} catch (SQLException e) {
// TODO Auto-generated catch block
logger.error("Error closing statement or resultset.", e);
}
}
That's my 2 cents worth...hope it helps you.
It should be relatively simple to instrument your code with AOP of your choice. I was using AspectWerkz number of years ago to do load-time weaving of web app and collecting performance related statistics. Also if you're using IOC framework, such as Spring it's very easy to wrap your DataSources and trace calls to getConnection() etc.
