Is there any tool or method for identifying an open ResultSet

Question

In the context of a Java application using SQLIte to save data, I use the Zentus JDBC Driver. Thus, I use the java.sql package to access my database.

I came across something strange (in an environment with multiple Connection objects in the same database), and I'm sure that my problems arise from a non-closed ResultSet.

Is there any tool or technique that allows me to determine where to look for the source code to find these non-closed objects?

Change Can I use AspectJ ??

Answer 1

It seems that this aspect may be useful.

How to wrap methods that return a result set to an aspect. Sort of:

execution(public java.sql.ResultSet+ java.sql.Statement+.*(..)) 

Another aspect can control the closure method in ResultSets. Maybe:

 execution(public * java.sql.ResultSet.close()) 

The first aspect, when returning each ResultSet, will create a new Exception object and store it in a static map somewhere, using the ResultSet hash as a key. The second aspect, which is closing the result set, will remove the Exception from the Map using the same hash code as the key. At any time, the card must have one exception instance for each open ResultSet. As an exception, you can get a stack trace to see where the ResultSet was opened.

Perhaps you can save a larger object that includes an exception and some other contextual information; ResultSet creation time, etc.

Answer 2

A practical suggestion is to add some debugging code to the csv file and create a log and close the result sets. You could later examine this file and see if there is a “closing” entry for each “creation”.

So, if you have a utility class with static methods that allows you to write lines to a file, you can do this as follows:

  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 using Excel or any other program with extended sheets, sort the table and see if the result sets are closed. If so, add additional debugging information to clearly identify open sets.

---

BTW. Wrapping interfaces (like JAMon) is pretty simple, if you have an eclipse or something else, it is encoded in less than 15 minutes. You will need to wrap Connection, Statement (and PreparedStatement?) And ResultSet, the ResultSet wrapper can be used to track and monitor the 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 } // ... } 

In the end, you will only need to change one line in the code:

 Connection con = DriverManager.getConnection(ur); 

to

 Connection con = new MonitoredConnection(DriverManager.getConnection(ur)); 

Answer 3

A Google Search pointed me directly to JAMon . It also allows you to track JDBC connections and cursors.

Personally, I would check the code and make sure that all Statement , PreparedStatement and ResultSet close when it is not needed. Even when using the connection pool, only the JDBC connection is returned to the pool, and the statements and ResultSet are closed.

This example shows how I can complete the closure of ResultSet and PreparedStatement at the end of finally (to 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 my 2 cents is worth ... hope this helps you.

Answer 4

This should be a relatively simple tool for your code with the AOP of your choice. I used AspectWerkz a few years ago to download a download web application and collect performance related statistics. Also, if you use an IOC framework like Spring, it is very easy to wrap your data and trace getConnection () calls, etc.