Java Database Connection Pool

The database connection pool is written according to your own understanding.

It realizes dynamic growth, connection reuse and so on.

When the connection pool is initially, there will be a number of connections, and as the connection requests increase, the connection is dynamically growing.

Problem: When the available connection is a percentage of the total number of connections, there will be a lot of connections will not be used, and may not be used in the future, resulting in a waste of resources, I think is to generate a thread, every time the dynamic scan, calculate this percentage, and then close a certain number of connections. Better is based on the statistics of connection requests, dynamic growth and close connections, such as user requests for the site, the early morning visits will certainly be less than the daytime, can close some connections in the early hours, the appropriate increase in the daytime number of connections.

Main code:

Import Java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import java.sql.SQLException; Import Java.sql.statement;import Java.util.arraylist;import Java.util.list;public class ConnectionPool {private String Drivername;private string Dburl;private string username;private string userpwd;private string testtablename; private int minconnectionsize = 10;//Connection pool minimum number private int maxconnectionsize = 100;private int addconnectionsize = 5;//increment each time The number of private list<myconnection> connections;//the linked container private static int count = 0;//number of available connections public ConnectionPool ( String drivername, String Dburl, String username,string userpwd, String testtablename) {this.drivername = Drivername;this . Dburl = Dburl;this.username = Username;this.userpwd = Userpwd;this.testtablename = Testtablename;} /** * Initialize, load driver etc * * @throws classnotfoundexception * @throws SQLException */public void Createconnectionpool () throws Cla ssnotfoundexception,sqlexception {class.forname (drivername); connections = new ArRaylist<myconnection> (); addconnection (minconnectionsize);} /** * Remove an available connection from the connection pool * * @return * @throws SQLException */public Connection getconnection () throws SQLException {for (MyCo Nnection conn:connections) {if (!conn.isbusy ()) {conn.setbusy (true); if (!testconnection (Conn.getconnection ())) {// A new Connection conn.setconnection (Newconnection ()) is generated when the connection is not available; This.count--;return conn.getconnection ();}} if (Connections.size () < maxconnectionsize && maxconnectionsize > 0) {//No links are available, generate some connections addconnection ( addconnectionsize);} else {//when the maximum number of connections is reached, the load status is return null;} return getconnection ();} /** * Test If a connection is available * * @param connection * @return */private boolean testconnection (Connection connection) {try {Statement s = connection.createstatement (); ResultSet rs = s.executequery ("SELECT count (*) from" + Testtablename);} catch (SQLException e) {e.printstacktrace (); return false;} return true;} /** * Add a certain number of connections * * @param num * @throws SQLException */private void addconnection (int num) throws Sqlexception {if (num + connections.size () > maxconnectionsize&& maxconnectionsize > 0) {num = Maxconnectionsize-c Onnections.size ();} for (int i = 0; i < num; i++) {Connections.add (New myconnection (Newconnection ()));}} /** * Generate a new connection * * @return * @throws SQLException */private Connection newconnection () throws SQLException {Connection conn ection = Drivermanager.getconnection (Dburl, username,userpwd); This.count++;return connection;}  /** * Release Connection * * @param connection */public void Freeconnection (Connection connection) {for (myconnection mc:connections) {if (mc.getconnection () = = connection) {Mc.setbusy (false); this.count++;break;}}} /** * Close Connection * * @param connection * @throws SQLException */public void CloseConnection (Connection connection) throws Sqlex ception {for (myconnection mc:connections) {if (mc.getconnection () = = connection) {connection.close (); this.count--; Connections.remove (MC); break;}}} /** * Empty Connection Pool * * @throws SQLException */public void Clearconnections () throws SQLException {for (myconnection mc:connections) {mc.getconnection (). Close ();} Connections.clear (); this.count = 0;} Public String toString () {return "max Connections" + maxconnectionsize + ", Minimum connections:" + minconnectionsize+ ", current number of connections" + (connections.size ()-count) + ", Number of connections available:" + count+ ", total number of connections:" + connections.size ();} Class MyConnection {private Connection connection;//link private Boolean busy = false;//is using public myconnection (connectio N connection) {this.connection = connection;} Public Connection getconnection () {return Connection;} public Boolean isBusy () {return busy;} public void Setbusy (Boolean busy) {this.busy = busy;} public void SetConnection (Connection Connection) {this.connection = Connection;}} Public String Gettesttablename () {return testtablename;} public void Settesttablename (String testtablename) {this.testtablename = Testtablename;} public int getminconnectionsize () {return minconnectionsize;} public void setminconnectionsize (int minconnectionsize) {this.minconnectionsize = minconnectionsize;} Public int Getmaxconnectionsize () {return maxconnectionsize;} public void setmaxconnectionsize (int maxconnectionsize) {this.maxconnectionsize = maxconnectionsize;} public int getaddconnectionsize () {return addconnectionsize;} public void setaddconnectionsize (int addconnectionsize) {this.addconnectionsize = Addconnectionsize;}}

Test code:

Import Java.sql.connection;import java.sql.sqlexception;import Java.util.arraylist;import Java.util.List;import Java.util.random;public class Test {private static String drivername = "Com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static String Dburl = "jdbc:sqlserver://192.168.92.3:1433; Databasename=message_list_system ";p rivate static string userName =" sa ";p rivate static string userpwd =" 123 ";p rivate STA Tic String tableName = "User_info";p ublic static void Main (string[] args) throws Classnotfoundexception,sqlexception, Int erruptedexception {ConnectionPool ConnectionPool = new ConnectionPool (drivername, Dburl,username, USERPWD, tableName); Connectionpool.createconnectionpool (); list<connection> lists = new arraylist<connection> (); for (int i = 0; i <; i++) {//Simulate multiple connection requests SYSTEM.OUT.P Rintln (ConnectionPool); Connection Connection = Connectionpool.getconnection (); Lists.add (Connection); Random random = new random (), int r = Random.nextint (2), or//generate 0-2 of the stochastic number analog release connection if (r = = 0) {//Analog release connection, One-third probability release connection for (int j = 0; (J < Random.nextint (2) + 1) && lists.size () > 0; J + +) {//free 1-2 connections int temp = Random.nextint (Lists.size ()) per release, connectionpool.freeconnection (Lists.get (temp));// Randomly select one of the available links to release the connection Lists.remove (temp); System.out.println ("Freed up" + temp + "connect");}} Thread.Sleep (200);}}

：

Java Database Connection Pool