Because the work needs to be used to connect the pool, I have read the article on the Internet, I have learned a lot of experience. Here I want to be a summary, plus my thoughts and together, I hope to give you some help.
purpose:
Eliminate the overhead and bottlenecks that are frequently connected in the database.
solution:
However, there are many restrictions of users, and there is neither too much require users to get and use databases as specified.
Try to keep users' habits
Many of the current methods are required to use only connections to the specified method, and cannot use methods that directly turn off data connections. The solution is to use the proxy class to solve the intermediate. Can refer to http://www-900.ibm.com/developerWorks/cn/java/l-connpoolproxy/index.shtml
Can maintain the normal state of the connection
Because resources created for database connections, if it is not possible to release, it will affect the next data connection. For example, in SQL 2K, a connection differently creates a plurality of statement otherwise there is an exception of data connection, so these resources must be released after returning the connection.
// Judgment is the use of CreateStatement statements
IF (createstate.equals))))
{
Obj = method.invoke (conn, args);
StATREF = (statement) OBJ; // Record statement
Return Obj;
}
/ / Judgment whether the method is called, if the CLOSE method is called, the connection is used to useless.
IF (close.equals ())))
{
// Setting not using the flag
Setisfree (false);
/ / Check if there is a follow-up, clear the connection free resources
IF (statref! = null)
statnef.close ();
IF (PRESTATREF! = NULL)
PRESTATREF.CLOSE ();
Return NULL;
}
Correct protection is not used by violations
One consideration is that it is not allowed to use the proxy class, but can only use itself, one is to use the internal private class, one is to use the flag that only the specified classes can call. My implementation is the latter.
/ **
* Create a connection factory, can only call the factory
* @Param Factory To call the factory, and must be initialized correctly
* @Param param connection parameters
* @return connection
* / static public _Connection getConnection (ConnectionFactory Factory, ConnectionParam param)
{
IF (factory.iscreate ()) // Determines whether it is correctly initialized
{_CONNECTION _CONN = New _Connection (param);
Return_Conn;
Else
Return NULL;
}
Provide a good user interface, simple and practical
Create a factory using a static method, then get a connection, just like a fully connected Connection method, is not limited. At the same time, the connection parameter class and factory parameter class are set for convenience.
ConnectionParam Param = New ConnectionParam (Driver, URL, User, Password);
ConnectionFactory Cf = NULL; // New ConnectionFactory (Param, New Factoryparam ());
Try
{
Cf = New ConnectionFactory (param, new factoryparam ());
Connection conn1 = cf.getfreeConnection (); connection conn2 = cf.getfreeConnection ();
CONNECTION CONN3 = Cf.getFreeConnection ();
Statement stmt = conn1.createstatement ();
ResultSet RS = Stmt.executeQuery ("SELECT * from Requests");
IF (rs.next ())
{
System.out.println ("Conn1 Y");
}
Else
{
System.out.println ("conn1 n");
}
Stmt.close ();
CONN1.CLOSE ();
In order to achieve the normal operation of the connection pool, a single model is used.
/ ** * Create a connection pool using the specified parameters * /
Public ConnectionFactory (ConnectionParam Param, FactoryParam fParam) throws Sqlexception
{
// Do not allow parameters to be empty
IF ((param == null) | (fParam == NULL) "Throw new SQLEXCEPTION (" ConnectionParam and FactoryParam cannot be empty);
IF (m_instance == null)
{
Synchronized (ConnectionFactory.class)
{
IF (m_instance == null)
{
// new instance
// Parameter customization
M_INSTANCE = New connectionFactory ();
m_instance.connparam = param;
m_instance.maxconnectioncount = fParam.getMaxConn ();
m_instance.minConnectionCount = fParam.getminConn ();
m_instance.managetype = fparam.gettype ();
m_instance.isflag = true;
// Initialization, create a MINCONNECTIONCOUNT connection
System.out.println ("Connection Factory!");
Try
{
For (int i = 0; i { _Connection _conn = _Connection.getConnection (m_instance, m_instance.connparam); IF (_CONN == NULL) Continue; System.out.println ("Creative"); m_instance.freeConnectionPool.Add (_CONN); // Add Idle Connection Pool m_instance.current_conn_count ; // Sign support transaction m_instance.supporttransaction = _conn.issupporttransaction (); } } Catch (exception e) { E.PrintStackTrace (); } // Determine whether to query according to the policy IF (m_instance.managetype! = 0) { Thread T = New Thread (New FactoryMANGETHREAD (M_INSTANCE)); T.Start ();}}}}} Connection pool management For the management of the connection pool, I envision the use of static management and dynamic management, setting up the maximum limit, and the constant connection. 2 pools, an idle pool, a pool. Static is used, I found that the idle connection is not enough to check. The dynamic is to use a line-of-time check. / / Determine whether to query according to the policy IF (m_instance.managetype! = 0) { Thread T = New Thread (New FactoryMANGETHREAD (M_INSTANCE)); T.Start (); } // Connect pool scheduling thread Public Class FactoryMANGETHREAD IMPLEMENTS RUNNABLE { ConnectionFactory CF = NULL; Long delay = 1000; Public FactoryMANGETHREAD (ConnectionFactory Obj) { Cf = Obj; } / * (non-javadoc) * @see java.lang.Runnable # run () * / Public void Run () { While (True) { Try { Thread.sleep (delay); } Catch (interruptedexception e) {} System.out.println ("eeeee"); // Judging whether the factory has closed, then exit the monitor IF (cf.iscreate ()) Cf.schedule (); Else System.exit (1);}}} Finally, give a complete source code: -------------------------------------------------- ------------------------------ _Connectio.java Package scut.aiLab.connectionpool; Import java.lang.reflect. *; Import java.sql. *; / ** * @Author YouYongming * Define the proxy class of the database connection * / Public class _Connection Implements InvocationHandler { // Define Connection Private Connection Conn = NULL; / / Define the statement created by the monitoring connection Private statement statnef = NULL; Private preparedStatement PrestatRef = NULL; / / Do you support a transaction sign? PRIVATE BOOLEAN SupportTransaction = FALSE; // Database busy state Private boolean isfree = false; // Last visited time Long lastaccesstime = 0; / / Define the name of the function you want to take over String createState = "createstatement"; String close = "close"; string prepaarestatement = "preparestatement"; String commit = "commit"; String rollback = "rollback"; / ** * Constructor, private, prevent direct creation of * @Param param connection parameters * / Private _Connection (ConnectionParam Param) { // Record date Try { // Create a connection Class.Forname (param.getdriver ()). Newinstance (); Conn = drivermanager.getConnection (param.getUr (), param.getuser (), param.getpassword ()); DatabaseMetadata DM = NULL; DM = conn.getMetadata (); / / Judgment whether to support a transaction SupportTransaction = DM.Supportstransactions (); } Catch (exception e) {E.PrintStackTrace ();}} / ** * (non-javadoc) * @see java.lang.reflect.invocationHandler # invoke (java.lang.Object, java.lang.Reflect.method, java.lang.object []) * / Public Object Invoke (Object Proxy, Method Method, Object [] args throws throwable { Object obj = NULL; / / Judgment whether the method is called, if the CLOSE method is called, the connection is used to useless. IF (close.equals ()))) { // Setting not using the flag Setisfree (false); / / Check if there is a follow-up, clear the connection free resources IF (statref! = null) statnef.close (); IF (PRESTATREF! = NULL) PRESTATREF.CLOSE (); Return Null; // Judgment is the use of CreateStatement statements IF (createstate.equals)))) { Obj = method.invoke (conn, args); StATREF = (statement) OBJ; // Record statement Return Obj;} // Judgment is the prepareStatement statement IF (preted.getname ()))) {Obj = method.invoke (conn, args); PRESTATREF = (preparedStatement) Obj; return obj;} // If you do not support transactions, you do not execute the code of this matter. IF ((method.getname ()) || rollback.equals (Method.getName ()) && (! issupporttransaction ())))) Return NULL; Obj = method.invoke (conn, args); / / Set the last access time to clear the timeout connection in time Lastaccesstime = system.currenttimemillis (); Return Obj;} / ** * Create a connection factory, can only call the factory * @Param Factory To call the factory, and must be initialized correctly * @Param param connection parameters * @return connection * / Static public _Connection getConnection (ConnectionFactory Factory, ConnectionParam Param) { IF (Factory.Iscreate ()) / / Judgment Whether the factory is correctly initialized {_CONNECTION _CONN = New _Connection (param); Return_Conn; } Else Return NULL; } Public connection getFreeConnection () { / / Return to the database connection CONN's takeover class to intercept the Close method Connection conn2 = (connection) proxy.newproxyinstance (conn.getclass (). GetClassLoader (), conn.getclass (). GetInterfaces () (); Return conn2; } / ** * This method really turns off the connection of the database * @Throws SQLException * / Void Close () THROWS SQLEXCEPTION { // Since the class attribute conn is not connected to the connection, once the Close method is called, the connection is turned directly. CONN.CLOSE (); } Public void setisfree (Boolean Value) {Isfree = value;} Public boolean isfree () {return isfree;} / ** * Judging whether a transaction is supported * @Return Boolean * / Public Boolean IssupportTransaction () {Return SupportTransaction; } -------------------------------------------------- ------------------------------ ConnectionFactory.java Package scut.aiLab.connectionpool; / ** * @Author YouYongming * / Import java.util.linkedhashset; import java.sql. *; Import java.util.iterator; Public Class ConnectionFactory {private static connectionFactory m_instance = null; // In use Private LinkedHashset ConnectionPool = NULL; // idle connection pool Private LinkedHashSet FREECONNECTIONPOOL = NULL; // maximum connection number Private int maxConnectionCount = 4; // Minimum connection number Private int minConnectioncount = 2; // Current connection private int current_conn_count = 0; // Connection parameters Private connectionparam connparam = null; / / Do you create a logo of a factory Private boolean isflag = false; / / Do you support a transaction? PRIVATE BOOLEAN SupportTransaction = FALSE; // Define management strategy Private Int ManageType = 0; Private connectionFactory () { ConnectionPool = New LinkedHashSet (); FREECONNECTIONPOOL = New LinkedHashSet (); / ** * Create a connection pool using the specified parameters * / Public ConnectionFactory (ConnectionParam Param, FactoryParam fParam) throws Sqlexception { // Do not allow parameters to be empty IF ((param == null) | (fParam == NULL) "Throw new SQLEXCEPTION (" ConnectionParam and FactoryParam cannot be empty); IF (m_instance == null) { Synchronized (ConnectionFactory.class) { IF (m_instance == null) { // new instance // Parameter customization M_INSTANCE = New connectionFactory (); m_instance.connparam = param; m_instance.maxconnectioncount = fParam.getMaxConn (); m_instance.minConnectionCount = fParam.getminConn (); m_instance.managetype = fparam.gettype (); m_instance.isflag = true; // Initialization, create a MINCONNECTIONCOUNT connection System.out.println ("Connection Factory!"); Try { For (int i = 0; i { _Connection _conn = _Connection.getConnection (m_instance, m_instance.connparam); IF (_CONN == NULL) Continue; System.out.println ("Creative"); m_instance.freeConnectionPool.Add (_CONN); // Add Idle Connection Pool m_instance.current_conn_count ; / / Sign support transaction m_instance.supporttransaction = _conn.issupporttransaction (); } } Catch (exception e) {E.PrintStackTrace (); / / Determine whether to query according to the policy IF (m_instance.managetype! = 0) { Thread T = New Thread (New FactoryMANGETHREAD (M_INSTANCE)); T.Start ();}}}}} / ** * Sign Whether the factory has created * @Return Boolean * / Public boolean iscreate () {Return m_instance.isflag;} / ** * Take an idle connection from the connection pool * @Return Connection * @Throws SQLException * / public synchronized connection getFreeConnection () THROWS SQLEXCEPTION { Connection conn = null; // Get idle connection Iterator it = m_instance.freeConnectionPool.iterator (); While (item.hasnext ()) { _Connection _Conn = (_Connection) iter.next (); / / Find unconnected connection IF (! _ conn.isfree ()) { CONN = _Conn.getfreeConnection (); _Conn.setisfree (TRUE); // remove the idle area m_instance.freeConnectionPool.Remove (_CONN); / / Add to join pool m_instance.connectionpool.add (_conn); Break;}} / / Check if the idle pool is empty IF (m_instance.freeConnectionPool.Isempty ()) { / / Check if it is possible to assign IF (m_instance.current_conn_count { // Newly connected to idle connection pool INT newcount = 0; // Number of getting to be established IF (m_instance.maxconnectioncount - m_instance.current_conn_count> = m_instance.minConnectionCount) { Newcount = m_instance.minConnectionCount; } Else { Newcount = m_instance.maxConnectionCount - m_instance.current_conn_count;} // Create a connection For (int i = 0; i { _Connection_Conn = _Connection.getConnection (m_instance, m_instance.connparam); m_instance.freeConnectionPool.Add (_conn); m_instance.current_conn_count ;}} Else { // If you can't create it, check if there is a connection that has been returned. iter = m_instance.connectionpool.ITERATOR (); While (item.hasnext ()) {_CONNECTION _CONN = (_Connection) iter.next (); IF (! _ conn.isfree ()) { CONN = _Conn.getfreeConnection (); _Conn.setisfree (false); m_instance.connectionpool.remove (_CONN); m_instance.freeConnectionPool.Add (_CONN); Break;}}}}} FREECONNECTIONPOOL.ISEMPTY ()) / / Check if it can be assigned a connection again IF (conn == null) {{= M_instance.freeConnectionPool.iterator (); While (item.hasnext ()) {_CONNECTION _CONN = (_Connection) iter.next (); IF (! _ conn.isfree ()) { CONN = _Conn.getfreeConnection (); _Conn.setisfree (TRUE); M_inStance.freeConnectionPool.remove (_CONN); m_instance.connectionpool.add (_conn); break;}} IF (conn == null) // If you can't, you will use it without connection. Throw new SQLException ("Database connection available");} System.out.println ("Get Connection"); Return Conn;} / ** * Close all database connections in the connection pool * @Throws SQLException * / Public synchronized void close () THROWS SQLEXCEPTION {This.isflag = false; sqlexception eXCP = NULL; // Close the idle pool Iterator it = m_instance.freeConnectionPool.iterator (); While (item.hasnext ()) { Try {(_Connection) iter.next ()). Close (); System.out.Println ("Close Connection: Free"); m_instance.current_conn_count -; } catch (Exception E) {IF (E InstanceOf Sqlexception) EXCP = (SQLException) E;}} // Close the connection pool in use iter = m_instance.connectionpool.ITERATOR (); While (item.hasnext ()) { Try { ((_Connection) ore.next ()). Close (); System.out.println ("Close Connection: Inused"); m_instance.current_conn_count -; } catch (Exception E) {IF (E InstanceOf Sqlexception) EXCP = (SQLException) E;}} IF (excp! = null) throw excp;} / ** * Return to support transactions * @Return Boolean * / Public Boolean IssupportTransaction () { Return m_instance.supporttransaction;} / ** * Connection pool scheduling management * * / public void schedule () {Connection conn = null; / / Check if it is possible to assign Iterator it = null; / / Check if there is a connection that has been returned { iter = m_instance.connectionpool.ITERATOR (); While (item.hasnext ()) {_CONNECTION _CONN = (_Connection) iter.next (); IF (! _ conn.isfree ()) {Conn = _conn.getfreeConnection (); _Conn.setisfree (false); m_instance.connectionpool.remove (_CONN); m_instance.freeConnectionPool.Add (_CONN); Break;}}} IF (m_instance.current_conn_count // Newly connected to idle connection pool INT newcount = 0; // Number of getting to be established IF (m_instance.maxconnectioncount - m_instance.current_conn_count> = m_instance.minConnectionCount) { Newcount = m_instance.minConnectionCount; } Else { Newcount = m_instance.maxConnectionCount - m_instance.current_conn_count;} // Create a connection For (int i = 0; i { _Connection_Conn = _Connection.getConnection (m_instance, m_instance.connparam); m_instance.freeConnectionPool.Add (_conn); m_instance.current_conn_count ;}}}} -------------------------------------------------- ------------------------------ ConnectionParam.java Package scut.aiLab.connectionpool; Import java.io.serializable; / ** * @Author YouYongming * Implement the parameter class for database connections * / Public Class ConnectionParam IMPLEments Serializable {Private string driver; // Database driver Private string url; // data connection URL Private string user; // Database Username Private string password; // Database password / ** * Unique constructor, you need to specify four necessary parameters * @Param Driver Data Drive * @Param URL Database Connection URL * @Param User User Name * @Param Password password * / Public ConnectionParam (String Driver, String Url, String User, String Password) {This.driver = driver; this.url = url; this.user = user; this.password = password;} Public string getDriver () {returnid;} Public string getpassword () {return password;} Public string geturl () {return url;} Public string getuser () {return user;} Public void setdriver (string driver) {this.driver = driver;} Public void setpassword (string password) {this.password = password;} Public void seturl (String URL) {this.url = url;} Public void setuser (string user) {this.user = user;} / ** * @see java.lang.Object # clone () * / Public Object Clone () { ConnectionParam Param = New ConnectionParam (Driver, URL, User, Password); Return Param; } / ** * @see java.lang.Object # equals (java.lang.object) * / Public Boolean Equals (Object Obj) { IF (Obj InstanceOf Connectionparam) { ConnectionParam Param = (ConnectionParam) OBJ; Return ((Driver.comParetoIgnorecase (param.GetDriver ()) == 0) && (Url.comParetoIgnore (param.geturl ()) == 0) && ()) == 0) && () get (Password.comPareToIgnore ") == 0)); Return False; } } -------------------------------------------------- ------------------------------ FactoryMANGETHREAD.JAVA / * * Created on 2003-5-13 * * To change the template for this generated file go to * Window> Preferences> Java> Code Generation> Code and Comments * / Package scut.aiLab.connectionpool; / ** * @Author YouYongming * * / // Connect pool scheduling thread Public Class FactoryMANGETHREAD IMPLEMENTS RUNNABLE { ConnectionFactory CF = NULL; Long delay = 1000; Public FactoryMANGETHREAD (ConnectionFactory Obj) {CF = Obj;} / * (non-javadoc) * @see java.lang.Runnable # run () * / Public void Run () {While (TRUE) { Try { Thread.sleep (delay); } Catch (interruptedexception e) {} System.out.println ("eeeee"); / / Judgment whether the factory has closed, then you will exit the monitor IF (cf.iscreate ()) Cf.schedule (); Else System.exit (1);}}} -------------------------------------------------- ------------------------------ FactoryParam.java / * * Created on 2003-5-13 * * To change the template for this generated file go to * Window> Preferences> Java> Code Generation> code and comments * / Package scut.aiLab.connectionpool; / ** * @Author YouYongming * * /// Connect pool plant parameters Public Class FactoryParam { // maximum connection number Private int maxConnectionCount = 4; // Minimum connection number Private int minConnectioncount = 2; // Recycling strategy Private Int ManageType = 0; Public factoryparam () {} / ** * Structure of the object to connect the pool plant parameters * @Param Max maximum connection * @Param min minimum connection number * @Param Type management strategy * / Public FactoryParam (int Max, int min, int type) { THIS.ManageType = Type; this.maxConnectioncount = max; THIS.MINCONNECNT = min;} / ** * Set the maximum number of connections * @Param Value * / Public void setMaxConn (int value) { THIS.MAXCONNECNT = Value;} / ** * Get the maximum number of connections * @Return * / Public int getMaxconn () {Return this.maxConnectioncount; / ** * Set the minimum number of connections * @Param Value * / Public void setminconn (int value) {This.minConnectioncount = value;} / ** * Get minimum connections * @Return * / Public int getminConn () { Return this.minConnectionCount; } Public int GETTYPE () {Return this.manageType; } -------------------------------------------------- ---------------------------- TestmyPool.java / * * CREATED ON 2003-5-13 * To change the template for this generated file go to * window> preferences> java> code generation> code and comments * / Package scut.aiLab.connectionpool; / ** * @Author YouYongming * * / Import java.sql. *; Public Class TestmyPool { Public void test1 () { String User = "devteam"; String password = "devteam"; String driver = "sun.jdbc.odbc.jdbcodbcdriver"; String url = "JDBC: ODBC: GFQH2"; ConnectionParam Param = New ConnectionParam (Driver, URL, User, Password); ConnectionFactory Cf = NULL; New ConnectionFactory (param, new factoryparam ()); Try {CF = New ConnectionFactory (param, new factoryparam ()); Connection conn1 = cf.getfreeConnection (); CONNECTION CONN2 = Cf.getFreeConnection (); CONNECTION CONN3 = Cf.getFreeConnection (); Statement stmt = conn1.createstatement (); ResultSet RS = Stmt.executeQuery ("SELECT * from Requests"); IF (rs.next ()) {System.out.println ("conn1 y"); Else { System.out.println ("conn1 n"); } Stmt.close (); CONN1.CLOSE (); CONNECTION CONN4 = Cf.getfreeConnection (); Connection conn5 = cf.getfreeConnection (); STMT = conn5.createstatement (); RS = stmt.executequery ("Select * from requests"); IF (rs.next ()) { System.out.println ("Conn5 Y"); } Else { System.out.println ("conn5 n"); } CONN2.CLOSE (); CONN3.CLOSE (); CONN4.CLOSE (); CONN5.CLOSE (); } Catch (Exception E) { E.PrintStackTrace (); Finally { Try { Cf.close (); } Catch (exception e) { E.PrintStackTrace (); }}} Public static void main (string [] args) {String User = "devteam"; String password = "devteam"; String driver = "sun.jdbc.odbc.jdbcodbcdriver"; String url = "JDBC: ODBC: GFQH2"; ConnectionParam Param = New ConnectionParam (Driver, URL, User, Password); ConnectionFactory CF = NULL; New ConnectionFactory (param, new factoryparam ()); Try { Cf = New ConnectionFactory (param, new factoryparam ()); ConnectionFactory CF1 = New ConnectionFactory (param, new factoryparam ()); Connection conn1 = NULL; Long time = system.currenttimemillis (); for (int i = 0; i <10; i ) {Conn1 = cf.getfreeConnection (); Statement stmt = conn1.createstatement (); ResultSet RS = Stmt.executeQuery ("SELECT * from Requests"); IF (rs.next ()) {System.out.println ("conn1 y"); Else {System.out.println ("conn1 n");} CONN1.CLOSE (); System.out.println ("pool:" (System.currentTimeMillis () - TIME)); Time = system.currenttimemillis (); Class.Forname (param.getdriver ()). Newinstance (); For (int i = 0; i <10; i ) { Conn1 = drivermanager.getConnection (param.getUr (), param.getuser (), param.getpassword (); Statement stmt = conn1.createstatement (); ResultSet RS = Stmt.executeQuery ("SELECT * from Requests"); IF (rs.next ()) { System.out.println ("Conn1 Y"); } Else { System.out.println ("conn1 n"); } Conn1.close (); } System.out.println ("NO POOL:" (System.currentTimeMillis () - TIME)); } Catch (Exception E) {E.PrintStackTrace (); Finally { Try { Cf.close (); } Catch (Exception E) { E.PrintStackTrace (); } } } }
