Returning a connection to a pool

Discussions

EJB programming & troubleshooting: Returning a connection to a pool

  1. Returning a connection to a pool (9 messages)


    Hello Everyone,

    This is probably going to be a real silly question for some of you Java "Ninjas" out there. But I figured I'll never learn unless I ask.

    I am using Weblogic 6.0 for a transactional J2EE application. We are still early in development. This is what I did for linking to the database.

    1) Set up a JDBC Connection Pool in Weblogic.
    2) Link this Connetion Pool to a DataSource
    3) Add the Datasource to the JNDI context

    When my application needs to do anything in the database, it does the following.

    4) Identifies the JNDI context.
    5) Looks up the datasource (javax.sql.DataSource)
    6) Gets a connection from the datasource (java.sql.Connection)
    7) Executes SQL on the connection
    8) Call the con.close() method

    My assumption here is that con.close() will return this connection to the connection pool. BUt apparently it does not. It infact, closes out the connection completely.
    Eventually the container reaches its max capactiy of connections and i get no resources available exception.

    My question is as to whats the best way to return a connection back to the pool once I am done using it.

    Thanks in advance.

    Saurabh

    Threaded Messages (9)

  2. Saurabh,

    The best way to return a connection, derived from a pool, back to the pool is conn.close().

    so whatever u r doing seems fine. it shud work the way u want it to work. also u mentioned that "the connection closes out completely". so if thats actually happening how can the pool max out.

    anyway conn.close is the key. if it does not execute the connection pool will max out. make sure that conn.close is being executed. the best make sure of that is to put this piece of code in the finally block of ur method.

    also even if ur connection pool max's out, u shud not be denied a connection. u just have to wait till a connection gets free in the pool.

    hope this helps

    kapil
  3. Very interesting..thanks for your response Kapil..I'll check out if then the error is related to some other problem.

    Have you ever seen a No Resources Available Exception.

    I thought it might have been related to this but apparently its not.

    Thanks,
    Saurabh

  4. A second question in line with the above.

    What is a good approach to making and closing connections in an EJB transactaction..

    For example, should I just make and hold on to a connection during EJB Create method.

    Or, should I just make and close connections prior to the exection of EJB load and following the logic execution.

    What is the general approach?
  5. do'nt hold on to the connection in the EJB create method... just make sure you cache the DataSource in ejbActivate, the JNDI lookup (ictx.lookup(...))is usually more expencive than DataSource.getConnection(). Also release the db connection as soon as you are done with it.... remember it is being shared between other components, if you hold on to a connection, other components starve on it.

    cheers

    Adi
  6. well connection is an important and expensive resource, so make sure u ask for one just before u need it and get rid of it as soon as u r done with it.

    kapil
  7. Returning a connection to a pool[ Go to top ]

    The best way to use the database data is to use CMP,so that you needn't worry about the connection situation.But CMP 1.1 has some shortcomings that will be modified in EJB2.0.

    I have write a connection pool by myself for the reason of application server-independance.So I wonder if I could write sth. here.
    You must not do con.close(),which means you close the real connection,while as the pool don't know.eg,there are 5 connections in a pool,you close one,so the real connection is 4,while the pool connection count is still 5.So you should return the connection handle to the pool but not close the connection.
  8. Returning a connection to a pool[ Go to top ]

    Jerry,
      If you obtained a connection from a pool (using the getConnection method of a DataSource), calling connection.close() will return the connection to the pool, so that it can be reused. As others in this thread have pointed out, connections are expensive resources, so it is best to get a connection, use it, and return it to the pool (using the close method). A connection should never be cached or held open, but it is safe to cache a DataSource (to avoid another jndi lookup).

    Tim
  9. Returning a connection to a pool[ Go to top ]

    Thanks.I know, the concrete connection class is re-defined different from the formal and has different meaning of close() fuction.
    Could you also clarify a piece of code which has confused me for long:

          if (statement != null)
            statement.close();
          if (connection != null)
            connection.close();

    There is no relationship between if the statement is point to null and if the statement is closed.Same as the connection.
    statement is a reference,there maybe another statement refer to this object,say statement2.if the object is closed status,the reference maybe still point to this object.While if the reference point to null,that does not means the object is in closed status.Eg.,we could do:

    statement=null;

    at the time the object may be still not closed.
    The difference is obvious,why people(including many IDE and books) like do this.

    The reasonable code should like this as my view:

          if (connection.isClosed()==false)
            connection.close();

    leave the VM do the other.

  10. Returning a connection to a pool[ Go to top ]

    Jerry,
      Usually you'll find the code you mentioned (checking to see if a statement is not null, and if so, closing it, etc) in a "finally" block after performing a database operation. You are correct in that there is not really a relationship between a statement or connection being referenced (not null) and being "open". But since it is good practice to always close your database objects, this closing code is usually placed in a finally block, so that it gets called when the method exits normally or under exception conditions. Since the exact state of your database objects is not known when the finally block is executed (for example, if an exception is raised when you create your connection, the statement has not yet been created and will still be null), it is good practice to test them for null before calling the close method (to avoid a NullPointerException). The code may look something like the following:

    Connection con = null;
    PreparedStatment ps = null;
    try {
      con = getConnection();
      ps = con.prepareStatement(SQL_QUERY);
      // Perform database actions...
    catch ( SQLException sqle ) {
      // handle database errors...
    }
    finally {
      if ( ps != null )
        try { ps.close() }
        catch ( SQLException sqle ) { // log}
      if ( con != null )
        try { con.close() }
        catch ( SQLException sqle ) { // log }
    }

    Hope this helps,
    Tim