Jdbc Connection Pool

 A JDBC connection pool is a group of reusable connections for a particular database. 

Because creating each new physical connection is time consuming, the server maintains a pool of available connections to increase performance.

 When an application requests a connection, it obtains one from the pool. When an application closes a connection, the connection is returned to the pool.

When creating a connection pool , you are actually defining the aspects of a connection to a specific database. Before creating the pool, you must first install and integrate the JDBC driver. 

The properties of connection pools can vary with different database vendors.

1. Some common properties are the database’s name (URL), user name, and password.
2. Certain data specific to the JDBC driver and the database vendor must be entered. Before proceeding, gather the following information:
3. Database vendor name
4. Resource type, such as javax.sql.DataSource (local transactions only) javax.sql.XADataSource (global transactions)
5. Data source class name: If the JDBC driver has a Datasource class for the resource type and database, then the value of the Datasource Classname field is required.
6. Required properties, such as the database name (URL), user name, and password

A JDBC connection pool is a group of reusable connections for a particular database. When creating the pool with the Administration Console, the Administrator is actually defining the aspects of a connection to a specific database.

Before creating the pool, you must first install and integrate the JDBC driver. When building the Create Connection Pool pages, certain data specific to the JDBC driver and the database vendor must be entered. 

Before proceeding, gather the following information:

1. Database vendor name
2. Resource type, such as javax.sql.DataSource (local transactions only) javax.sql.XADataSource (global transactions)
3. Data source class name
4. Required properties, such as the database name (URL), user name, and password

Connection Pool Libraries considered for analysis:

1) Apache Commons DBCP2:

• Easy to configure. Very detailed configuration page.
• Supports prepared statement caching
• Does not validate connections on borrow
• Does not reset transaction isolation when returns to the pool
• Does not reset the read-only status of the connection
• Does not reset the auto-commit status of the connection
• Does not clear SQL warnings
• DBCP2 compiles and runs under Java 7 only (JDBC 4.1), not sure if it runs on Java 8
• More than 60 classes in the library
• Does not validate connections on borrow
• The code is hosted on GitHub https://github.com/apache/commons-dbcp. The repository has 21 contributors and only 2 contributions in the year 2017. A lot of broken links on the home page, java doc links are broken.
• I logged into Apache Bugtracker (JIRA) to check for the number of bugs, there are 11 open and reopened bugs.
• Licensed under Apache 2.0

2)C3P0:

C3P0 is one of the oldest and best-known libraries. It is very well documented. However, according to some reports, it’s easy to misconfigure the pool and can result in poor performance and deadlocks.

• It comes in a package with hibernate.
• Works well in the heavy concurrent environment.
• Supports prepared statement pooling
• Resets auto commit status when returning to the pool
• Resets read the only status when returning to the pool
• Resets isolation level when returning to the pool
• Does not test connections at getConnection()
• Around 200 classes in the library. Huge code base compared to others.
• The code is hosted on Github -> Repository, the library has 5 contributors with 5 contributions to the code this year, 4th May had the last check-in.
• Issues are tracked on Github tracker and have 41 issues listed.
• The library is licensed under LGPL v.2.1 and EPL v.1.0

3)Tomcat JDBC:

• Supports highly concurrent environments and multi-core/CPU systems.
• It is very well documented here.
• Async connection retrieval. Can queue connection requests and return Future back.
• Ability to configure custom interceptors to enhance functionality.
• It provides multiple ways to configure the pool. One is inside the Apache container, JMX and standalone by using DataSource bean.
• It does not by default reset auto-commit and transaction levels for connections in the pool, users must configure custom interceptors to do this.
• Does not by default test connections on getConnection()
• It does not close abandoned open statements.
• Pool prepared statement properties are not used.
• The code is hosted on GitHub. It has 13 active contributors.
• This is a very active community, one can see looking at the commits and bug list.
• licensed under Apache 2.0

4) HikariCP:

• Very light library, around 130 kb.
• Tests connections at getConnection()
• Tracks and closes abandoned connections.
• Clears connections before returning the connection to the client.
• Resets auto-commit, transaction isolation, read-only status
• The library has 58 active contributors and has a very detailed configuration page with performance benchmarks.
• The library has 33 issues listed on GitHub
• Licensed under Apache 2.0