Database connection pool Java implementation

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 statement if (createstate.equals)) {obj = method.invoke (conn, args); statref = (statement) OBJ; // record statement returno;}

// Determine whether a Close method is called, if the CLOSE method is called to use the connection to useless status IF (Close.equals ()) {// setting does not use the flag setisfree (false); // Check if Subsequent work, clear the connection free resource IF (statref! = Null) statref.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 let the factory call * @Param factory to call the factory, and must be correctly initialized * @Param param connection parameters * @return connection * / static public _Connection getConnection (ConnectionFactory Factory, ConnectionParam param) {IF (Factory.Iscreate ()) // Determines whether the factory {_Connection_Conn = new _connection (param); returnne_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 (); for implementation Normal operation of the connection pool, using a single mode

/ ** * Create a connection pool using the specified parameters * / public connectionFactory (ConnectionParam param, factoryparam fparam) throws SQLEXCEPTION {// Does not allow parameters Empty IF ((param == null) || (fParam == null)) throw new SQLException ( "ConnectionParam and FactoryParam not empty"); if (m_instance == null) {synchronized (ConnectionFactory.class) {if (m_instance == null) {// new instance // custom parameter 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; // initialize, create MinConnectionCount Connect System.Out.println ("Connection Factory!"); Try {for (int i = 0; i

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 if the policy is required to query if (m_instance.managetype! = 0) {thread t = new thread (new factoryMANGETHREAD (M_Instance)); T.Start ();} // Connection 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) {TREAD.SLEP (DELAY);} catch (interruptedException e) {} system.out.println ("eeeee"); // Judgment whether it has closed the factory, then exit the listener 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 * database connection proxy class definition * / public class _Connection implements InvocationHandler {// definition of private connection connection conn = null; // define the monitor connected to create a statement that private statement statRef = null; private PreparedStatement prestatRef = null; // whether to support the transaction flag private boolean supportTransaction = false; // database busy private boolean isFree = false; / / last access time long lastAccessTime = 0; // defined to take over the function name String CREATESTATE = "createStatement"; String CLOSE = "close"; String pREPARESTATEMENT = "prepareStatement"; String COMMIT = "commit"; String ROLLBACK = "rollback"; / ** * Constructor, use private, prevents direct creation * @Param param connection parameters * / private _connection (connectionparam param) {// record date to try {// Create Connection Class.Forname (param. GetDriver ()). NewInstance (); conn = drivermanager.getConnection (param.geturl (), param.getuser (), param.getpassword (); DatabaseMetadata DM = NULL; DM = Conn.getMetadata (); // Judgment Whether to support business supportTransaction = DM.Supportstransactio NS ();} catch (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; // Determine if a Close method is called, if the Close method is called Then set the connection to the useless state IF (close.equals ()) {// setting does not use the flag setisfree (false); / / Check if there is a follow-up, clear the connection free resource IF (statRef! = NULL) statref.close (); if (Prestatref! = null) preStatref.close (); Return NULL;

} // Judgment is the use of createStatement statement if (CreateState.equals)) {obj = method.invoke (conn, args); statref = (statement) Obj; // record statement return} / / Judgment is a prepareStatement statement if (preparestatement.equals)) {obj = method.invoke (conn, args); PRESTATREF = (preparedStatement) Obj; returnobj;} // If you do not support transactions, The code IF does not execute the matter ((method.getname ()) || rollback.equals (Method.GetName ()) && (! Issupporttransaction ()) Return Null; Obj = method.invoke Conn, args); // Set the last access time to clear the timeout connection LastAccesstime = system.currenttimemillis (); return obj;} / ** * Create a connection factory, can only call * @Param Factory Call the factory, and must be correctly initialized * @Param param connection parameters * @return connection * / static public _connection getConnection (ConnectionFactory Factory, ConnectionParam param) {if (factory.iscreate ()) // Determine whether it is correctly initialized factory {_Connection _conn = new _connection (param); return_conn;} else return null;} public connection getfreeConnection () {// Return to the database connection CONN's takeover class for interception CLOSE Method Connection Conn2 = (Connection) proxy.newproxyinstance (Conn.getClass (). GetClassLoader (), conn.getclass (); getInterface (); return conn2;} / ** * This method is really turned off Database connection * @throws sqlexception * / void close () throwXception {// Since the class attribute conn is not connected to the connection, once the close method is called, the connection conn.close ();} public void setisfree boolean value) {isFree = value;} public boolean isFree () {return isFree;} / ** * is determined whether transactional * @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; / / connection pooling private LinkedHashSet ConnectionPool = null use; // free connection pool private LinkedHashSet FreeConnectionPool = null; // maximum number of connections private int MaxConnectionCount = 4; // minimum number of connections private int MinConnectionCount = 2; // current number of connections private int current_conn_count = 0; // connection parameters private ConnectionParam connparam = null; // whether to create a private boolean flag factory isflag = false; // whether to support the transaction private boolean supportTransaction = false; // define management strategies private int ManageType = 0 ; private ConnectionFactory () {ConnectionPool = new LinkedHashSet (); FreeConnectionPool = new LinkedHashSet ();} / ** * Create a specified connection pool parameters * / public ConnectionFactory (ConnectionParam param, FactoryParam fparam) throws SQLException {// do not Allow parameters is empty IF ((param == null) | (fParam == NULL)) Throw new SQLException ("ConnectionParam and FactoryParam cannot be empty); i f (m_instance == null) {synchronized (ConnectionFactory.class) {if (m_instance == null) {// new instance // custom parameter 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; // initialize, create MinConnectionCount connection System.out.println ( "connection factory to create! "); Try {for (int i = 0; i

_Connection.getConnection (m_instance, m_instance.connparam); if (_conn == null) continue; System.out.println ( "connection creation"); m_instance.FreeConnectionPool.add (_conn); // adding free connection pool m_instance.current_conn_count ; // logo supports transaction m_instance.supporttransaction = _conn.issupportTransAction ();}} catch (exception e) {E.PrintStackTrace ();} // Depending on policy judgment if you need to query if (m_instance.managetype! = 0 ) {Thread t = new thread (new factoryMANGETHREAD (M_INSTANCE)); T.Start ();}}}}} / ** * Sign Factory has created * @return boolean * / public boolean iscreate () {return m_instance. isflag;} / ** * taken from an idle connection pool * @return connection * @throws SQLException * / public synchronized connection getFreeConnection () throws SQLException {connection conn = null; // Get an idle connection Iterator iter = m_instance. FREECONNECTIONPOOL.ITERATOR (); while (ore) {_connection _conn = (_connection) ney orext (); // Find unconnected IF (! _ Conn.isfr EE ()) {conn = _conn.getfreeConnection (); _conn.setisfree (true); // removes idle area m_instance.freeConnectionPool.Remove (_CONN); // Add to connect pool m_instance.connectionpool.add (_CONN); BREAK; }} // Check if the idle pool is empty if (m_instance.freeConnectionPool.isempty ()) {// Check if IF (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

}}} // Close the connection pool used in use 1.ITERATOR (); while (iter.hasnext ()) {try {(_connection) ney (). Close (); system.out. Println ("Close Connection: INUSED"); m_instance.current_conn_count -;} catch (eXception E) {if (e instanceof sqlexception) EXCP = (SQLEXCEPTION) E;}}}}}}}} / ** * 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 iterator it = null; / / checks if there is a returned connection {it = m_instance.connectionpool.iterator (); while (ore.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;}}} = 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

package scut.ailab.connectionpool; import java.io.Serializable; / ** * @author youyongming * implemented in class * / public class ConnectionParam implements Serializable {private String driver database connection; // database driver private String url; / / Database URL Private String User; // Database User Name Private String Password; // Database Code / ** * Unique Constructor, you need to specify four necessary parameters * @Param Driver Data Driver * @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 () {return driver;} 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 = u Ser;} / ** * @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) && (param.getull ()) == 0) && (user.comparetoirenorecase (param.getuser ()) == 0) && () ") == 0) && () (Param.getPassword () ) == 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 * * /// connection pool scheduling threads 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) {THREAD.SLEP (DELAY);} catch (interruptedExcection e) {} system.out.println ("eeeee"); //// Decision has closed the factory, then exit the listener IF (cf.iscreate ()) cf.schedule (); else system.exit (1);}}} fectoryparam.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 * * /// connection pool parameters factory public class FactoryParam {// the maximum number of connections private int MaxConnectionCount = 4; // minimum number of connections private int MinConnectionCount = 2; // recovery policy private int ManageType = 0; public FactoryParam () {} / ** * Structural connection pool factory parameters * @Param max Maximum connection * @Param min minimum connection * @Param Type management policy * / public factoryparam (int max, int min, int type) {this. ManageType = Type; this.maxconnectioncount = max; this.minconnectioncount = min;} / ** * Set the maximum number of connections * @Param value * / public void setMaxConn (int value) {this.maxconnectioncount = value;} / ** * Get the maximum number of connections * @return * / public int getMaxConn () {return this.maxconnectioncount;} / ** * Set the minimum connection * @Param value * / public void setminconn (int value) {this.minConnectioncount = value; } / ** * Get minimum connections * @return * / public int getminConn () {Return THIS.M Inconnectioncount;} 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.); 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 = 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.forname ()). NewInstance (); for (int i = 0; i <10; i ) {conn1 = driver Manager.getConnection (param.geturl (), param.getuser (), param.getPassword ()); statement stmt = conn1.createstatement (); resultset = 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 POL : " (System.currentTimeMillis () - TIME));} catch (exception e) {E.PrintStackTrace ();} finally {try {cf.close ();} catch (Exception E) {E.PrintStackTrace () }}}}}