searching for multiple occurrences of variables in a method

Question

I have a problem with cursor leakage in my Java project.

Typical example:

private void doSomething() throws Exception {
    String sql1= "some sql statement";
    String sql2= "some other sql statement";        
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {

        Connection con = getConnection();
        ps = con.prepareStatement(sql1);
        rs = ps.executeQuery();
        //do something with the ResultSet rs        
        //[Need to call ps.close here. Otherwise I risk getting ORA-01000.]
        ps = con.prepareStatement(sql2);              
        ps.executeQuery();
    } catch (Exception e) {
    } finally {
        ps.close();
        rs.close();
    }
}

Since I have a rather large code base I would like to be able to find all methods having two or more variables named sql.

Alternativly finding methods with two (or more) calls for prepareStatement without calling ps.close; between the two.

I am using Eclipse and the file search has a regex option. Maybe thet is the way to go? If so, what would it look like?

Answer 1

Recent versions of Eclipse (I used Juno [4.2]) will show a warning on those lines:

You can enable this warning in the Eclipse preferences:

Even for larger size code bases, you can filter the problems view for this warning in order to find these places in the code.

Answer 2

You can avoid having to close resources manually (if you are using at least JDK 7) by using a try with resources

try(con=getConnection()){
   try(ps = con.prepareStatement()){
      try(rs=ps.executeQuery()){
         ...
      }
   }
}

Anything that implements Autoclosable can be used in a try with resources and the resource gets automatically closed for you. No need to implement complicated resource closing operations anymore.

See the Try-with-Resources Tutorial

Answer 3

What you want to do is kind of static program analysis. As there are specialized tools for that, you may also write your own tool for this specific task. In that way, you could count lines containing rs=ps.executeQuery and rs.close():

for(File file:sourceFiles) { 
  int openedResultSets = count("rs=ps.executeQuery");
  int closedResultSets = count("rs.close()");
  if (openedResultSets > closedResultSets) {
     log.error(file.getName());
  }
 } 

But it should be more complex because probably not only this snippet is used in your projects. Therefore I suppose you should write some code, not only one regexp. Although specialized tools are expensive in most cases, probably some trial version will be enough for you.

Answer 4

There are FindBugs rules to find this, such as

• ODR_OPEN_DATABASE_RESOURCE
• ODR_OPEN_DATABASE_RESOURCE_EXCEPTION_PATH
• OBL_UNSATISFIED_OBLIGATION
• more if you look for the keyword 'resource' in the bug description

If you are not specifically looking for resource leaks, but rather for two or more variables named sql*, you can write a Checkstyle check to find them, possibly as a subclass of LocalVariableNameCheck. This is not a difficult kind of check, but requires coding and deployment work.