Use the connection pool to improve the efficiency of Servlet access to the database (2) _ MySQL

III. DBConnectionPool indicates that the class is implemented in rows 209 to 345. it indicates that it points to the connection pool of a database. The database is identified by JDBCURL. A jdbcurl consists of three parts: the protocol identifier (always jdbc), the driver identifier (such as odbc, idb, and oracle), and the database identifier (the format depends on the driver ). For example, jdbc: odbc: demo, servlet

III. DBConnectionPool description

This class is implemented in rows 209 to 345. it indicates the connection pool pointing to a database. The database is identified by the jdbc url. A jdbc url consists of three parts: the protocol identifier (always jdbc), the driver identifier (such as odbc, idb, and oracle), and the database identifier (the format depends on the driver ). For example, jdbc: odbc: de
Mo is a jdbc url pointing to the demo database, and the JDBC-ODBC driver is used to access the database. Each connection pool has a name for the client program, an optional user account, password, and maximum number of connections. If some database operations supported by Web applications can be performed by all users, and other operations should be performed by users with special permissions, the connection pool can be defined for the two types of operations respectively, the two connection pools use the same jdbc url, but use different accounts and passwords.
The construction function of DBConnectionPool requires all the above data as its parameter. As shown in rows 222 to 238, the data is saved as its instance variable:
As shown in rows 252 to 283 and 285 to 305, the customer program can use the two methods provided by the DBConnectionPool class to obtain available connections. The two are in common: if there is a available connection in the connection pool, it will be returned directly. Otherwise, a new connection will be created and a new connection will be returned. If no connection is available and the total number of existing connections is equal to the maximum limit
Number, the first method will return null directly, and the second method will wait until there is a available connection.
All available connection objects are registered in the Vector named freeConnections. If there is more than one connection in the vector, getConnection () always selects the first connection. At the same time, because the new available connections always add vectors from the end, the risk of database connections being closed due to long idle times is minimized.
Before returning the available connection to the client program, the first getConnection () method is called to verify that the connection is still valid. If the connection is closed or an exception is triggered, getConnection () recursively calls itself to try to obtain another available connection. If no connection is available in vector freeConnections
The getConnection () method checks whether the maximum number of connections has been specified. If specified, check whether the current number of connections has reached the limit. If maxConn is 0, there is no limit. If the maximum number of connections is not specified or the current number of connections is smaller than this value, this method attempts to create a new connection. If the connection is successfully created, the connection count is increased and a null value is returned.
As shown in rows 325 to 345, creating a new connection is implemented by the newConnection () method. The creation process depends on whether the database account and password have been specified.
The JDBC DriverManager class provides multiple getConnection () methods. These methods use JDBC URLs and other parameters, such as user accounts and passwords.
DriverManager uses the specified jdbc url to determine the driver suitable for the target database and establish a connection.
The second getConnection () method implemented in rows 285 to 305 requires a time parameter in milliseconds. this parameter indicates the maximum waiting time of the client program. The specific operation for establishing a connection is still implemented by the first getConnection () method.
When this method is executed, initialize startTime to the current time. Try to get a connection in the while loop. If it fails, wait () is called with the given time value as the parameter (). Wait () may be returned because other threads call Y () or notifyAll (), or because the specified time has reached. To find out the real cause returned by wait (), the program uses the current time to reduce the start time (startTime). If the difference value is greater than the specified time, a null value is returned. otherwise, getConnection () is called again ().
Registers an idle connection to the freeConnection () method of the connection pool from 240 to 250. its parameter is the connection object returned to the connection pool. This object is added to the end of the freeConnections vector, and then the used connection count is reduced. Call policyall () to notify other threads waiting for available connections.
Many Servlet engines provide multiple methods for implementing secure shutdown. The database connection pool needs to know the event to ensure that all connections can be closed normally. The DBConnectionManager class coordinates the entire closing process, but the DBConnectionPool class is responsible for closing all connection tasks in the connection pool. The release () method implemented in lines 307 to 323 is called by DBConnectionManager. This method traverses the freeConnections vector, closes all connections, and then deletes these connections from the vector.


IV. DBConnectionManager description

This class can only create one instance, and other objects can call its static method (also called class method) to obtain the reference of this unique instance. As shown in rows 031 to 036, the DBConnectionManager class constructor is private to prevent other objects from creating instances of this class.
The customer program of the DBConnectionManager class can call the getInstance () method to obtain reference to the unique instance of this class. As shown in rows 018 to 029, the unique instance of the class is created during the first call of the getInstance () method, and its reference is always stored in the static variable instance. Each call to getInstance ()
Increase the customer program count of a DBConnectionManager. That is, the count indicates the total number of client programs that reference the unique DBConnectionManager instance. it is used to control the closing operation of the connection pool.
The initialization of this type of instance is completed by the private method init () between 146 and 168 rows. The getResourceAsStream () method is used to locate and open an external file. The method for locating external files depends on the implementation of the class loader. The standard local classloader search operation always starts from the path where the class file is located, and can also search for the path declared in CLASSPATH. Db. properties is an attribute file that contains key-value pairs that define the connection pool. The public attributes that can be defined are as follows:

List of JDBC driver classes separated by spaces by drivers
Absolute path of the logfile log file

Other attributes are related to a specific connection pool. the connection pool name should be added before the attribute name:

. Url jdbc url of the database
Maximum number of connections allowed by. maxconn. 0 indicates no limit
. User: The database account used for the connection pool
. Password

The url attribute is required, while other attributes are optional. The database account and password must be valid. Example of db. properties File for Windows Platform
As follows:

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

Note that two backslashes must be entered in the Windows path, because the backslashes in the attribute file are also an escape character.
The init () method checks the logfile attributes after creating the property object and reading the db. properties file. If no log file is specified in the attribute file, the DBConnectionManager. log file in the current directory is used by default. If the log file cannot be used, log records are output to System. err.
Load and register all the JDBC drivers specified in the drivers attribute from the loadDrivers () method between 170 and 192. This method uses StringTokenizer to split drivers attribute values into strings corresponding to driver names, load these classes in sequence, create their instances, and register them in DriverManager.
Add the instance to a private vector drivers. The vector drivers will be used to cancel registration of all JDBC drivers from DriverManager when the service is disabled.
The last task of the init () method is to call the private method createPools () to create a connection pool object. As shown in rows 109 to 142, the createPools () method first creates an Enumeration object (Enumeration object) for all attribute names. this object can be considered as an element series and calls its nextElement () method returns
And then search for the attributes whose names end with ". url. For each qualified attribute, extract the name of the connection pool, read all attributes belonging to the connection pool, create a connection pool object, and save it in the instance variable pools. Hashed list (Hashtable class) pools implementation connection
The ing between the pool name and the connection pool object. here, the connection pool name is the key and the connection pool object is the value.
DBConnectionManager provides the getConnection () and freeConnection () methods to facilitate the client program to obtain available connections from the specified connection pool or return the connection to the connection pool (). All these methods require specifying the connection pool name in the parameter. the corresponding connection pool object is called to complete the specific connection obtaining or returning operation. They are implemented in the 051 to 064 rows, 066 to 080 rows, and 038 to 049 rows.
As shown in rows 107 to, DBConnectionManager provides the release () method to secure the connection pool (). As mentioned above, all customer programs of DBConnectionManager should call the static method getInstance () to obtain reference from this manager. this call will increase the customer program count.
When the customer program is closed, call release () to decrease the count. When the last client program Calls release () and the decreasing reference count is 0, the release () method of each connection pool can be called to close all connections. The final task of the management class release () method is to cancel registration of all JDBC drivers.


5. example of using the connection pool of Servlet

The Servlet lifecycle classes defined by Servlet APIs are as follows:

1) create and initialize the Servlet (init () method ).
2) respond to the service () method of the customer program ).
3) the Servlet stops running and releases all resources (destroy () method ).

This example demonstrates the connection pool application. the operations in the preceding key steps are as follows:

1) in init (), use the instance variable connMgr to save the reference returned by calling DBConnectionManager. getInstance.
2) at service (), call getConnection () to perform database operations and return the connection to the connection pool using freeConnection.
3) In destroy (), call release () to close all connections and release all resources.

The following is a list of sample programs:

Import java. io .*;
Import java. SQL .*;
Import javax. servlet .*;
Import javax. servlet. http .*;
Public class TestServlet extends HttpServlet {
Private DBConnectionManager connMgr;

Public void init (ServletConfig conf) throws ServletException {
Super. init (conf );
ConnMgr = DBConnectionManager. getInstance ();
}

Public void service (HttpServletRequest req, HttpServletResponse res)
Throws IOException {

Res. setContentType ("text/html ");
PrintWriter out = res. getWriter ();
Connection con = connMgr. getConnection ("idb ");
If (con = null ){
Out. println ("The database connection cannot be obtained .");
Return;
}
ResultSet rs = null;
ResultSetMetaData md = null;
Statement stmt = null;
Try {
Stmt = con. createStatement ();
Rs = stmt.exe cuteQuery ("SELECT * from employee ");
Md = rs. getMetaData ();
Out. println ("employee data ");
While (rs. next ()){
Out. println ("
");
For (int I = 1; I <md. getColumnCount (); I ++ ){
Out. print (rs. getString (I) + ",");
}
}
Stmt. close ();
Rs. close ();
}
Catch (SQLException e ){
E. printStackTrace (out );
}
ConnMgr. freeConnection ("idb", con );
}

Public void destroy (){
ConnMgr. release ();
Super. destroy ();
}
}