Connection Pool details

Currently, the connection pool of JDBC2 is only an interface and is not actually implemented. JDBC3 is under development,
It is reported that the connection pool is supported, ..........
JDBC3 uses the JNDI technology, and the configuration of the connection pool can upset a master.

Currently, the connection pool implemented by third parties is of course poolman. Version 1.0 is sufficient for general users. the configuration is also simple. Although some functions are added to version 2.0, the configuration also uses JNDI, which may be annoying for friends who do not understand RMI and EJB. we recommend that you use the 1.0 version.

If you are interested, you can also implement the connection pool. The most critical technology is to pass the connection as a parameter to a BEAN, and then return this parameter link instead of closing it.
The following is a simple implementation:
The list of DBConnectionManager. java programs is as follows:

001 import java. io .*;
002 import java. SQL .*;
003 import java. util .*;
004 import java. util. Date;
005
006 /**
007 * management class DBConnectionManager supports connection to one or more databases defined by attribute files
008 * pool access. The client program can call the getInstance () method to access the unique instance of this class.
009 */
010 public class DBConnectionManager {
011 static private DBConnectionManager instance; // unique instance
012 static private int clients;
013
014 private Vector drivers = new Vector ();
015 private PrintWriter log;
016 private Hashtable pools = new Hashtable ();
017
018 /**
019 * a unique instance is returned. If this method is called for the first time, an instance is created.
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}
030
031 /**
032 * construct a private function to prevent other objects from creating this class instance
033 */
034 private DBConnectionManager (){
035 init ();
036}
037
038 /**
039 * return the connection object to the connection pool specified by the name
040 *
041 * @ param name the connection pool name defined in the property File
042 * @ param con connection object
043 */
044 public void freeConnection (String name, Connection con ){
045 DBConnectionPool pool = (DBConnectionPool) pools. get (name );
046 if (pool! = Null ){
047 pool. freeConnection (con );
048}
049}
050
051 /**
052 * obtain an available (idle) connection. If there is no available connection and the number of existing connections is less than the maximum number of connections
053 * limit, a new connection is created and returned.
054 *
055 * @ param name the connection pool name defined in the property File
056 * @ return Connection available Connection or null
057 */
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}
065
066 /**
067 * obtain an available connection. If no available connection is available and the number of existing connections is smaller than the maximum number of connections,
068 * is created and a new connection is returned. Otherwise, wait for other threads to release the connection within the specified time.
069 *
070 * @ param name connection pool name
071 * @ param time wait time in milliseconds
072 * @ return Connection available Connection or null
073 */
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}
081
082 /**
083 * close all connections and cancel driver registration
084 */
085 public synchronized void release (){
086 // wait until the last client program calls
087 if (-- clients! = 0 ){
088 return;
089}
090
091 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 ("revoking JDBC driver" + driver. getClass (). getName () + "registration \\");
102}
103 catch (SQLException e ){
104 log (e, "Registration of the following JDBC driver cannot be revoked:" + driver. getClass (). getName ());
105}
106}
107}
108
109 /**
110 * create a connection pool instance based on the specified attributes.
111 *
112 * @ param props connection pool attributes
113 */
114 private 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 ("not specified URL for connection pool" + poolName + ");
123 continue;
124}
125 String user = props. getProperty (poolName + ". user ");
126 String password = props. getProperty (poolName + ". password ");
127 String maxconn = props. getProperty (poolName + ". maxconn", "0 ");
128 int max;
129 try {
130 max = Integer. valueOf (maxconn). intValue ();
131}
132 catch (NumberFormatException e ){
133 log ("Maximum number of wrong connections limit:" + maxconn + ". Connection Pool:" + poolName );
134 max = 0;
135}
136 DBConnectionPool =
137 new DBConnectionPool (poolName, url, user, password, max );
138 pools. put (poolName, pool );
139 log ("successfully created connection pool" + poolName );
140}
141}
142}
143
144 /**
145 * initialize the read attribute
146 */
147 private void init (){
148 InputStream is = getClass (). getResourceAsStream ("/db. properties ");
149 Properties dbProps = new Properties ();
150 try {
151 dbProps. load (is );
152}
153 catch (Exception e ){
154 System. err. println ("attribute files cannot be read." +
155 "make sure that db. properties is in the path specified by 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 ("log file cannot be opened:" + logFile );
164 log = new PrintWriter (System. err );
165}
166 loadDrivers (dbProps );
167 createPools (dbProps );
168}
169
170 /**
171 * load and register all JDBC drivers
172 *
173 * @ param props attributes
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)
182 Class. forName (driverClassName). newInstance ();
183 DriverManager. registerDriver (driver );
184 drivers. addElement (driver );
185 log ("successfully registered JDBC driver \" + driverClassName );
186}
187 catch (Exception e ){
188 log ("unable to register JDBC driver:" +
189 driverClassName + ", error:" + e );
190}
191}
192}
193
194 /**
195 * write text information to the log file
196 */
197 private void log (String msg ){
198 log. println (new Date () + ":" + msg );
199}
200
201 /**
202 * write text information and exceptions to log files
203 */
204 private void log (Throwable e, String msg ){
205 log. println (new Date () + ":" + msg );
206 e. printStackTrace (log );
207}
208
209 /**
210 * this internal class defines a connection pool. It can create new connections as required until the predefined
211 * the maximum number of connections. It can verify the connection validity before returning the connection to the client program.
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;
221
222 /**
223 * Create a new connection pool
224 *
225 * @ param name connection pool name
226 * @ param url jdbc url of the database
227 * @ param user database account, or null
228 * @ param password, or null
229 * @ param maxConn the maximum number of connections allowed in the connection pool
230 */
231 public DBConnectionPool (String name, String URL, String user, String password,
232 int maxConn ){
233 this. name = name;
234 this. URL = URL;
235 this. user = user;
236 this. password = password;
237 this. maxConn = maxConn;
238}
239
240 /**
241 * return unused connections to the connection pool
242 *
243 * @ param con connection released by the client program
244 */
245 public synchronized void freeConnection (Connection con ){
246 // Add the specified join to the end of the Vector
247 freeConnections. addElement (con );
248 checkedOut --;
249 notifyAll ();
250}
251
252 /**
253 * obtain an available connection from the connection pool. If there is no idle connection and the current number of connections is less than the maximum number of connections
If the number limit is 254 *, a new connection is created. If the previously registered available connection is no longer valid, it is deleted from the vector,
255 * Then recursively call yourself to try a new available connection.
256 */
257 public synchronized Connection getConnection (){
258 Connection con = null;
259 if (freeConnections. size ()> 0 ){
260 // obtain the first available connection in the vector
261 con = (Connection) freeConnections. firstElement ();
262 freeConnections. removeElementAt (0 );
263 try {
264 if (con. isClosed ()){
265 log ("deleting an invalid connection from the connection pool" + name + ");
266 // call yourself recursively and try to obtain available connections again
267 con = getConnection ();
268}
269}
270 catch (SQLException e ){
271 log ("deleting an invalid connection from the connection pool" + name + ");
272 // call yourself recursively and try to obtain available connections again
273 con = getConnection ();
274}
275}
276 else if (maxConn = 0 | checkedOut <maxConn ){
277 con = newConnection ();
278}
279 if (con! = Null ){
280 checkedOut ++;
281}
282 return con;
283}
284
285 /**
286 * obtain available connections from the connection pool. You can specify the maximum waiting time for the client program.
287 * See the previous getConnection () method.
288 *
289 * @ param timeout wait time limit in milliseconds
290 */
291 public synchronized Connection getConnection (long timeout ){
292 long startTime = new Date (). getTime ();
293 connect con;
294 while (con = getConnection () = null ){
295 try {
296 wait (timeout );
297}
298 catch (InterruptedException e ){}
299 if (new Date (). getTime ()-startTime)> = timeout ){
300 // wait () returns timeout
301 return null;
302}
303}
304 return con;
305}
306
307 /**
308 * close all connections
309 */
310 public synchronized void release (){
311 Enumeration allConnections = freeConnections. elements ();
312 while (allConnections. hasMoreElements ()){
313 Connection con = (Connection) allConnections. nextElement ();
314 try {
315 con. close ();
316 log ("close a connection pool" + name + "in a connection pool ");
317}
318 catch (SQLException e ){
319 log (e, "unable to close connection pool" + name + "connection ");
320}
321}
322 freeConnections. removeAllElements ();
323}
324
325 /**
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 + "Create a new connection ");
338}
339 catch (SQLException e ){
340 log (e, "unable to create connections to the following urls:" + URL );
341 return null;
342}
343 return con;
344}
345}
346}


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: demo is a jdbc url pointing to the demo database, and accessing the database uses the JDBC-ODBC driver. 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 available connections exist and the total number of existing connections is equal to the maximum limit, the first method returns null, and the second method waits 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 connections are 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 () adds the customer program count of 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:

<Poolname>. url jdbc url of the database
<Poolname>. maxconn indicates the maximum number of connections allowed. 0 indicates no limit.
<Poolname>. user is the database account used for the connection pool.
<Poolname>. password

The url attribute is required, while other attributes are optional. The database account and password must be valid. An example of the db. properties file for Windows is 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 the drivers attribute value into strings corresponding to the driver name, and then load these classes and create their instances in sequence, finally, register the instance in DriverManager and add it 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 the elements in sequence), and then searches for the elements with the name ". 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. Pools (Hashtable class) maps connection pool names to connection pool objects. 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 ("<H1> employee data </H1> ");
While (rs. next ()){
Out. println ("<BR> ");
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 ();
}
}