Java Database Connection Pooling implementation principle

In General, the process of accessing a database by a Java application is:
① loading the database driver;
② database connection through JDBC;
③ Access database, execute SQL statement;

④ Disconnect the database.

public class DBConnection {private Connection con;//define database Connection class object private PreparedStatement pstm;private String user= "root" ;//Connect database user name private string password= "123456";//Connect database Password private string drivername= "com.mysql.jdbc.Driver";// Database driver Private String url= "Jdbc:mysql://localhost:3306/qingqingtuan";//The URL of the connection database, followed by to prevent the insertion of the database garbled,? useunicode= true&characterencoding=utf-8//Constructor Public DBConnection () {}/** CREATE database connection */public Connection getconnection () {try{ Class.forName ("Com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {System.out.println ("Load Database driver failed!") "); E.printstacktrace ();} try {con=drivermanager.getconnection (Url,user,password);//Get Database connection} catch (SQLException e) {System.out.println (" Failed to create DATABASE connection! "); Con=null;e.printstacktrace ();} Return con;//Returns the database connection object} list<shop> mshoplist=new arraylist<shop> (); Mconnection=new DBConnection (). getconnection (); if (mconnection!=null) {try {String sql= "select * from shop"; PreparedStatement pstm=mconnection.preparestatement (SQL); ResultSet Rs=pstm.executequery (); while (Rs.next ()) {...//package POPJ operation}   Rs.close ();p stm.close (); } catch (SQLException e) {e.printstacktrace ();}  Finally{try {if (mconnection!=null) {mconnection.close ();}} catch (SQLException e) {e.printstacktrace ();}}

In the process of program development, there are many problems:

First, a database connection is established for each Web request. establishing a connection is a time-consuming activity that takes 0.05s~1s times each time, and the system allocates memory resources. This time for one or several database operations, you may not feel how much overhead the system has.

But for today's web applications, especially large e-commerce sites, there are hundreds of people or even thousands of people online is very normal. In this case, the frequent database connection operation is bound to occupy a lot of system resources, the response speed of the site must be reduced, serious or even cause a server crash . is not alarmist, this is restricting the development of some e-commerce website technology bottleneck problem. Second, for each database connection, you have to disconnect after use. Otherwise, if the program fails to shut down, it will cause a memory leak in the database system and will eventually have to restart the database

Through the above analysis, we can see that "database connection" is a scarce resource, in order to protect the normal use of the site, it should be properly managed. In fact, after we query the database, if we do not close the connection, but temporarily stored up, when others use, the connection to their use. It avoids the time consuming of establishing a database connection and a disconnected operation.

The basic idea of database connection pooling is to establish a "buffer pool" for database connections. A certain number of connections are pre-placed in the buffer pool, and when a database connection needs to be established, simply take one out of the buffer pool and put it back when you are finished. we can prevent the system from endlessly connecting to the database by setting the maximum number of connections to the connection pool

There are about 3 steps to creating a database connection pool:

① creates a ConnectionPool instance and initializes the creation of 10 connections, saved in vector (thread safe)
② Implement getconnection () get an available connection from the connection library
③ returnconnection (conn) provides methods for putting connections back into the connection pool

Connectionpool.java

Database connection Pool class Connectionpool.java/////////////////////////////////////////* This example is written according to the PostgreSQL database, and should be adjusted according to the actual database. The call method is as follows: ①connectionpool connpool = new ConnectionPool ("Com.microsoft.jdbc.sqlserver.SQLServerDriver", "JDBC: Microsoft:sqlserver://localhost:1433;databasename=mydatafortest "," Username "," Password "); ②connpool. CreatePool (); Connection conn = Connpool. getconnection ();  Connpool.returnconnection (conn); Connpool.refreshconnections (); Connpool.closeconnectionpool (); */import java.sql.connection;import java.sql.databasemetadata;import Java.sql.driver;import Java.sql.DriverManager ; Import Java.sql.sqlexception;import java.sql.statement;import java.util.enumeration;import java.util.Vector; public class ConnectionPool {private String jdbcdriver = ""; Database-driven private String Dburl = ""; Data urlprivate String dbusername = ""; Database user name private String Dbpassword = ""; Database user Password private String testtable = ""; Test table Name If the connection is available, default no test table private int InitialcOnnections = 10; Initial size of the connection pool private int incrementalconnections = 5;//Connection pool automatically increases the size of the private int maxconnections = 50; The maximum size of the connection pool is the private Vector connections = null;  The vector that holds the database connection in the connection pool, initially null//the object in it is pooledconnection type/** * constructor * * @param jdbcdriver * String JDBC driver class string            * @param dburl * String Database URL * @param dbusername * String Connection database user name * @param dbpassword * String connecting the database user's password * */public connectionpool (String jdbcdriver, String Dburl, String dbusername,string dbpassword) {th Is.jdbcdriver = Jdbcdriver;this.dburl = Dburl;this.dbusername = Dbusername;this.dbpassword = DbPassword;} /** * Returns the initial size of the connection pool * * @return The number of connections available in the initial connection pool */public int getinitialconnections () {return this.initialconnections;} /** * Set Initial size of connection pool * * @param used to set the number of connections in the initial connection pool */public void setinitialconnections (int initialconnections) {This.initialconn Ections = initialconnections;} /** * Returns the size of the connection pool automatically increased, * * @return the connection pool automatically increases the size */public int getincrementalconnections () {return THIS.INCREmentalconnections;} /** * Set the connection pool automatically increased size * * @param connection pool automatically increases the size */public void setincrementalconnections (int incrementalconnections) {This.increm Entalconnections = incrementalconnections;} /** * Returns the maximum number of available connections in the connection pool * * @return The maximum number of connections available in the connection pool */public int getmaxconnections () {return this.maxconnections;} /** * Set the maximum number of connections available in the connection pool * * @param set the maximum number of connections available in the connection pool value */public void setmaxconnections (int maxconnections) {this.maxconnection s = maxconnections;} /** * Get the name of the test database table * * @return The name of the test database table */public String gettesttable () {return this.testtable;} /** * Set the name of the test table * * @param testtable * string test table name */public void settesttable (string testtable) {This.testtab Le = testtable;} /** * * Create a database connection pool, the number of connections available in the connection pool takes the value set in class member Initialconnections */public synchronized void CreatePool () throws Exception {/ /Make sure the connection pool is not created//If the connection pool has been created, the vector connections that holds the connection will not be empty if (connections! = null) {return;//If it is created, returns the driver specified in}//instantiation JDBC Driver class Instance Driver Driver = (driver) (Class.forName (This.jdbcdriver). newinstance ());D RivermanAger.registerdriver (driver); Register the JDBC driver//Create a vector that holds the connection, initially with 0 elements connections = new vector ();//Create a connection based on the value set in Initialconnections. Createconnections (this.initialconnections);//SYSTEM.OUT.PRINTLN ("Database connection pool creation succeeded! ");} /** * Creates a specified number of database connections by Numconnections and puts these connections into the connections vector * * @param numconnections * The number of database connections to create */pri vate void createconnections (int numconnections) throws SQLException {//loop creates a specified number of database connections for (int x = 0; x < numconnection S X + +) {//is the number of database connections in the connection pool reached the maximum? The maximum value is indicated by the class member maxconnections//that if the MaxConnections is 0 or negative, there is no limit to the number of connections. If the number of connections has reached its maximum, exit. if (This.maxconnections > 0&& this.connections.size () >= this.maxconnections) {break;} Add a new Pooledconnection object to Connections vector//adds a connection to the connection pool (in Vector connections) try {connections.addelement (new Pooledconnection (Newconnection ()));} catch (SQLException e) {System.out.println ("CREATE DATABASE connection failed! "+ e.getmessage ()); throw new SQLException ();} SYSTEM.OUT.PRINTLN ("Database connection created ...");} /** * Create a new database connection andreturn it * * @return return a newly created database connection */private Connection newconnection () throws SQLException {//Create a database connection Connection conn = Dri Vermanager.getconnection (Dburl, Dbusername,dbpassword);//If this is the first time a database connection is created, that is, check the database for the number of//maximum client connections that this database is allowed to support// Connections.size () ==0 indicates that there is currently no connection created if (connections.size () = = 0) {DatabaseMetaData metaData = Conn.getmetadata (); int Drivermaxconnections = Metadata.getmaxconnections ();//The database returns a drivermaxconnections of 0, indicating that there is no maximum//connection limit for this database, or the maximum connection limit for the database does not know//Drivermaxconnections is an integer returned, indicating the number of client connections that this database allows//if the maximum number of connections set in the connection pool is greater than the number of connections allowed for the database, the maximum//of the connection pool Number of connections is the maximum number allowed for the database if (drivermaxconnections > 0&& this.maxconnections > Drivermaxconnections) { This.maxconnections = Drivermaxconnections;}} Return conn; Returns a new database connection created}/** * Returns an available database connection by calling the Getfreeconnection () function, if no database connection is currently available, and more database connections cannot be built (such as the limit for connection pool size). This function waits for a second to try to get it. * * @return Returns an available database connection object */public synchronized Connection getconnection () throws SQLException {//Make sure the connection Chi is created if (connecti ONS = = null) {return null;//connection pool not created yet,Then return null}connection conn = Getfreeconnection (); Get an available database connection//If there are no connections available at this time, that is, all connections are in use while (conn = = null) {//Wait a second to retry//SYSTEM.OUT.PRINTLN ("Wait"); wait (); conn = Getfreeconnection (); Retry until a usable connection is obtained, and if//Getfreeconnection () returns a null//indicating that a batch of connections is not available}return conn;//return available connections}/** * This function from the connection pool vector An available database connection is returned in connections, and if no database connection is currently available, this function creates several database connections based on the values set by * incrementalconnections and puts them into the connection pool. If all connections are still in use after creation, returns NULL * * @return Returns an available database connection */private Connection getfreeconnection () throws SQLException {//From connection pool Get an available database connection connection conn = Findfreeconnection (), if (conn = = null) {//If there are no connections available in the current connection pool//create some connections createconnections ( incrementalconnections);//Find out from the pool if there is an available connection conn = Findfreeconnection (), if (conn = = null) {//If the connection is not made available after creating it, returns Nullreturn null;}} Return conn;} /** * finds all connections in the connection pool, finds an available database connection, returns NULL if no connection is available * * @return returns an available database connection */private Connection findfreeconnection () thro WS SQLException {Connection conn = null; Pooledconnection Pconn = null;//gets all the objects in the connection pool vector enumeRation enumerate = Connections.elements ();//traverse all objects to see if there is an available connection while (Enumerate.hasmoreelements ()) {Pconn = ( pooledconnection) enumerate.nextelement (); if (!pconn.isbusy ()) {//If this object is not busy, obtain its database connection and set it to busy conn = Pconn.getconnection ();p conn.setbusy (TRUE);//test If this connection is available if (!testconnection (conn)) {//If this connection is no longer available, create a new connection,// and replace this unavailable connection object, if creation fails, return nulltry {conn = Newconnection ();} catch (SQLException e) {System.out.println ("CREATE DATABASE connection failed! "+ e.getmessage ()); return null;} Pconn.setconnection (conn);} Break            Already found an available connection, exit}}return conn;//return to the available connections found}/** * Test If a connection is available, if not available, turn it off and return FALSE otherwise available true * * @param conn * Database connection required to test * @return returns True to indicate that this connection is available, False for */private Boolean testconnection (Connection conn) {try {//To determine if the test table exists if (Testtable.equals ("")) {//If the test table is empty, try using the Setautocommit () method of this connection//To determine if the connection is available (this method is available only in some databases, if not available,//throws an exception). Note: Using a test table method is more reliable conn.setautocommit (true);} else {//test table tested with Test table test//Check if this connection is validstatement stmt = Conn.createstatement (); Stmt.execute ("SeleCT Count (*) from "+ TestTable);}} catch (SQLException e) {//above throws an exception, this connection is not available, closes it, and returns FALSE;CLOSECONNECTION (conn); return false;} Connection available, return Truereturn true;} /** * This function returns a database connected to the connection pool, and the connection is set to idle. All database connections that are obtained using connection pooling should be returned when this connection is not used.  * * @param the Connection object to be returned to the connection pool */public void ReturnConnection (Connection conn) {//Ensure that the connection pool exists, if the connection is not created (not present), return directly if (connections = = NULL) {System.out.println ("Connection pool does not exist, cannot return this connection to the connection pool!"); return;} Pooledconnection pconn = null; Enumeration enumerate = Connections.elements ();//traverse all connections in the connection pool to find the connection object to return while (Enumerate.hasmoreelements ()) {Pconn = (pooledconnection) enumerate.nextelement ();//Find the Connection object to return in the connection pool first if (conn = = Pconn.getconnection ()) {//found, Set this connection to idle state pconn.setbusy (FALSE); /** * Flush all connection objects in the connection pool * */public synchronized void refreshconnections () throws SQLException {//Ensure that the connection pool has been innovated if (connections = = null) {System.out.println ("Connection pool does not exist, cannot be refreshed!"); return;} Pooledconnection pconn = null; Enumeration enumerate = Connections.elements (); while (Enumerate.hasmoreelements ()) {//Get a Connection objectPconn = (pooledconnection) enumerate.nextelement ();//If the object is busy then wait 5 seconds, 5 seconds after the direct flush if (pconn.isbusy ()) {Wait (5000);//wait 5 seconds}//Close This connection, replacing it with a new connection. CloseConnection (Pconn.getconnection ());p conn.setconnection (Newconnection ());p conn.setbusy (FALSE);}} /** * Closes all connections in the connection pool and empties the connection pool. */public synchronized void Closeconnectionpool () throws SQLException {//ensures that the connection pool exists, if it does not exist, returns if (connections = = null) {System . OUT.PRINTLN ("Connection pool does not exist, cannot be closed!"); return;} Pooledconnection pconn = null; Enumeration enumerate = Connections.elements (); while (Enumerate.hasmoreelements ()) {Pconn = (pooledconnection) Enumerate.nextelement ();//If Busy, wait 5 seconds if (Pconn.isbusy ()) {Wait (5000);//wait 5 seconds}//5 seconds to close it directly closeconnection (Pconn.getconnec tion ());//Remove it from the connection pool vector connections.removeelement (pconn);} The connection pool is empty connections = null;} /** * Close a database connection * * @param need to close the database connection */private void CloseConnection (Connection conn) {try {conn.close ()} catch (Sqlexce Ption e) {System.out.println ("Error closing database connection:" + e.getmessage ());}} /** * Causes the program to wait for a given number of milliseconds * * @param given number of milliseconds */private void Wait (int mseconds) {try {thread.sleep (mseconds);} catch (Interruptedexception e) {}}/** * * internal used to save the connection object in the connection pool class in this class There are two members, one is the connection to the database, and the other is a flag indicating whether this connection is being used. */class pooledconnection {Connection Connection = null;//database connection Boolean busy = false;//This connection is using a flag that is not being used by default//constructor, according to a Connection a Pooledconnection object public pooledconnection (Connection Connection) {this.connection = Connection;} Returns the connection in this object public Connection getconnection () {return Connection;} To set this object, connect public void SetConnection (Connection Connection) {this.connection = Connection;} Gets whether the object connection is busy public boolean isBusy () {return busy;} The connection to the setting object is busy public void Setbusy (Boolean busy) {this.busy = Busy;}}}

Connectionpoolutils.java

/* Connection Pooling tool class, returns a unique database connection pool object, Singleton mode */public class Connectionpoolutils {private Connectionpoolutils () {};//private static method private static ConnectionPool poolinstance = Null;public static ConnectionPool getpoolinstance () {if (poolinstance = = null) { Poolinstance = new ConnectionPool ("Com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/test?useunicode=true& Characterencoding=utf-8 "," root "," 123456 "); try {poolinstance.createpool ();} catch (Exception e) {//TODO Auto-generated catch Blocke.printstacktrace ();}} return poolinstance;}}

Connectionpooltest.java

Import Java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import java.sql.SQLException; Import Java.sql.statement;public class Connectiontest {/** * @param args * @throws Exception */public static void Main (St  Ring[] args) throws Exception {try {/* * Use connection pool to create 100 connections *//*//Create database Connection library object ConnectionPool Connpool = new       ConnectionPool ("Com.mysql.jdbc.Driver", "Jdbc:mysql://localhost:3306/test", "root", "123456"); New Database Connection library Connpool.createpool (); */ConnectionPool connpool=connectionpoolutils.getpoolinstance ();//Singleton mode       Create connection pool object//SQL TEST statement String sql = "SELECT * from Pet";             Set program run start time long start = System.currenttimemillis (); Cyclic test 100 database connection for (int i = 0; i < i++) {Connection conn = connpool.getconnection ();                  Get an available connection from the connection library Statement stmt = Conn.createstatement ();               ResultSet rs = stmt.executequery (SQL);   while (Rs.next ()) {String name = rs.getstring ("name");                  SYSTEM.OUT.PRINTLN ("query result" + name);                  } rs.close ();                  Stmt.close (); Connpool.returnconnection (conn);//After the connection is used, release the connection to the connection pool} System.out.println ("After 100 cycles of calls, the time spent using the connection pool:"              + (System.currenttimemillis ()-start) + "MS"); Connpool.refreshconnections ();//Refreshes all connections in the database connection pool, i.e. all connections are freed and put back into the connection pool regardless of whether the connection is running.             Note: This time is much more expensive. Connpool.closeconnectionpool ();//Close database connection pool.              Note: This time is much more expensive.                            Set Program run start time start = System.currenttimemillis ();              /* Do not use connection pooling to create 100 connection times *//import drive Class.forName ("Com.mysql.jdbc.Driver");                           for (int i = 0; i < i++) {//create connection Connection conn = Drivermanager.getconnection (              "Jdbc:mysql://localhost:3306/test", "root", "123456");    Statement stmt = Conn.createstatement ();                 ResultSet rs = stmt.executequery (SQL);                 while (Rs.next ()) {} rs.close ();                 Stmt.close (); Conn.close ();//Close connection} System.out.println ("After 100 cycles of calls, time spent without connection pooling:" + (System         . Currenttimemillis ()-start) + "MS");         } catch (SQLException e) {e.printstacktrace ();        } catch (ClassNotFoundException e) {e.printstacktrace (); }}

Java Database Connection Pooling implementation principle