Is it OK to put Statement and ResultSet in DAO base class?

[N Carlo]

Hi all,

In my web application, I have a base class called Dao that contains the following:

protected Connection conn = null;
protected PreparedStatement stmt = null;
protected ResultSet rs = null;

public Connection getConnection() {
...
}

Then I have subclasses that extend Dao and inherit the Connection, PreparedStatement, and ResultSet. Here is an example of how they are used:

String sql = "SELECT * FROM SOME_TABLE";
		
conn = getConnection();
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
...

The reason for doing this is simply that the subclasses always need those variables to do their work, so it seemed sensible to put them in a parent class. Is there any risk or drawback to doing it this way? Is it better to just always declare the Connection, PreparedStatement, and ResultSet as local variables? Thanks for your help.

[James Boswell]

Hi

I think any PreparedStatement or ResultSet should be local to methods within the DAOs that extend your base class.

Similarly, a Connection object should come from a DataSource which is set on construction of your DAO.

[N Carlo]

Hi James,

Thanks for your reply. Can you explain the reason why they should be local variables? What is the danger of putting them in the base class?

[Kunal Lakhani]

What if i create a separate class for getting and closing connection, and , use PreparedStatement or ResultSet as local to methods within the DAO?

[Martin Vajsar]

N Carlo wrote:Hi James,

Thanks for your reply. Can you explain the reason why they should be local variables? What is the danger of putting them in the base class?

I assume you're using a connection pool. In this case, you need to open and close all your database resources as part of servicing every request. This is somewhat obvious for connections, as you want the connection returned to the pool after servicing one request to be available when servicing another request. The other resources have to be closed simply because they are tied to the connection they came from. To reduce the overhead, some connection pools cache these objects (such as PreparedStatements) transparently.

Edit: therefore, you cannot keep these resources open outside of servicing a request, which means they do not belong to the class, but should be declared locally.

[N Carlo]

OK, sounds like using local variables is the standard way to do it, so I'll go that route. Thanks for your help.