A detailed explanation of the principles and mechanisms of the database connection pool in Java.

I went to the interview today and met a question about the database connection pool, which is clearly something that is often used. But after I shut down my computer and book, I found many things I still cannot tell clearly, I have specially reproduced this article to supplement it.

 

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 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.

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:

[Java]
View plaincopyprint?

1. Public class dbconnectionpool implements timerlistener {
2. Private int checkedout; // Number of allocated connections
   
3. Private arraylist freeconnections = new arraylist ();
4. // Container, idle pool, store the connections that have been created but not allocated according to // Creation Time Sequence
   
5. Private int minconn; // the minimum number of connections in the connection pool
   
6. Private int maxconn; // The maximum number of connections allowed in the connection pool
   
7. Private string name; // name the connection pool for convenient management
   
8. Private string password; // Password required for database connection
   
9. Private string URL; // the address of the database to be connected
   
10. Private string user; // user name required for database connection
    
11. Public timer; // Timer
    
12. Public dbconnectionpool (string name, string URL, string user,
13. String password, int maxconn) // public Constructor
    
14. Public synchronized void freeconnection (connection con)
15. // Return the connection to the idle pool after use
16. Public synchronized connection getconnection (long timeout)
17. // Get a connection. Timeout is the waiting time.
    
18. Public synchronized void release ()
19. // Disconnect all connections and release occupied system resources
20. Private connection newconnection ()
21. // Create a database connection
22. Public synchronized void timerevent ()
23. // Timer event processing function
24. }
25. Public class dbconnectionmanager {
26. Static private dbconnectionmanager instance;
27. // Unique instance of the connection pool management class
28. Static private int clients; // number of customers
    
29. Private arraylist drivers = new arraylist ();
30. // Container to store the database driver
31. Private hashmap pools = new hashmap ();
32. // Access the connection pool Object Name and connection pool object in the form of name/value
    
33. Static synchronized public dbconnectionmanager getinstance ()
34. /** If the unique instance has been created, this instance is directly returned. Otherwise, the private constructor is called,
35. Create a unique instance of the connection pool management class */
36. Private dbconnectionmanager ()
37. // Private constructor, in which the initialization function Init () is called ()
    
38. Public void freeconnection (string name, connection con)
39. // Release a connection. Name is the name of a connection pool object.
    
40. Public connection getconnection (string name)
41. // Obtain a connection from the connection pool object named name
    
42. Public connection getconnection (string name, long time)
43. // Obtain a connection from the connection pool object named "name". Time is the waiting time.
    
44. Public synchronized void release () // release all resources
    
45. Private void createpools (properties props)
46. // Create one or more connection pools based on the information provided by the property File
47. Private void Init () // initialize the unique instance of the connection pool management class, called by the private constructor
    
48. Private void loaddrivers (properties props) // load the database driver
    
49. }

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:

[Java]
View plaincopyprint?

1. Public void Init () throws servletexception
2. {
3. Connmgr = dbconnectionmanager. getinstance ();
4. }
5. 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:
6. Public void destroy (){
7. Connmgr. Release ();
8. Super. Destroy ();
9. }

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 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.