Connection Pooling using Apache common DBCP And DBUtils

Posted on September 5, 2011 by tahertinwala

Post moved to ExamPit please click below link to explore

Connection Pooling using Apache common DBCP And DBUtils…

This entry was posted in JDBC, Just 4 Knowledge. Bookmark the permalink.

9 Responses to Connection Pooling using Apache common DBCP And DBUtils

  1. Ellipsis says:
    November 16, 2011 at 6:30 pm

    Hi,

    I am new to DB connection pooling. I have been using connection pooling so far by configuring context.xml and using following code in the class like ‘DataTransaction’. Is it the right way to set up connection pooling?

    Context ctx = (Context) new InitialContext().lookup(“java:comp/env”);
    conn = ((DataSource) ctx.lookup(“jdbc/dbname”)).getConnection();

    Will appreciate any suggestion/comments. Thank you

    Reply
  2. GrapeJuice says:
    January 20, 2012 at 10:29 am

    Why singleton concept is not used in this example?

    Reply
  3. GrapeJuice says:
    January 21, 2012 at 9:12 am

    Hi,
    It is not a suggestion. I am doing similarly to the above with the little variation using singleton & lock. I am just wondering, if I am over killing. Here is my code. Any suggestion is appreciated.
    /**
    * This class is used for Connection pooling & act as DataSource for the connections.
    */
    package com.testing.datasource.connectionpooling;

    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;

    import org.apache.commons.dbcp.BasicDataSource;
    import org.apache.commons.dbutils.DbUtils;
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.ResultSetHandler;

    public class DataSourceTransaction {

    private static BasicDataSource ds;
    private static DataSourceTransaction instance = null;
    private static final Object lock=new Object();
    // QueryRunner Constructor is set to true so that it allows workaround for
    // Oracle drivers
    private QueryRunner queryRunner=new QueryRunner(true);
    /**
    * ********************************************************
    * Creates a BasicDataSource with the required parameters
    * ********************************************************
    * @param dbDriver –
    * @param dbURL –
    * @param dbUserId –
    * @param dbPassword –
    */

    // Constructor marked as private so that it can’t be instantiated directly
    private DataSourceTransaction (String dbDriver, String dbURL, String dbUserId, String dbPassword) throws Exception {
    try {
    ds = new BasicDataSource();
    ds.setDriverClassName(dbDriver);
    ds.setUrl(dbURL);
    ds.setUsername(dbUserId);
    ds.setPassword(dbPassword);
    ds.setMaxActive(10);
    ds.setMaxIdle(1);
    ds.setMaxWait(40000);
    }
    catch(Exception e) {
    throw e;
    }
    }

    /**
    * ********************************************************
    * Singleton instance is created through this method call
    * ********************************************************
    *
    * @param dbDriver –
    * @param dbURL –
    * @param dbUserId –
    * @param dbPassword –
    * Database password
    */
    public static DataSourceTransaction getInstance(String dbDriver, String dbURL, String dbUserId, String dbPassword) throws Exception{
    // check is the dataSource instance is already created
    if (instance == null)
    try{
    // synchronize the logical block of create an instance of
    // DataSourceTransaction
    synchronized (lock) {
    // check again if the instance is already created
    if (instance == null)
    {
    instance = new DataSourceTransaction(dbDriver,dbURL,dbUserId,dbPassword);
    }
    }
    }catch(Exception e){
    throw e;
    }
    return instance;
    }

    /**
    * Returns Connection objects from the DataSource created
    */
    public Connection getConnection() throws SQLException
    {
    try {
    synchronized (lock) {
    return ds.getConnection();
    }
    } catch (SQLException e) {
    throw e;
    }
    }
    /**
    * ********************************************************
    * Close the connection that has been passed as the argument
    * The closed connection would then return to the connection pool
    * ********************************************************
    *
    * @param dbConnection –
    * Connection Object
    */
    public void closeConnection(Connection dbConnection) throws Exception {
    try {
    DbUtils.close(dbConnection);

    }
    catch(SQLException sqlE) {
    throw sqlE;
    }
    catch(Exception e) {
    throw e;
    }
    finally{
    dbConnection.close();
    }
    }
    }

    Reply
    • tahertinwala says:
      January 22, 2012 at 11:59 pm

      Hey Grape,
      Good Job but I m little bit confused with synchronized block as it may cause connection timeout. Because our all request of JSP(Servlets) are manged by threads that’s why…

      Reply
      • GrapeJuice says:
        January 23, 2012 at 9:39 am

        Hi
        Thanks for pointing about the threads.This is the code I am going to use for all the batch jobs. These batch jobs will get the connection using this connection pool. These jobs can run simultaneously at the same time. However, more than one instance of the same job is not possible, as it is controlled by a different thread handling. I am used to the application server connection pooling and not used to handle connections manually(and I do not know how app server handles connection pooling internally). Can you please tell me which synchronized block can cause connection timeout? Is the one in the getInstance method or the one in the getConnection method? If so, why? I appreciate your help.

  4. tahertinwala says:
    January 24, 2012 at 1:30 am

    I think the whole funda of connection pooling will fail as you are seeking lock on object so what happens if one job does’nt release the lock other will not gain it and single connection will be used every time I think. And connection timeout might not come if above is your case 🙂

    Reply
    • GrapeJuice says:
      January 24, 2012 at 9:19 am

      That helps. Thanks for the good explanation. I would like to know which lock u r referring to? Is it both of them? Or just one of them? This s of great help.

      Reply
  5. Kumar says:
    February 3, 2012 at 10:46 pm

    I think the example look great and useful. Only one comment, I would like to pass:
    In the example of “public List DB3()”, if you are using the datasource like this
    QueryRunner run = new QueryRunner(DataTransaction.dataSource);
    I think we do not have to do a close on the connection like this.
    DbUtils.close(conn); //Do not need this

    This is as per the example in http://commons.apache.org/dbutils/examples.html
    Let me know, if my understanding is right.

    Reply

Leave a comment