Database Connection Pool

Statement: The material used in this column is written by the VIP students of the Kay Academy, who has the right to be anonymous and has the final right to interpret the article; The Edith Academy is designed to promote students to learn from each other on the basis of public notes.

Introduction to Database connection pooling:

Database connection objects are limited resources, so database connection pooling is used to allocate, manage, and free database connection objects, which allows applications to reuse an existing database connection object instead of re-establishing one, which is virtually the same concept as the thread pool. The database connection pool frees up database connections that have been idle for more than the maximum idle time to avoid missing database connections due to not releasing the database connection, which can significantly improve the performance of database operations. Without a thread pool, users need to get links to the database each time they request it, and database creation connections typically consume relatively large resources and create longer periods of time. Assuming that the site 100,000 visits a day, the database server needs to create 100,000 connections, greatly wasting the resources of the database, and it is very easy to cause database server memory overflow, extension machine. As shown in the following:

The database connection pool is responsible for allocating, managing, and freeing the database connection, which allows the application to reuse an existing database connection instead of re-establishing one. As shown in the following:

In the Java connection MySQL database, the most common is the DBCP and C3P0 connection pool. DBCP database Connection pool, which is a Java database connection pool, is developed by Apache and is also a connection pool component used by Tomcat. C3P0 is an open source JDBC connection pool that implements the data source and Jndi bindings and supports the standard extensions of the JDBC3 specification and JDBC2. The open source projects that currently use it are hibernate,spring and so on. The difference between C3P0 and DBCP:
DBCP does not automatically reclaim idle connection functions
C3P0 has automatic recycle idle connection function
In general, the use of c3p0 higher than DBCP, in the company used more is also c3p0.

Database connection pool running mechanism:

(1) Create connection pool when program initializes
(2) Request an available connection to the connection pool when used
(3) After use, return the connection to the connection pool
(4) When the program exits, disconnect all connections and release resources

：

The minimum number of connections and the maximum number of connections for a database connection pool are set to take into account the following factors:

1, minimum number of connections: a database connection that is maintained by the connection pool, so if the application has a small amount of database connections, there will be a lot of wasted database connection resources.
2, maximum number of connections: is the maximum number of connections the connection pool can request, and if the database connection request exceeds the number of times, subsequent database connection requests are added to the wait queue, which can affect future database operations
3, if the minimum number of connections differs greatly from the maximum number of connections: Then the first connection request will take profit, and then the connection request that exceeds the minimum number of connections is equivalent to establishing a new database connection. However, these database connections that are larger than the minimum number of connections are not released immediately after they are used. He will be placed in the connection pool waiting for reuse or after a space timeout is released.

DBCP:

DBCP, where the difference between 1.x and 2.x versions is large, and in the 2.x version it is called DBCP2. DBCP 2 is based on Commons Pool 2, providing higher performance, JMX support, and many other new features compared to the DBCP 1.x. Since DBCP 2.x is not binary compatible with DBCP 1.x, users upgrading to 2.x should know that the Java package name has changed, as well as MAVEN coordinates. Users should also note that some configuration options, such as maxactive to Maxtotal, have been renamed to align them with the new name used by Commons Pool 2.
Different versions of DBCP have requirements for the JDK:
DBCP version 1.3 can only be run on JDK 1.4-1.5, the JDBC 3
DBCP 1.4 version can only run on JDK 1.6, support JDBC 4
DBCP 2.x version can only be shipped Line with JDK 1.7, support for JDBC 4.1

DBCP jar package can be downloaded directly in the MAVEN library, only need to find a dependency configuration, the general project to the Apache official website to download the jar and then import into the project can:
Apache Official website:
http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

MAVEN Central Warehouse configuration dependencies:

The configuration depends on the pom file:

You can see that the following three jar packages are downloaded:

If the version is below 2.x, there are only 2 packages: Commons-dbcpjar and Commons-pool.jar

We all know that Java does not do the specification implementation, so the same, Java developed a database connection pool implementation interface, this interface is datasource, all database connection pool data source classes need to implement this interface. The class that implements the DataSource data source interface in DBCP is Basicdatasource, and we need to get the connection object through the data source implementation class, which acts as an administrator:

Let's do an experiment to demonstrate how to use the DBCP connection pool:
code example:

Operation Result:

As can be seen from this example, the actual use of connection pooling and the direct use of JDBC to create database connection objects, there is no significant difference in code, the same load driver driver class, but also to set the URL and user password. Just using a connection pool is to get the connection object through the data source object to the pool.

C3P0:

C3P0 also can go to the MAVEN Central warehouse to query the dependencies, and then configure to the Pom file, c3p0 and dbcp the use of a different way, only the method and the data source implementation class name is not the same.

Configuration syntax:

You can see that the following two jar packages are downloaded:

Let's use the C3P0 connection pool to do just that experiment:
code example:

Operation Result:

One thing to note is that if you have log4j in your engineering environment and the level in the config file is set to debug, C3P0 will automatically call log4j to print some debug messages:

If you do not want to print so many c3p0 debug information, change the level in the configuration file to error only print the wrong message level:

Database Connection Pool