Java JDBC database connection pool Implementation Method

Although J2EE programmers generally have JDBC database connection pools provided by ready-made application servers, there are not many available JDBC database connection pools for general Java application, applet, JSP, and velocity development, and the general performance is not good. Java programmers envy windows ADO. They only need new connection to directly return the connection from the database connection pool. In addition, ADO connection is thread-safe and multiple threads can share one connection. Therefore, ASP programs generally put getconnection in the global. Asa file to establish a database connection When IIS is started. The connection and result of ADO are well buffered and easy to use.

In fact, we can write a JDBC database connection pool by ourselves. 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 into 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 connections.
4. When the number of connections in the database connection pool increases at a particular time, but only a small part of the connections will be used for a long time, the redundant connections should be automatically closed.
5. If possible, the debug information should be provided to report the new connection that is not closed.

If you want to create a new connection, you can directly return the connection from the database connection pool. You can write (Mediator Pattern) as follows (the Chinese character space is used in the following code ):

Public class easyconnection implements java. SQL. Connection {
Private connection m_delegate = NULL;

Public easyconnection (){
M_delegate = getconnectionfrompool ();
}

Public void close (){
Putconnectionbacktopool (m_delegate );
}

Public preparedstatement preparestatement (string SQL) throws sqlexception {
M_delegate.preparestatement (SQL );
}

// ...... Other method

}

It does not seem difficult. However, this writing method is not recommended because Java interface should be avoided as much as possible. I will write an article to discuss the disadvantages of Java interface. We are concerned about the implementation of connection pool. The following provides an implementation method.

Import java. SQL .*;
Import java. Lang. Reflect .*;
Import java. util .*;
Import java. Io .*;

Public class simpleconnetionpool {
Private Static Response List m_notusedconnection = new Response List ();
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 hours

Static {
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 (driver );
} Catch (exception e ){
}
}

Public static void installdriver (driver Driver ){
Try {
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 ();
Jsonlist list = new jsonlist ();
Connectionwrapper wrapper = NULL;
For (INT I = 0; I <newcount; I ++ ){
Wrapper = getnewconnection ();
If (wrapper! = NULL ){
List. Add (wrapper );
}
}
If (list. Size () = 0 ){
Return NULL;
}
Wrapper = (connectionwrapper) List. removefirst ();
M_usedusedconnection.add (wrapper );

M_notusedconnection.addall (list );
List. Clear ();

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 = m_notusedconnection.iterator ();
While (iterator. hasnext ()){
Try {
(Connectionwrapper) iterator. Next (). 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 <m_lastclearclosedconnection ){
Time = m_lastclearclosedconnection;
Return;
}
// No need check very often
If (time-m_lastclearclosedconnection <check_closed_connection_time ){
Return;
}
M_lastclearclosedconnection = time;

// Begin check
Iterator = m_notusedconnection.iterator ();
While (iterator. hasnext ()){
Connectionwrapper wrapper = (connectionwrapper) iterator. Next ();
Try {
If (wrapper. Connection. isclosed ()){
Iterator. Remove ();
}
} Catch (exception e ){
Iterator. 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 () <decrease ){
Return;
}

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 );
Iterator = m_usedusedconnection.iterator ();
While (iterator. 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 = NULL;
Private connection m_originconnection = NULL;
Public long lastaccesstime = system. currenttimemillis ();
Throwable debuginfo = new throwable ("connection initial statement ");

Connectionwrapper (connection con ){
This. Connection = (connection) proxy. newproxyinstance (
Con. getclass (). getclassloader (),
Con. getclass (). getinterfaces (), 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 ();
Return OBJ;
}
}

Usage

Public class testconnectionpool {
Public static void main (string [] ARGs ){
Simpleconnetionpool. seturl (dbtools. getdatabaseurl ());
Simpleconnetionpool. setuser (dbtools. getdatabaseusername ());
Simpleconnetionpool. setpassword (dbtools. getdatabasepassword ());

Connection con = simpleconnetionpool. getconnection ();
Connection con1 = simpleconnetionpool. getconnection ();
Connection con2 = simpleconnetionpool. 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 = simpleconnetionpool. getconnection ();
Try {
Con1.close ();
} Catch (exception e ){}

Con2 = simpleconnetionpool. getconnection ();
Simpleconnetionpool. printdebugmsg ();

}
}

After the test program is run, the connection status in the connection pool is printed and the information about the connection that is in use is not closed.