Speaking of our company’s connection pool

First of all, we need to mention the connection pooling guidelines provided by our company

There are five methods in this principle

3 The first is to initialize the second is to get the link the third is to return the link the fourth is to create the connection the fifth is to close the connection

Initialize the connection pool

So to initialize we’re actually calling a method of our connection pool implementation class and we’re initializing the initial number of connections so what parameters do we need if we take our mysql connection pool for example and there are four parameters in the mysql connection pool implementation class that are idle queue active queue It’s the total number of connections to the queue and the basic configuration of mysql and the basic configuration of mysql includes the mysql address, the port library, the port, all of that stuff and all of the basic information about mysql and how many initializes the maximum number of connections and how many wait times we can get from that configuration when we call init So we have this information to create connections and the number of connections that we have to create is this initial number of connections that we’re talking about and then once we’re initialized the two queues need to be initialized and the length of the two queues is the maximum length that we have in our connection base information

Get connected

So we are after the initialization is complete, we can go to get links for link from idle queue and then into the event queue So this time there are two The first kind of circumstance is There is one is returned directly The second is no no we should go to a judge Does it reach the maximum number of connections And if you do, there are two things that happen when you spin and you spin and you spin and you spin and you don’t get the connection and if you don’t get the connection then you throw a connection timeout exception after you get the connection

Return the link

There’s also the return link and the return link is deleting the connection from the active queue and putting it in the free queue

Create links

Again in the future is to create links Why this create a connection open separately Because sometimes We made a reserved The reserve is a remedy our data Is our connection has been couldn’t get to try more than three times We will manually to connect a alone But this connection is only used once So what happens when this is connected

Close the connection

And then finally we have a closed connection and we close the connection recursively and we close the connection information between the two queues

The following code

MysqlPollConfig

import com.poll.BaseConfig; /** * @author: @date: 2021/5/21 14:27 **/ public class MysqlPollConfig {//id private String ID; Private int initTotal; Private int maxTotal; Private long maxWaitTime; // Drive private String driverName; //url DATABASE id of the port private String IP; private int port; private String databaseName; private String param; Private String userName; // Password private String password; public String getUrl(){ StringBuilder urlsb = new StringBuilder(BaseConfig.MYSQLJDBCPRO); urlsb.append(this.ip); / / JDBC: mysql: / / 127.0.0.1 urlsb. Append (" : "); / / JDBC: mysql: / / 127.0.0.1: urlsb. Append (enclosing port); / / JDBC: mysql: / / 127.0.0.1:3306 urlsb. Append ("/"); / / JDBC: mysql: / / 127.0.0.1:3306 / urlsb. Append (enclosing databaseName); / / JDBC: mysql: / / 127.0.0.1:3306 / etl urlsb. Append ("?" ); / / JDBC: mysql: / / 127.0.0.1:3306 / etl? urlsb.append(this.param); / / JDBC: mysql: / / 127.0.0.1:3306 / etl? serverTimezone=UTC&characterEncoding=utf-8 return urlsb.toString(); } public int getInitTotal() { return initTotal; } public void setInitTotal(int initTotal) { this.initTotal = initTotal; } public int getMaxTotal() { return maxTotal; } public void setMaxTotal(int maxTotal) { this.maxTotal = maxTotal; } public long getMaxWaitTime() { return maxWaitTime; } public void setMaxWaitTime(long maxWaitTime) { this.maxWaitTime = maxWaitTime; } public String getDriverName() { return driverName; } public void setDriverName(String driverName) { this.driverName = driverName; } public String getIp() { return ip; } public void setIp(String ip) { this.ip = ip; } public int getPort() { return port; } public void setPort(int port) { this.port = port; } public String getDatabaseName() { return databaseName; } public void setDatabaseName(String databaseName) { this.databaseName = databaseName; } public String getParam() { return param; } public void setParam(String param) { this.param = param; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; }}Copy the code

MysqlConnException

/** * @author: @date: 2021/5/21 16:24 **/ public class MysqlConnException extends RuntimeException { public MysqlConnException(String message)  { super(message); }}Copy the code

BaseConfig

Public static final String MYSQLJDBCPRO = "JDBC :mysql://"; public static void driver(String dirverName){ try { Class.forName(dirverName); } catch (ClassNotFoundException e) { e.printStackTrace(); }}}Copy the code

BasePoll

/** * @author * @className Basepoll * @description TODO * @date 2021/5/20 ** Provides a connection pool criteria */ public interface BasePoll<T> {/** * init */ public void init(); @return */ public T getConn(); /** * return connection * @param conn */ public void replease(T conn); /** * create a connection * @return */ public T createCon(); /** * close the connection */ public void closeConn(); }Copy the code

mysqlpoll

import com.poll.BaseConfig; import com.poll.BasePoll; import com.poll.Mysql; import com.poll.config.MysqlPollConfig; import com.poll.exeption.MysqlConnException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Queue; import java.util.concurrent.LinkedBlockingQueue; import java.util.concurrent.atomic.AtomicInteger; /** * @author: @date: 2021/5/21 14:53 **/ public class MysqlPoll implements BasePoll<Connection> { private static final Logger log = LoggerFactory.getLogger(Mysql.class); Private Queue<Connection> mysqlConnQueue = null; Private Queue<Connection> activeMysqlConnQueue = null; Private AtomicInteger count = new AtomicInteger(); Public MysqlPollConfig MysqlPollConfig; Public MysqlPoll(MysqlPollConfig MysqlPollConfig){this. MysqlPollConfig = MysqlPollConfig; BaseConfig.driver(mysqlpollConfig.getDriverName()); Log.info ("mysql connection pool instantiation completed "); } / / initializes the connection pool @ Override public void init () {int maxTotal = this. MysqlpollConfig. GetMaxTotal (); int initTotal = this.mysqlpollConfig.getInitTotal(); this.mysqlConnQueue = new LinkedBlockingQueue<Connection>(maxTotal); this.activeMysqlConnQueue = new LinkedBlockingQueue<Connection>(initTotal); for (int i = 0; i < initTotal; i++) { this.mysqlConnQueue.offer(createCon()); count.incrementAndGet(); } log.info("mysql connection pool initialization completed "); } //1. Check whether the current milliseconds have timed out //2. Check whether there are idle connections in the queue //3. If there are connections, obtain the connection and return //4. If there are no connections, check whether the maximum number of connections is reached //5. If the maximum number of connections is reached, wait //6. If not, create a connection //7. 8. If the connection times out, @Override public Connection getConn() {long startTime = system.currentTimemillis (); While (true){Connection conn = this.mysqlconnqueue.poll (); Connection conn = this.mysqlconnqueue.poll (); if(conn! =null){ return conn; } / / if the current quantity is less than the maximum number of connections is to create a new Connection if (count. The get () < this. MysqlpollConfig. GetMaxTotal ()) {Connection mysqlConn = createCon (); this.activeMysqlConnQueue.offer(mysqlConn); return mysqlConn; } the if (System. CurrentTimeMillis () - startTime > enclosing mysqlpollConfig. GetMaxWaitTime ()) {throw new MysqlConnException (" connection timeout!" ); }}} / / return Connection @ Override public void replease (Connection conn) {/ / delete the Connection among active queue if (this. ActiveMysqlConnQueue. Remove (conn)) { / / put the connection in the free connection of enclosing mysqlConnQueue. Offer (conn); }} / / mysql Connection information public Connection createCon () {String url = this. MysqlpollConfig. GetUrl (); String userName = this.mysqlpollConfig.getUserName(); String password = this.mysqlpollConfig.getPassword(); Connection mysqlConn = null; try { mysqlConn = DriverManager.getConnection(url, userName, password); } catch (SQLException throwables) { throwables.printStackTrace(); } the info (" initialize a database connection, {IP: "+ this. MysqlpollConfig. GetIp () +" port: "+ this. MysqlpollConfig. GetPort () +" databaseNameL:"+this.mysqlpollConfig.getDatabaseName()+"}"); return mysqlConn; } @override public void closeConn() {closeBaseConn(); closeActiveConn(); Public void closeBaseConn(){Connection poll = this.mysqlconnqueue.poll (); if(poll! =null){ try { poll.close(); } catch (SQLException throwables) { throwables.printStackTrace(); try { if(poll.isClosed()){ this.mysqlConnQueue.offer(poll); } } catch (SQLException e) { e.printStackTrace(); } }finally { closeBaseConn(); }}} / / close the active Connection public void closeActiveConn () {Connection poll = this. ActiveMysqlConnQueue. The poll (); if(poll! =null){ try { poll.close(); } catch (SQLException throwables) { throwables.printStackTrace(); try { if(! poll.isClosed()){ this.activeMysqlConnQueue.offer(poll); } } catch (SQLException e) { e.printStackTrace(); } }finally { closeActiveConn(); }}}}Copy the code

There are tests for Redis and mysql because we wrote the connection pool for Redis and we just delete the tests for Redis

import com.poll.config.MysqlPollConfig; import com.poll.config.RedisPollConfig; import com.poll.poll.MysqlPoll; import com.poll.poll.RedisPoll; import redis.clients.jedis.Jedis; import java.sql.Connection; import java.sql.SQLException; import java.util.UUID; /** * @author: @date: 2021/5/21 13:47 **/ public class Mysql { public static void main(String[] args) throws SQLException { MysqlPollConfig mysqlPollConfig = new MysqlPollConfig(); mysqlPollConfig.setIp(UUID.randomUUID().toString()); MysqlPollConfig. SetIp (" 127.0.0.1 "); mysqlPollConfig.setPort(3306); mysqlPollConfig.setDatabaseName("etl"); mysqlPollConfig.setUserName("root"); mysqlPollConfig.setPassword("root"); mysqlPollConfig.setParam("serverTimezone=UTC&characterEncoding=utf-8"); mysqlPollConfig.setDriverName("com.mysql.cj.jdbc.Driver"); mysqlPollConfig.setInitTotal(5); mysqlPollConfig.setMaxTotal(15); mysqlPollConfig.setMaxWaitTime(10); BasePoll<Connection> mysqlConnon = new MysqlPoll(mysqlPollConfig); mysqlConnon.init(); for (int i = 0; i < 5; i++) { Connection conn = mysqlConnon.getConn(); System.out.println(conn); mysqlConnon.replease(conn); } mysqlConnon.closeConn(); RedisPollConfig redisPollConfig = new RedisPollConfig(); redisPollConfig.setIp(UUID.randomUUID().toString()); RedisPollConfig. SetIp (" 127.0.0.1 "); redisPollConfig.setPort(3306); redisPollConfig.setInitTotal(5); redisPollConfig.setMaxTotal(15); redisPollConfig.setMaxWaitTime(10); BasePoll<Jedis> redispoll = new RedisPoll(redisPollConfig); redispoll.init(); for (int i = 0; i < 5; i++) { Jedis jedis = redispoll.getConn(); System.out.println(jedis); redispoll.replease(jedis); } redispoll.closeConn(); }}Copy the code

Stupid guy wrote pool poll if you mind

I hope I can summarize some things for you