Regarding Connection pool

[Thanuja Vishwanath]

Hi,

The advantage of using connection pool is get the open connection......use it and return it to the pool once the request is processed so that other request acquires that connection and uses it.

I have a doubt...........

We generally acquire the connection and close it in finally like

con.close()

here does it mean that we are closing that acquired connection or does it mean that we are returning it to the pool but connection is not closed?

I was told in an interview that connection should not be closed ...........i dont understand this.

[Abhishek Ralhan]

Yes, and the other advantage is:
- The overhead of opening and closing the connection is avoided, which could be a huge performance hit.

You may read below page and get some clarity:
http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html

Now coming to your doubt:

Whenever you close a connection, when connection pooling is implemented, the connection always returns to connection pool.
So, you must answer like whenever we do connection.close(), below two things can happen:
- If connection is obtained from the pool, connection is returned to the pool.
- Otherwise it is simply closed.

[Thanuja Vishwanath]

Hi,

So, you must answer like whenever we do connection.close(), below two things can happen:
- If connection is obtained from the pool, connection is returned to the pool. (this is the case when connection pool is implemented?)
- Otherwise it is simply closed. (this is the case when connection pool is not implemented? is that so?)

correct me if iam wrong and also when the connection will be closed in connection pooling?. say i have 3 connections in a pool and all the request to my application uses it. now all the request has been processed. there is no more request to be processed. so my last request uses a connection and returns to the pool. now i dont have any request to process. so what will happen ie. when the connection will actually be closed.

[Jan Cumps]

so what will happen ie. when the connection will actually be closed.

When the application stops.

[Sandeep Sanaboyina]

Or, if you are using a custom connection pool, there will probably be methods to purge/close/destroy the physical connections.

For Example, purgeCache in OracleConnectionCacheManager

[Avinash Pendse]

i have implemented the connection pooling in my web Application (For the First time) . the application is for online application form which may active for specific days. i don't know how many user will access this form at a time. My question is it that sufficient or i have to make any modifications?

i have three classes 1]DBConnector : used for creating connection pool and makes a connection available from the pool.
2]connection : used to connect to connection pool.
3]appform : from where connection request is send to connection pool via connection class.

code snip in reverse i.e. from appform to dbconnector.

calls the method  in connection class
            NMVConnection dbconnection = new NMVConnection();
            dbconnection.connectDB();

Method connectDB in connection class

public int connectDB() {
        String driver = "com.mysql.jdbc.Driver";
                url = "jdbc:mysql://localhost:3306/";
        uname = "root";
        passwd = "welcome";
        String dbName = "preprimary";

        //  END

        int status = 0;
        try {
            dbConnector = DBConnector.getInstance(url + dbName, uname, passwd);

           conn = DriverManager.getConnection(url + dbName, uname, passwd);
            conn = dbConnector.getConnection();

            if (conn.isClosed() == false) {
                status = 1; //connected
            } else {
                status = 0; //not connected
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return status;
    }

connection pooling class

package spmenglish;

// Necessary classes to get Pooled Database Connection

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class DBConnector {

    private static DBConnector connector_ = null;
    private static PoolingDataSource dataSource_ = null;

    // Create a sinlgeton object
    public static DBConnector getInstance() throws Exception {
        return connector_;
    }

    // Create a sinlgeton object
    public static DBConnector getInstance(String url, String user, String password) throws Exception {
        if (connector_ == null) {
            connector_ = new DBConnector(url, user, password);
        }

        return connector_;
    }

        private DBConnector() throws SQLException {
    }

    private DBConnector(String url, String user, String password) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        GenericObjectPool connectionPool = new GenericObjectPool(null);
        
        connectionPool.setMaxActive(5);
        connectionPool.setMaxWait(2);
        connectionPool.setMaxIdle(2);
        
      
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, user, password);



                PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);


       
        dataSource_ = new PoolingDataSource(connectionPool);




    }

    public Connection getConnection() throws Exception {
        //return
        
        return dataSource_.getConnection();


    }
}

will this is sufficient for any no of users