Connection Pool technology decryption, connection pool is no stranger to us, decryption connection pool

Connection Pool technology decryption, connection pool is no stranger to us, decryption connection pool

1. Why should we use the connection pool technology?

There are some defects in the method for establishing and disabling resources for the previous database connection. Traditional database access mode in the control module: each database access corresponds to a physical connection. The physical connection must be enabled and disabled each time the database is operated. The system performance is seriously impaired.

Solution: Connection Pool ).
When the system is initially running, it actively establishes enough connections to form a pool. each time an application requests a database connection, it does not need to re-open the connection. Instead, it retrieves the existing connection from the pool. After the connection is used up, it is not closed, but returned.

2. The connection pool consists of the establishment of the connection pool, the use and management of connections in the connection pool, and the closure of the connection pool.

Iii. Core Idea of connection pool technology

Connection reuse: by establishing a database connection pool and a set of connection usage, allocation, and management policies, the connections in the connection pool can be efficiently and securely reused, this avoids the overhead of frequent establishment and shutdown of database connections.

1. Establish a connection pool
During system initialization, create a connection according to the corresponding configuration and place it in the connection pool, so that you can obtain the connection pool when necessary, so as to avoid the overhead caused by the arbitrary establishment or closure of the connection.

2. Connection usage management in the connection pool
The connection pool management policy is the core of the connection pool mechanism. After the connection pool is established, how to manage the connections in the connection pool can solve the problem of allocating and releasing connections in the connection pool, which has a great impact on the system performance. Reasonable Distribution and release of connections can improve the reuse of connections, reduce the overhead of establishing new connections, and accelerate user access.

The method used is a famous design pattern: Reference Counting (Reference count ). This mode is widely used in resource reuse. It applies this method to the distribution and release of connections and retains a reference count for each database connection, used to record the number of users of the connection.

(1) When the customer requests a database connection, first check whether there is any idle connection in the connection pool (that is, the connection that is not allocated currently ). If there is an idle connection, the connection is allocated to the customer and processed accordingly (that is, the connection is marked as in use, and the reference count is increased by 1 ). If there is no idle connection, check whether the number of connections currently opened has reached maxConn (maximum number of connections). If not, create a new connection to the requesting customer; if maxWaitTime is reached, it will be waited according to the set maxWaitTime (maximum wait time). If there is still no idle connection after waiting for maxWaitTime, an exception Without idle connection will be thrown to the user.
(2) When the customer releases the database connection, the customer first checks whether the reference times of the connection have exceeded the specified value. If so, the connection will be deleted, determine whether the total number of connections in the current connection pool is less than minConn (minimum number of connections). If the number is less than, the connection pool is full. If the number does not exceed, the connection is marked as open for reuse. It can be seen that this policy ensures the effective reuse of database connections and avoids system resource overhead caused by frequent connection establishment and release.

3. Close the connection pool
When the application exits, the connection pool should be closed. When the connection pool is established, the connection objects applied to the database should be returned to the database (that is, all database connections should be closed ), this is the opposite process of establishing a connection pool.

We use DBCP (DataBase connection pool) as the DataBase connection pool. DBCP is a java connection pool project on apache and a connection pool component used by tomcat. Using dbcp alone requires 3 packages: commons-dbcp.jar, commons-pool.jar, commons-collections.jar Since establishing a database connection is a very time-consuming behavior of resources, so through the connection pool in advance with the database to establish some connections, put in memory, when the application needs to establish a database connection, apply for one in the connection pool, and then store it back.

 

IV. Implementation of Connection Pool

Create a java project and import the corresponding 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 the connection pool is started (default value: 0)
2. maxActive: the maximum number of connections that can be connected at the same time in the connection pool (the default value is 8, and the value is adjusted to 20. The maximum number of concurrent connections for a single machine during peak hours is about 20, which can be determined based on the Application Scenario)
3. maxIdle: the maximum number of idle connections in the connection pool. If the number of idle connections exceeds the limit, it is released. If it is set to a negative number, it means no limit (eight connections by default, and maxIdle cannot be set too small, if the connection is opened at a high load, the connection opening time is faster than the closing time, which causes the number of idle in the connection pool to rise more than maxIdle, resulting in frequent connection destruction and creation, similar to the Xmx settings in jvm parameters)
4. minIdle: the minimum number of idle connections in the connection pool. If the number is lower than this, a new connection is created. (the default value is 0 and the value is adjusted to 5. The closer this parameter is to maxIdle, the better the performance, because the creation and destruction of connections consume resources, but they cannot be too large, because when the machine is idle, it will also create connections smaller than the number of minidle, similar to the Xmn setting in jvm parameters)
5. maxWait: maximum wait time. When no connection is available, the maximum wait time for the connection pool to wait for the connection to be released is exceeded. If the value-1 is set, an exception is thrown. The default value is "unlimited, adjusted to 60000 ms to avoid unlimited suspension of requests due to insufficient thread pool)

DBUtil source code is as follows:

1 package com. daliu. jdbc; 2 3 import java. io. inputStream; 4 import java. SQL. connection; 5 import java. SQL. SQLException; 6 import java. util. properties; 7 8 import org. apache. commons. dbcp. basicDataSource; 9 10/** 11 * use connection pool technology to manage database connection 12 */13 public class DBUtil {14 15 // database connection pool 16 private static BasicDataSource dbcp; 17 18 // manage connections for different threads 19 private static ThreadLocal <Connection> tl; 20 21 // obtain data through the configuration file Library parameter 22 static {23 try {24 Properties prop 25 = new Properties (); 26 27 InputStream is 28 = DBUtil. class. getClassLoader () 29. getResourceAsStream (30 "com/daliu/jdbc/db. properties "); 31 32 prop. load (is); 33 is. close (); 34 35 // 1. initialize the Connection Pool 36 dbcp = new BasicDataSource (); 37 38 39 // set the driver (Class. forName () 40 dbcp. setDriverClassName (prop. getProperty ("jdbc. driver "); 41 // set url 42 dbcp. setUrl (prop. getPro Perty ("jdbc. url "); 43 // set the database username 44 dbcp. setUsername (prop. getProperty ("jdbc. user "); 45 // set the Database Password 46 dbcp. setPassword (prop. getProperty ("jdbc. password "); 47 // initial connection count 48 dbcp. setInitialSize (49 Integer. parseInt (50 prop. getProperty ("initsize") 51) 52); 53 // maximum number of connections allowed by the connection pool 54 dbcp. setMaxActive (55 Integer. parseInt (56 prop. getProperty ("maxactive") 57) 58); 59 // sets the maximum wait time of 60 dbcp. setMaxWait (61 Inte Ger. parseInt (62 prop. getProperty ("maxwait") 63) 64); 65 // set the minimum number of idle tasks 66 dbcp. setMinIdle (67 Integer. parseInt (68 prop. getProperty ("minidle") 69) 70); 71 // set the maximum number of idle tasks to 72 dbcp. setMaxIdle (73 Integer. parseInt (74 prop. getProperty ("maxidle") 75) 76); 77 // initialization Thread Local 78 tl = new ThreadLocal <Connection> (); 79} catch (Exception e) {80 e. printStackTrace (); 81} 82} 83 84/** 85 * Get database connection 86 * @ return 87 *@ Throws SQLException 88 */89 public static Connection getConnection () throws SQLException {90/* 91 * Get an idle Connection 92 */93 Connection conn 94 = dbcp through the Connection pool. getConnection (); 95 tl. set (conn); 96 return conn; 97} 98 99 100/** 101 * close database connection 102 */103 public static void closeConnection () {104 try {105 Connection conn = tl. get (); 106 if (conn! = Null) {107/* 108 * The Connection109 * close () method obtained through the connection pool does not actually close the 110 * connection, but returns the link. 111 */112 conn. close (); 113 tl. remove (); 114} 115} catch (Exception e) {116 e. printStackTrace (); 117} 118} 119 120/** 121 * test connection success 122 * @ param args123 * @ throws SQLException124 */125 public static void main (String [] args) throws SQLException {126 System. out. println (getConnection (); 127} 128}

The effect is as follows: