KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > javaexchange > dbConnectionBroker > DbConnectionBroker


1 /**
2  * DbConnectionBroker.
3  * @version 1.0.13 3/12/02
4  * @author Marc A. Mnich
5  */

6 package com.javaexchange.dbConnectionBroker;
7 import java.io.*;
8 import java.sql.*;
9 import java.text.*;
10 import java.util.Date JavaDoc;
11
12
13 /**
14  * DbConnectionBroker
15  * A servlet-based broker for database connections.
16  * Creates and manages a pool of database connections.
17  * @version 1.0.13 3/12/02
18  * @author Marc A. Mnich
19  */

20 public class DbConnectionBroker implements Runnable JavaDoc {
21     private Thread JavaDoc runner;
22     
23     private Connection[] connPool;
24     private int[] connStatus;
25
26     private long[] connLockTime, connCreateDate;
27     private String JavaDoc[] connID;
28     private String JavaDoc dbDriver, dbServer, dbLogin, dbPassword, logFileString;
29     private int currConnections, connLast, minConns, maxConns, maxConnMSec,
30     maxCheckoutSeconds, debugLevel;
31
32     //available: set to false on destroy, checked by getConnection()
33
private boolean available=true;
34
35     private PrintWriter log;
36     private SQLWarning currSQLWarning;
37     private String JavaDoc pid;
38
39     private final int DEFAULTMAXCHECKOUTSECONDS=60;
40     private final int DEFAULTDEBUGLEVEL=2;
41     
42     /**
43      * Creates a new Connection Broker<br>
44      * dbDriver: JDBC driver. e.g. 'oracle.jdbc.driver.OracleDriver'<br>
45      * dbServer: JDBC connect string. e.g. 'jdbc:oracle:thin:@203.92.21.109:1526:orcl'<br>
46      * dbLogin: Database login name. e.g. 'Scott'<br>
47      * dbPassword: Database password. e.g. 'Tiger'<br>
48      * minConns: Minimum number of connections to start with.<br>
49      * maxConns: Maximum number of connections in dynamic pool.<br>
50      * logFileString: Absolute path name for log file. e.g. 'c:/temp/mylog.log' <br>
51      * maxConnTime: Time in days between connection resets. (Reset does a basic cleanup)<br>
52      * logAppend: Append to logfile (optional)<br>
53      * maxCheckoutSeconds: Max time a connection can be checked out before being recycled. Zero value turns option off, default is 60 seconds.
54      * debugLevel: Level of debug messages output to the log file. 0 -> no messages, 1 -> Errors, 2 -> Warnings, 3 -> Information
55      */

56     public DbConnectionBroker(String JavaDoc dbDriver, String JavaDoc dbServer, String JavaDoc dbLogin,
57         String JavaDoc dbPassword, int minConns, int maxConns,
58             String JavaDoc logFileString, double maxConnTime) throws IOException {
59     
60     setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns,
61             maxConns, logFileString, maxConnTime, false,
62             DEFAULTMAXCHECKOUTSECONDS, DEFAULTDEBUGLEVEL);
63     }
64
65     /*
66      * Special constructor to handle logfile append
67      */

68     public DbConnectionBroker(String JavaDoc dbDriver, String JavaDoc dbServer, String JavaDoc dbLogin,
69         String JavaDoc dbPassword, int minConns, int maxConns,
70             String JavaDoc logFileString, double maxConnTime, boolean logAppend)
71     throws IOException {
72     
73     setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns,
74             maxConns, logFileString, maxConnTime, logAppend,
75             DEFAULTMAXCHECKOUTSECONDS, DEFAULTDEBUGLEVEL);
76     }
77
78     /*
79      * Special constructor to handle connection checkout expiration
80      */

81     public DbConnectionBroker(String JavaDoc dbDriver, String JavaDoc dbServer, String JavaDoc dbLogin,
82         String JavaDoc dbPassword, int minConns, int maxConns,
83             String JavaDoc logFileString, double maxConnTime, boolean logAppend,
84                   int maxCheckoutSeconds, int debugLevel)
85     throws IOException {
86     
87     setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns,
88             maxConns, logFileString, maxConnTime, logAppend,
89             maxCheckoutSeconds, debugLevel);
90     }
91
92
93
94     private void setupBroker(String JavaDoc dbDriver, String JavaDoc dbServer, String JavaDoc dbLogin,
95         String JavaDoc dbPassword, int minConns, int maxConns,
96             String JavaDoc logFileString, double maxConnTime, boolean logAppend,
97                  int maxCheckoutSeconds, int debugLevel)
98     throws IOException {
99             
100         connPool = new Connection[maxConns];
101         connStatus = new int[maxConns];
102         connLockTime = new long[maxConns];
103         connCreateDate = new long[maxConns];
104         connID = new String JavaDoc[maxConns];
105         currConnections = minConns;
106         this.maxConns = maxConns;
107         this.dbDriver = dbDriver;
108         this.dbServer = dbServer;
109         this.dbLogin = dbLogin;
110         this.dbPassword = dbPassword;
111         this.logFileString = logFileString;
112     this.maxCheckoutSeconds = maxCheckoutSeconds;
113     this.debugLevel = debugLevel;
114         maxConnMSec = (int)(maxConnTime * 86400000.0); //86400 sec/day
115
if(maxConnMSec < 30000) { // Recycle no less than 30 seconds.
116
maxConnMSec = 30000;
117         }
118         
119
120         try {
121         log = new PrintWriter(new FileOutputStream(logFileString,
122                                logAppend),true);
123         
124         // Can't open the requested file. Open the default file.
125
} catch (IOException e1) {
126         try {
127         log = new PrintWriter(new FileOutputStream("DCB_" +
128                        System.currentTimeMillis() + ".log",
129                        logAppend),true);
130         
131         } catch (IOException e2) {
132         throw new IOException("Can't open any log file");
133         }
134         }
135     
136
137     
138     // Write the pid file (used to clean up dead/broken connection)
139
SimpleDateFormat formatter
140         = new SimpleDateFormat ("yyyy.MM.dd G 'at' hh:mm:ss a zzz");
141     Date JavaDoc nowc = new Date JavaDoc();
142     pid = formatter.format(nowc);
143     
144     BufferedWriter pidout = new BufferedWriter(new
145         FileWriter(logFileString + "pid"));
146     pidout.write(pid);
147     pidout.close();
148     
149         log.println("-----------------------------------------");
150         log.println("-----------------------------------------");
151         log.println("Starting DbConnectionBroker Version 1.0.13:");
152         log.println("dbDriver = " + dbDriver);
153         log.println("dbServer = " + dbServer);
154         log.println("dbLogin = " + dbLogin);
155         log.println("log file = " + logFileString);
156         log.println("minconnections = " + minConns);
157         log.println("maxconnections = " + maxConns);
158         log.println("Total refresh interval = " + maxConnTime + " days");
159         log.println("logAppend = " + logAppend);
160         log.println("maxCheckoutSeconds = " + maxCheckoutSeconds);
161     log.println("debugLevel = " + debugLevel);
162         log.println("-----------------------------------------");
163         
164         
165         // Initialize the pool of connections with the mininum connections:
166
// Problems creating connections may be caused during reboot when the
167
// servlet is started before the database is ready. Handle this
168
// by waiting and trying again. The loop allows 5 minutes for
169
// db reboot.
170
boolean connectionsSucceeded=false;
171         int dbLoop=20;
172         
173         try {
174             for(int i=1; i < dbLoop; i++) {
175                 try {
176                     for(int j=0; j < currConnections; j++) {
177                         createConn(j);
178                     }
179                     connectionsSucceeded=true;
180                     break;
181                 } catch (SQLException e){
182             if(debugLevel > 0) {
183             log.println("--->Attempt (" + String.valueOf(i) +
184                     " of " + String.valueOf(dbLoop) +
185                     ") failed to create new connections set at startup: ");
186             log.println(" " + e);
187             log.println(" Will try again in 15 seconds...");
188             }
189             try { Thread.sleep(15000); }
190                     catch(InterruptedException JavaDoc e1) {}
191                 }
192             }
193             if(!connectionsSucceeded) { // All attempts at connecting to db exhausted
194
if(debugLevel > 0) {
195             log.println("\r\nAll attempts at connecting to Database exhausted");
196         }
197                 throw new IOException();
198             }
199         } catch (Exception JavaDoc e) {
200             throw new IOException();
201         }
202         
203         // Fire up the background housekeeping thread
204

205         runner = new Thread JavaDoc(this);
206         runner.start();
207
208     }//End DbConnectionBroker()
209

210
211     /**
212      * Housekeeping thread. Runs in the background with low CPU overhead.
213      * Connections are checked for warnings and closure and are periodically
214      * restarted.
215      * This thread is a catchall for corrupted
216      * connections and prevents the buildup of open cursors. (Open cursors
217      * result when the application fails to close a Statement).
218      * This method acts as fault tolerance for bad connection/statement programming.
219      */

220     public void run() {
221         boolean forever = true;
222         Statement stmt=null;
223         String JavaDoc currCatalog=null;
224     long maxCheckoutMillis = maxCheckoutSeconds * 1000;
225
226
227         while(forever) {
228             
229         // Make sure the log file is the one this instance opened
230
// If not, clean it up!
231
try {
232         BufferedReader in = new BufferedReader(new
233                        FileReader(logFileString + "pid"));
234         String JavaDoc curr_pid = in.readLine();
235         if(curr_pid.equals(pid)) {
236             //log.println("They match = " + curr_pid);
237
} else {
238             //log.println("No match = " + curr_pid);
239
log.close();
240             
241             // Close all connections silently - they are definitely dead.
242
for(int i=0; i < currConnections; i++) {
243             try {
244                 connPool[i].close();
245             } catch (SQLException e1) {} // ignore
246
}
247             // Returning from the run() method kills the thread
248
return;
249         }
250         
251         in.close();
252         
253         } catch (IOException e1) {
254         log.println("Can't read the file for pid info: " +
255                 logFileString + "pid");
256         }
257         
258         
259         // Get any Warnings on connections and print to event file
260
for(int i=0; i < currConnections; i++) {
261         try {
262             currSQLWarning = connPool[i].getWarnings();
263             if(currSQLWarning != null) {
264             if(debugLevel > 1) {
265                 log.println("Warnings on connection " +
266                     String.valueOf(i) + " " + currSQLWarning);
267             }
268             connPool[i].clearWarnings();
269             }
270         } catch(SQLException e) {
271             if(debugLevel > 1) {
272             log.println("Cannot access Warnings: " + e);
273             }
274         }
275         
276         }
277             
278         for(int i=0; i < currConnections; i++) { // Do for each connection
279
long age = System.currentTimeMillis() - connCreateDate[i];
280         
281
282         try { // Test the connection with createStatement call
283
synchronized(connStatus) {
284             if(connStatus[i] > 0) { // In use, catch it next time!
285

286                 // Check the time it's been checked out and recycle
287
long timeInUse = System.currentTimeMillis() -
288                 connLockTime[i];
289                 if(debugLevel > 2) {
290                 log.println("Warning. Connection " + i +
291                         " in use for " + timeInUse +
292                         " ms");
293                 }
294                 if(maxCheckoutMillis != 0) {
295                 if(timeInUse > maxCheckoutMillis) {
296                     if(debugLevel > 1) {
297                     log.println("Warning. Connection " +
298                             i + " failed to be returned in time. Recycling...");
299                     }
300                     throw new SQLException();
301                 }
302                 }
303                 
304                 continue;
305             }
306             connStatus[i] = 2; // Take offline (2 indicates housekeeping lock)
307
}
308             
309             
310             if(age > maxConnMSec) { // Force a reset at the max conn time
311
throw new SQLException();
312             }
313             
314             stmt = connPool[i].createStatement();
315             connStatus[i] = 0; // Connection is O.K.
316
//log.println("Connection confirmed for conn = " +
317
// String.valueOf(i));
318

319             // Some DBs return an object even if DB is shut down
320
if(connPool[i].isClosed()) {
321             throw new SQLException();
322             }
323
324             
325             // Connection has a problem, restart it
326
} catch(SQLException e) {
327
328             if(debugLevel > 1) {
329             log.println(new Date JavaDoc().toString() +
330                     " ***** Recycling connection " +
331                     String.valueOf(i) + ":");
332             }
333             
334             try {
335             connPool[i].close();
336             } catch(SQLException e0) {
337             if(debugLevel > 0) {
338                 log.println("Error! Can't close connection! Might have been closed already. Trying to recycle anyway... (" + e0 + ")");
339             }
340             }
341
342             try {
343             createConn(i);
344             } catch(SQLException e1) {
345             if(debugLevel > 0) {
346                 log.println("Failed to create connection: " + e1);
347             }
348             connStatus[i] = 0; // Can't open, try again next time
349
}
350         } finally {
351             try{if(stmt != null) {stmt.close();}} catch(SQLException e1){};
352         }
353         
354         }
355         
356         try { Thread.sleep(20000); } // Wait 20 seconds for next cycle
357

358         catch(InterruptedException JavaDoc e) {
359         // Returning from the run method sets the internal
360
// flag referenced by Thread.isAlive() to false.
361
// This is required because we don't use stop() to
362
// shutdown this thread.
363
return;
364         }
365         
366         }
367         
368     } // End run
369

370     /**
371      * This method hands out the connections in round-robin order.
372      * This prevents a faulty connection from locking
373      * up an application entirely. A browser 'refresh' will
374      * get the next connection while the faulty
375      * connection is cleaned up by the housekeeping thread.
376      *
377      * If the min number of threads are ever exhausted, new
378      * threads are added up the the max thread count.
379      * Finally, if all threads are in use, this method waits
380      * 2 seconds and tries again, up to ten times. After that, it
381      * returns a null.
382      */

383     public Connection getConnection() {
384     
385         Connection conn=null;
386
387         if(available){
388             boolean gotOne = false;
389             
390             for(int outerloop=1; outerloop<=10; outerloop++) {
391             
392                 try {
393                     int loop=0;
394                     int roundRobin = connLast + 1;
395                     if(roundRobin >= currConnections) roundRobin=0;
396                     
397                     do {
398                          synchronized(connStatus) {
399                             if((connStatus[roundRobin] < 1) &&
400                    (! connPool[roundRobin].isClosed())) {
401                                     conn = connPool[roundRobin];
402                                     connStatus[roundRobin]=1;
403                                     connLockTime[roundRobin] =
404                     System.currentTimeMillis();
405                                     connLast = roundRobin;
406                                     gotOne = true;
407                                     break;
408                             } else {
409                                 loop++;
410                                 roundRobin++;
411                                 if(roundRobin >= currConnections) roundRobin=0;
412                             }
413                          }
414                     }
415                     while((gotOne==false)&&(loop < currConnections));
416                     
417                 }
418                 catch (SQLException e1) {
419             log.println("Error: " + e1);
420         }
421             
422                 if(gotOne) {
423                     break;
424                 } else {
425                     synchronized(this) { // Add new connections to the pool
426
if(currConnections < maxConns) {
427                             
428                             try {
429                                 createConn(currConnections);
430                 currConnections++;
431                             } catch(SQLException e) {
432                 if(debugLevel > 0) {
433                     log.println("Error: Unable to create new connection: " + e);
434                 }
435                             }
436                         }
437                     }
438                     
439                     try { Thread.sleep(2000); }
440                     catch(InterruptedException JavaDoc e) {}
441             if(debugLevel > 0) {
442             log.println("-----> Connections Exhausted! Will wait and try again in loop " +
443                     String.valueOf(outerloop));
444             }
445                 }
446                 
447             } // End of try 10 times loop
448

449         } else {
450         if(debugLevel > 0) {
451         log.println("Unsuccessful getConnection() request during destroy()");
452         }
453         } // End if(available)
454

455     if(debugLevel > 2) {
456         log.println("Handing out connection " +
457             idOfConnection(conn) + " --> " +
458             (new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a")).format(new java.util.Date JavaDoc()));
459     }
460
461         return conn;
462              
463     }
464     
465     /**
466      * Returns the local JDBC ID for a connection.
467      */

468     public int idOfConnection(Connection conn) {
469         int match;
470         String JavaDoc tag;
471         
472         try {
473             tag = conn.toString();
474         }
475         catch (NullPointerException JavaDoc e1) {
476             tag = "none";
477         }
478         
479         match=-1;
480         
481         for(int i=0; i< currConnections; i++) {
482             if(connID[i].equals(tag)) {
483                 match = i;
484                 break;
485             }
486         }
487         return match;
488     }
489     
490     /**
491      * Frees a connection. Replaces connection back into the main pool for
492      * reuse.
493      */

494     public String JavaDoc freeConnection(Connection conn) {
495         String JavaDoc res="";
496         
497         int thisconn = idOfConnection(conn);
498         if(thisconn >= 0) {
499             connStatus[thisconn]=0;
500             res = "freed " + conn.toString();
501             //log.println("Freed connection " + String.valueOf(thisconn) +
502
// " normal exit: ");
503
} else {
504         if(debugLevel > 0) {
505         log.println("----> Error: Could not free connection!!!");
506         }
507         }
508         
509         return res;
510          
511     }
512     
513     /**
514      * Returns the age of a connection -- the time since it was handed out to
515      * an application.
516      */

517     public long getAge(Connection conn) { // Returns the age of the connection in millisec.
518
int thisconn = idOfConnection(conn);
519         return System.currentTimeMillis() - connLockTime[thisconn];
520     }
521
522     private void createConn(int i)
523
524         throws SQLException {
525
526         Date JavaDoc now = new Date JavaDoc();
527         
528         try {
529             Class.forName (dbDriver);
530         
531             connPool[i] = DriverManager.getConnection
532                           (dbServer,dbLogin,dbPassword);
533                            
534             connStatus[i]=0;
535             connID[i]=connPool[i].toString();
536             connLockTime[i]=0;
537             connCreateDate[i] = now.getTime();
538         } catch (ClassNotFoundException JavaDoc e2) {
539         if(debugLevel > 0) {
540         log.println("Error creating connection: " + e2);
541         }
542     }
543                 
544         log.println(now.toString() + " Opening connection " + String.valueOf(i) +
545                     " " + connPool[i].toString() + ":");
546     }
547     
548     /**
549      * Shuts down the housekeeping thread and closes all connections
550      * in the pool. Call this method from the destroy() method of the servlet.
551      */

552
553     /**
554      * Multi-phase shutdown. having following sequence:
555      * <OL>
556      * <LI><code>getConnection()</code> will refuse to return connections.
557      * <LI>The housekeeping thread is shut down.<br>
558      * Up to the time of <code>millis</code> milliseconds after shutdown of
559      * the housekeeping thread, <code>freeConnection()</code> can still be
560      * called to return used connections.
561      * <LI>After <code>millis</code> milliseconds after the shutdown of the
562      * housekeeping thread, all connections in the pool are closed.
563      * <LI>If any connections were in use while being closed then a
564      * <code>SQLException</code> is thrown.
565      * <LI>The log is closed.
566      * </OL><br>
567      * Call this method from a servlet destroy() method.
568      *
569      * @param millis the time to wait in milliseconds.
570      * @exception SQLException if connections were in use after
571      * <code>millis</code>.
572      */

573     public void destroy(int millis) throws SQLException {
574     
575         // Checking for invalid negative arguments is not necessary,
576
// Thread.join() does this already in runner.join().
577

578         // Stop issuing connections
579
available=false;
580
581         // Shut down the background housekeeping thread
582
runner.interrupt();
583
584         // Wait until the housekeeping thread has died.
585
try { runner.join(millis); }
586         catch(InterruptedException JavaDoc e){} // ignore
587

588         // The housekeeping thread could still be running
589
// (e.g. if millis is too small). This case is ignored.
590
// At worst, this method will throw an exception with the
591
// clear indication that the timeout was too short.
592

593         long startTime=System.currentTimeMillis();
594
595         // Wait for freeConnection() to return any connections
596
// that are still used at this time.
597
int useCount;
598         while((useCount=getUseCount())>0 && System.currentTimeMillis() - startTime <= millis) {
599             try { Thread.sleep(500); }
600             catch(InterruptedException JavaDoc e) {} // ignore
601
}
602
603         // Close all connections, whether safe or not
604
for(int i=0; i < currConnections; i++) {
605             try {
606                 connPool[i].close();
607             } catch (SQLException e1) {
608         if(debugLevel > 0) {
609             log.println("Cannot close connections on Destroy");
610         }
611             }
612         }
613
614         if(useCount > 0) {
615             //bt-test successful
616
String JavaDoc msg="Unsafe shutdown: Had to close "+useCount+
617         " active DB connections after "+millis+"ms";
618             log.println(msg);
619             // Close all open files
620
log.close();
621             // Throwing following Exception is essential because servlet authors
622
// are likely to have their own error logging requirements.
623
throw new SQLException(msg);
624         }
625
626         // Close all open files
627
log.close();
628
629     }//End destroy()
630

631
632     /**
633      * Less safe shutdown. Uses default timeout value.
634      * This method simply calls the <code>destroy()</code> method
635      * with a <code>millis</code>
636      * value of 10000 (10 seconds) and ignores <code>SQLException</code>
637      * thrown by that method.
638      * @see #destroy(int)
639      */

640     public void destroy() {
641         try {
642             destroy(10000);
643         }
644         catch(SQLException e) {}
645     }
646
647
648
649     /**
650      * Returns the number of connections in use.
651      */

652     // This method could be reduced to return a counter that is
653
// maintained by all methods that update connStatus.
654
// However, it is more efficient to do it this way because:
655
// Updating the counter would put an additional burden on the most
656
// frequently used methods; in comparison, this method is
657
// rarely used (although essential).
658
public int getUseCount() {
659         int useCount=0;
660         synchronized(connStatus) {
661             for(int i=0; i < currConnections; i++) {
662                 if(connStatus[i] > 0) { // In use
663
useCount++;
664                 }
665             }
666         }
667         return useCount;
668     }//End getUseCount()
669

670     /**
671      * Returns the number of connections in the dynamic pool.
672      */

673     public int getSize() {
674         return currConnections;
675     }//End getSize()
676

677 }// End class
678

679
680
Popular Tags