Managing SQL Resources

I was thinking of solving the problem of connection leakages in enterprise applications. The first suggestion that caomes to my mind is "Why dont you close the resources where they are opened". Though this sugestion looks very simple, it is seen that this is very difficult to enforce as there are many places in the code where the resources are opened. even if we factor out the resource opening code into a utility method in a utility class, there is still many places from where the utility method will be called. These are the places where we need to also close the resources. One of the techniques that caught my attnetion was that of using aspect oriented programming (especially Aspectj). A sample implementation is described below.

Step 1: Entry Criteria

We will define a marker interface called Managed DAO whose sub classes will enjoy automatic management of resources (JDBC Sstatements and Connections). This interface will help the aspect (discussed later) control the usage of SQL resources (Connections, Statements, Resultsets, etc.).
public interface ManagedDAO {}

Step 2: Monitoring resource initializations and close them after use

Once we have the marker interface, we will make an aspect to monitor the call to the methods in the classes. I have included the narration as comments within the code for your ease of copying this code ;)

public aspect ConnectionManagerAspect {
    /**
     * This aspect will define a pointcut that will intercept all method calls to a DAO but will
     * exclude the management functions defined in this aspect. The pointcut definition is as follows
     * !within (ConnectionManagerAspect) && call ( * ManagedDAO+.*(..)) && target(dao)
     * here, the first part (!within (ConnectionManagerAspect)) will ensure that all activity
     * within this aspect is avoided in the pointcut. The second part (call ( * ManagedDAO+.*(..)))
     * will intercept all calls to all methods in the ManagedDAO and its subclasses. The third part
     * (target(dao)) is a way of obtaining the instance of the adviced object
     */ 
    pointcut allManagedMethods (ManagedDAO dao): !within (ConnectionManagerAspect) && call ( * ManagedDAO+.*(..)) && target(dao);

    /**
     * Next, using inter-type declarations, we declare a stack for maintaining the connetions,
     * statements and resultsets used by each method call. The resources are stored in a list
     * for each method ececution. This way we can handle recursion and nesting of method calls
     */
    private Stack<ArrayList<Connection>> ManagedDAO.connectionStack = new Stack<ArrayList<Connection>>();
    private Stack<ArrayList<Statement>> ManagedDAO.statementStack = new Stack<ArrayList<Statement>>();
    private Stack<ArrayList<ResultSet>> ManagedDAO.resultSetStack = new Stack<ArrayList<ResultSet>>();

    /**
     * Also, we define a method to initialize the stacks.
     * In this method, we push an empty list on to the top of the stack. This list
     * will later be filled with resources used within this method.
     */
    private void ManagedDAO.initializeStack(){
        System.out.println("Depth : "+connectionStack.size());
        connectionStack.add(new ArrayList<Connection>());
        statementStack.add(new ArrayList<Statement>());
        resultSetStack.add(new ArrayList<ResultSet>());
    }

    /**
     * Now, we define a advice before the pointcut to initialize the resources for a
     * method stack.
     */
    before (ManagedDAO dao) : allManagedMethods (dao) {
        dao.initializeStack();
    }

    /**
     * Next, we define a method to close the resources.
     * In this method, we pop the top of the stack and iterate through the list
     * to close the resources used within the method.
     */
    private void ManagedDAO.closeResources(){
        System.out.println("Closing Resources");
        ArrayList<ResultSet> resultSets = resultSetStack.pop();
        for(ResultSet resultSet : resultSets){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        ArrayList<Statement> statements = statementStack.pop();
        for(Statement statement : statements){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        ArrayList<Connection> connections = connectionStack.pop();
        for(Connection connection : connections){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Then, we define an advice to close the resources after each method call
     */
    after (ManagedDAO dao) : allManagedMethods (dao) {
        dao.closeResources();
    }

    /**
     * Now, we define a pointcut that will intercept all creation of connections within a method
     * This can be done with a definition as follows
     * cflow (allManagedMethods(dao)) && call (Connection DataSource+.*(..))
     * the first part (cflow (allManagedMethods(dao))) will enclose all activities within any
     * method call to a ManagedDAO (see the pointcut allManagedMethods above). The second part
     * call (Connection DataSource+.*(..)) will intercept all the connection opens using a
     * datasource.
     */
    pointcut getConnection (ManagedDAO dao): cflow (allManagedMethods(dao)) && call (Connection DataSource+.*(..));

    /**
     * The connections that are opened within a method are captured using the following advice.
     * It gets the connection from the pointcut defined above and adds it to the list that is
     * on top of the stack that manages the connections
     */
    after (ManagedDAO dao) returning(Connection connection) : getConnection (dao) {
        ArrayList<Connection> connections = dao.connectionStack.peek();
        connections.add(connection);
        System.out.println("After returning" + thisJoinPoint);
    }

    /**
     * Now, we define a pointcut that will intercept all creation of Statements within a method
     * This can be done with a definition as follows
     * cflow (allManagedMethods(dao)) && call (Statement+ Connection+.*(..))
     * the first part (cflow (allManagedMethods(dao))) will enclose all activities within any
     * method call to a ManagedDAO (see the pointcut allManagedMethods above). The second part
     * call (Statement+ Connection+.*(..)) will intercept all the Statemwent opens using a
     * connection.
     */
    pointcut getStatement (ManagedDAO dao): cflow (allManagedMethods(dao)) && call (Statement+ Connection+.*(..));

    /**
     * The statements that are opened within a method are captured using the following advice.
     * It gets the statement from the pointcut defined above and adds it to the list that is
     * on top of the stack that manages the statements
     */
    after (ManagedDAO dao) returning(Statement statement) : getStatement (dao) {
        ArrayList<Statement> statements = dao.statementStack.peek();
        statements.add(statement);
        System.out.println("After returning" + thisJoinPoint);
    }

    /**
     * Now, we define a pointcut that will intercept all creation of Resultsets within a method
     * This can be done with a definition as follows
     * cflow (allManagedMethods(dao)) && call (ResultSet+ Statement+.*(..))
     * the first part (cflow (allManagedMethods(dao))) will enclose all activities within any
     * method call to a ManagedDAO (see the pointcut allManagedMethods above). The second part
     * call (ResultSet+ Statement+.*(..)) will intercept all the Statemwent opens using a
     * Statement.
     */
    pointcut getResultSet (ManagedDAO dao): cflow (allManagedMethods(dao)) && call (ResultSet+ Statement+.*(..));

    /**
     * The resultSets that are opened within a method are captured using the following advice.
     * It gets the statement from the pointcut defined above and adds it to the list that is
     * on top of the stack that manages the ResultSets
     */
    after (ManagedDAO dao) returning(ResultSet resultSet) : getResultSet (dao) {
        ArrayList<ResultSet> resultSets = dao.resultSetStack.peek();
        resultSets.add(resultSet);
        System.out.println("After returning" + thisJoinPoint);
    }
}

Step3: Weaving the aspect into your code

For building the application with the aspect, please refer to the AspectJ Ant Tasks Use the ajc task instead of the javac task. this will weave the aspect into your code. In case you are using eclipse, you can download the AspectJ Development Tools from the eclipse site and use the AspectJ project to build your code.

Step3: Testing the Framework

Now that we have the concept clear and the framework in place, Let us try to test the framework. Since this framework uses resources like Connection, Statement and Resultset, we would need to create mock objects for these. All you need to do is to make Dummy classes that implement these interfaces. The Connection can be obtained from a DataSource, the Statement from a connection and the resultset from a Statement. Though we are not interested in the functioning of the classes in general, we are interested in the creation of the resources. So we inplement the following methods to returne the dummy resources that we created. eg:.

public class MockDataSource implements DataSource {
    public Connection getConnection(String username, String password) throws SQLException {
        return new MockConnection();
    }
    ...
    ...
}

public class MockConnection implements Connection {

// implement all the methods that return the connection as
// follows
    public Statement createStatement() throws SQLException {
        return new MockStatement();
    }
...
...
}

public class MockStatement implements CallableStatement, PreparedStatement {
    public ResultSet executeQuery(String sql) throws SQLException {
        System.out.println("Executing Statement");
        return new MockResultSet();
    }
...
...
}

public class MockResultSet implements ResultSet {
...
...
}

in addition to this add print statements to the close methods in the mock objects as well so that we can see the resources getting closed.

the code that would test the above is given below

class DataDao implements ManagedDAO{
    public void printDetails() throws SQLException{
        System.out.println("Executing.. printDetails");
        Connection con = new MockDataSource().getConnection();
        PreparedStatement statement = con.prepareStatement("abcd...");
        ResultSet result = statement.executeQuery();
        result.next();
        printDetails1();
    }
    public void printDetails1() throws SQLException{
        System.out.println("Executing.. printDetails1");
        Connection con = new MockDataSource().getConnection();
        con.prepareCall("abcd...");
    }
}


public class Test {
    public static void main(String a[]) {
        new DataDao().printDetails();
    }
}

A sample output of the program is shown below


Depth : 0
Executing.. printDetails
After returningcall(Connection MockDataSource.getConnection())
After returningcall(PreparedStatement java.sql.Connection.prepareStatement(String))
After returningcall(ResultSet java.sql.PreparedStatement.executeQuery())
Depth : 1
Executing.. printDetails1
After returningcall(Connection MockDataSource.getConnection())
After returningcall(CallableStatement java.sql.Connection.prepareCall(String))
Closing Resources
Closing Statement
Closing Connection
Closing Resources
Closing ResultSet
Closing Statement
Closing Connection

As you can see, the test code does not really bother about the management of the resources that it uses. it just uses the resources and exits.. So the reviewer of the code does not have to worry about the connection opens and closses.

Limitations

This approach however is not a good option if we are managing the transactions programatically as we do not have a control on the life of a connection and a transaction can span multiple DAO method calls. However in a container managed environment, this makes sense as the connections are managed by the connection pool and in most containers, the connections are not released untill the transaction either commits or rollbacks.
Another limitation is that this approach can manage only resources that are opened within the control flow of a DAO. This may be a problem in certain implementations where the Connections are opened inside a EJB or in the business logic layer. But we can argue that EJBS or any layer above the data management layer should not worry about managing such resources.