Connection pooling technology decryption, connection pool no longer familiar to us

First, why should we use connection pooling technology?

There are some drawbacks to the method of establishing and shutting down resources in the previous database connection. Steerage Traditional Database access: Once the database access corresponds to a physical connection, each operation of the database to open, close the physical connection, the system performance is severely compromised.

Solution: Database connection pooling (Connection pool).
When the system is initially running, it actively establishes enough connections to form a pool. Each time the application application requests a database connection, it does not need to reopen the connection, but instead removes the existing connection from the pool, and then returns it instead of closing it.

Two, the connection pool mainly consists of three parts: Connection pool establishment, connection pool connection management, connection pool shutdown.

Third, the core idea of connection pool technology

Connection multiplexing, through the establishment of a database connection pool and a set of connection usage, allocation, management policies, so that the connection pool connections can be efficient and secure reuse, to avoid the database connection frequent establishment, shutdown overhead.

1. Establishment of Connection pool
When the system is initialized, a connection is created based on the corresponding configuration and placed in the connection pool so that it can be obtained from the connection pool when it is needed, thus avoiding the overhead associated with arbitrary establishment and shutdown of the connection.

2. Connection usage management for connections in pools
The connection pooling management policy is the core of the connection pooling mechanism. When the connection pool is established, how to manage the connections in the connection pool, and solve the allocation and release of connections in the connection pool, has a great impact on the performance of the system. The reasonable allocation and release of the connection can improve the reuse of the connection, reduce the cost of the system to establish new connection, and accelerate the user's access speed.

The method used is a well-known design pattern: Reference counting (reference count). This model is widely used in reusing resources, applying this method to the allocation release for connections, and keeping a reference count for each database connection, to record the number of users of the connection.

(1) When a customer requests a database connection, first look at whether there is an idle connection in the connection pool (refers to a connection that is not currently assigned). If there is an idle connection, assign the connection to the customer and process it accordingly (that is, mark the connection as being used, the reference count plus 1). If there is no idle connection, check to see if the number of connections currently open has reached Maxconn (maximum number of connections), re-create a connection to the requested customer if it is not reached, and wait for the set Maxwaittime (maximum wait time) if it is reached. If there is still no idle connection after waiting for Maxwaittime, the exception to the user is thrown without idle connections.
(2) When the customer releases the database connection, first determine whether the number of references to the connection exceeds the specified value, if the connection is deleted more than the total number of connections in the current connection pool is less than minconn (minimum number of connections), if less than the connection pool is full, if not more than the connection is marked as open state, Available for re-use. It can be seen that this policy ensures the effective reuse of database connections and avoids the overhead of establishing and releasing connections frequently.

3. Closing the connection pool
When the application exits, the connection pool should be closed and the connection object requested for the database at the time the connection pool is established should be returned to the database uniformly (that is, all database connections are closed), which is exactly the opposite of the connection pool establishment.

We use the DBCP (database connection pool), the connection pool of databases. DBCP (is a Java Connection pool project on Apache and the connection pool component used by Tomcat.) Use of DBCP alone requires 3 packages: Commons-dbcp.jar,commons-pool.jar, Commons-collections.jar because establishing a database connection is a very time-consuming and resource-intensive behavior, the connection pool is pre-established with the database to make some connections, put in memory, the application needs to establish a database connection directly to the connection pool to apply for a line, run out and then put back.

Iv. implementation of the connection pool

Create a new Java project and import the appropriate package.

Configuration file:

1 jdbc.driver=com.mysql.jdbc.Driver2 jdbc.url=jdbc:mysql://localhost : 3306/csdn3 jdbc.user=root4 jdbc.password=1234565 initsize=16 maxactive=17 maxwait=50008 maxidle=19 minidle=1

Introduction to the basic configuration of DBCP

1.initialSize: Number of initial connections created when connection pooling starts (default is 0)
2.maxActive: The connection pool can be connected to the maximum number of connections (the default is 8, adjusted to 20, peak single machine in 20 concurrency, depending on the application scenario)
3.maxIdle: The maximum number of idle connections in the connection pool, the excess idle connection will be released, if set to negative numbers means no limit (the default is 8, Maxidle cannot be set too small, because if in high load, the connection is opened more quickly than the shutdown time, Causes the number of idle in the connection pool to rise above maxidle, causing frequent connection destruction and creation, similar to the XMX setting in the JVM parameters)
4.minIdle: The minimum number of idle connections in the connection pool, lower than this number will be created a new connection (default is 0, adjusted to 5, the closer the Maxidle, the better performance, because the creation and destruction of the connection is required to consume resources, but not too large, because when the machine is very idle, Also creates a connection that is lower than the number of minidle, similar to the XMN setting in the JVM parameter)
5.maxWait: Maximum wait time, when there is no available connection, the connection pool waits for the maximum time the connection is released, exceeding that time limit throws an exception, if set-1 means infinite wait (default is infinity, adjust to 60000ms, avoid the thread pool is not enough, and cause the request to be suspended indefinitely)

Dbutil source code is as follows:

1  PackageCom.daliu.jdbc;2 3 ImportJava.io.InputStream;4 Importjava.sql.Connection;5 Importjava.sql.SQLException;6 Importjava.util.Properties;7 8 ImportOrg.apache.commons.dbcp.BasicDataSource;9 Ten /** One * Manage database connections using connection pooling technology A  */ -  Public classDbutil { -      the     //Database Connection Pool -     Private StaticBasicdatasource dbcp; -      -     //managing connections for different threads +     Private StaticThreadlocal<connection>tl; -      +     //to get database parameters through a configuration file A     Static{ at         Try{ - Properties prop -=NewProperties (); -              - InputStream is -= Dbutil.class. getClassLoader () in . getResourceAsStream ( -"Com/daliu/jdbc/db.properties"); to              + Prop.load (IS); - is.close (); the              *             //First, initialize the connection pool $DBCP =NewBasicdatasource ();Panax Notoginseng              -              the             //Set Driver (Class.forName ()) +Dbcp.setdriverclassname (Prop.getproperty ("Jdbc.driver")); A             //Set URL theDbcp.seturl (Prop.getproperty ("Jdbc.url")); +             //Set Database user name -Dbcp.setusername (Prop.getproperty ("Jdbc.user")); $             //Set Database Password $Dbcp.setpassword (Prop.getproperty ("Jdbc.password")); -             //number of initial connections - Dbcp.setinitialsize ( the Integer.parseint ( -Prop.getproperty ("Initsize")Wuyi                     ) the             ); -             //Maximum number of connections allowed for a connection pool Wu dbcp.setmaxactive ( - Integer.parseint ( AboutProp.getproperty ("Maxactive") $                     ) -             ); -             //set maximum wait time - dbcp.setmaxwait ( A Integer.parseint ( +Prop.getproperty ("Maxwait") the                     ) -             ); $             //Set minimum idle number the Dbcp.setminidle ( the Integer.parseint ( theProp.getproperty ("Minidle") the                     ) -             ); in             //Set maximum idle number the Dbcp.setmaxidle ( the Integer.parseint ( AboutProp.getproperty ("Maxidle") the                     ) the             ); the             //Initialize thread local +TL =NewThreadlocal<connection>(); -}Catch(Exception e) { the e.printstacktrace ();Bayi         } the     } the      -     /** - * Get database connection the      * @return the      * @throwsSQLException the      */ the      Public StaticConnection getconnection ()throwssqlexception{ -         /* the * Get an idle connection from the connection pool the          */ the Connection Conn94=dbcp.getconnection (); the Tl.set (conn); the         returnConn; the     }98      About      -     /**101 * Close Database connection102      */103      Public Static voidCloseConnection () {104         Try{ theConnection conn =tl.get ();106             if(Conn! =NULL){107                 /*108 * Connection obtained through the connection pool109 * The Close () method does not actually the * The connection is closed, but the link is returned. 111                  */ the conn.close ();113 Tl.remove (); the             }     the}Catch(Exception e) { the e.printstacktrace ();117         }118     }119      -     /**121 * Test If the connection is successful122      * @paramargs123      * @throwsSQLException124      */ the      Public Static voidMain (string[] args)throwsSQLException {126 System.out.println (getconnection ());127     } -}

The effect is as follows:

Connection pooling technology decryption, connection pool no longer familiar to us