Principles and mechanisms of database connection pools

1. Basic Concepts and Principles
There is a well-known design pattern for shared resources: 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 create a "buffer pool" for the database connection ". 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? Provides the basis for testing and performance adjustment.

Basic working principle of Connection Pool
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, using the synchronized keyword can 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 to design a single-instance connection pool management class and read a resource file when the unique instance of the connection pool management class is created, the resource file contains the URL addresses of multiple databases ()? Username ()? Password () and other information. For example, TX. url = 172.21.15.123: 5000/tx_it, TX. User = Yang, TX. Password = yang321. Create multiple connection pool instances based on the information provided by the resource file. 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 connection pools.
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 to false, and then explicitly call the commit or rollback method. However, for efficient connection reuse, a corresponding transaction support mechanism must be provided. Each transaction can be 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 whether there is any idle connection in the idle pool. 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 the connection pool is unavailable, delete the connection from the idle pool and re-check whether there are connections in the idle pool). If not, check whether the connection pool currently opened has reached the maximum number of connections allowed by the connection pool (maxconn ), if the connection fails, a new connection is created. If the connection is reached, a certain time (timeout) will be waited ). If a connection is released within the waiting time, the connection can be allocated to the waiting user. If the waiting time exceeds the specified time timeout, a null value 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 the idle connection status, special thread timing detection can be opened up, 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 in the connection 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 quickly and responds slowly. 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. The maximum 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; ② returning the connection to the connection pool after use; ③ disconnect all connections and release the system resources occupied by connections before the system is shut down; ④ it can also handle invalid connections (originally registered as available connections, which are no longer available for some reason, such as timeout, communication problems), 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 to manage multiple connection pool objects and has the following functions: ① load and register the JDBC driver for a specific database; ② create a connection pool object based on the information given in the property file; ③ to facilitate the management of multiple connection pool objects, a name is given for each connection pool object to realize the ing between the connection pool name and its instance; ④ tracking the customer's use of connections, in this case, you need to close the connection to release resources. Connection Pool Management is introduced to facilitate the use and management of multiple connection pools. For example, the system needs to connect to different databases or the same database. However, due to security issues, different users must use different names and passwords.
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 {
Private int checkedout; // Number of allocated connections
Private arraylist freeconnections = new arraylist (); // container, idle pool, store the connections that have been created but not allocated according to // Creation Time Sequence
Private int minconn; // the minimum number of connections in the connection pool
Private int maxconn; // The maximum number of connections allowed in the connection pool
Private string name; // name the connection pool for convenient management
Private string password; // Password required for database connection
Private string URL; // the address of the database to be connected
Private string user; // user name required for database connection
Public timer; // Timer
Public dbconnectionpool (string name, string URL, string user, string
Password, int maxconn) // public Constructor
Public synchronized void freeconnection (connection con) // after use, // return the connection to the idle pool
Public synchronized connection getconnection (long timeout) // get a connection, // timeout is the waiting time
Public synchronized void release () // disconnect all connections and release occupied system resources
Private connection newconnection () // create a database connection
Public synchronized void timerevent () // timer event processing function
}
Public class dbconnectionmanager {
Static private dbconnectionmanager instance; // unique instance of the connection pool management class
Static private int clients; // number of customers
Private arraylist drivers = new arraylist (); // container, which stores the database driver
Private hashmap pools = new hashmap (); // access the connection pool in the form of name/value // Object Name and connection pool object
Static synchronized public dbconnectionmanager getinstance () // If the unique // instance has been created, this instance is directly returned. Otherwise, the private constructor is called to create a unique instance of the connection pool management class.
Private dbconnectionmanager () // private constructor, in which the initialization function Init () is called ()
Public void freeconnection (string name, connection con) // release a connection, // name is the name of a connection pool object
Public connection getconnection (string name) // obtain a connection from the connection pool object named name //
Public connection getconnection (string name, long time) // name from
// Gets a connection from the connection pool object. Time is the waiting time.
Public synchronized void release () // release all resources
Private void createpools (properties props) // create one or more connection pools based on the information provided in the property File
Private void Init () // initialize the unique instance of the connection pool management class, called by the private constructor
Private void loaddrivers (properties props) // load the database driver
}
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
{
Connmgr = dbconnectionmanager. getinstance ();
}
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, for example:
Public void destroy (){
Connmgr. Release (); Super. Destroy ();
}
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 bottleneck restricting the efficiency of large-scale enterprise applications. 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 JDBC to access the database? This paper discusses the key issues of database connection management based on connection pool technology and provides an implementation model. The article provides a basic mode for the connection pool management program. In order to improve the overall performance of the system, many meaningful extensions can be made on this basis.