CODEWITHSUNDEEP
javajdbcconnection-pooling
Tanuboddi
Tanuboddi

Reputation: 3

how to check whether a connection is closed or not in java connection pooling

In the following program I kept execution in sleep mode. But still the con obj is not closed.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
public class ConnPool {

public static void main(String[] args) throws Exception {
    PoolProperties p = new PoolProperties();
    p.setUrl("jdbc:mysql://localhost:3306/users");
    p.setDriverClassName("com.mysql.jdbc.Driver");
    p.setUsername("root");
    p.setPassword("root1");
    p.setJmxEnabled(true);
    p.setTestWhileIdle(false);
    p.setTestOnBorrow(true);
    p.setValidationQuery("SELECT 1");
    p.setTestOnReturn(false);
    p.setValidationInterval(10000);
    p.setTimeBetweenEvictionRunsMillis(10000);
    p.setMaxActive(100);
    p.setInitialSize(10);
    p.setMaxWait(10000);
    p.setRemoveAbandonedTimeout(60);
    p.setMinEvictableIdleTimeMillis(10000);
    p.setMinIdle(10);
    p.setLogAbandoned(true);
    p.setRemoveAbandoned(true);
    p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
            + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
    DataSource datasource = new DataSource();
    datasource.setPoolProperties(p);

    Connection con = null;
    try {
        con = datasource.getConnection();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("select * from emp");
        int cnt = 1;
        while (rs.next()) {
            System.out.println( "Name:" + rs.getString(1)+ " Address:" + rs.getString(2) );
        }
        rs.close();
        st.close();
        Thread.sleep(40000);
        System.out.print(con.isClosed());
    } finally {
       /* if (con != null) {
            try {
                con.close();
            } catch (Exception ignore) {
            }
        }*/
    }
}

}

Upvotes: 0

Views: 4400

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108994

When you use a connection pool, you still need to explicitly close the connection by calling Connection.close(). This will signal to the connection pool that the connection is free again and can be returned to the connection pool.

The Connection you get from the connection pool is not the actual physical connection to the database, but a logical connection that is a wrapper or proxy for the actual physical connection. For some tasks (most notable close()) it will implement slightly different behaviour (so that the connection pool can re-use it), but to the end-user it behaves as if it is a normal connection (eg all dependent objects should be closed).

You are not very explicit in your question, but I will assume that you meant to ask why the connection pool hasn't reclaimed the connection yet even though you set a timeout. I can think of several reasons:

  1. You set the removeAbandonedTimeout to 60 seconds, while you sleep for only 40000 milliseconds (40 seconds),
  2. The implementation might use a cooperative way of reclaiming 'abandoned' connections (for example it might only check when a new Connection is requested, or when the pool is getting low on available connections)

Upvotes: 3

Related Questions