Implement Connection pooling in JDBC using apache dbcp [duplicate]

Question

Is this Good Code to implement pooling? I want to implement this in my project which has 30 threads operating concurrently and each thread requires more than four connection for each request? Does this code work?

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;

/**
 *
 * @author
 * taher_JAVAHUNTER
 */

    public class JDBCHelper {

        private final static String username = "root";
        private final static String password = "";
        private final static String url = "jdbc:mysql://localhost:3306/treamisdemo";
        public static Connection connection = null;
        public static int connectionCount = 0;
    //    public JDBCHelper(boolean setCon) {
    //        try {
    //            setConnectionTest();
    //        } catch (Exception e) {
    //            System.out.println("Error in Connection:" + e.toString());
    //        }
    //    }
        public static BasicDataSource dataSource;

        public static Connection getConnection() throws SQLException {
            try {
                if (dataSource == null) {
                    dataSource = new BasicDataSource();
                    String driver = "com.mysql.jdbc.Driver";
                    try {
                        dataSource.setDriverClassName(driver);
                        dataSource.setUrl(url);
                        dataSource.setUsername(username);
                        dataSource.setPassword(password);
                        dataSource.setMaxActive(100);
                        dataSource.setMaxWait(10000);
                        dataSource.setMaxIdle(10);
                        if (connection == null || connection.isClosed()) {
                            System.out.println(" requeition CONNECTION WITH FIRST SERVER.");
                            connection = dataSource.getConnection();
                            connectionCount++;
                        }
                    } catch (SQLException e) {
                        System.out.println("***Connection Requisition*** Could not connect to the database msg :" + e.getMessage());
                    }
                } else {
                    connection = dataSource.getConnection();
                    connectionCount++;
                }
            } catch (Exception e) {
                System.out.println("open connection exception" + e);
            }
            return connection;
        }

        public static void close(ResultSet c) {
            try {
                if (c != null) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        public static void close(Statement c) {
            try {
                if (c != null) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        public static void close(Connection c) {
            try {
                if (c != null) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

}

DBCP is largely abandoned, I recommend HikariCP, but then again I am biased as I helped write it. — brettw
– brettw, Commented Nov 16, 2013 at 8:17
@brettw 'Largely abandoned' by whom? Do you have statistics on that? — user207421
– user207421, Commented Aug 10, 2015 at 22:24
@EJP Context. At the time I made that comment, Nov. 2013, the most recent release of DBCP was Feb. 2010. It seems to have gotten a little life recently. — brettw
– brettw, Commented Aug 11, 2015 at 0:03

brettw · Accepted Answer · 2013-11-16 08:37:07Z

1

I would not recommend this approach. You are basically creating a connection and hanging on it it. I'm not in love with your pattern, but something like this would be better:

public class DataTransaction {
   private final static BasicDataSource dataSource;

   static {
      dataSource = new BasicDataSource();
      dataSource.setDriverClassName("com.mysql.jdbc.Driver");
      dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/registrationtest");
      dataSource.setUsername("root");
      dataSource.setPassword("root");
      dataSource.setMaxActive(100);
      dataSource.setMaxWait(10000);
      dataSource.setMaxIdle(10);
   }

   private DataTransaction() {
   }

   public static DataSource getDataSource() {
      return dataSource;
   }
}

Further, I would not hardcode any of the DataSource parameters, but rather initialize the DataSource from a properties file.

answered Nov 16, 2013 at 8:37

brettw

11.1k2 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2998826 Over a year ago

hi could see my updated code above plz whether it work or not?

brettw Over a year ago

Looks ok enough. Because getConnection() is un-synchronized two threads hitting at the same time (the first time) could initialize the DataSource twice (because both think it is null). If what you've shown is all you're trying to do, I recommend just using the SQLClosure class from the SansORM library and not bothering writing your own helper.

Collectives™ on Stack Overflow

Implement Connection pooling in JDBC using apache dbcp [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related