Wechat official account: Su Yanlun
Integrate theory with practice and speak freely about technology and life.

1 Preface

Database connection pool is a problem that must be faced and considered in the current system development. The principle is not complex, mainly to reduce the cost of repeatedly connecting to the database; Create the expected number of database connections in the system, and manage these connections as a collection or similar to the pool in life. When they are used, they are directly taken over and used up and returned to the system management; Main difficulties to be noted:
1. Synchronization of connection pool;
2. Connection usage and idle management;
3. Management and response when the connection pool is full.

2. Application scenario of connection pool

1. Online system;
2. High concurrency and multithreading system;
3. Systems with independent service management database connection, such as middleware;

3 design

ConnectionPool defines the structure and function information of the connection pool;
PooledConnection: the connection object data structure in the connection pool;
The internal structure and implementation of ConnectionPoolImpl connection pool, creating / closing / obtaining connections;
ConnectionPoolUtil connection pool tool class, open to the public;
DBOperation database operation (read/write) simple implementation class;

4 develop and test software version

JDK 1.8，MySQL 5.7.30

5 source code

https://github.com/LanstonWu/DBConnectionPool

6 use examples

6.1 define database connection configuration file

# Database type
dbType=MySQL
# Database driven class
jdbcDriver=com.mysql.jdbc.Driver
# Database url
dbUrl=jdbc:mysql://10.192.168.1:3306/test?useSSL=false
# Database user name
dbUsername=user01
# Database password
dbPassword=Uw@0801%
# Initialize database connection
initialConnections=5
# Automatically expand the number of connections when the connection pool is full
incrementalConnections=5
# Maximum database connection
maxConnections=30

6.2 use of connection pool

//Get database connection pool tool class instance
ConnectionPoolUtil pool = ConnectionPoolUtil.getInstance();
//Initialize the connection pool according to the db configuration file
pool.initPool("/tmp/dbConnConfig");

/*
 * Query data
 */
//Get connections from connection pool
Connection conn = pool.getConnection(); 
DBOperation.read(pool.getConnection(),"select * from partiton_tab_info limit 1",null);
//Return the connection to the connection pool
pool.returnConnection(conn);

/***
 * Condition query
 */
//Get connections from connection pool
conn = pool.getConnection(); 
String[] filt={"dd01","test001"};
DBOperation.read(pool.getConnection(),"select * from partiton_tab_info where dbname=? and tabname=?",filt);
//Return the connection to the connection pool
pool.returnConnection(conn);

/***
 * insert data
 */
// Get connections from connection pool
conn = pool.getConnection();
String[] insert={"hdfs:/tmp/t001.gz",DateUtil.getCurrentDate("yyyy-MM-dd-HH:mm:ss"),"1"};
DBOperation.write(pool.getConnection(),"insert into flume_hdfs_monitor_detail(path,createdate,status) values(?,?,?)",insert);
//Return the connection to the connection pool
pool.returnConnection(conn);

/***
 * Close all connections in the connection pool
 */
pool.closeConnectionPool();

Test output;

2020-09-12 17:15:16.347 INFO  com.sywu.dao.ConnectionPoolImpl 261 <init> - dbType:MySQL,dbUrl:jdbc:mysql://10.192.168.1:3306/test?useSSL=false,dbUsername:user01,initialConnections:5,incrementalConnections:5,maxConnections:30
2020-09-12 17:15:16.874 INFO  com.sywu.dao.ConnectionPoolImpl 97 createConnections - Database connection:Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 Is created and added to the connection pool...
2020-09-12 17:15:16.874 INFO  com.sywu.dao.ConnectionPoolImpl 102 createConnections - Database connection created ....
2020-09-12 17:15:16.950 INFO  com.sywu.dao.ConnectionPoolImpl 97 createConnections - Database connection:Conn_c310ebaa-c880-496e-a4d6-c45a1df51ca8 Is created and added to the connection pool...
2020-09-12 17:15:16.950 INFO  com.sywu.dao.ConnectionPoolImpl 102 createConnections - Database connection created ....
2020-09-12 17:15:17.016 INFO  com.sywu.dao.ConnectionPoolImpl 97 createConnections - Database connection:Conn_3b209618-1319-4cac-8a88-8571e9dfbc54 Is created and added to the connection pool...
2020-09-12 17:15:17.016 INFO  com.sywu.dao.ConnectionPoolImpl 102 createConnections - Database connection created ....
2020-09-12 17:15:17.082 INFO  com.sywu.dao.ConnectionPoolImpl 97 createConnections - Database connection:Conn_e163d3b7-50e2-46c0-81ec-ff2d9d3a4f7f Is created and added to the connection pool...
2020-09-12 17:15:17.082 INFO  com.sywu.dao.ConnectionPoolImpl 102 createConnections - Database connection created ....
2020-09-12 17:15:17.152 INFO  com.sywu.dao.ConnectionPoolImpl 97 createConnections - Database connection:Conn_313319ac-8a7b-463d-a396-155de18915c0 Is created and added to the connection pool...
2020-09-12 17:15:17.152 INFO  com.sywu.dao.ConnectionPoolImpl 102 createConnections - Database connection created ....
2020-09-12 17:15:17.153 INFO  com.sywu.dao.ConnectionPoolImpl 323 createPool - Database connection pool created successfully... 
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:07.0
2020-09-12 17:15:17.182 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 Connection used...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:07.0
2020-09-12 17:15:17.193 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_c310ebaa-c880-496e-a4d6-c45a1df51ca8 Connection used...
row:1,column:1,columnName:dbname,value:testdb01
row:1,column:2,columnName:tabname,value:test_tab01
row:1,column:3,columnName:partspec,value:day
row:1,column:4,columnName:location,value:hdfs://hd01/user/data/test_tab01
2020-09-12 17:15:17.206 INFO  com.sywu.dao.ConnectionPoolImpl 365 returnConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 The connection is returned to the connection pool...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:07.0
2020-09-12 17:15:17.218 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 Connection used...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:07.0
2020-09-12 17:15:17.229 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_3b209618-1319-4cac-8a88-8571e9dfbc54 Connection used...
row:1,column:1,columnName:dbname,value:testdb01
row:1,column:2,columnName:tabname,value:test_tab01
row:1,column:3,columnName:partspec,value:day
row:1,column:4,columnName:location,value:hdfs://hd01/user/data/test_tab01
2020-09-12 17:15:17.242 INFO  com.sywu.dao.ConnectionPoolImpl 365 returnConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 The connection is returned to the connection pool...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:07.0
2020-09-12 17:15:17.254 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 Connection used...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:07.0
2020-09-12 17:15:17.267 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_e163d3b7-50e2-46c0-81ec-ff2d9d3a4f7f Connection used...
sql:com.mysql.jdbc.JDBC42PreparedStatement@5ae50ce6: insert into flume_hdfs_monitor_detail(path,createdate,status) values('hdfs:/tmp/t001.gz','2020-09-12-17:15:17','1'),insert or update:1 rows.
2020-09-12 17:15:17.280 INFO  com.sywu.dao.ConnectionPoolImpl 365 returnConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 The connection is returned to the connection pool...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:08.0
2020-09-12 17:15:17.292 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 Connection used...
row:1,column:1,columnName:CURRENT_TIMESTAMP(),value:2020-09-12 17:15:08.0
2020-09-12 17:15:17.304 WARN  com.sywu.dao.ConnectionPoolImpl 183 findFreeConnection - Conn_313319ac-8a7b-463d-a396-155de18915c0 Connection used...
sql:com.mysql.jdbc.JDBC42PreparedStatement@be64738: update flume_hdfs_monitor_detail set status='2',closedate='2020-09-12-17:15:17' where path='hdfs:/tmp/t001.gz' and status=1,insert or update:1 rows.
2020-09-12 17:15:17.317 INFO  com.sywu.dao.ConnectionPoolImpl 365 returnConnection - Conn_1a6deeb1-2a97-4307-a2c3-ec99b0970a45 The connection is returned to the connection pool...
2020-09-12 17:15:17.317 INFO  com.sywu.dao.ConnectionPoolImpl 409 closeConnectionPool - Connection Pools has 5 connections,closing now...
2020-09-12 17:15:27.318 INFO  com.sywu.dao.ConnectionPoolImpl 421 closeConnectionPool - Connection Pools has closed...