How to create JDBC Connection pool?

Question

how to create JDBC connection pool with configurable parameters, client should able to change the default number of connection ??? can anybody explain this with sample code

Please read our [ask] page for hints on how to improve this question — blurfus, Feb 21 '17 at 05:10
most popular connection pools should supply example code and options for configuration — slipperyseal, Feb 21 '17 at 05:12
http://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc — Isuru Liyanage, Feb 21 '17 at 05:18
Don't roll your own connection pool, use an existing library and check their documentation. — Mark Rotteveel, Feb 21 '17 at 13:14

Dilip Belgumpi · Answer 1 · 2017-02-21T06:13:59.247

I hope this will help you.

  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.Statement;
  import org.apache.tomcat.jdbc.pool.DataSource;
  import org.apache.tomcat.jdbc.pool.PoolProperties;


  public class SimplePOJOExample {

  public static void main(String[] args) throws Exception {
      PoolProperties p = new PoolProperties();
      p.setUrl("jdbc:mysql://localhost:3306/mysql");
      p.setDriverClassName("com.mysql.jdbc.Driver");
      p.setUsername("root");
      p.setPassword("password");
      p.setJmxEnabled(true);
      p.setTestWhileIdle(false);
      p.setTestOnBorrow(true);
      p.setValidationQuery("SELECT 1");
      p.setTestOnReturn(false);
      p.setValidationInterval(30000);
      p.setTimeBetweenEvictionRunsMillis(30000);
      p.setMaxActive(100);
      p.setInitialSize(10);
      p.setMaxWait(10000);
      p.setRemoveAbandonedTimeout(60);
      p.setMinEvictableIdleTimeMillis(30000);
      p.setMinIdle(10);
      p.setLogAbandoned(true);
      p.setRemoveAbandoned(true);
      p.setJdbcInterceptors(
        "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
        "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
      DataSource datasource = new DataSource();
      datasource.setPoolProperties(p);

      Connection con = null;
      try {
        con = datasource.getConnection();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("select * from user");
        int cnt = 1;
        while (rs.next()) {
            System.out.println((cnt++)+". Host:" +rs.getString("Host")+
              " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
        }
        rs.close();
        st.close();
      } finally {
        if (con!=null) try {con.close();}catch (Exception ignore) {}
      }
  }

 }

Source : https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

score 0 · Answer 2 · answered Feb 21 '17 at 06:26

0

You can use OracleDataSource as well, supported with Ojdbc6.jar. The concept remains same but will differ based on what libraries you choose.

Also, As a general practice, you should put all the setter parameters in a properties file.

answered Feb 21 '17 at 06:26

PankajT

145
3

How to create JDBC Connection pool?

2 Answers2