Dao methods, manipulating single/multiple objects and closing resources

Question

The usual advice is to close JDBC ressources once they're no longer needed. This could be done in a catch and finally. However, what if a DAO method only manipulates one domain object and an operation requires several of these to be retrieved/created in one go? Would getting a statement and then closing it repeatedly be an issue in terms of performance? If so, should a separate method be made to handle several objects in one go or should closing be delayed in some way?

Answer 1

i think it would be no difference if the application is Dao based or not. Those resources should be closed. If you work without any framework (spring, hibernate etc)

java.sql.Connection should be put back to the pool, if there was a pool. ResultSet and Statement objects should be closed after the execution of the query.

Depends on your architecture, those resource management codes could be placed in Dao classes or other classes. for example there are classes focusing on building up and executing sql queries. The resource mgmt codes could be in those classes.

If you work with some frameworks, the framework will usually do the resource mgmt for you.

Answer 2

You could add an extra transaction layer on top of the DAO layer and call setAutoCommit(false) on the Connection in the beginning of the transaction/session, let the DAO methods use the same Connection instance and then commit() the Connection when the transaction/session is finished/closed. You'll however need to change the DAO methods to take a Connection as an extra argument, or to store it ThreadLocal (which however needs to be done very carefully since threads may be pooled).

Creating statements shouldn't be that expensive as long as you're consistently using PreparedStatement which are usually been compiled and cached in DB side.