File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Obtain connection in DAO implementation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Obtain connection in DAO implementation" Watch "Obtain connection in DAO implementation" New topic

Obtain connection in DAO implementation

Ahsan Bagwan
Ranch Hand

Joined: Oct 05, 2010
Posts: 245

I have more or less based this code on the JDBC sample programs from the official site. Here my aim is to write a DAO for fetching a list on some predefined condition.

So I will write something that will be of the following form:

The doubts I have can be listed as:

- Does the connection to the db performed correctly?

- Do I need a separate connection pool from where I can grab a connection (keeping in mind this is a small application) ?

- Do libraries like DBUtils really make life a breeze?
Jelle Klap

Joined: Mar 10, 2008
Posts: 1951

- 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.
Ahsan Bagwan
Ranch Hand

Joined: Oct 05, 2010
Posts: 245

Appreciate the given suggestions; I will take an effort to incorporate these points when improving the program.
I agree. Here's the link:
subject: Obtain connection in DAO implementation
It's not a secret anymore!