Detailed explanation of the principles and mechanisms of the database connection pool in Java

List of Reading Notes and articles

Basic working principle of Connection Pool

1. Basic Concepts and Principles

From the above analysis, we can see that the root cause of the problem is the inefficient management of database connection resources. We know that there is a well-known design pattern for shared resources: resource pools
(Resource
Pool ). Is this pattern designed to solve frequent resource allocation? Release issues. To solve the above problems, you can use the database connection pool technology. The basic idea of the database connection pool is to connect to the database.
Create a buffer pool ". A certain number of connections are put in the buffer pool in advance. When you need to establish a database connection, you only need to extract one from the "buffer pool" and put it back after use. We can set
The maximum number of connections in the connection pool to prevent endless connections to the database. More importantly, we can monitor the number of database connections through the connection pool management mechanism? Usage, for system development? Testing and Performance Tuning
Provide the basis.

2. Connection Pool provided by the server

The jdbc api does not provide the connection pool method. Some large web application servers such as BEA's WebLogic and IBM WebSphere provide connection pool mechanisms, but they must have third-party dedicated class methods to support connection pool usage.

Key issues in connection pool

1. Concurrency Problems

To maximize the versatility of the connection management service, you must consider the multi-threaded environment, that is, the concurrency issue. This problem is relatively well solved, because the Java language itself provides support for concurrent management
Run the synchronized keyword to ensure that the thread is synchronized. The usage is to add the synchronized keyword directly before the class method, for example:

Public synchronized connection getconnection ()

2. Multiple database servers and multiple users

For large enterprise applications, it is often necessary to connect different databases (such as Oracle and Sybase) at the same time ). How to connect to different databases? Our strategy is: Design
A single-instance connection pool management class reads a resource file when a unique instance of the connection pool management class is created. The resource file contains the URL addresses of multiple databases ()? Username ()? Password
Code () and other information. For example
TX. url = 172.21.15.123: 5000/tx_it, TX. User = Yang, TX. Password = yang321. Based on resource files
Create multiple connection pool instances. Each instance is a connection pool of a specific database. The connection pool management instance takes a name for each connection pool instance and uses different names to manage different connections
Connection Pool.

When multiple users in the same database use different names and passwords to access the database, you can also use resource file processing to set multiple URLs in the resource file, database connection information with different user names and passwords.

3. Transaction Processing

As we know, transactions are atomic, and database operations must comply with the "all-nothing" principle, that is, for a group of SQL statements, either all or no.

In Java, the connection class provides transaction support. You can set the autocommit attribute of connection
False, and then call the commit or rollback method explicitly. However, for efficient connection reuse, a corresponding transaction support mechanism must be provided. Available
Each transaction is exclusively connected. This method can greatly reduce the complexity of transaction management.

4. Connection Pool allocation and release

The allocation and release of the connection pool have a great impact on the system performance. Reasonable Allocation and release can increase the reusability of connections, reduce the overhead of establishing new connections, and speed up user access.

Idle pools can be used for connection management. That is, the connections that have been created but have not been allocated are stored in an idle pool at the creation time. When a user requests a connection, the system first checks the idle pool.
Is there any idle connection. If there is one, allocate the connection with the longest creation time (stored in the container order) to it (actually, determine whether the connection is valid first, and assign it to the user if it is available, if not available
Delete the connection from the idle pool and re-check whether there are connections in the idle pool). If not, check whether the current connection pool has reached the maximum number of connections allowed by the connection pool (maxconn). If not
When it reaches, a new connection is created. If it reaches, it will wait for a certain time (timeout ). If a connection is released within the waiting time, the connection can be allocated to the waiting user. If
If the wait time exceeds the specified time timeout, null is returned ). The system only counts connections that have been allocated and are in use, and returns the connections to the idle pool after use. For idle connections
State, which can open up special thread timing detection, which will consume a certain amount of system overhead, but can ensure a fast response speed. You can also choose not to open up special threads, but to detect them before allocation.

5. Configuration and maintenance of the Connection Pool

How many connections should be placed in the connection pool to optimize system performance? You can set minconn and maxconn to control connections.
Connection in the pool. The minimum number of connections is the number of connections created in the connection pool when the system starts. If too many instances are created, the system starts slowly, but the system responds quickly after creation. If too few instances are created, the system starts very quickly.
Fast, but slow response. In this way, you can set a smaller minimum number of connections during development, and the development will be faster, while the system will set a larger number in actual use, because it will be faster for customers to access. Dalian
The number of connections is the maximum number of connections allowed in the connection pool. The specific setting depends on the System Access traffic. You can find the best point through repeated tests.

How can we ensure the minimum number of connections in the connection pool? There are two policies: Dynamic and Static. Dynamically checks the connection pool at a certain time. If the number of connections is smaller than the minimum number of connections, the corresponding number of new connections are added to ensure the normal operation of the connection pool. Static checks are performed when idle connections are not enough.

Connection Pool implementation

1. Connection Pool Model

The connection pool discussed in this article includes a connection pool class (dbconnectionpool) and a connection pool management class
(Dbconnetionpoolmanager ). The connection pool class is the "buffer pool" for all connections to a database. It mainly implements the following functions: ① obtaining or creating available connections from the connection pool; ②
After use, return the connection to the connection pool. ③ disconnect all connections and release the system resources occupied by the connections before the system is closed; ④ it can also handle invalid connections (originally registered as available connections, for some reason
No longer available, such as timeout and communication issues), and can limit the total number of connections in the connection pool to be no less than a predetermined value and not more than a predetermined value.

The connection pool management class is the wrapper of the connection pool class. It complies with the singleton mode, that is, the system can only have one instance of the connection pool management class. It is mainly used for managing multiple connection pool objects
To facilitate management of multiple connection pool objects, for each connection pool
Object To Realize the ing between the connection pool name and its instance; ④ tracking the customer's use of the connection, so that it is necessary to close the connection to release resources. The connection pool management class is introduced to facilitate multiple connections
Pool usage and management. If the system needs to connect to different databases or connect to the same database, different users need to use different names and passwords due to security issues.

2. Connection Pool implementation

The following describes the main attributes of the connection pool class and connection pool management class and the basic interface to be implemented:

Public class dbconnectionpool implements timerlistener {<br/> private int checkedout; // Number of allocated connections <br/> private arraylist freeconnections = new arraylist (); <br/> // container, idle pool, stores the connections that have been created but not allocated in the order of // creation <br/> private int minconn; // The minimum number of connections in the connection pool <br/> private int maxconn; // The maximum number of connections allowed in the connection pool <br/> private string name; // name the connection pool to facilitate management <br/> private string password; // Password required for database connection <br/> private string URL; // address of the database to be connected <br/> private string user; // username required for database connection <br/> Public timer; // timer <br/> Public dbconnectionpool (string name, string URL, string user, <br/> string password, int maxconn) // public constructor <br/> Public synchronized void freeconnection (connection con) <br/> // after use, return the connection to the idle pool <br/> Public synchronized connection getconnection (long timeout) <br/> // get a connection, timeout is the waiting time <br/> Public synchronized void release () <br/> // disconnect all connections and release occupied system resources <br/> private connection newconnection () <br/> // create a database connection <br/> Public synchronized void timerevent () <br/> // timer event processing function <br/>}< br/> public class dbconnectionmanager {<br/> static private dbconnectionmanager instance; <br/> // unique instance of the connection pool management class <br/> static private int clients; // number of customers <br/> private arraylist drivers = new arraylist (); <br/> // container to store the database driver <br/> private hashmap pools = new hashmap (); <br/> // access the connection pool Object Name and connection pool object in the form of name/value <br/> static synchronized public dbconnectionmanager getinstance () <br/>/** if the unique instance has been created, this instance is directly returned. Otherwise, the private constructor is called, <br/> create a unique instance of the connection pool management class */<br/> private dbconnectionmanager () <br/> // private constructor, where the initialization function Init () is called () <br/> Public void freeconnection (string name, connection con) <br/> // release a connection, name is the name of a connection pool object <br/> Public connection getconnection (string name) <br/> // obtain a connection from the connection pool object named name <br/> Public connection getconnection (string name, long time) <br/> // obtain a connection from the connection pool object named "name". Time is the waiting time. <br/> Public synchronized void release () // release all resources <br/> private void createpools (properties props) <br/> // based on the information provided by the property file, create one or more connection pools <br/> private void Init () // initialize the unique instance of the connection pool management class, called by the private constructor <br/> private void loaddrivers (properties props) // load the database driver <br/>}

3. connection pool usage

How can the connection pool implemented above be applied to the system during program development? The following uses Servlet as an example to describe how to use the connection pool.

The Servlet's life cycle is to call its initialization (init) method when starting to establish a servlet. Each user request causes a thread to call the service method of the Instance created earlier. Finally, when the server decides to uninstall a servlet, it first calls the destroy method of the servlet.

Based on servlet features, we can generate a unique instance of the connection pool management class in the initialization function (including creating one or more connection pools ). For example:

Public void Init () throws servletexception <br/>{< br/> connmgr = dbconnectionmanager. getinstance (); <br/>}< br/> then you can use the connection pool name in the service method to perform database operations. Finally, release the occupied system resources in the destroy method, such as: <br/> Public void destroy () {<br/> connmgr. release (); <br/> super. destroy (); <br/>}

Conclusion

Database connection management is a challenge in database-related application development using JDBC. In many cases, the overhead of system resources caused by chaotic management of connections becomes a constraint on large enterprises.
Industry-level application efficiency bottleneck. For Web applications accessed by many users, the efficiency and stability of systems using database connection technology are much better than those using other traditional methods. This article describes how to use
How does JDBC access the database? This paper discusses the key issues of database connection management based on connection pool technology and provides an implementation model. This article provides a basic mode for the connection pool management program.
The overall performance of the system can be significantly expanded.