An immature database connection pool (Part 1)

An immature database connection pool (Part 1) ShegG original (Participation: 32, expert points: 100) Published: 2004-6-7 10:35 PM: 2004-6-11 3:50 pm Version: 2.0 Read: 2325 times

Recently, I started to develop a website with a powerful background. When using the connection pool, I feel that the connection pool that uses the server to bring some restrictions. At the same time, in order to deepen the learning and research on Java. Write the connection pool class below. The connection pool mainly has a function; 1) Initialization once, it is used everywhere. 2) Powerful log function, record each SQL action, including connection, resultset, and statement3), according to the number of connections, automatically reclaim the released or timeout connection. 4) Configuration is flexible, you can use a variety of JDBC drivers to support multi-driver.

Source code: / ** @TITLE connection pool * @author: zxg * @version 1.0 * @Memo: Define database connection and its database connection pool, etc. * / package com.drsl.db; import java.io. *; Import Java . SQL. *; Import java.util. *; import java.util.date; import java.lang.reflect. *; import com.mysql.jdbc.driver; import com.drsl.db. *; public class connectionManager { static private ConnectionManager instance; // unique instance of static private int clients; static private int maxonlinetime = 30 * 60 * 1000; private Vector drivers = new Vector (); private Hashtable pools = new Hashtable (); private Timer checkConnTimer = new Timer ( ); / ** * Return unique instance. If this method is called, create an instance * * @return connectionManager unique instance * / static synchronized public connectionManager getInstance () {if (instance == null ) {Instance = new connectionManager ();} // clients ; returnial;} / ** * Construct function private to prevent other objects from creating this type of instance * / private connectionManager () {init ();} / ** * read Take the attribute completion initialization * / Private void init () {Try {inputStream is = getClass (). GetResourceAsStream ("db.properties"); Properties dbprops = new Properties (); dbprops.load (IS);} catch (exception e) {e. PrintStackTrace (); system.err.println ("You cannot read attribute file =" "Please ensure that DB.Properties is in the path specified by classpath"); return;} string logfile = dbprops.getProperty ("logfile", "log .txt "); try {log = new PrintWriter (new filewriter (logfile, true), true);

} Catch (ioException e) {system.err.println ("Unable to open the log file:" logfile); log = new printwriter (} loadDrivers (dbprops); createpools (dbprops);} / ** * load and register all JDBC drivers * * @param props attribute * / 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 (); if (driver = null) {DriverManager.registerDriver (driver)!; Drivers.addelement (driver); log ("successfully registered JDBC driver" driverclassname);} else {log ("begin"); log ("Register JDBC Driver" Driver ClassName "failed");}}}}} Catch (Exception E) {log ("begin"); log ("Unable to register JDBC driver:" driverclassname ", error:" e);}}} / ** * Create a connection pool instance specified property * * @param props connection pool properties * / 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) {log ("No Pool" PoolName "designated URL"); continue;} String user = props.getProperty (poolName ".user"); String password = props.getProperty (poolName ".password"); String maxconn = props.getProperty (poolName ". "0", "0"); string minconn = props.getProperty (poolname ".minconn", "10"); string option = props.getProperty (poolname ". Option", ""); int MAX, min; Try {MAX = INTEGER. Valueof (MaxConn) .intValue ();} catch ("NumberFormatexcection E) {log (" Error maximum connection limit: " maxconn ". Connection pool: " poolname); max = 0;} Try { Min = INTEGER.VALUEOF (MinConn) .intValue ();} catch ("NumberFormatexcection E) {log (" Error minimum connection limit: " minconn ". Connection pool: " poolname); min = 0;} TRY {ConnectionPool Pool = New ConnectionPool (PoolName, URL, User, Password, Min, Max, Option); Pools.Put (PoolName, Pool);

// 2 seconds start each one minute to check the connection pool in the case of CHECKCONNTIMER.SCHEDULE (Pool, 2000, 60 * 1000); log ("successfully created connection pool" poolname);} catch (Exception E) {log e, "Create DBConnectionPool error");}}}} / ** * Return the connection object to the connection pool of the connection pool by the name * @Param Name in the properties file * @Param CON connection object * / Public void freeconnection (String Name, Connection Conn) Pools.get (Name); if (pool! = null) {pool.freeConnection (conn);}} / ** * Get a available ( Idle) connection. If there is no connection available, the existing connections is less than the maximum number of connections * restrictions, then create and return new connections * @Param name defined in the properties file Name * @Return Connection Available Connection or Null * / Public connection getConnection (String Name) {connectionPool pool = (connectionpool) pools.get (name); if (pool! = Null) {Return pool.getConnection ();} return null;} / ** * get a available Connection. If there is no connection available, and the number of connections is less than the maximum number of connections, * Create and return to the new connection. Otherwise, wait for other thread release connections in the specified time. * * @Param Name connection pool name * @Param Time Waiting time in milliseconds * @return connection Available Connection or null * / public connection getConnection (String name, long time) {connectionPool pool = (connectionpool) pools.get (name); if (pool! = Null) {Return pool.getConnection (Time);} return null;} / ** * Close all connections, Registration of the driver * / public synchronized void release () {// Waiting until the last client call // if (--clients! = 0) {// Return; //} checkconntimer.cancel (); Enumeration Allpools = Pools.efficient ();

while (allPools.hasMoreElements ()) {ConnectionPool pool = (ConnectionPool) allPools.nextElement (); pool.cancel (); pool.release ();} Enumeration allDrivers = drivers.elements (); while (allDrivers.hasMoreElements () ) {Driver Driver = (driver) alldrivers.nextelement (); try {drivermanager.deregisterdriver; log ("revocation JDBC driver" driver.getClass (). GetName () "Registration");} catch (SQLEXCEPTION E) {log (e, "Unable to revoke the following JDBC driver registration:" Driver.getClass (). GetName ());}}} / ** * Write text information to log files * / static public public Void log (string msg) {log.println (new date () ":" msg);} / ** * Writing text information with exception write log file * / static public void log (throwable E, STRING MSG) {Log.println (new date () ":" msg); E.PrintStackTrace (log);} // Test static public void main (string [] args) {Connect ionManager dcm = null; try {dcm = ConnectionManager.getInstance (); Connection conn = dcm.getConnection ( "mysql"); Connection conn1 = dcm.getConnection ( "mysql"); Connection conn2 = dcm.getConnection ( "mysql") ; Connection conn3 = dcm.getConnection ( "mysql"); Connection conn4 = dcm.getConnection ( "mysql"); Connection conn5 = dcm.getConnection ( "mysql"); Connection conn6 = dcm.getConnection ( "mysql"); Connection CONN7 = DCM.GetConnection ("mysql");

Connection conn8 = dcm.getConnection ( "mysql"); Connection conn9 = dcm.getConnection ( "mysql"); Connection conn10 = dcm.getConnection ( "mysql"); Connection conn11 = dcm.getConnection ( "mysql"); Connection conn12 = DCM.GetConnection ("mysql"); connection conn13 = DCM.GetConnection ("mysql"); connection conn14 = dcm.getConnection ("mysql"); ResultSet RS; string sql = "select * from css"; statement st = Conn.createStatement (); if (st == null) {log ("main - error while get /" statement / "); return;}}} = st.executeQuery (SQL); if (rs == null) {Log ("Main - Error While Get /" ResultSet / "); return;} system.out.println (" / r / n "); while (rs.next ()) {system.out.println rs.getstring (1));}}}}}}}}}}} rs.close (); ST.Close (); rs = null; ST = null; conn.close (); conn1.close (); conn2.close (); conn4.close (); conn4.close (); conn5.close (); conn5.close (); Conn6.close (); conn7.close (); conn9.close (); conn9.close (); conn11.close (); conn12.close (); conn12.close (); conn13.close (); conn14. .. CONN14; CONN13.CLOSE (); conn14. CLOSE (); conn = null; conn1 = null; conn2 = NULL;

CONN3 = NULL; conn5 = null; conn6 = null; conn7 = null; conn8 = null; conn9 = null; conn9 = null; conn10 = null; conn11 = null; conn12 = null; conn13 = null; conn14 = null;} catch; catch (SQLEXCEPTION E) {dcm.log (e, "main - error");}}} /// ************** Connection pool ******* ***************************************** * *** This type of definition A connecting pool. It can create a new connection as required until the predetermined maximum of the larger connection. Before returning to the client program, it can verify the validity of the connection. * It inherits the Timer member of Timertask by the ConnectionManager class Scheduling * / package com.drsl.db; import java.io. *; Import java.sql. *; Import java.util. *; Import java.util.date; import java.lang.reflect. *; Import COM. drsl.db *;. public class ConnectionPool extends TimerTask {private int countConn; private Vector freeConns = new Vector (); private Vector usingConns = new Vector (); private int maxUseTime; // use the maximum idle time is connected private int maxFreeTime ; // The maximum idle time (when the number of connections is not less than the minimum connection, turn off this connection) private int MAXCO NN; // Maximum connector private int minConn; // minimum connection number private string name; // pool name private string URL; private string user; private string password; private string option; // private printwriter log; / ** * Create a new connection pool * * @Param Name connection pool name * @Param URL database JDBC URL * @Param DBINFO Database Connection Information * @Param MaxConn This connection pool allows the maximum number of connections * / public connectionPool (String Name, String Name URL, STRING User, String Password, int Minconn, int maxconn, string option) {this.name = name; this.url = url; this.user = user; this.password = password; this.Option = Option;

THIS.MAXCONN = MaxConn; this.minConn = MinConn; if (this.minConn <= 0) this.minConn = 10; log ("end one part / r / n"); for (int i = 0; i

NULL; Connection Conn = NULL; // Gets the first available connection in the vector Try {connobj = (connectionObject) freeConns.get (0);} catch (exception e) {log ("end one part / r / n") LOG ("From the connection pool" Name "get a connection failed"); if (MaxConn == 0 || CountConn =

Timeout) {// Wait () The reason is timeout ????????? Return NULL;}} Return Conn;} / ** * Close all connections * / public synchronized void release () {// Cancel (); Enumeration allConnections = freeConns.elements (); while (allConnections.hasMoreElements ()) {ConnectionObject connobj = (ConnectionObject) allConnections.nextElement (); try {connobj.close (); connobj = null; log ( "close connection A connection in the pool " Name " ");} catch (sqlexception e) {// sqlexception log (e," cannot close the connection in the connection pool " Name ");}} FreeConns.removeLleleleference (); / / allConnections = usingConns.elements (); while (allConnections.hasMoreElements ()) {ConnectionObject connobj = (ConnectionObject) allConnections.nextElement (); try {connobj.close (); connobj = null; LOG ("Close a connection in the connection pool" Name "");} catch (SQLException E) {// SQLException log (e, "Unable to close the connection in the connection pool" Name ");}} usingConns. RemoveAllelements ();} / ** * Create a new connection * / private connectionObject newconnection () {connectionObject connobj = null; try {log ("Connection pool" Name "Creates a new connection object"); string url = url Option; log ("URL =" URL); connection conn =

DriverManager.getConnection (URL, user, password); connobj = new ConnectionObject (conn, false); freeConnection (connobj); countConn ;} catch (SQLException e) {log (e, "not create a connection at the following URL:" url "For user =" user "password =" password); return null;} Return connobj;} // Check each connection status (once per minute) public void run () {connectionObject connobj = null; // Recycle is being used The connection of "closed" is "closed" connection INT i = 0; while (i 20 * 60 * 1000) {// Timeout is 20 minutes log ("RUN - Recycling Time Vacation"); FreeConnection (CONNOBJ); USINGCONNS.RemoveEleMentat (i); i --Dexoutofboundsexception e ) {Log (e, "RUN - Recycling time has timeout connection - failed");

}}}} I ;} // Delete I = 0; While (I 1) {TRY {connobj = (connectionObject) freeConns.get (0); connobj.close (); connobj = null; freeConns.removeElementat 0); countconn -; log ("RUN - Delete Excess (larger than the minimum number of connections) from idle connections");} catch (SQLException E ) {Log (e, "RUN - Unnecessary (greater than minimum connection) connection from idle connections - failure");} i ;} // increase the minimum connection number IF required for connection maintenance requirements (CC <0) {Cc = -cc; log ("End One Part / R / N"); log ("RUN - increases the minimum number of connections required"); for (i = 0; i

} / ** * Write text information to log files * / private void log (string msg) {connectionManager.log (msg);} / ** * Writing text information and exception write log file * / private void log (throwable E, STRING MSG) {ConnectionManager.log (e, msg);}} // ** * Self-encapsulation of data connection, blocking the Close method and CreateStatement, preplestatement method to return to your own takeover * @Author zxg * / package com.drsl.db; import java.io *;. import java.sql *;. import java.util *;. import java.util.Date; import java.lang.reflect *;. public class ConnectionObject implements InvocationHandler {private final static String CLOSE_METHOD_NAME = "close"; private final static String CREATSTATMENT_METHOD_NAME = "createStatement"; private final static String PREPARESTATEMENT_METHOD_NAME = "prepareStatement"; private Connection conn = null; private Connection conn_proxy = null; private PreparedStatementObject pso = null; private StatementObject stmo = NULL; // Database busy status private bolean inuse = false; // User last visited the connection method of this connection method Private Static Long L Astaccesstime = new date (). getTime (); public connectionObject (connection conn, boolean inuse) {this.conn = conn; this.inuse = inuse;} / ** * returns the conn. * @return connection * / ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Returns the database connection CONN's tubing class to intercept the various methods of Connection PUBLIC Connection getConnection () {if (conn_proxy == null) {ClassLoader ClassLoader = Conn.getClass (). GetClassLoader (); class [] interfaces = conn.getClass () .GetInterfaces (); if (interfaces == null || interfacs.length == 0) {interfaces = new class [1]; interfaces [0] =

Connection.class;} try {conn_proxy = (Connection) Proxy.newProxyInstance (classloader, interfaces, this);} catch (NullPointerException e) {log (e, "ConnectionObject getConnection () - error");} if (conn_proxy! = NULL) LOG ("" ConnectionObject getConnection () - Success ");} Return conn_proxy;} / ** * This method is really turned off in the database * @throws sqlexception * / synchronized void close () throws sqlexception {// Since the class attribute conn is not connected to the connection, once the CLOSE method is called, it will directly turn off the connection conn.close (); conn = null;} / ** * returns the inuse. * @Return boolean * / public boilean isinuse ) {Return inuse;} / ** * @see java.lang.reflect.InvocationHandler # invoke (java.lang.object, java.lang. object) * / Public Object Invoke (Object Proxy , Method M, Object [] args) throws throwable { Object obj = null; log ("" ConnectionObject - Invoke: Method: /"" m.getname () "/ "); // Determine if a Close method is called, if the CLOSE method is called to Useless State IF (Close_Method_name.equals (M.GetName ())) {setinuse (false);} else if (creatstant_method_name.equals (m.getname ())) {// If CreateStatment method is called, package Statement class Accept Manage Statement STM = (Statement) M.Invoke (CONN, ARGS); if (STM! = Null && stmo == null) {Stmo = New StatementObject (STM); Obj = stmo.getStatement ();

Else {ConnectionManager.log ("ConnectionObject - Invoke: Method: /"" m.getname () "/"-- failed");}} else f (preparestatement_method_name.equals (m.getname ()))) {// If the method invocation createStatment, the package management class accepts PreparedStatement PreparedStatement ps = (PreparedStatement) m.invoke (conn, args); if (! ps = null && pso == null) {pso = new PreparedStatementObject (ps) } Else if (pso! = Null) {obj = pso.getpreparedStatement ();} else log ("ConnectionObject - invoke: Method: /"" m.getname () "/"-- failed"; } else Obj = m.invoke (conn, args); // Set the last access time to clear the timeout connection LastAccesstime = new Date (). getTime (); returnobj;} / ** * returns the lastaccesstime. * @Return long * / public static long getlastaccesstime () {return lastaccesstime;} / ** * set the l . AstAccessTime * @param latimelong * / public static void setLastAccessTime (long latime) {lastAccessTime = latime;} / ** * Sets the inUse * @param inUse The inUse to set * / public void setInUse (boolean inUse) {this.. Inuse = inuse;} public synchronized void release () {Try {close ();} catch (sqlexception e) {log (e, "ConnectionObject - Release call close failed");}} / ** * Write text information Entering the log file * / private void log (string msg) {ConnectionManager.log (MSG);