Using a connection pool to improve the efficiency of Servlet database access (1)

As the preferred server-side data processing technology, Java Servlet is rapidly replacing CGI scripts. One of the advantages of Servlet over CGI is that not only can multiple requests share public resources, but also can retain continuous data between different user requests. This article introduces a practical technology that gives full play to this feature, that is, the database connection pool.


I. Significance of implementing the connection pool

Dynamic Web sites often use the information stored in the database to generate Web pages. Each page request leads to a database access. Connecting to a database requires a certain amount of communication and memory resources, as well as user authentication and security context configuration. Therefore, it is often the most time-consuming operation. Of course, the actual connection time overhead is ever-changing, but the delay between 1 and 2 seconds is not uncommon. If a database-based Web application only needs to establish an initial connection
When different page requests share the same connection, the performance can be significantly improved.
Servlet is a Java class. Servlet Engine (it may be part of Web service software or an independent additional module) when the system starts or the Servlet is requested for the first time, load the class into the Java Virtual Machine and create an instance of the Servlet. Different user requests are processed by multiple independent threads of the same Servlet instance. Those
Data that is continuously valid between different requests can be saved using Servlet instance variables or independent auxiliary objects.
To access a database using JDBC, you must first create a Connection with the database to obtain a Connection object. The Connection object provides the method for executing SQL statements.
The database connection pool described in this article includes a management class DBConnectionManager that provides interfaces with multiple connection pool objects (DBConnectionPool class. Each connection pool object manages a set of JDBC connection objects, and each connection object can be shared by any number of servlets.
DBConnectionPool provides the following functions:

1) Obtain (or create) available connections from the connection pool.
2) return the connection to the connection pool.
3) release all resources and close all connections when the system is shut down.

In addition, the DBConnectionPool class can also handle invalid connections (originally registered as available connections are no longer available for some reason, such as timeout and communication problems)
And the total number of connections in the connection pool cannot exceed a certain predefined value.
Management class DBConnectionManager is used to manage multiple connection pool objects. It provides the following functions:

1) load and register the JDBC driver.
2) create a connection pool object based on the attributes defined in the property file.
3) ing between the connection pool name and its instance.
4) tracking the client program's reference to the connection pool to ensure that all connection pools are safely closed when the last client program ends.

The remaining part of this article will detail these two classes. Finally, an example is provided to demonstrate the general process of using the connection pool of Servlet.


II. Specific 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}