Improve the efficiency of servlet access database with connection pool

Author: The Good Soldier

As the preferred server-side data processing technology, Java servlet is quickly replacing the CGI script. One of the advantages of servlet beyond CGI is that not only multiple requests can share public resources, but also reserve continuous data between different user requests. This article describes a practical technology that gives full play to this characteristic, that is, a database connection pool. First, the meaning dynamic web site that implements the connection pool often generates a web page with information stored in the database, each page request, resulting in a database access. The connection database not only has to overhead a certain communication and memory resources, but also the user verification, security context is configured to configure such tasks, and it is often the most time consuming operation. Of course, the actual connection time is overhead, but the 1 to 2 seconds delay is not very common. If a database-based web app requires only an initial connection, the different page requests can share the same connection, and a significant performance improvement can be obtained. Servlet is a Java class. The servlet engine (it may be part of the web service software, or a separate additional module) puts the class into a Java virtual machine when the system start or servlet is requested for the first time, and one instance of the Java virtual machine and creates it. Different users are processed by multiple independent threads of the same servlet instance. Those data that requires continuous effectiveness between different requests can be saved with instance variables of the servlet, or in a separate auxiliary object. Accessing the database with a JDBC first creates a connection between the database to obtain a connection object (Connection), providing a method of executing a SQL statement by the connection object. The database connection pool described herein includes a management class DBConnectionManager, which is responsible for providing an interface between multiple connection pool objects (DBConnectionPool classes). Each connection pool object manages a set of JDBC connection objects, each connection object can be shared by any number of servlet. Class DBConnectionPool provides the following features: 1) Get (or create) available connections from the connection pool. 2) Return the connection to the connection pool. 3) Release all resources when the system is turned off, close all connections. In addition, the DBConnectionPool class can also handle invalid connections (originally registered as available connections, for some reason no longer available, such as timeout, communication issues), and can limit the total number of connections in the connection pool does not exceed a certain predetermined value. Administration DBConnectionManager is used to manage multiple connection pool objects, which provide the following features: 1) Loading and registering a JDBC driver. 2) Creating a connection pool object based on the attribute defined in the properties file. 3) Implement the mapping between the connection pool name and its instance. 4) Track the client's reference to the connection pool to ensure that all connected pools are securely turned off at the end of the last client. The rest of this article will detail the two classes in detail, and finally give an example demonstrate the general process of servlet uses the connection pool.

Second, the specific implementation of the DBConnectionManager.java program list is as follows: 001 Import java.io. *; 002 Import java.sql. *; 003 Import java.util. *; 004 import java.util.date; 005006 / ** 007 * Management Class DBConnectionManager supports access to a database connection 008 * pool defined by the properties file. The client can call the GetInstance () method to access the unique instance of this class .009 * / 010 public class dbconnectionManager {011 static private private DBConnectionManager Instance; // unique instance 012 static private int clients; 013014 private Vector drivers = new Vector (); 015 private PrintWriter log; 016 private Hashtable pools = new Hashtable (); 017018 / ** 019 * returns the unique case of the first example. this method is called, then create an instance 020 * 021 * @return DBConnectionManager unique instance 022 * / 023 static synchronized public DBConnectionManager getInstance () {024 if (instance == null) {025 instance = new DBConnectionManager (); 026} 027 clients ; 028 Return Instance; 029} 030031 / ** 032 * Constructing a function private to prevent other objects from creating this type of instance 033 * / 034 private dbconnectionManager () {035 init (); 036} 037038 / ** 039 * Return the connection object to Connection pool 040 * 041 * @Param name 042 * @Param CON connection object 043 * / 044 public void freeConnection (String name, connection con) {0 45 dbconnectionPool pool = (dbconnectionpool) pools.get (name); 046 if (pool! = Null) {047 pool.freeConnection (con); 048} 049} 050051 / ** 052 * Get a available (idle) connection If there is no connection available, and the existing number of connections is less than the maximum number of connections 053 * limit, then create and return new connection 054 * 055 * @Param Name defined in the properties file Name 056 * @return connection Available connection or null057 * / 058 public Connection getConnection (String name) {059 DBConnectionPool pool = (DBConnectionPool) pools.get (name); 060 if (pool = null!) {061 return pool.getConnection (); 062} 063 return null; 064} 065066 / ** 067 * Get a available connection. If no connection is available, the existing connections is less than the maximum connection limit, 068 * is created and returned to the new connection. Otherwise, wait for other thread release connections in the specified time. 069 * 070 * @Param Name connection pool name 071 * @Param Time in milliseconds waiting time 072 * @

return Connection available connections or null073 * / 074 public Connection getConnection (String name, long time) {075 DBConnectionPool pool = (DBConnectionPool) pools.get (name); 076 if (! pool = null) {077 return pool.getConnection (time ); 078} 079 Return NULL; 080} 081082 / ** 083 * Close all connections, cancel the driver registration 084 * / 085 public synchronized void release () {086 // Wait until the last client call 087 IF (- ! -clients = 0) {088 return; 089} 090091 Enumeration allPools = pools.elements (); 092 while (allPools.hasMoreElements ()) {093 DBConnectionPool pool = (DBConnectionPool) allPools.nextElement (); 094 pool.release ( ); 095} 096 Enumeration allDrivers = drivers.elements (); 097 while (allDrivers.hasMoreElements ()) {098 Driver driver = (Driver) allDrivers.nextElement (); 099 try {100 DriverManager.deregisterDriver (driver); 101 log ("Undo JDBC Driver" Driver.getClass (). GetName () "Registration"); 102} 103 Catch (SQLException E) {104 log (e, "Unable to revoke the following JDBC driver registration:" Driver.getClass (). getName ()); 105} 106} 107} 108109 / ** 110 * Create a connection pool instance according to the specified property .111 * 112 * @Param PROPS connection pool attribute 113 * / 114 P rivate void createPools (Properties props) {115 Enumeration propNames = props.propertyNames (); 116 while (propNames.hasMoreElements ()) {117 String name = (String) propNames.nextElement (); 118 if (name.endsWith ( ". URL ")) {119 string poolname = name.substring (0, name.lastIndexof (".)); 120 string url = props.getProperty (poolname ".url"); 121 if (URL == null) { 122 log ("No Pool" PoolName "Specify URL"); 123 Continue; 124} 125 String User = Props.getProperty (PoolName ".user"); 126 string password = props.getProperty (poolname " .password "); 127 string maxiConn =

Props.getProperty (Poolname ".maxConn", "0"); 128 int max; 129 try {130 max = integer.Valueof (maxconn) .intValue (); 131} 132 catch (NumberFormatexcection e) {133 log (" Error maximum connection limit: " MaxConn ". Connection pool: " poolname); 134 max = 0; 135} 136 dbconnectionpool pool = 137 New DbconnectionPool (PoolName, URL, User, Password, Max); 138 pools. PUT (PoolName, Pool); 139 log ("successfully created connection pool" poolname); 140} 141} 142} 143144 / ** 145 * Read attribute completion initialization 146 * / 147 private vidinit () {148 inputStream IS = getClass (). getResourceAsStream ("/ db.properties"); 149 Properties dbprops = new proties (); 150 try {151 dbprops.load (IS); 152} 153 catch (Exception E) {154 system.rr.println ("You cannot read attribute files." 155 "Please ensure that db.properties is in the path specified in ClassPath"); 156 return; 157} 158 string logfile = dbprops.getProperty ("logfile", "dbconnectionmanager.log"); 159 Try {160 log = New printwriter (new filewriter (logfile, true), true); 161} 162 Catch (IOException E) {163 system.err.println ("Unable to open the log file:" logfile); 164 log = New PrintWriter (System.err); 165} 166 LoadDriver s (dbProps); 167 createPools (dbProps); 168} 169170 / ** 171 * load and register all JDBC drivers 172 * 173 * @param props attribute 174 * / 175 private void loadDrivers (Properties props) {176 String driverClasses = props.getProperty ( "drivers"); 177 StringTokenizer st = new StringTokenizer (driverClasses); 178 while (st.hasMoreElements ()) {179 String driverClassName = st.nextToken () trim ();. 180 try {181 Driver driver = (DRIVER) (DRIVERCLASSNAME) .NEWINSTANCE (); 183 DriverManager.RegisterDriver; 184 drivers.addelement (driver); 185 log ("successfully registered JDBC driver" DriverClassName);

187 catCH (Exception E) {188 log ("Unable to register JDBC driver:" 189 driverclassname ", error:" e); 190} 191} 192} 193194 / ** 195 * Write text information Log file 196 * / 197 Private void log (string msg) {198 log.println (new date () ":" msg); 199} 200201 / ** 202 * Write text information and exception write log file 203 * / 204 private void log (throwable e, string msg) {205 log.println (new date () ":" msg); 206 E.PrintStackTrace (log); 207} 208209 / ** 210 * This internal class definition A connecting pool. It can create a new connection as required until the predetermined number 211 * Dalian number. Before returning to the client, it can verify the validity of the connection. 212 * / 213 Class DbConnectionPool {214 private Int checkedOut; 215 private Vector freeConnections = new Vector (); 216 private int maxConn; 217 private String name; 218 private String password; 219 private String URL; 220 private String user; 221222 / ** 223 * create a new connection pool 224 * 225 * @Param name connection pool name 226 * @Param URL database JDBC URL227 * @Param User database account, or null228 * @Param password password, or null229 * @Param MaxConn This connection pool allows the maximum number of connections 230 * / 231 Public DBConnectionPool (String Name, String Url, String User, String Password, 232 INT MAXCONN) {233 this.name = name; 2 34 this.url = url; 235 this.user = user; 236 this.password = password; 237 this.maxconn = maxconn; 238} 239240 / ** 241 * will no longer use the connection to the connection pool 242 * 243 * @Param Connection 244 * / 245 Public Synchronized void freeConnection (Connection Con) {246 // The specified connection is added to the vectors of the vectors 247 freeConnections.addelement (248 checkedout -; 249 notifyall (); 250 } 251252 / ** 253 * Get a available connection from the connection pool. If there is no idle connection and the current connection is less than the maximum connection 254 * number limit, the new connection is created. If the original registration is available, it is no longer effective, then Vector delete, 255 * then recursively call yourself to try new available connections. 256 * / 257 public synchronized connection getConnection () {258 connection con = NULL; 259 if (FreeConnections.Size ()> 0) {260 // Get The first available connection 261 con = (connection) freeConnections.FirstElec ();

262 FreeConnections.removeEleMentat (0); 263 Try {264 if ("" "" "" "" From the connection pool " Name " deletes an invalid connection "); 266 // Removable call yourself, try again to get available again Connection 267 con = getConnection (); 268} 269} 270 CATCH ("Squexception E) {271 log (" From the connection pool " Name " delete an invalid connection "); 272 // Removable call yourself, try again to get available connection 273 Con = getConnection (); 274} 275} 276 else if (MaxConn == 0 || Checkedout = timeout) {300 // Wait () returned reason is timeout 301 return null; 304 Return Con; 305} 306307 / ** 308 * Close all connections 309 * / 310 public synchronized void release () {311 enumeration al lConnections = freeConnections.elements (); 312 while (allConnections.hasMoreElements ()) {313 Connection con = (Connection) allConnections.nextElement (); 314 try {315 con.close (); 316 log ( "Close Connection Pool" Name "One connection"); 317} 318 Catch (SQLEXCEPTION E) {319 log (e, "Unable to close the connection in the connection pool" Name ""); 320} 321} 322 FreeConnections.removeLelements (); 323 } 324325 / ** 326 * Create a new connection 327 * / 328 private connection newconnection () {329 connection con = NULL; 330 try {331 if (user == null) {332 con = drivermanager.getConnection (URL); 333 } 334 else {335 con =

Drivermanager.getConnection (URL, User, Password); 336} 337 Log ("Connection pool" Name "creates a new connection"); 338} 339 Catch (SQLEXCEPTION E) {340 log (e, "Unable to create the following URL Connection: " URL); 341 Return NULL; 342} 343 Return Con; 344} 345} 346} 3. Class DBConnectionPool Description This class is implemented in 209 to 345 rows, which indicates the connection pool to a database. The database is identified by the JDBC URL. A JDBC URL consists of three parts: protocol identifier (always JDBC), driver ID (such as ODBC, IDB, ORACLE, etc.), database identity (its format depends on the driver). For example, JDBC: ODBC: Demo is a JDBC URL pointing to the DEMO database, and accesses the database to use the JDBC-ODBC driver. Each connection pool has a name for the client and an optional user account, password, maximum connection limit. If some database operations supported by the web application can be executed by all users, while other operations should be performed by special licensed users, they can define the connection pool for two types of operations, and the two connecting pools use the same JDBC URL, But use different accounts and passwords. The construction function of class DBConnectionPool requires all of the above data as its parameters. As shown in 222 to 238, these data are saved as their instance variables: such as 252 to 283 lines, 285 to 305 lines, and client programs can use two methods available to DBConnectionPool class to get available connections. The commonality is that there is a available connection in the connection pool, but it will return directly, otherwise create a new connection and returns. If there is no connection available and the total number of connections is equal to the maximum limit, the first method will return directly to NULL, and the second method will wait until there is available connection. All available connection objects are registered in the vector (Vector) called FreeConnections. If there are more connections in the vector, getConnection () always selects the first. At the same time, since the new available connection is always added from the tail, the database connection is reduced to the lowest degree due to long-term idleness. The first getConnection () calls the isclosed () method to verify the connection is still valid before returning to the client. If the connection is turned off or triggered, getConnection () recursively calls yourself to attempt to get additional available connections. If there is no available connection in the vector freeConnections, the getConnection () method checks if the maximum connection limit is specified. If you have already specified, check if the current connection has reached the limit. There is no limit to the MAXCONN 0 here. If the maximum number of connections is not specified or the current connection is less than this value, the method attempts to create a new connection. If you create success, add a count that has been used and returned, otherwise return null value. As shown in the 325 to 345 line, create a new connection is implemented by the newconnection () method. The creation process is related to whether the database account has been specified, the password is related. JDBC's DriverManager class provides multiple getConnection () methods, which use the JDBC URL and other parameters such as user accounts and passwords. DriverManager determines the driver that is suitable for the target database with the specified JDBC URL and establishes the connection. The second getConnection () method implemented at 285 to 305 rows requires a time parameter in milliseconds, which indicates the maximum time of the client to wait.

The specific operation of establishing a connection is still implemented by the first getConnection () method. This method is executed to initialize the startTime to the current time. Try a connection in the While loop. If it fails, call WAIT () is called with a given time value. The return of Wait () may be due to other threads to call notify () or notifyall (), or may be due to the predetermined time. To find the real cause of Wait () returned, the program is used to reduce the start time (startTime), if the difference is greater than the predetermined time, returns a null value, otherwise getConnection () again. Register idle connections to the connection pool by a 240 to 250 line FREECONNECTION () method implementation, its parameters are the connection object that is returned to the connection pool. This object is added to the end of the FreeConnections vectors, and then reduces the use of the connection count. Calling NotifyAll () is to inform the other thread that is waiting for the available connection. Many servlet engines provide a variety of ways to implement security shutdown. The database connection pool needs to be aware of the event to ensure that all connections can be turned off. DBConnectionManager class negative coordinates the closing process, but the task of all connected connections is turned off by the DBConnectionPool class. The Release () method implemented at 307 to 323 rows is called by DBConnectionManager. This method traverses the FreeConnections vector and closes all connections, then removes these connections from the vector. 4. Class DBConnectionManager Description This class can only create an instance, and other objects can call their static methods (also known as class methods) to obtain the unique instance reference. As shown in the 031 to 036, the construction function of the DBConnectionManager class is private, which is to avoid instances of other objects to create this class. The client of the DBConnectionManager class can call the getInstance () method to get a reference to the unique instance of this class. As shown in the 018 to 029 line, the unique example of the class is created during the GetInstance () method, which is created during the call, which will now be stored in the static variable instance. Each time you call getInstance () add a DBConnectionManager's customer count. That is, the count represents the total number of client programs that reference the DBConnectionManager unique instance, which will be used to control the closing operation of the connection pool. The initialization of this class is completed by the private method init () between 146 to 168 lines. Where the getResourceAsStream () method is used to locate and open the external file. The positioning method of external documents depends on the implementation of the class loader. The standard local type loader lookup operation always begins the path where the class file is located, and it is also possible to search the path declared in classpath. Db.properties is a property file that contains the key-value pair that defines the connection pool. The common attributes available for definition are as follows: Drivers are related to the absolute path of the Logfile log file in space-separated JDBC driver class Logfile log files, and should be added to the pool name before the attribute name: .url database JDBC URL .maxConn allows the maximum number of connections, 0 indicates that the .User is not limited to the database account of the connection pool .password, the URL property is required, and other attributes It is optional. The database account account and password must be legitimate.

Examples of DB.Properties file for Windows platforms are as follows: drivers = sun.jdbc.odbc.jdbcodbdriver jdbc.idbdriverlogfile = D: //user/src//java//dbconnectionManager//log.txtidb.url=jdbc: IDB : c: //local//javawebserver1.1//db//db.prpidb.maxconn=2access.url=jdbc: ODBC: Demoaccess.user = demoaccess.password = demopw Note that the backslash in the Windows path must be entered 2, this is because the backslash in the properties file is also a escape character. The init () method begins to check the logfile property after creating a property object and reading the DB.Properties file. If you do not specify a log file in the properties file, the DBConnectionManager.log file in the current directory is default. If the log file cannot be used, the log record is output to System.err. Loading and register all the JDBC drivers specified in the DRIVERS attribute implementation of the loadDrivers () method between 170 to 192 lines. This method first uses StringTokenizer to split the drivers attribute value to a string corresponding to the driver name, and then load these classes and create its instance, and finally register this instance in the DriverManager and add it to a private vector Drivers. Vector Drivers will cancel all JDBC drivers from DRIVERMANAGER to close the service. The last task of the init () method is to call the private method CreatePools () Create a connection pool object. For example, the CreatePools () method first creates an enumeration object of all attribute names (ie ENUMERATION object, the object can be imagined as a element series, and call its nextElement () method to return each element), then The search name is the attribute ending with ".url". For each of the qualified properties, first extract its connection pool name, and then read all properties belonging to the connection pool, and finally create the connection pool object and save it in the instance variable pools. Hash Table (Hashtable Class) Pools implements the mapping between the connection pool name to the connection pool object, here the connection pool name is key, the connection pool object is the value. To facilitate customer programs to get available connections from the specified connection pool or return the connection to the connection pool, class DBConnectionManager provides methods getConnection () and FreeConnection (). All of these methods require that the connection pool name is specified in the parameter, the specific connection acquisition or return operation calls the corresponding connection pool object to complete. Their implementations are from 051 to 064, 066 to 080 rows, 038 to 049. As shown in the 082 to 107 lines, the security shutdown of the connection pool is achieved, and DBConnectionManager provides methods Release (). In the above, we have mentioned that all DBConnectionManager's clients should call the static method getInstance () to get a reference to the manager, which will increase the customer count. The client calls Release () can decrement the count when shutting down. When the last customer program is called Release (), the decremented reference count is 0, you can call the Release () method of each connection pool to close all connections. The Last task of the management class Release () method is to revoke all JDBC drivers registration. 5. Servlet uses the servlet lifecycle class defined by the connection pool sample Servlet API to create and initialize the servlet (INIT () method).