best way to return connection back to the connection pool

[jay donald]

Hi,

Could someone please tell me the correct way to return a connection to a connection pool. I have seen these two so far:
1. conn.close() // releases the connection object's database
2. conn.commit()// makes database changes permanent and releases the lock

The reason i'm asking is that i always use the first option but i saw second option used in some other application. Could someone tell me which is better and why? Thanks.

Jay
[ April 20, 2006: Message edited by: jay donald ]

[Scott Dunbar]

I've never seen that a commit() releases the connection in any pool I've ever used. It makes no sense - after a commit you could still want to do other things with the Connection. After a close() you should never do anything with the Connection.

Just my <small amount of your local currency> worth.

[Jass Singh]

The practice we follow for foolproof connection releasing is:

        Connection           objConn            =null;
PreparedStatement    objPstmt           =null;
        ResultSet            rsQuery            =null;
 
        try {
             objConn= //Get connection
 
// Do your JDBC stuff here
   
catch(Exception objExp) {
//Handle Exception your way
        }
        finally {
            try {
                if(rsQuery!=null) {
                    rsQuery.close();
                }
                if(objPstmt!=null) {
                    objPstmt.close();
                }
                if (objConn != null)
                    UtilClass.releaseConnection(objConn);
            }
            catch(Exception objExp) {
          //To handle Exception generated while closing objPstmt or rsQuery
           objLogger.error("Exception in finally block"+objExp.toString());
              UtilClass.releaseConnection(objConn);
           }
        }

Here UtilClass is our custom class and its releaseConnection(objConn) method essentially contains objCon.close() along with normal Exception handling.


Regards,
Jass

[Jesper de Jong]

If you are using a "standard" connection pool (i.e., you get the connection via a javax.sql.DataSource), you should call close() on the connection to return it to the pool.

Note that commit() does not return the connection to the pool. You need to close() it after calling commit().

Note that if you use a connection pool in this way, close() doesn't really close the connection (like when you make a direct connection to the database), it just returns the connection to the pool.

(This is how it works with connection pool implementations like Apache Jakarta Commons DBCP).