Physical connection re-use in nested method calls

Discussions

EJB programming & troubleshooting: Physical connection re-use in nested method calls

  1. We have a number of stateless session beans that get a connection from a data source, and while going through the resultset call methods on other stateless session beans to get additional data. What we're finding is that this results in a single user taking up multiple physical connections, which is bad from a resource perspective.

    What is the best practice for situations such as these?
     1) Store the results in a temporary structure, close the connection and then make the subsequent calls?
     2) Is there a way to force the reuse of the same physical connection for each logical connection?
     3) ????

    Btw, we're deploying to Oracle 9iAS. Thx.
  2. Set the transaction attributes in your Session Bean methods to "required". This should force them to share a single connection.
  3. Are you saying that even for the following case:

        Connection conn = dataSource.getConnection();
        PreparedStatement prepStmt = conn.prepareStatement("INSERT INTO blah...);
        ResultSet rs = prepStmt.executeQuery();
        while (rs.hasNext()) {
            // Call a method on FooBean that
            // gets its own connection using dataSource.getConnection()
        }
        prepStmt.close();
        rs.close();
        conn.close();

    That FooBean will use the same physical connection as this method? Is that possible if there are still open resultsets/etc?

    Thx.
  4. Are you saying that even for the following case:

    Connection conn = dataSource.getConnection();
    // Snip ...
    conn.close();

    That FooBean will use the same physical connection as this method? Is that possible if there are still open resultsets/etc?.
    That is correct. When you retrieve a connection from a DataSource, the EJB server manages the connection for you. As long as you are participating in an ongoing transaction, it will keep return the same connection to you.

    When you "close" a connection in an ongoing transaction, the EJB server intercepts that call, and performs its own connection management. The connection will not really be closed if there is an ongoing transaction.

    For this reason, it is important to be sure to close your ResultSet and Statement objects, because you cannot rely on them being closed when the connection is closed. This is generally true anytime you have connection pooling.