Java Database Connection Pooling implementation principle

Why connection pooling is used when connecting to a database

database Connectivity is a critical, limited, and expensive resource that is particularly prominent in multi-user Web applications. A database Connection object corresponds to a physical database connection, each of which opens a physical connection and closes the connection after use, which results in poor performance of the system. The solution to a database connection pool is to establish enough database connections when the application starts, and to make these connections a pool of connections (simply put a lot of semi-finished database join objects in a "pool"), and the application dynamically requests, uses, and frees the connections in the pool. For concurrent requests that are more than the number of connections in the connection pool, they should be queued in the request queue. And the application can dynamically increase or decrease the number of connections in the pool based on the utilization of the connections in the pool. Connection pooling technology uses as much memory resources as possible, greatly saving memory, improving Server service efficiency, and supporting more customer service. The use of connection pooling will greatly improve the efficiency of the program, while we can monitor the number of database connections, usage, etc. through its own management mechanism.

Second, the basic principle of database connection pool

The basic idea of a database connection pool 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. More importantly, we can monitor the number of connections to the database through the management mechanism of the connection pool? Usage, provide basis for system development, test and performance adjustment.

Third, how the database connection pool works

The connection pooling works mainly consists of three parts, namely the establishment of connection pool, the use management of connection pool, and the closing of connection pool.

First, the establishment of the connection pool. Typically, when the system is initialized, the connection pool is established according to the system configuration and several connection objects are created in the pool so that it can be obtained from the connection pool when it is used. Connections in the connection pool cannot be created and closed at will, thus avoiding the overhead associated with arbitrary connection creation and shutdown. There are many container classes available in Java that make it easy to build connection pools, such as vectors, stacks, and so on.

Second, the management of the connection pool. The connection pooling management policy is the core of the connection pooling mechanism, and the allocation and release of connections within the connection pool have a great impact on the performance of the system. Its management strategy is:

When a customer requests a database connection, first to see if there is an idle connection in the connection pool, assign the connection to the customer if there is an idle connection, and if there is no idle connection, see if the number of connections currently open has reached the maximum number of connections and re-create a connection to the requested customer if not met If it is reached and waits at the maximum waiting time set, an exception is thrown to the customer if the maximum wait time is exceeded.

When a customer releases a database connection, it first determines whether the number of references to the connection exceeds the specified value, and if the connection is removed from the connection pool, it is reserved for other customer services.

This strategy ensures the effective reuse of database connection, avoids the system resource overhead caused by frequent establishment and release of connection.

Third, the connection pool is closed. When the application exits, closes all connections in the connection pool, releasing the connection pool-related resources, which is exactly the opposite of creation.

Analysis of key problems of connection pool

　　1, concurrency problems

In order for the connection Management Service to be the most versatile, you must consider a multithreaded environment, which is a concurrency problem. This is a relatively good problem to solve because the Java language itself provides support for concurrency management, and using the Synchronized keyword ensures that threads are synchronized. Use the method to precede the class method with the Synchronized keyword, such as:

Public synchronized Connection getconnection ()

2, multi-database server and multi-user

For large enterprise applications, it is often necessary to connect different databases at the same time (such as connecting Oracle and Sybase). How do I connect to different databases? The strategy we employ is to design a connection pool management class that conforms to the singleton pattern, reading a resource file when the unique instance of the connection pool management class is created, where the resource file holds the URL address () of multiple databases (). User name (), password (), and other information. such as tx.url=172.21.15.123:5000/tx_it,tx.user=yang,tx.password=yang321. Creates instances of multiple connection pool classes, each of which is a connection pool for a particular database, based on the information provided by the resource file. The connection pool management class instance takes a name for each connection pool instance and manages the different connection pools with different names.

In the case of multiple users with different names and passwords for the same database, it is also possible to process the resource file by setting up multiple database connection information in the resource file with the same URL address but with a different user name and password.

3. Transaction processing

We know that transactions are atomic, which requires that the operation of the database conform to the "all-all-nothing" principle, which is to either do the whole set of SQL statements or do nothing at all.

In the Java language, the connection class itself provides support for transactions by setting the Autocommit property of connection to false and then explicitly invoking either the commit or Rollback method. However, for efficient connection reuse, it is necessary to provide the corresponding transaction support mechanism. Each transaction can be implemented with a single connection, which can greatly reduce the complexity of transaction management.

4. Allocation and release of connection pool

The allocation and release of the connection pool has a great impact on the performance of the system. Reasonable allocation and release can increase the reusability of the connection, thus reducing the cost of establishing a new connection and speeding up the user's access speed.

You can use the free pool for management of connections. A connection that has been created but not yet allocated is stored in a free pool at the time of creation. Whenever a user requests a connection, the system first checks that there are no idle connections in the free pool. If there is the longest established (through the container in the order of the implementation) of the connection assigned to him (the actual connection is to make a valid judgment, if available to assign to the user, if it is not available to delete the connection from the free pool, re-detect the free pool is still connected); If not, check whether the currently open connection pool reaches the maximum number of connections allowed by the connection pool (maxconn), and if not, create a new connection and wait a certain amount of time (timeout) if it is reached. If a connection is freed within the waiting time, the connection can be assigned to the waiting user, and a null value (NULL) is returned if the wait time exceeds the scheduled time of timeout. The system only counts the connections that are already being used, and then returns them to the free pool when it is finished. For the state of idle connections, a dedicated thread timing detection can be created, which can cost a certain amount of overhead, but ensures a faster response time. You can also take a method that does not open up specialized threads, just before the allocation is detected.

5, the connection pool configuration and maintenance

How many connections should be placed in the connection pool in order to make the system performance best? The system can take control of connections in the connection pool by setting the minimum number of connections (minconn) and the maximum number of connections (maxconn). The minimum number of connections is the number of connections created by the connection pool when the system starts. If you create too many, the system starts slowly, but the system responds quickly after creation, and if you create too little, the system starts quickly and responds slowly. In this way, you can set a smaller minimum number of connections at development time, develop quickly, and set a larger size when the system is actually used, because it will be faster to access the customer. Maximum connections is the maximum number of connections allowed in the connection pool, depending on the amount of access to the system, you can find the best point by repeated testing.

How do you ensure the minimum number of connections in a connection pool? There are both dynamic and static strategies. Dynamic is to detect the connection pool at regular intervals, and if the number of connections is found to be less than the minimum number of connections, the corresponding number of new connections will be replenished to ensure the proper operation of the connection pool. Static is found when idle connection is insufficient to check again.

V. Connection pool Implementation code (Java)

Package Book.util;import Java.sql.connection;import Java.sql.databasemetadata;import java.sql.date;import Java.sql.driver;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.ResultSet;import Java.sql.sqlexception;import Java.sql.statement;import Java.util.vector;public class Pool {public static void main ( String[] args) {Pool pool = new Pool ("Com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://localhost:1433 ;D Atabasename=book "," sa "," aaaaaa "); try {pool.createconnections (4);} catch (SQLException e) {e.printstacktrace ();} Connection conn = pool.getconnection (); try {String sql = "SELECT * from Allbook"; PreparedStatement ps;ps = conn.preparestatement (sql); ResultSet Rs=ps.executequery (); while (Rs.next ()) {System.out.println (rs.getstring ("BookName"));}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} Finally{pool.returnconnection (conn);} Long Starttime=system.currenttimemillis (); Long Endtime=system.currenttimemillis (); SYSTEM.OUT.PRINTLN ("program Run Time:" + (Endtime-starttime) + "MS"); }private string jdbcdriver = "";//Database driver private String dburl = "";//Database urlprivate String dbusername = "";//database user name Private St Ring Dbpassword = "";//Database password private String testtable = "";p rivate int initialconnectionsnum = 10;//Connection Pool initial connection number private int max Connectionsnum = 50;//Connection pool maximum number of connections private int incrementalconnections = 5;//Each dynamically added number of connections private vector<pooledconnection > connections = null;//vector, holds connections in connection pool, initially empty//parameterless constructor */public Pool () {}/* constructor with parameters * Initialize database driver, database URL, database user name, database password, TEST table * */  Public Pool (string driver, string URL, string name, String pass) {This.jdbcdriver = Driver;this.dburl = Url;this.dbusername  = Name;this.dbpassword = Pass;//this.testtable = table;try {This.createpool ();} catch (Instantiationexception e) {//TODO Auto-generated catch Blocke.printstacktrace ();} catch (Illegalaccessexception e) {//TODO auto-generated catch Blocke.printstacktrace ();} catch (ClassNotFoundException e) {//TODO auto-generated catch Blocke.printstacktracE ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}} /* function, create connection pool */public synchronized void CreatePool () throws Instantiationexception, Illegalaccessexception, ClassNotFoundException, sqlexception{/* ensure that the connection pool is created, and if it has already been created, the vector that holds the connection is not empty * */if (this.connections! = null) {return;} Drive Instantiation Driver Driver = (driver) (Class.forName (This.jdbcdriver). newinstance ());// Register drive Drivermanager.registerdriver (driver);//Create a vector to save the connection this.connections = new vector<pooledconnection> ();// Create DATABASE connection this.createconnections (this.initialconnectionsnum);}  /* function, creating a database connection * */private void createconnections (int num) throws sqlexception{/* loop Creating a connection * requires first checking that the current number of connections has exceeded the connection pool maximum connections * */for (int i = 0; i < num; ++i) {//Check if (this.connections.size () >= this.maxconnectionsnum) {return;} Create Connection This.connections.addElement (new Pooledconnection (Newconnection ()));}} /* function, create a database connection */private Connection newconnection () throws sqlexception{/* Create a connection */connection con = Drivermanager.getconnection (This.dburl, This.dbusername, this.dbpassword);/* If you are creating a connection for the first time, check that the maximum number of connections allowed for the database is less than the maximum number of connections we set */if (this.connections.size () = = 0) { DatabaseMetaData metadata = Con.getmetadata ();//Get database maximum number of connections int dbmaxconnectionsnum = Metadata.getmaxconnections ();// If the maximum number of database connections is smaller, change the maximum number of connection pool connections we set if (Dbmaxconnectionsnum > 0 && this.maxconnectionsnum > Dbmaxconnectionsnum) {this.maxconnectionsnum = Dbmaxconnectionsnum;}} return con;} /* function, get an available connection * */public synchronized Connection getconnection () {Connection con = null;/* check if the connection pool has been established */if (THIS.CONNECTI ONS = = null) {return con;} Get a usable connection try {con = this.getfreeconnection ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktra CE ();} If no suitable connection is found, loop wait, find, know to find the appropriate connection while (con = null) {this.wait (); try {con = this.getfreeconnection ();} catch ( SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}} return con;} /* function, get an available connection */private Connection getfreeconnection () throws sqlexception{connection con = null;//find an available connection con = This.findfreeconnection ();//If no available connections are found, create some new connections, and look again if (con = null) {this.createconnections (this.incrementalconnections);//find con again = This.findfreeconnection ();} return con;} /* function, find an available connection from an existing connection * Find an idle connection in the existing connection (vector connections), * and test if the link is available, reestablish the connection if it is not available, and replace the original connection */private Connection Findfreeconnection () throws sqlexception{connection con = null;for (int i = 0; i < this.connections.size (); ++i) {Poole Dconnection pol = (pooledconnection) this.connections.get (i); if (!pol.isbusy ()) {/* If this link is not used, return this connection and, set in use flag */con = Pol.getcon ();p ol.setbusy (TRUE);/* Test If the connection is available */if (!this.testcon (con)) {con = this.newconnection ();p ol.setcon (con);} Break;}} return con;} /* function to test if the connection is available * */private boolean testcon (Connection con) {boolean useable = True;try{statement st = con.createstatement () ; ResultSet rs = st.executequery ("SELECT count (*) from" + this.testtable); Rs.next ();} catch (SQLException e) {/* above throws an exception, connection not available, close */useable = False;this.closeconnection (con);} return useable;} /* function to put the completed connection back into the connection pool * */public void returnconnection (Connection con){/* Ensure that the connection pool exists */if (this.connections = = null) {return;} for (int i = 0; i < this.connections.size (); ++i) {Pooledconnection pool = this.connections.get (i);//Find appropriate connection, setting is using flag as FA Lseif (con = = Pool.getcon ()) {pool.setbusy (false);}}} /* function, flush connections in the connection pool */public synchronized void Refreshconneciontpool () throws sqlexception{/* ensure that the connection pool exists */if (this.connections = = null) {return;} for (int i = 0; i < this.connections.size (); ++i) {Pooledconnection pool = this.connections.get (i); if (Pool.isbusy ()) {th Is.wait (5000);} This.closeconnection (Pool.getcon ());p Ool.setcon (This.newconnection ());p ool.setbusy (FALSE);}} /* function, close connection pool */public void Closeconnectionpool () {/* Ensure that the connection pool exists */if (this.connections = = null) {return;} for (int i = 0; i < this.connections.size (); ++i) {Pooledconnection pool = this.connections.get (i); if (Pool.isbusy ()) {th Is.wait (5000);} This.closeconnection (Pool.getcon ()); This.connections.remove (i);} this.connections = null;} /* function, temporarily no connection available, go into wait queue for M seconds, retry * */private void wait (int msecond) {try {thread.sleep (msecond);} catch (Interruptedexception e) {//TODO auto-generated catch Blocke.printstacktrace ();}} /** * @return The Jdbcdriver */public String getjdbcdriver () {return jdbcdriver;} /** * @param jdbcdriver the jdbcdriver to set */public void Setjdbcdriver (String jdbcdriver) {this.jdbcdriver = Jdbcdriver ;} /** * @return The Dburl */public String Getdburl () {return dburl;} /** * @param dburl the dburl to set */public void Setdburl (String dburl) {this.dburl = Dburl;} /** * @return The Dbusername */public String getdbusername () {return dbusername;} /** * @param dbusername the dbusername to set */public void Setdbusername (String dbusername) {this.dbusername = Dbusername ;} /** * @return The Dbpassword */public String Getdbpassword () {return dbpassword;} /** * @param dbpassword the Dbpassword to set */public void Setdbpassword (String dbpassword) {This.dbpassword = Dbpassword ;} /** * @return The testtable */public String gettesttable () {return testtable;} /** * @param testtable the testtable to set */public void Settesttable (String testtable) {this.testtable = testtable;} /** * @return the initialconnectionsnum */public int getinitialconnectionsnum () {return initialconnectionsnum;} /** * @param initialconnectionsnum the initialconnectionsnum to set */public void setinitialconnectionsnum (int initialCon Nectionsnum) {this.initialconnectionsnum = Initialconnectionsnum;} /** * @return the maxconnectionsnum */public int getmaxconnectionsnum () {return maxconnectionsnum;} /** * @param maxconnectionsnum the maxconnectionsnum to set */public void setmaxconnectionsnum (int maxconnectionsnum) {thi S.maxconnectionsnum = Maxconnectionsnum;} /** * @return the incrementalconnections */public int getincrementalconnections () {return incrementalconnections;} /** * @param incrementalconnections the incrementalconnections to set */public void setincrementalconnections (int increme ntalconnections) {this.incrementalconnections = incrementalconnections;} /** * @return The connections */public vector<pooledconnection> geTconnections () {return connections;} /** * @param connections The connections to set */public void Setconnections (vector<pooledconnection> connections) { This.connections = connections;} /* Function, the connection is finished using, close connection */private void CloseConnection (Connection con) {try{con.close ();} catch (SQLException e) {e.printstacktrace ();}} /* The class that holds the database connection used internally * Two member variables: connection, whether you are using */class pooledconnection{private Connection con = null;//connection private Boolean busy = Fals e;//is being used, the default is not/* constructor */public pooledconnection (Connection con) {this.con = con;} /** * @return The con */public Connection Getcon () {return con;} /** * @param con the con to set */public void Setcon (Connection con) {this.con = con;} /** * @return The busy */public boolean isBusy () {return busy;} /** * @param busy the busy to set */public void Setbusy (Boolean busy) {this.busy = busy;}}