DB connection pool memo

In network programming, it is proposed to put sockets into the pool so that multiple threads can share the conenction of these sockets)

The DB connection pool is an implementation based on the connection pool. The connection pool is a technology used by the client. For example, c3p0 and DBCP both use applications as clients to access the database through the connection pool.

 

 

 

1. Connection Pool past: access the database through JDBC:

Generally, the Java application accesses the database (1) as follows:
① Load the database driver;
② Establish a database connection through JDBC;
③ Access the database and execute SQL statements;
④ Disconnect the database.

 

 

2. Why not use JDBC now:

As a database access technology, JDBC is easy to use. However, using this mode for Web application development has many problems:

1) Each Web request requires a database connection. Establishing a connection is a time-consuming activity, and it takes seconds each time ~ 1 S, and the system also needs to allocate memory resources. This time may not feel the system overhead for one or several database operations. However, for today's Web applications, frequent database connection operations by many people are bound to occupy a lot of system resources.

2) each database connection must be closed after use. Otherwise, if the program fails to be closed due to an exception, memory leakage in the data library system will occur and the database will have to be restarted. In addition, such development cannot control the number of connection objects to be created, and system resources will be allocated without any consideration. For example, too many connections may also cause memory leaks and server crashes.

Solution: DB connection pool

 

3. Connection Pool principle: resource pool ). This mode is designed to solve the problems caused by frequent resource allocation and release. 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 and usage of database connections through the connection pool management mechanism to provide a basis for system development, testing, and performance tuning.

 

 

 

 

4. Analysis of key connection pool problems
4.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 ().
Over 4.2 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 (<poolname. URL>), user name (<poolname. user>), password (<poolname. password>. 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.
4.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.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 is unavailable, delete the connection from the idle pool and check whether there is any connection 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, create a new connection, 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 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.
4.5 connection pool configuration and Maintenance
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 access volume of the system. 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.

 

5. Implementation of the Connection Pool kW: connection pool management class and connection pool class. The connection pool management class is initialized through XML or attribute files. Supports connection pools for different types of databases. The connection pool of different users in the same database. Service provider framework.
5.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 a wrapper of the connection pool class. It complies with the singleton mode, that is, the system can only have one connection pool management class instance. 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 by the attribute 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, to close the connection, release the resource. 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.
5.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
}

5.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();
    }

 

6. Implement connection pool based on existing Java. SQL. datasource

6.1 to write a connection pool, you must implement the java. SQL. datasource interface. The datasource interface defines two overloaded getconnection methods:

Connection. getconnection ();

Connection. getconnection (string username, string password );

6.2 perform the following steps to implement the datasource interface and the connection pool function: kW: Close rewrite, three rewrite Methods

In
The datasource constructor creates connections to the database in batches and adds the created connections to the listlist object. Implement getconnection
Method, so that each call of the getconnection method will return a connection from the consumer list to the user. When the user is used up
The connection object should ensure that the connection is returned to the connected list, rather than to the database. (This is a very important part of connection pool implementation. Must overwrite the logic of the original close .)

When writing the database connection pool, we should consider that it is easy to obtain the connection from the database, but when the user uses up the connection, call connection. Close ()
Method, the connection will be directly handed back to the database instead of being put back in the connection pool. Therefore, we need to dynamically change the connection method. There are three ways to implement (1) write one
Subclass of connection. All methods are rewritten. However, this method has a problem, because the connection object contains too much data, how can we
Connection
Is the data copied to our current subclass? This is a complex process. (2) The decoration mode can dynamically add new features to a class. The decoration mode involves several steps: decoration class and mounted
The decoration class must have a common parent interface. Secondly, the decoration class maintains an object of the decoration class. At the same time, the decoration class constructor must pass in the decorated object to assign values. Then we can
Add the original modification function. When we want to use the decoration class, we will return the objects after the decoration to the user, from
And achieve the goal. However, the decoration mode is suitable for simple classes and is not suitable for objects with many methods, because sometimes we only need to modify some functions, and other functions still want to call the methods of the decoration class,
In this case, we must modify the method one by one to call the method of the decoration class. This project is very huge. (3) Dynamic proxy can intercept direct access to objects. If I
To modify a method, you only need to make a judgment before calling the object. If we want to modify the method, we can directly intercept it. If not, we can directly hand it over to the proxy object for processing. Because
Dynamic proxy is based on the proxy object and the proxy object must have the same interface, so we can call it in a unified manner based on the interface, and the runtime is expressed in a multi-state form.

Public class performancepool {

Private Static consumer list <connection> List = new consumer list <connection> ();

Static {
For (INT I = 0; I <5; I ++ ){
Connection con = dbhelper. getconnection ();
List. Add (CON );
}
}

Public connection getconnection () throws sqlexception {
If (list. Size ()> 0 ){
System. Out. println ("current connection pool:" + list. Size () + "Connections ");
Final connection con = List. removefirst ();
System. Out. println ("current connection pool:" + list. Size () + "Connections ");
Return (connection) proxy. newproxyinstance (performancepool. Class. getclassloader (), Con. getclass (). getinterfaces (), new invocationhandler (){

Public object invoke (Object proxy, method, object [] ARGs)
Throws throwable {
String methodname = method. getname ();
If (methodname. Equals ("close ")){
List. Add (CON );
System. Out. println ("current connection pool:" + list. Size () + "Connections ");
Return NULL;
} Else {
Return method. Invoke (con, argS );
}
}

});
}
Return NULL;
}
}

 

7. WHO is providing the connection pool:

7.1 The application server provides a connection pool. Tomcat initializes the connection pool through JNDI. In the future, the application uses JNDI to find the corresponding connection pool to obtain the connection.

7.2.c3p0 and DBCP. DBCP use the decoration mode to overwrite close.

Refer:

Http://www.zclw.net/article/sort040/sort043/info-170013.html

Http://blog.csdn.net/driverking/article/details/6741348