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
- Online system;
- High concurrency and multithreading system;
- 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...