- It's not a good idea to establish a new connection or transaction in every DAO method, in fact it's better not to let the DAO be responsible for creating a connection / transaction at all. Although it is possible to combine such a solution with connection pooling, you will run into problems with transaction scoping. Often a single transaction will need to span invocations of multiple methods across multiple DAOs, which will become impossible if each DAO method establishes a new connection. A JDBC DAO should be treat a Connection as a dependency, which could be instance bound or thread bound, whatever you need.
- Connection pooling is probably always worth it.
- Things like Apache's DBUtils or Spring's JdbcTemplate can help you immensely with avoiding some of the boiler plate code and solving other issues, but there are learning curves associated with their proper use, especially in the case of the Spring framework.
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
Joined: Oct 05, 2010
Appreciate the given suggestions; I will take an effort to incorporate these points when improving the program.