Notes about connection pool

Recently, DBConnectionManager connection pool has been used for various reasons.

Unfortunately, no example can be found for the entire internet.

The following is a complete example of callable and usable.

The file sequence is as follows:

DBConnectionManager. java

Dbop. java

Db. properties

Test. jsp

Test2.jsp

DBConnectionManager. java source code

Package com. china3cts;
Import java. SQL .*;
Import java. io .*;
Import java. util .*;
Public class DBConnectionManager {

Private static int clients = 0;
Private static DBConnectionManager instance;
Private Vector drivers = new Vector ();
Private PrintWriter log;
Private Hashtable pools = new Hashtable ();

Public DBConnectionManager (){
Init ();
}
Private void log (String msg ){
Log. println (new java. util. Date () + ":" + msg );
}

/**
* Write text information and exceptions to log files
*/
Private void log (Throwable e, String msg ){
Log. println (new java. util. Date () + ":" + msg );
E. printStackTrace (log );
}

Public static synchronized DBConnectionManager getInstance (){
If (instance = null ){
Instance = new DBConnectionManager ();
}
Clients ++;
Return instance;
}
Private void init (){
InputStream is = getClass (). getResourceAsStream ("db. properties ");
Properties dbProps = new Properties ();
Try {
DbProps. load (is );
}
Catch (Exception e ){
System. err. println ("Can not read the properties file." +
"Make sure db. properties is in the CLASSPATH ");
Return;
}
 
LoadDrivers (dbProps );
CreatePools (dbProps );
}
Public void freeConnection (String name, Connection con ){
DBConnectionPool pool = (DBConnectionPool) pools. get (name );
If (pool! = Null ){
Pool. freeConnection (con );
}
}
Public Connection getConnection (String name ){
DBConnectionPool pool = (DBConnectionPool) pools. get (name );
If (pool! = Null ){
Return pool. getConnection ();
}
Return null;
}
Public Connection getConnection (String name, long time ){
DBConnectionPool pool = (DBConnectionPool) pools. get (name );
If (pool! = Null ){
Return pool. getConnection (time );
}
Return null;
}
Public synchronized void release (){
// Wait until the last client program is called
If (-- clients! = 0)
{
Return;
}
Enumeration allPools = pools. elements ();
While (allPools. hasMoreElements ())
{
DBConnectionPool pool = (DBConnectionPool) allPools. nextElement ();
Pool. release ();
}
Enumeration allDrivers = drivers. elements ();
While (allDrivers. hasMoreElements ())
{
Driver driver = (Driver) allDrivers. nextElement ();
Try {
DriverManager. deregisterDriver (driver );
Log ("revoking the JDBC driver" + driver. getClass (). getName () + "Registration ");
}
Catch (SQLException e ){
Log (e, "Registration of the following JDBC drivers cannot be revoked:" + driver. getClass (). getName ());
}
}
}

/*
Drivers = sun. jdbc. odbc. JdbcOdbcDriver jdbc. idbDriver

Logfile = D: // user // src // java // DBConnectionManager // log.txt

Idb. url = jdbc: idb: c: // local // javawebserver1.1 // db.

Idb. maxconn = 2

Access. url = jdbc: odbc: demo

Access. user = demo

Access. password = demopw

*/

Private void loadDrivers (Properties props ){
String driverClasses = props. getProperty ("drivers ");
StringTokenizer st = new StringTokenizer (driverClasses );
While (st. hasMoreElements ()){
String driverClassName = st. nextToken (). trim ();
Try {
Driver driver = (Driver)
Class. forName (driverClassName). newInstance ();
DriverManager. registerDriver (driver );
Drivers. addElement (driver );
// System. err. println ("Registered JDBC driver" + driverClassName );
}
Catch (Exception e ){
System. err. println ("Can not register JDBC driver:" + driverClassName + ", Exception:" + e. toString ());
}
}
}
Private void createPools (Properties props ){
Enumeration propNames = props. propertyNames ();
While (propNames. hasMoreElements ())
{
String name = (String) propNames. nextElement ();
If (name. endsWith (". url "))
{
String poolName = name. substring (0, name. lastIndexOf ("."));
String url = props. getProperty (poolName + ". url ");
If (url = null ){
System. err. println ("No URL specified for" + poolName );
Continue;
}
String user = props. getProperty (poolName + ". user ");
String password = props. getProperty (poolName + ". password ");
String maxconn = props. getProperty (poolName + ". maxconn", "0 ");
// System. err. println ("Url:" + url + ", User:" + user + ", password:" + password + ", maxconn:" + maxconn + "... ");
Int max;
Try {
Max = Integer. valueOf (maxconn). intValue ();
}
Catch (NumberFormatException e ){
System. err. println ("Invalid maxconn value" + maxconn + "for" + poolName );
Max = 0;
}
DBConnectionPool =
New DBConnectionPool (poolName, url, user, password, max );
Pools. put (poolName, pool );
// System. err. println ("Initialized pool" + poolName );
}
}
}

Class DBConnectionPool {
Private int checkedOut;
Private Vector freeConnections = new Vector ();
Private int maxConn;
Private String name;
Private String password;
Private String URL;
Private String user;

/**
* Create a new connection pool
*
* @ Param name connection pool name
* @ Param url jdbc url of the database
* @ Param user: database account, or null
* @ Param password, or null
* @ Param maxConn the maximum number of connections allowed in the connection pool
*/
Public DBConnectionPool (String name, String URL, String user
, String password, int maxConn)
{
This. name = name;
This. URL = URL;
This. user = user;
This. password = password;
This. maxConn = maxConn;
}

/**
* Return unused connections to the connection pool.
*
* @ Param con the connection released by the client program
*/
Public synchronized void freeConnection (Connection con ){
// Add the specified join to the end of the Vector
FreeConnections. addElement (con );
CheckedOut --;
Policyall ();
}

/**
* Obtain an available connection from the connection pool. If there is no idle connection and the current number of connections is smaller than the maximum number of connections
* When the number limit is reached, a new connection will be created. If the previously registered available connection is no longer valid, it will be deleted from the vector,
* Then call yourself recursively to try a new available connection.
*/
Public synchronized Connection getConnection ()
{
Connection con = null;
If (freeConnections. size ()> 0)
{
// Obtain the first available connection in the vector
Con = (Connection) freeConnections. firstElement ();
FreeConnections. removeElementAt (0 );
Try {
If (con. isClosed ())
{
Log ("deleting an invalid connection from the connection pool" + name + ");
// Call yourself recursively and try to obtain available connections again
Con = getConnection ();
}
}
Catch (SQLException e)
{
Log ("deleting an invalid connection from the connection pool" + name + ");
// Call yourself recursively and try to obtain available connections again
Con = getConnection ();
}
}
Else if (maxConn = 0 | checkedOut <maxConn)
{
Con = newConnection ();
}
If (con! = Null ){
CheckedOut ++;
}
Return con;
}

/**
* Obtain available connections from the connection pool. You can specify the maximum waiting time for the client program.
* See the previous getConnection () method.
*
* @ Param timeout the wait time limit in milliseconds
*/
Public synchronized Connection getConnection (long timeout)
{
Long startTime = new java. util. Date (). getTime ();
Connection con;
While (con = getConnection () = null)
{
Try {
Wait (timeout );
}
Catch (InterruptedException e ){}
If (new java. util. Date (). getTime ()-startTime)> = timeout)
{
// Wait () returns timeout.
Return null;
}
}
Return con;
}

/**
* Close all connections
*/
Public synchronized void release ()
{
Enumeration allConnections = freeConnections. elements ();
While (allConnections. hasMoreElements ())
{
Connection con = (Connection) allConnections. nextElement ();
Try {
Con. close ();
Log ("close a connection pool" + name + "in a connection pool ");
}
Catch (SQLException e ){
Log (e, "unable to close connections in connection pool" + name + ");
}
}
FreeConnections. removeAllElements ();
}

/**
* Create a new connection
*/
Private Connection newConnection ()
{
Connection con = null;
Try {
If (user = null ){
Con = DriverManager. getConnection (URL );
}
Else {
Con = DriverManager. getConnection (URL, user, password );
}
// System. err. println ("connection pool" + name + "Create a new connection ");
}
Catch (SQLException e ){
Log (e, "cannot create the following URL Connection:" + URL );
Return null;
}
Return con;
}
}
}

 

Dbop. java source code

Package com. china3cts;
Import java. SQL .*;
Public class dbop
{
ResultSet rs = null;
Public ResultSet execute (String SQL)
{
Rs = null;
Try
{
DBConnectionManager connMgr;
ConnMgr = DBConnectionManager. getInstance ();
Connection conn = connMgr. getConnection ("china3cts ");
Statement stmt = conn. createStatement ();
Rs = stmt.exe cuteQuery (SQL );
}
Catch (SQLException ex)
{
System. err. println ("query error:" + ex. getMessage ());
}
Return rs;
}

Public int update (String SQL)
{
Int outn = 0;
Try
{
DBConnectionManager connMgr;
ConnMgr = DBConnectionManager. getInstance ();
Connection conn = connMgr. getConnection ("china3cts ");
Statement stmt = conn. createStatement ();
Outn1_stmt.exe cuteUpdate (SQL );
}
Catch (SQLException ex)
{
System. err. println ("query error:" + ex. getMessage ());
}
Return outn;
}
}

The db. properties configuration file is as follows:

Drivers = org. gjt. mm. mysql. Driver
Logfile = D: // Tomcat // webapps // ROOT // logs // dbpool. log
China3cts. url = jdbc: mysql: // localhost/china3cts
China3cts. user = root
China3cts. password =
China3cts. maxconn = 1000

The java file javac generates the following files:

DBConnectionManager $ DBConnectionPool. class
DBConnectionManager. class
Dbop. class

Store files in different locations

Set class and db. properties

/Web-inf/Classes/com/china3cts directory

Test. jsp

<% @ Page contentType = "text/html; charset = GBK" %>
<% @ Page language = "java" import = "java. SQL. *" %>
<% @ Page language = "java" import = "com. china3cts. DBConnectionManager" %>
<%
Statement stmt = null;
DBConnectionManager connMgr;
ConnMgr = DBConnectionManager. getInstance ();
Connection conn = connMgr. getConnection ("china3cts ");
If (conn! = Null)
{
Out. print ("good ");
String SQL = "select id from sometable limit 1 ";
Stmt = conn. createStatement ();
ResultSet rs = null;
Rs1_stmt.exe cuteQuery (SQL );
If (rs. next ())
{
Out. print (rs. getInt (1 ));
}
Else
{
Out. print ("Error! ");
}
}
Else
{
Out. print ("worse ");
}
%>

 

The following is the source code of test2.jsp.

<% @ Page contentType = "text/html; charset = GBK" %>
<% @ Page language = "java" import = "java. SQL. *" %>
<% @ Page language = "java" import = "com. china3cts. *" %>
<%
ResultSet rs = null;
Dbop conn = new dbop ();
String SQL = "select id from sometable limit 1 ";
Rsw.conn.exe cute (SQL );
If (rs. next ())
{
Out. print (rs. getInt (1 ));
}
Else
{
Out. print ("Error! ");
}

%>

 

Annotations and descriptions are as follows:

0: DBConnectionManager is original for online prawns, and the original author cannot elaborate on it.

1: test. jsp simply calls the connection pool for testing. Remember to call the release method to clear it in actual applications.

2: Upgrade cute is better.

3: two test programs should get the same result, but the first program has a good one!

4: Use dbop. java as appropriate in actual applications. I wrote this myself.

5: china3cts that appears multiple times in the program or source code

The china3cts package in the source code indicates the package name. You can modify the package name.

The china3cts appearing in the test program is the key name china3cts in the configuration file.

Known bugs

1. After numerous tests, it seems that the log in the source DBConnectionManager. java program is unavailable.

I only change the log in the newly obtained connection to system. err. println.

2. The connection pool is a false concurrent connection pool, which is a queue connection pool. It is a false connection pool and cannot be determined. There is no time.

If you know how to debug the log, please tell me, thank you, little brother MSN: info@hkeb.com QQ: 3292957

Thank you very much.

Shenzhen