How to establish a connection pool in JDBC

Question

Can anybody provide examples or links on how to establish a JDBC connection pool   From searching google I see many different ways of doing this and it is rather confusing   Ultimately I need the code to return a java sql Connection object  but I am having trouble getting started  any suggestions welcome   Update   Doesn t javax sql or java sql have pooled connection implementations  Why wouldn t it be best to use these

User · Answer

Pool   Pooling Mechanism is the way of creating the Objects in advance  When a class is loaded  It improves the application performance  By re using same object s to perform any action on Object-Data   amp  memory  allocating and de-allocating many objects creates a significant memory management overhead   Object clean-up is not required as we are using same Object  reducing the Garbage collection load       Pooling   Object pool  String Constant Pool  Thread Pool  Connection pool   String Constant pool   String literal pool maintains only one copy of each distinct string value  which must be immutable  When the intern method is invoked  it check object availability with same content in pool using equals method     If String-copy is available in the Pool then returns the reference     Otherwise  String object is added to the pool and returns the reference    Example  String to verify Unique Object from pool   public class StringPoolTest       public static void main String   args       Integer valueOf    String equals           String eol   System getProperty  line separator      java7 System lineSeparator             String s1    Yash  intern            System out format  Val  s Hash  s SYS  s   eol  s1  s1 hashCode    System identityHashCode s1            String s2    Yas   h  intern            System out format  Val  s Hash  s SYS  s   eol  s2  s2 hashCode    System identityHashCode s2            String s3    Yas  intern    h  intern            System out format  Val  s Hash  s SYS  s   eol  s3  s3 hashCode    System identityHashCode s3            String s4    Yas   h           System out format  Val  s Hash  s SYS  s   eol  s4  s4 hashCode    System identityHashCode s4              Connection pool using Type-4 Driver using 3rd party libraries  DBCP2  c3p0  Tomcat JDBC   Type 4 - The Thin driver converts JDBC calls directly into the vendor-specific database protocol Ex Oracle - Thick  MySQL - Quora   wiki  In Connection pool mechanism  when the class is loaded it get s the physical JDBC connection objects and provides a wrapped physical connection object to user  PoolableConnection is a wrapper around the actual connection     getConnection   pick one of the free wrapped-connection form the connection objectpool and returns it  close   instead of closing it returns the wrapped-connection back to pool    Example  Using   DBCP2 Connection Pool with Java 7 try-with-resources   public class ConnectionPool       static final BasicDataSource ds dbcp2   new BasicDataSource        static final ComboPooledDataSource ds c3p0   new ComboPooledDataSource        static final DataSource ds JDBC   new DataSource         static Properties prop   new Properties        static           try               prop load ConnectionPool class getClassLoader   getResourceAsStream  connectionpool properties                  ds dbcp2 setDriverClassName  prop getProperty  DriverClass                  ds dbcp2 setUrl  prop getProperty  URL                  ds dbcp2 setUsername  prop getProperty  UserName                  ds dbcp2 setPassword  prop getProperty  Password                  ds dbcp2 setInitialSize  5                 ds c3p0 setDriverClass  prop getProperty  DriverClass                  ds c3p0 setJdbcUrl  prop getProperty  URL                  ds c3p0 setUser  prop getProperty  UserName                  ds c3p0 setPassword  prop getProperty  Password                  ds c3p0 setMinPoolSize 5               ds c3p0 setAcquireIncrement 5               ds c3p0 setMaxPoolSize 20                PoolProperties pool   new PoolProperties                pool setUrl  prop getProperty  URL                  pool setDriverClassName  prop getProperty  DriverClass                  pool setUsername  prop getProperty  UserName                  pool setPassword  prop getProperty  Password                  pool setValidationQuery  SELECT 1      SELECT 1 mysql  select 1 from dual oracle               pool setInitialSize 5               pool setMaxActive 3               ds JDBC setPoolProperties  pool              catch  IOException e      e printStackTrace              catch  PropertyVetoException e    e printStackTrace                 public static Connection getDBCP2Connection   throws SQLException           return ds dbcp2 getConnection               public static Connection getc3p0Connection   throws SQLException           return ds c3p0 getConnection               public static Connection getJDBCConnection   throws SQLException           return ds JDBC getConnection            public static boolean exists String UserName  String Password   throws SQLException       boolean exist   false      String SQL EXIST    SELECT   FROM users WHERE username   AND password         try   Connection connection   ConnectionPool getDBCP2Connection              PreparedStatement pstmt   connection prepareStatement SQL EXIST               pstmt setString 1  UserName            pstmt setString 2  Password             try  ResultSet resultSet   pstmt executeQuery                  exist   resultSet next       Note that you should not return a ResultSet here                      System out println  User     exist       return exist      jdbc  lt DB gt   lt drivertype gt   lt HOST gt   lt TCP IP PORT gt   lt dataBaseName gt  jdbc oracle thin  localhost 1521 myDBName jdbc mysql   localhost 3306 myDBName  connectionpool properties  URL           jdbc mysql   localhost 3306 myDBName DriverClass   com mysql jdbc Driver UserName      root Password        Web Application  To avoid connection problem when all the connection s are closed MySQL  wait timeout  default 8 hours  in-order to reopen the connection with underlying DB   You can do this to Test Every Connection by setting testOnBorrow   true and validationQuery   SELECT 1  and donot use autoReconnect for MySQL server as it is deprecated  issue              context xml              lt  xml version  1 0  encoding  UTF-8   gt   lt  -- The contents of this file will be loaded for a web application -- gt   lt Context gt       lt Resource name  jdbc MyAppDB  auth  Container           factory  org apache tomcat jdbc pool DataSourceFactory           type  javax sql DataSource            initialSize  5  minIdle  5  maxActive  15  maxIdle  10           testWhileIdle  true              timeBetweenEvictionRunsMillis  30000           testOnBorrow  true              validationQuery  SELECT 1              validationInterval  30000            driverClassName  com mysql jdbc Driver           url  jdbc mysql   localhost 3306 myDBName           username  yash  password  777        gt   lt  Context gt               web xml              lt resource-ref gt       lt description gt DB Connection lt  description gt       lt res-ref-name gt jdbc MyAppDB lt  res-ref-name gt       lt res-type gt javax sql DataSource lt  res-type gt       lt res-auth gt Container lt  res-auth gt   lt  resource-ref gt              DBOperations             servlet      init      Normal call used by sevlet     static     static DataSource ds  static       try           Context ctx new InitialContext            Context envContext    Context ctx lookup  java comp env            ds       DataSource  envContext lookup  jdbc MyAppDB          catch  NamingException e      e printStackTrace             See these also    am-i-using-jdbc-connection-pooling configuring-jdbc-pool-high-concurrency

User · Answer

HikariCP  It s modern  it s fast  it s simple  I use it for every new project  I prefer it a lot over C3P0  don t know the other pools too well

User · Answer

Apache Commons has a library for that purpose  DBCP  Unless you have strange requirements around your pools  I d use a library as it s bound to be trickier and more subtle than you would hope

User · Answer

Vibur DBCP is another library for that purpose  Several examples showing how to configure it for use with Hibernate  Spring Hibernate  or programatically  can be found on its website  http   www vibur org   Also  see the disclaimer here

User · Answer

I would recommend using the commons-dbcp library   There are numerous examples listed on how to use it  here is the link to the move simple one   The usage is very simple    BasicDataSource ds   new BasicDataSource     ds setDriverClassName  oracle jdbc driver OracleDriver    ds setUsername  scott     ds setPassword  tiger     ds setUrl connectURI         Connection conn   ds getConnection      You only need to create the data source once  so make sure you read the documentation if you do not know how to do that   If you are not aware of how to properly write JDBC statements so you do not leak resources  you also might want to read this Wikipedia page

User · Answer

MiniConnectionPoolManager is a one-java-file implementation  if you re looking for an embeddable solution and are not too concerned about performances  though I haven t tested it in that regard    It is multi-licensed EPL  LGPL and MPL   Its documentation also gives alternatives worth checking  on top of DBCP and C3P0     Proxool BoneCP Tomcat JDBC Connection Pool

User · Answer

As answered by others  you will probably be happy with Apache Dbcp or c3p0  Both are popular  and work fine   Regarding your doubt     Doesn t javax sql or java sql have   pooled connection implementations  Why   wouldn t it be best to use these    They don t provide implementations  rather interfaces and some support classes  only revelant to the programmers that implement third party libraries  pools or drivers   Normally you don t even look at that  Your code should deal with the connections from your pool just as they were  plain  connections  in a transparent way

User · Answer

In late 2017 Proxool  BoneCP  C3P0  DBCP are mostly defunct at this time   HikariCP  created in 2012  seems promising  blows the doors off anything else I know of  http   www baeldung com hikaricp  Proxool has a number of issues  - Under heavy load can exceed max number of connections and not return below max - Can manage to not return to min connections even after connections expire - Can lock up the entire pool  and all server client threads  if it has trouble connecting to the database during HouseKeeper thread  does not use  setQueryTimeout  - HouseKeeper thread  while having connection pool lock for its process  requests the Prototyper thread to recreate connections  sweep  which can result in race condition lockup   In these method calls the last parameter should always be sweep false during the loop  only sweep true below it  - HouseKeeper only needs the single PrototypeController sweep at the end and has more  mentioned above  - HouseKeeper thread checks for testing of connections before seeing what connections may be expired  some risk of testing expired connection that may be broken terminated through other timeouts to DB in firewall  etc   - The project has unfinished code  properties that are defined but not acted upon  - The Default max connection life if not defined is 4 hours  excessive  - HouseKeeper thread runs every five seconds per pool  excessive   You can modify the code and make these improvements   But as it was created in 2003  and updated in 2008  its lacking nearly 10 years of java improvements that solutions like hikaricp utilize

User · Answer

Usually if you need a connection pool you are writing an application that runs in some managed environment  that is you are running inside an application server  If this is the case be sure to check what connection pooling facilities your application server providesbefore trying any other options    The out-of-the box solution will be the best integrated with the rest of the application servers facilities  If however you are not running inside an application server I would recommend the Apache Commons DBCP Component  It is widely used and provides all the basic pooling functionality most applications require

User · Answer

You should consider using UCP   Universal Connection Pool  UCP  is a Java connection pool   It is a features rich connection pool and tightly integrated with Oracle s Real Application Clusters  RAC   ADG  DG databases     Refer to this page for more details about UCP

User · Answer

If you need a standalone connection pool  my preference goes to C3P0 over DBCP  that I ve mentioned in this previous answer   I just had too much problems with DBCP under heavy load  Using C3P0 is dead simple  From the documentation   ComboPooledDataSource cpds   new ComboPooledDataSource    cpds setDriverClass   org postgresql Driver       loads the jdbc driver cpds setJdbcUrl   jdbc postgresql   localhost testdb     cpds setUser  swaldman    cpds setPassword  test-password        the settings below are optional -- c3p0 can work with defaults cpds setMinPoolSize 5   cpds setAcquireIncrement 5   cpds setMaxPoolSize 20       The DataSource cpds is now a fully configured and usable pooled DataSource    But if you are running inside an application server  I would recommend to use the built-in connection pool it provides  In that case  you ll need to configure it  refer to the documentation of your application server  and to retrieve a DataSource via JNDI   DataSource ds    DataSource  new InitialContext   lookup  jdbc myDS

User · Answer

Don t reinvent the wheel   Try one of the readily available 3rd party components    Apache DBCP - This one is used internally by Tomcat  and by yours truly  c3p0   Apache DBCP comes with different example on how to setup a pooling javax sql DataSource   Here is one sample that can help you get started

User · Answer

In the app server we use where I work  Oracle Application Server 10g  as I recall   pooling is handled by the app server   We retrieve a javax sql DataSource using a JNDI lookup with a javax sql InitialContext   it s done something like this  try           context   new InitialContext       jdbcURL    DataSource  context lookup  jdbc CachedDS       System out println  Obtained Cached Data Source       catch NamingException e             System err println  Error looking up Data Source from Factory    e getMessage          We didn t write this code  it s copied from this documentation

[java] How to establish a connection pool in JDBC?

Examples related to java

Examples related to jdbc

Examples related to connection-pooling