DAO and DB connections


EJB design: DAO and DB connections

  1. DAO and DB connections (7 messages)

    Hi all,
    I have a Stateful and a Stateless Session Beans
    that use two DAOs to retrieve DB data.

    My beans in order:

    - retrieve DAO Factory
    - ask DAO Factory to create a new DAO
    - use the DAO

    inside DAO constructor method I put the code
    to get the DB Connection.
    More exactly DAO constructor method calls a static DAOFactory method, createConnection(): createConnection() uses JNDI to lookup the DataSource and the calls

    My question is:
    I must explicitly close the DB Connection created
    (for example writing a destroyConnection() static method
    inside DAOFactory ?) or the connection is closed
    by the Application Server when my Session Beans
    are removed (or passivated) ?

    In first case, destroyConnection() method to code,
    this method simply must call Connection.close() ?

    Many thanks in advance,

    Threaded Messages (7)

  2. DAO and DB connections[ Go to top ]

    The EJB specs are vague. To be on the safe side, close the connection.

    Also, if you ever use your DAO outside of an EJB, you will probably need to close the connection.
  3. DAO and DB connections[ Go to top ]

    You should not get the connection in the DAO constructor, but you should get it just before you want to perform an SQL statement. And you should also close your connection (and you Statement and ResultSet) right after you performed the SQL. No need to keep the connection as an instance variable inside the DAO. That's what a connection pool takes care about, the DataSource.getConnection call is inexpensive.

    Kind regards,

  4. DAO and DB connections[ Go to top ]

    What about multiple JDBC calls in one CMT transaction?
    If you get the connections just-in-time will the container hand you the same connection or will it create multiple ones?

    I have a SLSB with CMT transactions. It delegates to a DAO to do the JDBC work, and often one session-bean method will call multiple DAO methods, in one txn.

    I am debtaing whether to have the session-bean open the connection and pass it to the DAO methods, or to have each DAO methods get a connection and close it.

    The second option looks cleaner (EJB doesn't have to know about JDBC, one less param to pass), but I am concerned that if I make 5 JDBC calls in one txn, I will grab 5 connections from the pool.

    Any advice?
  5. DAO and DB connections[ Go to top ]

    1) Are you using the JDBC API to demarcate transactions?
    2) Are you using JTA to demarcate transactions?
    3) Are you using container managed transactions?

    The JTA 1.0.1B specification states:

    "The resource manager, once opened, is kept open until the resource
     is released (closed) explicitly. When the application invokes
     the connection's close method, the resource adapter invalidates the
     connection object reference that was held by the application and
     notifies the application server about the close. The transaction manager
     should invoke the XAResource.end method to disassociate the transaction
     from that connection.

     The close notification allows the application server to perform
     any necessary cleanup work and to mark the physical XA connection
     as free for reuse, if connection pooling is in place."

    The IBM Websphere 5.1 InfoCenter states:

    "When the application calls close() on a connection, it is returning the connection to
    the pool of free connections; it is not closing the connection to the data store. When the
    application calls close() on a currently shared connection, the connection is not returned to
    the free pool. Only after the application drops the last reference to the connection, and
    the transaction is over, is the connection returned to the pool. Applications using unsharable
    connections must take care to close connections in a timely manner. Failure to do so can
    starve out the connection pool making it impossible for any application running on the server
    to get a connection.

    When the application calls close() on a connection enlisted in a transaction, the connection
    is not returned to the free pool. Because the transaction manager must also hold a reference
    to the connection object, the connection cannot return to the free pool until the transaction
    ends. Once a connection is enlisted in a transaction, you cannot use it in any other
    transaction by any other application until after the transaction is complete."
  6. DAO and DB connections[ Go to top ]

    Hi all,
    I don't use JDBC or JTA to demarcate transactions.
    I use CMT (I'm not sure about the Transactional Attribute
    I need to use: my Session Beans need only to read data
    from DB, so I think that I could use "Not Required").

    Now I store connection in DAO object (when DAO is created)
    but I will try as Andreas told me.
    Every time I need to do a SQL query I get a connection
    and, after the query is performed, I release (close) it.

    Many thanks,
  7. DAO and DB connections[ Go to top ]

    inside DAO constructor method I put the code

    > to get the DB Connection.

    Take a look at this code:



    An alternate approach is described in Bob Lee's blog

  8. Hi:
    I am looking at this thread as I face the same concerns like that of Moreno. However I 'm curious to find a versatile / generic pattern around such a data access layer.

    I have a bunch of classes in the application layer that need access to database data. They make use of the DAOs in my simple DAL layer.

    Andreas suggested just-in-time opening and releasing of connections - all within the DAO methods that execute the SQL and that was a helpful tip.

    Now, I have the following questions:

    1. How to manage the instantiation of DAOs? Should one simply instantiate as many DAOs in various points in the app layer when data access is required, use them to retrieve data and then leave it alone to get garbage collected?

    2. Can DAOs be made singletons?

    3. Could someone suggest resources on patterns around this stuff?

    Thanks a lot.