A database connecting pool with very good effect
Preface:
Although many enterprise-class application servers have their own database connection pool features, even Tomcat also supports this feature. However, in many times, we still have to use the database connection pool, such as the Java desktop application that access the database, etc. This database connection pool is the renovation of the example in the "Inside Servlets" book, after the trial, the effect is very good. Special cloth sharing. (Author: abnerchai contact me: josserchai@yahoo.com)
Source code
//ConnectionPool.java
Package com.abner.dbconnector;
Import java.sql. *;
Import java.util. *;
/ **
* The ConnectionPool class creates a connection pool that specifies a size for a specific database. Connection pool object
* Allows the client to specify the JDBC driver, database, username and password using the database. and,
* The client can specify the connection pool at the initial creation is the number of database connections, and the specified connection
* Not enough time automatically increase the number of connections and the number of database connections in the connection pool.
*
* External methods available: ConnectionPool: Constructor
* GetInitialConnections: Returns the connection pool initialization size
* SETINITIALCONNECONNECTIONS: Set the connection pool initialization size
* GetIncrementalConnections: Return to the increment of the connection pool
* SETINCREMENTALCONNECTIONS: Sets the size of the connection pool to add
* GetMaxConnections: Get the maximum allowable connection of the connection pool
* SetMaxConnections: Set the maximum allowable connection to the connection pool
* GettestTable: Get the name of the test table
* SettestTable: Set the name of the test table
* CreatePool: Create a connection pool, thread and synchronization
* getConnection: Get a database connection from the connection pool
* RETURNCONNECTION: Return to a connection to the connection pool
* Refreshconnections: Refreshing connection pool
* CloseConnectionPool: Close connection pool
*
*
* @Author abnerchai email: josserchai@yahoo.com
* @version 1.0.0
*
* /
Public class connectionpool {
Private string jdbcdriver = ""; // database driver
Private string dburl = ""; // data URL
Private string dbusername = ""; // Database username
Private string dbpassword = ""; // Database User Password
Private string testtable = ""; // Test whether the connection available can be used, the default has no test table
Private int initialConnections = 10; // Connect the initial size of the pool
Private int incrementalconnections = 5; // Connection pool automatically increases
Private int maxConnections = 50; // Connect the largest size
Private Vector Connections = NULL; // Store vector of database connection in the connection pool, starting with NULL
// The object stored in it is PooledConnection type / **
* Constructor
*
* @Param JDBCDriver String JDBC drive class string
* @Param DBURL STRING Database URL
* @Param DBUSERNAME STRING Connection Database User Name
* @Param DBPassword String Connection Database User's Password
*
* /
Public ConnectionPool (String JDBCDRIVER, STRING DBURL, STRING DBUSERNAME, STRING DBPASSWORD) {
this.jdbcdriver = jdbcdriver;
THIS.DBURL = DBURL;
THIS.DBUSERNAME = DBUSERNAME;
this.dbpassword = dbpassword;
}
/ **
* Returns the initial size of the connection pool
*
* @Return's number of connections available in the initial connection pool
* /
Public int GetinitialConnections () {
Return this.initialConnections;
}
/ **
* Set the initial size of the connection pool
*
* @PARAM is used to set the number of connections in the initial connection pool
* /
Public void setInitialconnections (int initialconnections) {
THIS.INITIALCONNECONNECONNES
}
/ **
* Returns the size of the connection pool to increase,
*
* @return connection pool automatically increases
* /
Public int getincrementalconnections () {
Return this.incrementalConnectAlConnection
}
/ **
* Set the size of the connection pool automatically increase
* @PARAM connection pool automatically increases the size
* /
Public void setincrementalconnections (int in inctribenterconnections) {
THIS.Incrementalconnections = incrementalConnections
}
/ **
* Returns the maximum number of available connections in the connection pool
* @return the maximum number of available connections in the connection pool
* /
Public int getmaxconnections () {
Return this.maxConnections;
}
/ **
* Set the maximum number of connections in the connection pool
*
* @PARAM Sets the maximum number of connections in the connection pool
* /
Public void setMaxConnections (int maxConnections) {
THIS.MAXCONNECTIONS = MAXCONNECTIONS;
}
/ **
* Get the name of the test database table
*
* @return test database table name
* /
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.TESTTABLE = TESTTABLE;
}
/ **
*
* Create a database connection pool, using the number of available connections in the pool
* Settings set in InitialConnections
* /
Public synchronized void createpool () throws exception {
/ / Make sure the connection pool is not created
// If the connection pool is created, save the connection vector Connections will not be empty if (Connections! = Null) {
Return; // If you have been created, return
}
/ / Instantiate the driver instance specified in JDBC DRIVER
Driver Driver = (DRIVER) (this.jdbcdriver .newinstance ());
DriverManager.RegisterDriver (DRIVER); // Register a JDBC driver
// Create a saved vector, 0 elements are initially
CONNECTIONS = New Vector ();
/ / Create a connection according to the value set in InitialConnections.
CreateConnections (this.initialConnections);
System.out.println ("Database connection pool creation is successful!");
}
/ **
* Create a database connection specified by NumConnections and connect these connections
* Put in the Connections vector
*
* @Param NumConnections Number of database connections to be created
* /
Private Void CreateConnections (Int NumConnections) throws Sqlexception {
/ / Create a specified number of database connections
For (int x = 0; x / / Do you have the maximum number of database connections in the connection pool? Maximum value by class members MaxConnections // Indicates that if MaxConnections is 0 or negative, it indicates that the number of connections is not limited. // If the number of connections is maximized, you will exit. IF (this.maxConnections> 0 && this.connections.size ()> = this.maxconnections) { Break; } // Add A New PooledConnection Object To Connections Vector / / Add a connection to the connection pool (vector Connections) Try { Connections.AddeElement (New PooledConnection); } catch (sqlexception e) { System.out.println ("Create Database Connection Failed!" E.GetMessage ()); Throw new sqlexception (); } System.out.println ("Database Connection is created ..."); } } / ** * Create a new database connection and return it * * @Return returns a newly created database connection * / Private connection newconnection () throws sqlexception { // Create a database connection Connection conn = drivermanager.getConnection (DBURL, DBUSERNAME, DBPASSWORD); // If this is the first time you create a database connection, you check the database, get this database to allow support. // Maximum customer connection //Connections.size ()==0 means that there is currently no connection to be created IF (Connections.Size () == 0) { Databasemetadata metadata = conn.getMetadata (); int DRIVERMAXCONNECTIONS = metadata.getmaxconnections (); / / DRIVERMAXCONNECTIONS returned by the database is 0, indicating that this database is not the biggest // Connection restriction, or the maximum connection limit of the database does not know // DriverMaxConnections is an integer returned, indicating that this database allows the number of clients to connect / / If the maximum number of connections set in the connection pool is greater than the number of connections allowed by the database, the maximum connection pool is set. / / The number of connections is the maximum number of databases allowed IF (DriverMaxConnections> 0 && this.maxconnections> drivermaxconnections) { THIS.MAXCONNECTIONS = DriverMaxConnections; } } Return conn; // Return to the created new database connection } / ** * Returns a available database connection by calling the getFreeConnection () function. * If there is no database connection available, more database connections cannot be created * Construction (such as the size of the connection pool size), this function will wait for a while to try it. * * @return Returns an available database connection object * / Public synchronized connection getConnection () throws sqlexception { / / Ensure that the connection pool is created IF (Connections == Null) { Return null; / / The connection pool is not created, then return NULL } Connection conn = getfreeConnection (); // Get an available database connection // If there is no connection available, all connections are in use While (conn == null) { // Wait for a while Wait (250); Conn = getfreeConnection (); // Re-try until you get available connections, if // getFreeConnection () Returns NULL // indicates that it is not available after creating a batch of connections. } Return conn; // Returns the available connection } / ** * This function returns an available database connection from the connection pool vector Connections, if * There is currently no database connection available, this function is set according to IncrementalConnections * The value creates several database connections and puts them in the connection pool. * If you are created, all connections are still in use, return null * @return Returns an available database connection * / Private connection getfreeConnection () throws sqlexception { / / A available database connection from the connection pool Connection conn = findfreeConnection (); IF (conn == null) { // If there is no connection available in the current connection pool // Create some connections CreateConnections (INCREMENTALCONNECTIONS); // Re-look for whether there is available connection CONN = FINDFREECONNECTION (); IF (conn == null) { // Returns NULL if you still get available connections after creating a connection Return NULL; } } Return conn; / ** * Find all connections in the connection pool to find an available database connection, * Returns NULL if there is no connection available * * @return Returns an available database connection * / PRIVATE Connection FindfreeConnection () throws sqlexception { Connection conn = NULL; PooledConnection PCONN = NULL; // Get all objects in the connection pool vector Enumeration enum = connections.efficient (); // Traverse all objects to see if there is accessible connection While (enum.hasmoreElements ()) { PConn = (PooledConnection) enum.nexTelement (); IF (! PConn.isbusy ()) { // If this object is not busy, get its database connection and set it to busy CONN = PCONN.GETCONNECTION (); PConn.setbusy (TRUE); // Test if this connection is available IF (! TestConnection (conn) { // If this connection is not available, create a new connection. / / Replace this unavailable connection object, if you fail, return null Try { CONN = NewConnection (); } catch (sqlexception e) { System.out.println ("Create Database Connection Failed!" E.GetMessage ()); Return NULL; } PCONN.SETCONNECTION (CONN); } Break; // Since finding an available connection, exit } } Return conn; // Return to find available connection } / ** * Test if a connection is available, if you are not available, turn off it and return false * Otherwise returns True * * @Param CONN needs to be tested database connection * @return Returns True Indicates that this connection is available, FALSE is not available * / Private Boolean TestConnection (Connection CONN) { Try { / / Judgment if the test form exists IF (TestTable.equals (")) { // If the test table is empty, try to use the setAutocommit () method of this connection // To determine whether the connection is available (this method is only available in some databases, if not available, // Throw an exception). Note: The method of using the test form is more reliable Conn.setautocommit (TRUE); } Else {// use test table test when there is a test form // check if this connection is valid Statement Stmt = conn.createstatement (); Stmt.execute ("SELECT Count (*) from" TestTable); } } catch (sqlexception e) { // Throw an exception, this connection is not available, turn it off, and return false; CloseConnection (CONN); Return False; } / / Connection available, return true Return True; } / ** * This function returns a database to connect to the connection pool and set this connection to idle. * All database connections available to the connection pool should be returned when not using this connection. * * @PARAM needs to return to the connection object in the connection pool * / Public void returnConnection (Connection CONN) { / / Make sure the connection pool exists, if the connection is not created (not existed), return directly IF (Connections == Null) { System.out.println ("Connecting pool does not exist, can not return this connection to the connection pool!"); Return; } PooledConnection PCONN = NULL; Enumeration enum = connections.efficient (); // Traverse all connections in the connection pool, find this connection object to return While (enum.hasmoreElements ()) { PConn = (PooledConnection) enum.nexTelement (); // First find the connection object to be returned in the connection pool IF (conn == pconn.getConnection ()) { / / Found, set this connection to idle state PConn.setbusy (FALSE); Break; } } } / ** * Refresh all of the connection objects in the connection pool * * / Public synchronized void refreshconnections () throws sqlexception { / / Ensure that the connection pool has an innovation IF (Connections == Null) { System.out.println ("Connecting the pool does not exist, no refresh!"); Return; } PooledConnection PCONN = NULL; Enumeration enum = connections.efficient (); While (enum.hasmoreElements ()) { // Get a connection object PConn = (PooledConnection) enum.nexTelement (); // If the object is busy, wait for 5 seconds, directly refreshed after 5 seconds IF (PConn.isbusy ()) { Wait (5000); // is 5 seconds } // Turn this connection, replace it with a new connection. CloseConnection (PConn.getConnection ()); PCONN.SETCONNECTION (NewConnection ()); PConn.setbusy (FALSE); } } / ** * Turn off all connections in the connection pool and clear the pool. * / Public synchronized void closeconnectionpool () throws sqlexception { / / Make sure the connection pool exists, if there is no existence, return IF (Connections == Null) { System.out.println ("The connection pool does not exist, can't be turned off!"); Return; } PooledConnection PCONN = NULL; Enumeration enum = connections.efficient (); While (enum.hasmoreElements ()) { PConn = (PooledConnection) enum.nexTelement (); // If you are busy, wait for 5 seconds IF (PConn.isbusy ()) { Wait (5000); // is 5 seconds } // 5 seconds directly close it CloseConnection (PCONN.GetConnection ()); // Remove it from the connection pool vector Connections.RemoveElement (PCONN); } // Place the connection pool is empty Connections = NULL; } / ** * Close a database connection * * @PARAM needs to close database connection * / Private Void CloseConnection (Connection CONN) { Try { CONN.CLOSE (); } catch (sqlexception e) { System.out.println ("Close Database Connection Error:" E.GetMessage ()); } } / ** * Make the program waiting for a given millisecond number * * @Param gives a number of milliseconds * / Private Void Wait (int mseconds) { Try { Thread.sleep (mseconds); } catch (interruptedexception e) { } } / ** * * Internal use of classes for saving connecting objects in the connection pool * There are two members in this class, one is a database connection, and the other is to indicate if this connection is * The logo is being used. * / Class pooledConnection { Connection connection = null; // Database connection Boolean busy = false; / / This connection is not using the flag, the default is not in use. // Constructor, according to a connection to conscious a PooledConnection object Public PooledConnection (Connection Connection) { THIS.CONNECTION = Connection; } / / Returns the connection in this object Public connection getConnection () { Return Connection; } // Set this object, connection Public void setConnection (Connection Connection) { THIS.CONNECTION = Connection; } // Get the object connection is busy Public boolean isbusy () { Return busy; } // Set the connection of the object is busy Public void setbusy (boolean busy) { THIS.BUSY = BUSY; } } } All of this program all I have written in the source program in detail, I don't want to explain it. If you have improved or modified, please keep the original author's statement.
