java.sql.Connection object, can it be a class var of a DAO?

[Kim Kantola]

Hi All. I am wondering if there is any reason NOT to do this. I have many DAO object that start like this :

public class ProcedureDAO extends BaseDAO
{
 
public static Collection getProcedureList() throws DAOException
{
Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;    

Could I declare the conn, stmt, and rs variables in my BaseDAO class as protected class variables, and then in ProcedureDAO just do this ?

public class ProcedureDAO extends BaseDAO
{
public static Collection getProcedureList() throws DAOException
{
conn = null;
        stmt = null;
        null;    

Then in BaseDAO I could have a method that closes all these objects, I could call it in the finally block of the getProcedure method and any other method of classes that extend BaseDAO.

public static void closeAll() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
;
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
;
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
;
}
conn = null;
}
}
    

I never see this in any example code, so I am wondering if there is a reason NOT to do it.

[Paul Clapham]

You are proposing to move the declarations of those variables to a wider scope. There's a design rule of thumb which says to declare variables in the narrowest possible scope.

But rules can be broken if there's a good enough reason. So it's up to you to provide a reason why you want to break that rule, not up to somebody else to provide a reason why you shouldn't. What's your reason?