1 6 package com.javaexchange.dbConnectionBroker; 7 import java.io.*; 8 import java.sql.*; 9 import java.text.*; 10 import java.util.Date ; 11 12 13 20 public class DbConnectionBroker implements Runnable { 21 private Thread runner; 22 23 private Connection[] connPool; 24 private int[] connStatus; 25 26 private long[] connLockTime, connCreateDate; 27 private String [] connID; 28 private String dbDriver, dbServer, dbLogin, dbPassword, logFileString; 29 private int currConnections, connLast, minConns, maxConns, maxConnMSec, 30 maxCheckoutSeconds, debugLevel; 31 32 private boolean available=true; 34 35 private PrintWriter log; 36 private SQLWarning currSQLWarning; 37 private String pid; 38 39 private final int DEFAULTMAXCHECKOUTSECONDS=60; 40 private final int DEFAULTDEBUGLEVEL=2; 41 42 56 public DbConnectionBroker(String dbDriver, String dbServer, String dbLogin, 57 String dbPassword, int minConns, int maxConns, 58 String logFileString, double maxConnTime) throws IOException { 59 60 setupBroker(dbDriver, dbServer, dbLogin, dbPassword, minConns, 61 maxConns, logFileString, maxConnTime, false, 62 DEFAULTMAXCHECKOUTSECONDS, DEFAULTDEBUGLEVEL); 63 } 64 65 68 public DbConnectionBroker(String dbDriver, String dbServer, String dbLogin, 69 String dbPassword, int minConns, int maxConns, 70 String 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 81 public DbConnectionBroker(String dbDriver, String dbServer, String dbLogin, 82 String dbPassword, int minConns, int maxConns, 83 String 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 dbDriver, String dbServer, String dbLogin, 95 String dbPassword, int minConns, int maxConns, 96 String 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 [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); if(maxConnMSec < 30000) { maxConnMSec = 30000; 117 } 118 119 120 try { 121 log = new PrintWriter(new FileOutputStream(logFileString, 122 logAppend),true); 123 124 } 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 SimpleDateFormat formatter 140 = new SimpleDateFormat ("yyyy.MM.dd G 'at' hh:mm:ss a zzz"); 141 Date nowc = new Date (); 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 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 e1) {} 191 } 192 } 193 if(!connectionsSucceeded) { if(debugLevel > 0) { 195 log.println("\r\nAll attempts at connecting to Database exhausted"); 196 } 197 throw new IOException(); 198 } 199 } catch (Exception e) { 200 throw new IOException(); 201 } 202 203 205 runner = new Thread (this); 206 runner.start(); 207 208 } 210 211 220 public void run() { 221 boolean forever = true; 222 Statement stmt=null; 223 String currCatalog=null; 224 long maxCheckoutMillis = maxCheckoutSeconds * 1000; 225 226 227 while(forever) { 228 229 try { 232 BufferedReader in = new BufferedReader(new 233 FileReader(logFileString + "pid")); 234 String curr_pid = in.readLine(); 235 if(curr_pid.equals(pid)) { 236 } else { 238 log.close(); 240 241 for(int i=0; i < currConnections; i++) { 243 try { 244 connPool[i].close(); 245 } catch (SQLException e1) {} } 247 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 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++) { long age = System.currentTimeMillis() - connCreateDate[i]; 280 281 282 try { synchronized(connStatus) { 284 if(connStatus[i] > 0) { 286 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; } 308 309 310 if(age > maxConnMSec) { throw new SQLException(); 312 } 313 314 stmt = connPool[i].createStatement(); 315 connStatus[i] = 0; 319 if(connPool[i].isClosed()) { 321 throw new SQLException(); 322 } 323 324 325 } catch(SQLException e) { 327 328 if(debugLevel > 1) { 329 log.println(new Date ().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; } 350 } finally { 351 try{if(stmt != null) {stmt.close();}} catch(SQLException e1){}; 352 } 353 354 } 355 356 try { Thread.sleep(20000); } 358 catch(InterruptedException e) { 359 return; 364 } 365 366 } 367 368 } 370 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) { 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 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 } 449 } else { 450 if(debugLevel > 0) { 451 log.println("Unsuccessful getConnection() request during destroy()"); 452 } 453 } 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 ())); 459 } 460 461 return conn; 462 463 } 464 465 468 public int idOfConnection(Connection conn) { 469 int match; 470 String tag; 471 472 try { 473 tag = conn.toString(); 474 } 475 catch (NullPointerException 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 494 public String freeConnection(Connection conn) { 495 String res=""; 496 497 int thisconn = idOfConnection(conn); 498 if(thisconn >= 0) { 499 connStatus[thisconn]=0; 500 res = "freed " + conn.toString(); 501 } else { 504 if(debugLevel > 0) { 505 log.println("----> Error: Could not free connection!!!"); 506 } 507 } 508 509 return res; 510 511 } 512 513 517 public long getAge(Connection conn) { 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 now = new Date (); 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 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 552 553 573 public void destroy(int millis) throws SQLException { 574 575 578 available=false; 580 581 runner.interrupt(); 583 584 try { runner.join(millis); } 586 catch(InterruptedException e){} 588 593 long startTime=System.currentTimeMillis(); 594 595 int useCount; 598 while((useCount=getUseCount())>0 && System.currentTimeMillis() - startTime <= millis) { 599 try { Thread.sleep(500); } 600 catch(InterruptedException e) {} } 602 603 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 String msg="Unsafe shutdown: Had to close "+useCount+ 617 " active DB connections after "+millis+"ms"; 618 log.println(msg); 619 log.close(); 621 throw new SQLException(msg); 624 } 625 626 log.close(); 628 629 } 631 632 640 public void destroy() { 641 try { 642 destroy(10000); 643 } 644 catch(SQLException e) {} 645 } 646 647 648 649 652 public int getUseCount() { 659 int useCount=0; 660 synchronized(connStatus) { 661 for(int i=0; i < currConnections; i++) { 662 if(connStatus[i] > 0) { useCount++; 664 } 665 } 666 } 667 return useCount; 668 } 670 673 public int getSize() { 674 return currConnections; 675 } 677 } 679 680 | Popular Tags |