Database connection Pool

Question

Hi i am trying to create database connection pool. Is the below method is correct .

public Connection getMySQLConnection(){
            Connection conn = null;

            String url = "jdbc:mysql://" +  Config.getInstance().getProperty("DB_SERVER_HOST") + ":" + Config.getInstance().getProperty("DB_SERVER_PORT") + "/" + Config.getInstance().getProperty("DB_NAME");


            try {
                poolConn = new DbPool("com.mysql.jdbc.Driver", url, Config.getInstance().getProperty("DB_USERNAME"), Config.getInstance().getProperty("DB_PASSWORD"));
            } catch (SQLException e) {
                LOGGER.error("error while creating the connection pool : "+e);

            }
            try {
                conn = poolConn.getConnection();
            } catch (SQLException e) {
                LOGGER.error("Error while getting connection from db pool"+e);

            }
            return conn;
    }

This is my custom DbPool class's constructor. In the above code i am creating the object of this class.

public DbPool(String classname, String url, String username,
            String password) throws java.sql.SQLException {
        try {
            Class.forName(classname).newInstance();
        } catch (Exception e) { // Catch any exception from JDBC driver failure
           LOGGER.error("Failed to load JDBC driver: "+classname
                               +", Exception: "+e);
        }

        maxConnections = defaultMaxConnections;
        timeout = defaultTimeout;
        if (DEBUG) {
            maxConnections = debugDefaultMaxConnections;
            timeout = debugDefaultTimeout;
        }
        totalConnections = 0;
        freeList = new java.util.LinkedList<DBPoolConnection>();
        busy = new java.util.HashMap<Connection, DBPoolConnection>();
        this.url = url;
        connectionProperties = new java.util.Properties();
        if (username == null) LOGGER.info("ERROR: Null JDBC username");
        else connectionProperties.setProperty("user",username);
        if (password == null) LOGGER.info("ERROR: Null JDBC password");
        else connectionProperties.setProperty("password",password);
    }

why not use ORM framework and let it manage the connection pool? or use already existing ones - (BoneCP) http://jolbox.com/ for example — hovanessyan, Oct 08 '13 at 09:16
Why don't you simply give it a try and see if it works? Please be _much_ more specific about your actual problem (if any...). — dirkk, Oct 08 '13 at 09:17
I have created a custom DbPool class poolConn is the object refrence of that class in the constructor of this class i am writing the logic. — Dharm, Oct 08 '13 at 09:33

score 1 · Accepted Answer · answered Oct 08 '13 at 09:16

1

Totally wrong. Why do you create the pool everytime when getMySQLConnection method is called. Pool should be created once and when getMySQLConnection is called, get a connection from the pool and check whether the connection is valid and then return the connection.

It would be wise to use the 3rd party connection pools like boneCP, DBCP, C3P0 instead of writing one ourself.

Lot of parameters have to be considered before creating connection pool

answered Oct 08 '13 at 09:16

Abhijith Nagarajan

3,865
18
23

1

Well you can, but it takes lot of time to create one and that too a proper one. Connections are one of the most critical resources. Mismanagement of these resources can be very big problem. Nobody has time to create their own connection pool to build a application. They would prefer to use the popular one and concentrate on building the application. If your aim is to create a different pool, go ahead, else my advise would be to use the 3rd party connection pooling. – Abhijith Nagarajan Oct 08 '13 at 09:42

score 1 · Answer 2 · edited May 23 '17 at 12:10

Pascal Thivent wrote on Need Code to create Connection Pool in java:

Need code to create the connection pool in java?

Not sure what the question is but don't create yet another connection pool, use an existing solution like C3P0, Apache DBCP, Proxool or BoneCP (a new player in that field). I would use C3P0.

How does we make sure that connection pool doesn't return the same object which is already in use?

Because if a connection has been borrowed from the pool and not returned yet, it's just not in the pool and can't be assigned to another client of the pool (resources are removed from the pool until they are returned).

How happens if client closed the connection after taking it out from Connection pool?

The connection a client gets from a pool is not really a java.sql.Connection, it's a wrapper (a proxy) for a java.sql.Connection that customizes the behavior of some methods. The close() method is one of them and does not close the Connection instance but returns it to the pool.

also chek this link

Database connection Pool

2 Answers2