A detailed explanation of the basic working principle of database connection pool in Java __c#

Basic working principle of connection pool

1. Basic concepts and principles

As can be seen from the above analysis, the root of the problem lies in the inefficient management of the database connection resources. We know that for shared resources, there is a well-known design pattern: resource pools (Resource pool). The pattern is to solve the problem of the frequent distribution of resources and the release of the resource. In order to solve the above problems, we can use database connection pool technology. The basic idea of a database connection pool is to create a "buffer pool" for database connections. Put a certain number of connections in the buffer pool beforehand, and when you need to establish a database connection, simply remove one from the buffer pool, and then put it back when you have finished using it. We can prevent the system from endlessly connecting to the database by setting the maximum number of connections to the connection pool. More importantly, we can monitor the number of connections to the database through the connection pooling management mechanism, and provide the basis for system development test and performance adjustment.

2, the server from the connection pool with

There is no method in the JDBC API to provide connection pooling. Some large Web application servers, such as Bea's WebLogic and IBM's WebSphere, provide a mechanism for connection pooling, but there must be a Third-party dedicated class method that supports the use of connection pooling.

Analysis of key problems of connection pool

1, concurrency problems

In order to make the connection Management Service have the most universality, it is necessary to consider the multithread environment, that is concurrency problem. This is a relatively good solution because the Java language itself provides support for concurrency management, and using the Synchronized keyword ensures that threads are synchronized. Use the method to directly precede the class method with the Synchronized keyword, such as:

Public synchronized Connection getconnection ()

2. Multi-database server and multiuser

For large enterprise applications, it is often necessary to simultaneously connect different databases, such as Oracle and Sybase. How do I connect to a different database? The strategy we adopt is to design a connection pool management class that conforms to the singleton pattern, reading a resource file when a unique instance of the connection pool management class is created, where the URL address of multiple databases is stored in the resource file (). User name ()? password () and other information. such as tx.url=172.21.15.123:5000/tx_it,tx.user=yang,tx.password=yang321. Create multiple instances of the connection pool class based on the information provided by the resource file, each of which is a connection pool for a particular database. The connection pool management class instance takes a name for each connection pool instance and manages the different connection pools by different names.

With multiple users accessing different names and passwords for the same database, you can also use resource files to set up multiple database connection information with the same URL address, but with different user names and passwords, in the resource file.

3. Transaction processing

We know that a transaction is atomic, requiring that the operation of the database conform to the "all-all-nothing" principle, that is, to a set of SQL statements, either full or none at all.

In the Java language, the connection class itself provides support for transactions by setting the connection Autocommit property to False and then explicitly invoking the commit or Rollback method. However, in order to efficiently perform connection reuse, the corresponding transaction support mechanism must be provided. Each transaction can be implemented exclusively with one connection, which can greatly reduce the complexity of transaction management.

4. Distribution and release of connection pool

The allocation and release of the connection pool have a great effect on the performance of the system. Reasonable allocation and release can increase the reuse of the connection, reduce the cost of establishing new connection, and speed up the user's access.

You can use a free pool for connection management. A connection that has been created but not yet allocated is stored in a free pool at the time of creation. Each time a user requests a connection, the system first checks to see if there is an idle connection in the free pool. If there is a connection to the longest established (by the order of the container to be implemented) assigned to him (actually the first to do the connection is valid judgment, if available, assign to the user, if not available, delete the connection from the free pool, and re-detect if there is a connection to the free pool; If not, check whether the current open connection pool reaches the maximum number of connections allowed by the connection pool (maxconn), and if not, create a new connection and wait a certain amount of time if it has been reached (timeout). If a connection is released within the waiting time, the connection can be assigned to the waiting user, or null if the wait time exceeds the scheduled time timeout. The system counts only the connections that are already being used, and returns them to the free pool when it is used. For the state of the idle connection, a dedicated thread timing detection can be developed, which will cost a certain amount of overhead, but can ensure a faster response speed. It can also take a method that does not open up specialized threads, just before the distribution is detected.

5, the connection pool configuration and maintenance

How many connections should be placed in the connection pool to make the system performance best? The system can take the setting of the minimum number of connections (minconn) and the maximum number of connections (maxconn) to control connections in the connection pool. The minimum number of connections is the number of connections created by the connection pool at system startup. If you create too many, the system starts slowly, but the system responds quickly when it is created, and if you create too little, the system starts quickly and responds slowly. In this way, you can set a smaller minimum number of connections at development time, and it will be faster to develop, and larger when the system is actually in use, because it will be faster for the client to access. Maximum connection number is the maximum number of connections allowed in the connection pool, the specific settings, depending on the amount of access to the system, through repeated testing, to find the best point.

How do I make sure that the minimum number of connections in the connection pool? There are both dynamic and static strategies. Dynamic that is, every time the connection pool is detected, if the number of connections is found to be less than the minimum number of connections, then add a corresponding number of new connections to ensure the normal operation of the connection pool. Static is found when the idle connection is not enough to check again.

Implementation of connection pooling

1. Connection Pool Model

The connection pool discussed in this article includes a connection pool class (Dbconnectionpool) and a connection pooling management class (Dbconnetionpoolmanager). A connection pool class is a "buffer pool" for all connections to a database. The main functions are as follows: ① Gets or creates an available connection from the connection pool, ② the connection to the connection pool after it is used, ③ disconnects all connections and releases the system resources occupied by the connection before the system shuts down, and ④ can also handle invalid connections ( Connections that were originally registered as available are no longer available for some reason, such as timeouts, communication problems, and can limit the total number of connections in the connection pool to no less than a predetermined value and no more than a predetermined value.

The connection pool management class is the outer covering class (wrapper) of the connection pool class, which conforms to the singleton pattern, where only one instance of the connection pool management class can be in the system. It is mainly used for the management of multiple connection pool objects and has the following functions: ① load and register the JDBC driver for a particular database; ② create a connection pool object based on the information given by the property file; ③ for the convenience of managing multiple connection pool objects, take a name for each connection pool object, The mapping between the connection pool name and its instance is implemented, and ④ tracks the customer's use of the connection so that the connection is closed to release resources. The introduction of connection pool management classes is designed to facilitate the use and management of multiple connection pools, such as the need for a system to connect to different databases, or to connect to the same database, but because of security issues, different users are required to use different names and passwords.

2, Connection Pool implementation

The main attributes of the connection pool class and connection pool management class and the basic interfaces to implement are given below:[Java]  View Plain Copy public class dbconnectionpool implements timerlistener{   Number of connections private int checkedout;//have been assigned    private arraylist freeconnections=new  ArrayList ();  //container, free pool, to store connections created but not yet allocated    private int minconn;//according to//creation time sequence Minimum number of connections in connection pool    maximum number of connections allowed in private int maxconn;//connection pool    private string name //A name for this connection pool to facilitate management    private string password;//the password required to connect to the database    private string  url;//the address of the database you want to create the connection    private string user;//the username    public timer to connect to the database  timer;//timer    Public dbconnectionpool (String name,string url,string user,    string password,int maxconn)/Open constructor    public synchronized void  freeconnection (Connection con)   //After use, return the connection to the free pool    public synchronized  connectioN getconnection (long timeout)   //Get a connection, timeout is waiting time    public synchronized  void release ()   //Disconnect all connections, releasing occupied system resources    Private connection newconnection ( )   //Create a new database connection    public synchronized void timerevent ()   // Timer event handler function   }   public class dbconnectionmanager {   static  Unique instance of private dbconnectionmanager instance;  //connection Pool management class    static private  int clients;//Customer number    private arraylist drivers=new arraylist ();  //container, Store database driver    private hashmap pools = new hashmap ();  //To name/ The form of value to access the name of the connection pool object and the connection pool object