Increasing the efficiency of servlet access to databases with connection pooling (1) _jsp programming

The Java servlet, as the preferred server-side data processing technology, is rapidly replacing CGI scripts. One of the advantages of servlet beyond CGI is that not only can multiple requests share common resources, but it can also preserve persistent data between different user requests. This paper introduces a practical technique to give full play to this feature, that is, the database connection pool.


The significance of realizing connection pool

Dynamic Web sites often generate Web pages with information stored in the database, and each page request results in a database visit. Connecting to a database requires not only a certain amount of communication and memory resources, but also a task such as user authentication, security context configuration, and thus often the most time-consuming operation. Of course, the actual connection time overhead varies, but a 1-2-second delay is not uncommon. If a database based Web application only needs to establish an initial connection
Then, different page requests can share the same connection and achieve significant performance improvements.
A servlet is a Java class. The servlet engine, which may be part of the Web services software or a stand-alone add-on module, loads the class into a Java virtual machine and creates an instance of it when the system starts or when the servlet is requested for the first time. Different user requests are handled by multiple independent threads of the same servlet instance. Those who want to
Data that is persisted between different requests can be saved either with the instance variables of the servlet or in a separate auxiliary object.
Accessing a database with JDBC First, you create a connection to the database, a Connection object (Connection), and a connection object that provides a way to execute the SQL statement.
The database connection pool described in this article includes a management class Dbconnectionmanager that provides the interface between multiple connection pool objects (Dbconnectionpool classes). Each connection pool object manages a set of JDBC connection objects, and each connection object can be shared by any number of servlet.
Class Dbconnectionpool provides the following features:

1 get (or create) an available connection from the connection pool.
2) Return the connection to the connection pool.
3 Release all resources when the system shuts down, and turn off all connections.

In addition, the Dbconnectionpool class is able to handle invalid connections (previously registered as available connections, which are no longer available for some reason, such as timeouts, communication issues)
and to limit the total number of connections in the connection pool to a predetermined value.
The management class Dbconnectionmanager is used to manage multiple connection pool objects, and it provides the following features:

1 loading and registering the JDBC driver.
2 Create a connection pool object based on the properties defined in the properties file.
3) To implement the mapping between the connection pool name and its instance.
4 Follow the client's reference to the connection pool, ensuring that all connection pools are securely closed at the end of the last client program.

The remainder of this article describes these two classes in detail, and finally gives an example demonstrating the general process by which the servlet uses connection pooling.


Second, the concrete realization

The list of Dbconnectionmanager.java procedures 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 support for one or more database connections defined by a property file
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 * Returns a unique instance. If this is the first time this method is called, the 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 * Constructor Private to prevent other objects from creating instances of this class
033 */
034 Private Dbconnectionmanager () {
035 Init ();
036}
037
038/**
039 * Return the Connection object to the connection pool specified by name
040 *
041 * @param name of the connection pool 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 * Get an available (free) connection. If no connection is available and the number of connections is less than the maximum number of connections
053 * limit, create and return new connection
054 *
055 * @param name of the connection pool 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 connection is available and the number of connections is less than the maximum number of connections,
068 * Creates and returns a new connection. Otherwise, wait for the other thread to release the connection within the specified time.
069 *
070 * @param name Connection pool name
071 * @param time wait 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, revoke driver registration
084 */
085 public synchronized void release () {
086/Wait until the last client 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 {
Drivermanager.deregisterdriver (driver);
Log ("Undo JDBC Driver" + driver.getclass (). GetName () + "registration");
102}
The catch (SQLException e) {
Log (E, "cannot undo the registration of the following JDBC driver:" + driver.getclass (). GetName ());
105}
106}
107}
108
109/**
110 * Create a connection pool instance based on the specified properties.
111 *
112 * @param props Connection Pool Properties
113 */
114 private void Createpools (Properties props) {
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 ("."));
The String url = props.getproperty (poolname + ". url");
121 if (url = = null) {
122 log ("No connection pool" + poolname + "Specify URL");
123 continue;
124}
The props.getproperty String user = 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) {
The maximum number of connections: "+ 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 create connection pool" + poolname);
140}
141}
142}
143
144/**
145 * Read Property complete initialization
146 */
147 private void init () {
148 InputStream is = GetClass (). getResourceAsStream ("/db.properties");
149 Properties Dbprops = new properties ();
The try {
151 Dbprops.load (IS);
152}
153 catch (Exception e) {
154 System.err.println ("Cannot read property file." +
155 "Please make sure 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 ("Unable to open log file:" + logFile);
164 log = new PrintWriter (SYSTEM.ERR);
165}
166 Loaddrivers (Dbprops);
167 Createpools (Dbprops);
168}
169
170/**
171 * Loading and registering all JDBC drivers
172 *
173 * @param Props Properties
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)
Class.forName (Driverclassname). newinstance ();
183 Drivermanager.registerdriver (driver);
184 drivers.addelement (driver);
185 log ("Successfully registers 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 the log file
203 */
204 private void Log (Throwable e, String msg) {
205 Log.println (New Date () + ":" + msg);
206 E.printstacktrace (log);
207}
208
209/**
210 * This inner class defines a connection pool. It is able to create new connections as required until the most
211 * Large connection number. It can verify the validity of the connection before returning 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;
a private String user;
221
222/**
223 * Create a new connection pool
224 *
@param name Connection pool name
The JDBC URL for @param URL database
@param user Database account number, or null
228 * @param password password, or null
229 * @param maxconn The maximum number of connections allowed for this 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 the connection to the connection pool that is no longer in use
242 *
243 * @param con Client release connection
244 */
245 public synchronized void freeconnection (Connection con) {
246//Add the specified connection 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 free connection and the current number of connections is less than the maximum connection
254 * Number limit, a new connection is created. If the previously registered connection is no longer valid, remove it from the vector,
255 * Then recursively calls itself to try the new available connection.
256 */
257 Public synchronized Connection getconnection () {
258 Connection con = null;
259 if (freeconnections.size () > 0) {
260//Get the first available connection in the vector
261 con = (Connection) freeconnections.firstelement ();
262 Freeconnections.removeelementat (0);
263 try {
264 if (con.isclosed ()) {
265 log ("Remove an invalid connection from the connection pool" + name+);
266//Recursive call yourself, try to get the available connection again
267 con = getconnection ();
268}
269}
270 catch (SQLException e) {
271 log ("Remove an invalid connection from the connection pool" + name+);
272//Recursive call yourself, try to get the available connection 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 * Get the available connections from the connection pool. You can specify the maximum length of time that a client program can wait
287 * See 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 Connection con;
294 while (con = getconnection () = = null) {
295 try {
296 wait (timeout);
297}
298 catch (Interruptedexception e) {}
299 if (new Date (). GetTime ()-starttime) >= timeout) {
The reason for the return of A//wait () is a timeout
The rule 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 ();
The A-a-try {
315 Con.close ();
316 log ("Close connection pool" + name+ "one Connection");
317}
318 catch (SQLException e) {
319 log (E, "Connection in connection pool" + name+ ");
320}
321}
322 freeconnections.removeallelements ();
323}
324
325/**
326 * Create a new connection
327 */
328 private Connection newconnection () {
329 Connection con = null;
The # 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) {
Log (E, "cannot create a connection to the following URL:" + URL);
341 return null;
342}
343 return con;
344}
345}
346}