How to manage database connections properly?

Discussions

General J2EE: How to manage database connections properly?

  1. How to manage database connections properly? (2 messages)

    While looking at some basic DAO pattern examples at various places (sorry, no links or references currently), many of them seem to be getting database Connections at the DAO level, for example in constructor:

    DAO {
      Connection connection;
      public DAO() {
         connection = dataSource.getConnection();
      }
      public VO loadByPrimaryKey(Integer pk) {
    ...

    or even at each individual method:

    DAO {
      public VO loadByPrimaryKey(Integer pk) {
        Connection conn = dataSource.getConnection();
        PreparedStatement pst = conn.prepareStatement("BLA BLA");
    ...

    have I understood something completely wrong, or does this approach have serious drawbacks?

    1) Let's say one use case uses 6 DAO's - we end up consuming 6 connections from the pook temporarily, when 1 would be enough
    2) Transactional integrity is lost - since we have 6 separate connections there is no way to commit / rollback all database activities at once
  2. 1) Let's say one use case uses 6 DAO's - we end up consuming 6 connections from the pook temporarily, when 1 would be enough
    This depends on the DataSource. Most containers use ThreadLocal to bind the Connection to throughouth the transaction. If you want to code this from scratch, have fun :)
    2) Transactional integrity is lost - since we have 6 separate connections there is no way to commit / rollback all database activities at once
    A container will take care of the transaction and reuse the same connection throughout the life of the request if desired. Once again the container...

    As you see from my answers above I push everything back onto the container. JDBC programming when doen right is way to complicated for most programmers! Transactions and the like are very involved. I recomend using a containers implementation of transactions and connection pooling. The one I like best is Spring: Data Access with the Spring Framework
  3. standard behaviour?[ Go to top ]

    1) Let's say one use case uses 6 DAO's - we end up consuming 6 connections from the pook temporarily, when 1 would be enough
    This depends on the DataSource. Most containers use ThreadLocal to bind the Connection to throughouth the transaction. If you want to code this from scratch, have fun :)
    Hmmm. "most containers use ThreadLocal..." - is this some sort of standard behaviour documented in some specification? Or are you binding your code to container-specific features if you rely on this? Please, any pointers to a specification on this are much appreciated.

    The approach I have usually used so far is getting exactly one connection from datasource at a Controller servlet, passing it on to use-case handling classes, domain objects, DAOs etc., and finally committing / rolling it back depending on whether an exception was thrown or not. This works without any problems. Now I'm studying Struts, and wondering whether to manage connections in Actions, or whether the approach you described (relying on all objects in same thread sharing the connection) would be feasible.