CPD errors in a DAO class

Question

I have a DAO class which has multiple methods.In each method I used variable name "result" for ResultSet and "statement" for PreparedStatement and a closeResources() method to close the PreparedStatement and the Connection. I have used a DataManager class which has createConnection() method,getConnection() method.There are like 10 mwthods in my DAO.I am using DAO factory method to get DAO object in BO.This is Snippet of code,that is shown as violation when I ran CPD tool in eclipse.It is showing this snippet of code as a violation in around 6 to 8 methods of my DAO.

PreparedStatement statement=null;

try{

PreparedStatement statement=connection.prepareStatement(query);

//statements to set data in query

ResultSet result=statement.executeQuery();

if(result.next){

//some operation

   }
}
catch(SQLException e){

//encapsulating sql exception....


throw new BusinessException(e);

}
finally{

closeResources(connection,statement);

}

Since for many methods in DAO use the same approach,CPD is showing the above code as violation and I think no way you can make this piece of code to be more modular.My question is using the same variable name like "result" in many methods is a best practice or not.To clear the violation,I need to rename the "result" to "result1", "result2" etc but i feel those names as not meaningful.

Note:I am at the end of my training.I didn't work on Springs or Struts, I know just Servlets and JSP.I am doing a case study,and it is the first time I am using PMD,CPD tools.

Answer 1

Use Template method pattern:

public void runQuery(Callback callback) {
    PreparedStatement statement=null;
    try{
        PreparedStatement statement=connection.prepareStatement(query);
        ResultSet result=statement.executeQuery();
        if(result.next){
            callback.onRow(result);  //crucial!
       }
    }
    catch(SQLException e){
        throw new BusinessException(e);
    }
    finally{
        closeResources(connection,statement);
    }
}

interface Callback {
    void onRow(ResultSet resultSet);
}

You use your method like this:

runQuery(new Callback() {
    public void onRow(ResultSet result) {
        //extract one row here
    }
});

The only pieces of code that change are various Callback implementations, extracting rows from different tables. Once you familiarize with this pattern, check out JdbcTemplate which is built around that idea, but is much, much more powerful, yet still low-level and fast. You don't need full spring to use it.