Close database connection in connection pool

Question

I am using JDBC connection pool to make connection with mysql server.

Below is my code snippet

  try {
       InitialContext initialContext = new InitialContext();
        Context context = (Context) initialContext.lookup("java:comp/env"); 
        DataSource ds = (DataSource) context.lookup("connpool");
        Connection conn =  ds.getConnection();
        //some query is executed 
    }
    catch(SQLException ex)
   {   } 
   finally {  conn.close(); }

My Doubt:

My doubt here is even I am making connection close(conn.close()), in MySQL show processlist command it showing connection.

If I send more requests to servlet the connections count in show processlist is also increasing,

When this connection will be closed.

Why I am afraid means it it reached max connections count it will show error.

My Connection pool configuration is:

          <Resource name="connpool" auth="Container" 
             type="javax.sql.DataSource" 
             maxActive="1" maxIdle="0"
             maxWait="-1"
             username="xxxxx" 
             password="xxxxx"
             driverClassName="com.mysql.jdbc.Driver"
             url="jdbc:mysql://localhost:3306/govsocial"/>

Check the configuration of your datasource. Probably it's opening a new connection until it reaches the max of allowed connections, then it will start reusing them. — Luiggi Mendoza
The connection pool may not actually close the physical database connection when you issue conn.close, but rather just free it up for use by another getConnection. — GriffeyDog
@LuiggiMendoza I have set maxActive="1" maxIdle="0" maxWait="-1" — Manohar Gunturu
Can you post your full configuration for the connection pool? What library are you using? — John R

T.Gounelle T.Gounelle · Accepted Answer · 2015-04-20T16:42:53

From tomcat documentation, there are initial values that are not set consistently with what you are setting in the resource config.

minIdle (default is 10)
initialSize (default is 10)

If you have a look at the code for init() method, with the above defaults and your configuration with

maxActive = "1"
maxIdle = "0"

You will end up with:

maxActive = "1"
maxIdle = "1"
minIdle = "1"
initialSize = "1"

This is a pool of 1 connection, and the purpose of the pool is to keep some (depending on config) connections open for incoming requests. Calling close() will only pass the connection from the busy queue to the idle one.

If you really want no pool at all, you should try setting explicitly:

maxActive = "1"
maxIdle = "0"
minIdle = "0"
initialSize = "0"

Note that maxWait = -1 means a new connection will wait until a connection is available - without timeout.

Close database connection in connection pool

1 Answers