About Connection.close()

[Chetan Parekh]

Hello!!

I am getting Connection from the using DataSouce by following code...

 
InitialContext objInitialContext = new InitialContext();
DataSource objDataSource = (DataSource)objInitialContext.lookup(datasource);
System.out.println("Inside DBManager.getConnection() ");
return objDataSource.getConnection();

I am using following code to close the Connection

  
if(conn !=  null){
conn.close();
conn = null;
}

I want to know here, by doing Connection.close(), does the Connection object returns to the ConnectionPool?

Reg,
Chetan

[Roger Chung-Wee]

Yes, and close the connection in your finally block.

[Chetan Parekh]

Yeah closing in finally block only.

But suppose if I have 10 open connetions, and I close a connection using above mentioned code, then I will have 10-1 = 9 open connections, right?

[Roger Chung-Wee]

Let me rephrase my post. You should close your connection the moment that you do not need it and also close it in your finally block. This will keep the number of open connections to a minimum.

    Context ctx = null;
    Hashtable ht = new Hashtable();
    ht.put(Context.INITIAL_CONTEXT_FACTORY,
           "weblogic.jndi.WLInitialContextFactory");
    ht.put(Context.PROVIDER_URL,
           "t3://hostname:port");
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        ctx = new InitialContext(ht);
        javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("myDataSource");
        conn = ds.getConnection();
 
    // You can now use the conn object to create
    //  Statements and retrieve result sets:
    stmt = conn.createStatement();
    stmt.execute("select * from someTable");
    rs = stmt.getResultSet(); 
...
 
    //Close JDBC objects as soon as possible
    stmt.close();
    stmt = null;
    conn.close();
    conn = null;
    }
    catch (Exception e) {
        // a failure occurred
        log message;
    }
    finally {
        try {
            ctx.close();
        } catch (Exception e) {
            log message; }
        try {
            if (rs != null)
                rs.close();
            } catch (Exception e) {
                log message; }
        try {
            if (stmt != null) 
                stmt.close();
            } catch (Exception e) {
                log message; }
        try {
            if (conn != null)
                conn.close();
            } catch (Exception e) {
                log message; }
    }

[Carol Enderlin]

Originally posted by Chetan Parekh:
Yeah closing in finally block only.

But suppose if I have 10 open connetions, and I close a connection using above mentioned code, then I will have 10-1 = 9 open connections, right?

No. If you are getting connections from a connection pool set up in weblogic using that datasource, when you close the connection it is returned to the pool. It would then be available for the next request that asks for a connection.

You can monitor connections in the console, see Monitoring Connections in a JDBC Connection Pool.

If a new connection is opened for each new user or request, you have probabaly missed a place where the connection isn't getting closed.

[Roger Chung-Wee]

You can also configure WebLogic Server to pin a connection to a particular thread. Closing the connection will in such a case cause WebLogic Server to reserve the connection to the thread, so the connection will not be returned to the pool. When the same thread needs a connection again, WebLogic Server will provide the reserved connection. If the thread needs additional connections, then WebLogic Server will create additional connections and pin them to the thread.