Implement Connection pooling in JDBC using apache dbcp

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();
            }
        }

}

I suggest you read http://stackoverflow.com/questions/520585/connection-pooling-options-with-jdbc-dbcp-vs-c3p0?rq=1 — Elliott Frisch, Nov 16 '13 at 07:44
DBCP is largely abandoned, I recommend [HikariCP](http://brettwooldridge.github.io/HikariCP/), but then again I am biased as I helped write it. — brettw, Nov 16 '13 at 08:17
@brettw 'Largely abandoned' by whom? Do you have statistics on that? — user207421, Aug 10 '15 at 22:24
@EJP Context. At the time I made that comment, Nov. 2013, the most recent release of DBCP was [Feb. 2010](https://commons.apache.org/proper/commons-dbcp/changes-report.html). It seems to have gotten a little life recently. — brettw, Aug 11 '15 at 00:03

score 1 · Answer 1 · answered Nov 16 '13 at 08:37

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.

hi could see my updated code above plz whether it work or not? — user2998826, Nov 16 '13 at 08:41
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](https://github.com/brettwooldridge/SansOrm) library and not bothering writing your own helper. — brettw, Nov 16 '13 at 08:58

Implement Connection pooling in JDBC using apache dbcp

1 Answers1

Linked