Java JDBC Database Connection Pool Implementation Method

Java JDBC Database Connection Pool Implementation Method

Keywords: Java, JDBC, Connection Pool, Database, Database Connection Pool, SourceCode

Although J2EE programmers generally have a JDBC database connection pool belonging from ready-made application servers, it is not much for developing a general Java Application, Applet or JSP, Velocity, there are not many JDBC database connecting pools, and general performance is not it is good. Java programmers are envious of Windows ADO, just need to return to the database directly from the Database Connection Pool. And ADO Connection is a thread safe, multiple threads can share a connection, so the ASP program generally places the getConnection in the global.asa file, and establish a database connection when IIS is started. ADO's Connection and Result have a good buffer and is easy to use.

In fact, we can write a JDBC database connection pool. Precautions for writing JDBC Connection Pool:

1. There is a simple function to get a Connection from the connection pool. 2. The Close function must put the Connection back to the database connection pool. 3. When there is no idle Connection in the database connection pool, the database connection pool must be able to automatically increase the number of Connection. 4. When the number of Connection in the database connection pool is very large in a particular time, only one of the small parts will be used for a long time, and you should automatically turn off excess Connection. 5. If possible, you should provide the debug information report that does not close NEW Connection.

If you want new connection, you can return Connection directly from the Database Connection pool, you can write (Mediator Pattern) (Chinese full-width spaces):

Public class easyconnection implements java.sql.connection {private connection m_delegate = null;

Public easyConnection () {m_dlegate = getConnectionFromPool ();

Public void close () {putconnectionbacktopool (m_dlegate);

Public preparedStatement PrepareStatement (String SQL) THROWS SQLEXCEPTION {m_dlegate.preparestatement (SQL);}

// ... Other Method

}

It seems not difficult. However, this way is not recommended, because you should try to avoid using Java Interface, the shortcomings of Java Interface I will write another article discussions. Everyone pays attention to the implementation of Connection Pool. A implementation method is given below.

Import java.sql. *; import java.lang.reflect. *; import java.util. *; import java.io. *;

public class SimpleConnetionPool {private static LinkedList m_notUsedConnection = new LinkedList (); private static HashSet m_usedUsedConnection = new HashSet (); private static String m_url = ""; private static String m_user = ""; private static String m_password = ""; static final boolean DEBUG = true; static private long m_lastClearClosedConnection = System.currentTimeMillis (); public static long CHECK_CLOSED_CONNECTION_TIME = 4 * 60 * 60 * 1000; // 4 hoursstatic {initDriver ();}

Private SimpleConNetionPool () {}

private static void initDriver () {Driver driver = null; // load mysql driver try {driver = (Driver) Class.forName ( "com.mysql.jdbc.Driver") newInstance ();. installDriver (driver);} catch Exception E) {}

// load postgreSQL driver try {driver = (driver) class.forname ("org.postgreSql.driver). NewInstance (); installDriver;} catch (exception e) {}}

Public Static Void InstallDriver (Driver Driver) {DriverManager.RegisterDriver (DRIVER);} catch (exception e) {E.PrintStackTrace ();}}

public static synchronized Connection getConnection () {clearClosedConnection (); while (m_notUsedConnection.size ()> 0) {try {ConnectionWrapper wrapper = (ConnectionWrapper) m_notUsedConnection.removeFirst (); if (wrapper.connection.isClosed ()) {continue; } m_usedUsedConnection.add (wrapper); if (DEBUG) {wrapper.debugInfo = new Throwable ( "Connection initial statement");} return wrapper.connection;} catch (Exception e) {}} int newCount = getIncreasingConnectionCount (); LinkedList List = new LinkedList (); connectionWrapper wrapper = null; for (int i = 0; i

Return Wrapper.Connection;}

private static ConnectionWrapper getNewConnection () {try {Connection con = DriverManager.getConnection (m_url, m_user, m_password); ConnectionWrapper wrapper = new ConnectionWrapper (con); return wrapper;} catch (Exception e) {e.printStackTrace ();} return NULL;

Static Synchronized Void PushConnectionBackTopool (ConnectionWrapper Con) {Boolean Exist = M_USEDUSEDCONNECTION.Remove (Con); if (exist) {m_notusedconnection.addlast (con);}}}

Public static int close () {int count = 0;

Iterator itrator = m_notusedconnection.iterator (); while (item (connectionWrapper) {Try {() {Try {()). Close (); count ;} catch (exception e) {}} m_notusedconnection.clear ); iterator = m_usedUsedConnection.iterator (); while (iterator.hasNext ()) {try {ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next (); wrapper.close (); if (DEBUG) {wrapper.debugInfo.printStackTrace ( ); Count ;} catch (exception e) {}} m_usedusedconnection.clear ();

Return count;

private static void clearClosedConnection () {long time = System.currentTimeMillis (); // sometimes user change system time, just return if (time

// begin check Iterator iterator = m_notUsedConnection.iterator (); while (iterator.hasNext ()) {ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next (); try {if (wrapper.connection.isClosed ()) {iterator.remove ()}}}} Catch (exception e) {item.remove (); if (debug) {System.out.Println ("Connection is Closed, this connection initial stacktrace"); wrapper.debuginfo.printStackTrace ();} }

// Make Connection Pool Size Smaller if Too Big Int Decrease = getDecreasingConnectionCount (); if (m_notusedconnection.size ()

while (decrease--> 0) {ConnectionWrapper wrapper = (ConnectionWrapper) m_notUsedConnection.removeFirst (); try {wrapper.connection.close ();} catch (Exception e) {}}} / ** * get increasing connection count, not just add 1 connection * @return count * / public static int getIncreasingConnectionCount () {int count = 1; int current = getConnectionCount (); count = current / 4; if (count <1) {count = 1;} return count }

/ ** * get decreasing connection count, not just remove 1 connection * @return count * / public static int getDecreasingConnectionCount () {int count = 0; int current = getConnectionCount (); if (current <10) {return 0;} Return Current / 3;

Public synchronized static void printdebugmsg () {printdebugmsg (system.out);

public synchronized static void printDebugMsg (PrintStream out) {if (DEBUG == false) {return;} StringBuffer msg = new StringBuffer (); msg.append ( "debug message in" SimpleConnetionPool.class.getName ()); msg. Append ("/ r / n"); Msg.Append ("Total Count Is Connection Pool:" GetConnectionCount ()); msg.Append ("/ r / n"); msg.append ("NOT USED Connection Count: " getnotusedConnectionCount ()); msg.append (" / r / n "); msg.append (" Used connection, count: " getusedConnectionCount ()); Out.Println (MSG); item itrator itute = m_usedusedConnection.Item itrator ); While (item.hasnext ()) {ConnectionWrapper Wrapper = (ConnectionWrapper) Iterator.next (); wrapper.debuginfo.printStackTrace (out);} out.println ();}

public static synchronized int getNotUsedConnectionCount () {return m_notUsedConnection.size ();} public static synchronized int getUsedConnectionCount () {return m_usedUsedConnection.size ();}

Public static synchronized int getConnectionCount () {return m_notusedconnection.size () m_usedusedConnection.size ();}

Public static string geturl () {return m_url;}

Public static void seturl (String URL) {if (URL == NULL) {Return;} m_url = url.trim ();

Public static string getuser () {return m_user;}

Public Static Void SetUser (String User) {if (user == null) {return;} m_user = user.trim ();

Public static string getpassword () {return m_password;}

Public static void setpassword (string password) {if (password == null) {return;} m_password = password.trim ();

}

class ConnectionWrapper implements InvocationHandler {private final static String CLOSE_METHOD_NAME = "close"; public Connection connection = null; private Connection m_originConnection = null; public long lastAccessTime = System.currentTimeMillis (); Throwable debugInfo = new Throwable ( "Connection initial statement");

ConnectionWrapper (Connection con) {Class [] interfaces = {java.sql.Connection.class}; this.connection = (Connection) Proxy.newProxyInstance (. Con.getClass () getClassLoader (), interfaces, this); m_originConnection = con }

Void Close () throws sqlexception {m_originconnection.close ();

public Object invoke (Object proxy, Method m, Object [] args) throws Throwable {Object obj = null; if (CLOSE_METHOD_NAME.equals (m.getName ())) {SimpleConnetionPool.pushConnectionBackToPool (this);} else {obj = m .invoke (m_originconnection, args);} LastAccesstime = system.currenttimemillis (); returnobj;}}

public class TestConnectionPool {public static void main (String [] args) {SimpleConnetionPool.setUrl (DBTools.getDatabaseUrl ()); SimpleConnetionPool.setUser (DBTools.getDatabaseUserName ()); SimpleConnetionPool.setPassword (DBTools.getDatabasePassword ());

Connection Con = SimpleConnepool.getConnection (); connection con1 = SimpleConNetionPool.getConnection (); connection con2 = SimpleConnePool.getConnection ();

// Do Something with con ...

Try {con.close (); catch (exception e) {}

Try {con1.close ();} catch (exception e) {}

Try {con2.close ();} catch (exception e) {}

Con = SimpleConNetionPool.getConnection (); con1 = SimpleConnepool.getConnection (); try {con1.close ();} catch (exception e) {}

Con2 = SimpleConnepool.getConnection (); SimpleConNetionPool.PrintDebugmsg ();

}

After running the test program, print the Connection status in the connection pool, as well as not to close the Connection information.