1 /*
2 * Copyright (c) 1998 by Gefion software.
3 *
4 * Permission to use, copy, and distribute this software for
5 * NON-COMMERCIAL purposes and without fee is hereby granted
6 * provided that this copyright notice appears in all copies.
7 *
8 */
9
10 /*
11 * Heavily modified by Paneris
12 */
13
14 package org.paneris.jal.model;
15
16 import java.io.FileWriter;
17 import java.io.IOException;
18 import java.io.InputStream;
19 import java.io.PrintWriter;
20 import java.sql.Connection;
21 import java.sql.DatabaseMetaData;
22 import java.sql.Driver;
23 import java.sql.DriverManager;
24 import java.sql.SQLException;
25 import java.sql.Statement;
26 import java.util.Date;
27 import java.util.Enumeration;
28 import java.util.Hashtable;
29 import java.util.Properties;
30 import java.util.StringTokenizer;
31 import java.util.Vector;
32
33 /**
34 * This class is a Singleton that provides access to one or many
35 * connection pools for databases defined in a Property file. A client gets
36 * access to the single instance through the static getInstance()
37 * method and can then check-out and check-in connections from a pool.
38 * When the client shuts down it should call the release() method
39 * to close all open connections and do other clean up.
40 */
41 public class DBConnectionManager {
42
43 /** The single instance of this class */
44 private static DBConnectionManager instance;
45
46 /**
47 * Debug logging is also written to the log file if
48 * <code>true</code>.
49 * <p>
50 * This is also now configurable by setting the debug property
51 * to "true" or some other value.
52 */
53 static boolean debug = false;
54
55 private PrintWriter log;
56
57 /** The number of classes which are using the DBConnectionManager object */
58 private static int clients;
59
60 /** A list of JDBC drivers */
61 private Vector drivers = new Vector();
62
63 /** A list of connection pools */
64 private Hashtable pools = new Hashtable();
65
66 /** Just a string, really, but it's used by other classes to
67 find the upload directory */
68 private String uploaddir = "";
69
70 /** Constants representing different RDBMSs */
71 public static final int UNKNOWN = 0;
72 public static final int POSTGRES = 1;
73 public static final int ACCESS = 2;
74 public static final int MYSQL = 3;
75
76 // public int DatabaseEngineType = 0;
77
78
79 /**
80 * Returns the single instance, creating one if it's the
81 * first time this method is called.
82 *
83 * @return DBConnectionManager The single instance.
84 */
85 public static synchronized DBConnectionManager getInstance() {
86 if (instance == null) {
87 instance = new DBConnectionManager();
88 }
89 clients++;
90 return instance;
91 }
92
93 /**
94 * A private constructor since this is a Singleton
95 */
96 private DBConnectionManager() {
97 init();
98 }
99
100 /**
101 * Returns a connection to the named pool.
102 *
103 * @param name The pool name as defined in the properties file
104 * @param con The Connection
105 */
106 public void freeConnection(String name, Connection con) {
107 DBConnectionPool pool = (DBConnectionPool) pools.get(name);
108 if (pool != null) {
109 pool.freeConnection(con);
110 }
111 }
112
113 /**
114 * get the pool names
115 *
116 * @return Enumeration A list of names
117 */
118 public Hashtable getPools() {
119 return pools;
120 }
121
122 /**
123 * get the upload directory
124 */
125 public String getUploadDir() {
126 return uploaddir;
127 }
128
129 /**
130 * get the database name
131 *
132 * @param db The name of the database
133 * @return int A list of names
134 */
135 public int getDatabaseEngineType(String db) {
136 DBConnectionPool pool = (DBConnectionPool) pools.get(db);
137 if (pool != null) {
138 return pool.getDatabaseEngineType();
139 }
140 return UNKNOWN;
141 }
142 /*
143 public int getDatabaseEngineType(String db, String a) {
144 Connection conn = getConnection("DBConnectionManager",db);
145 int dbn = 0;
146 String dbname = "";
147 try {
148 DatabaseMetaData md = conn.getMetaData();
149 dbname = md.getDatabaseProductName();
150 } catch (SQLException e) {
151 log("Can't get Database metadata");
152 }
153 if (dbname.equals("postgresql") || dbname.equals("PostgreSQL")) {
154 dbn = POSTGRES;
155 } else if (dbname.equals("Access")) {
156 dbn = ACCESS;
157 } else if (dbname.equals("MySQL")) {
158 dbn = MYSQL;
159 } else {
160 dbn = UNKNOWN;
161 freeConnection(db,conn);
162 log("Unknown Database Engine Name: " + dbname);
163 }
164 freeConnection(db,conn);
165 return dbn;
166 }
167 */
168 /**
169 * get the database version
170 */
171 public String getDatabaseProductVersion(String db) throws Exception{
172 Connection conn = getConnection("DBConnectionManager",db);
173 DatabaseMetaData md = conn.getMetaData();
174 freeConnection(db,conn);
175 return md.getDatabaseProductVersion();
176 }
177
178 /**
179 * Returns an open connection. If no one is available, and the max
180 * number of connections has not been reached, a new connection is
181 * created.
182 *
183 * @param name The pool name as defined in the properties file
184 * @return Connection The connection or null
185 */
186 public Connection getConnection(String callingClass, String name) {
187 DBConnectionPool pool = (DBConnectionPool) pools.get(name);
188 if (pool != null) {
189 return pool.getConnection(callingClass);
190 }
191 return null;
192 }
193
194 /**
195 * Returns an open connection. If no one is available, and the max
196 * number of connections has not been reached, a new connection is
197 * created. If the max number has been reached, waits until one
198 * is available or the specified time has elapsed.
199 *
200 * @param name The pool name as defined in the properties file
201 * @param time The number of milliseconds to wait
202 * @return Connection The connection or null
203 */
204 public Connection getConnection(String callingClass, String name, long time) {
205 DBConnectionPool pool = (DBConnectionPool) pools.get(name);
206 if (pool != null) {
207 return pool.getConnection(callingClass, time);
208 }
209 return null;
210 }
211
212 /**
213 * Closes all open connections and deregisters all drivers.
214 */
215 public synchronized void release() {
216 // Wait until called by the last client
217 if (--clients != 0) {
218 return;
219 }
220
221 Enumeration allPools = pools.elements();
222 while (allPools.hasMoreElements()) {
223 DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
224 pool.release();
225 }
226 Enumeration allDrivers = drivers.elements();
227 while (allDrivers.hasMoreElements()) {
228 Driver driver = (Driver) allDrivers.nextElement();
229 try {
230 DriverManager.deregisterDriver(driver);
231 log("Deregistered JDBC driver " + driver.getClass().getName());
232 }
233 catch (SQLException e) {
234 log(e, "Can't deregister JDBC driver: " + driver.getClass().getName());
235 }
236 }
237 }
238
239 /**
240 * Creates instances of DBConnectionPool based on the properties.
241 * A DBConnectionPool can be defined with the following properties:
242 * <PRE>
243 * <poolname>.url The JDBC URL for the database
244 * <poolname>.user A database user (optional)
245 * <poolname>.password A database user password (if user specified)
246 * <poolname>.maxconn The maximal number of connections (optional)
247 * <poolname>.singleconn One connection per pool? (boolean, optional)
248 * </PRE>
249 *
250 * @param props The connection pool properties
251 */
252 private void createPools(Properties props) {
253 Enumeration propNames = props.propertyNames();
254 while (propNames.hasMoreElements()) {
255 String name = (String) propNames.nextElement();
256 if (name.endsWith(".url")) {
257 String poolName = name.substring(0, name.lastIndexOf("."));
258 String url = props.getProperty(poolName + ".url");
259 if (url == null) {
260 log("No URL specified for " + poolName);
261 continue;
262 }
263 String user = props.getProperty(poolName + ".user");
264 String password = props.getProperty(poolName + ".password");
265 String maxconn = props.getProperty(poolName + ".maxconn", "0");
266 String singleconn = props.getProperty(poolName + ".singleconn", "false");
267 String timezone = props.getProperty(poolName + ".timezone", "GMT");
268 log("timezone: " + timezone);
269 int max;
270 try {
271 max = Integer.valueOf(maxconn).intValue();
272 }
273 catch (NumberFormatException e) {
274 log("Invalid maxconn value " + maxconn + " for " + poolName);
275 max = 0;
276 }
277 DBConnectionPool pool =
278 new DBConnectionPool(poolName, url, user, password, max,
279 (new Boolean(singleconn)).booleanValue(),
280 timezone);
281 pools.put(poolName, pool);
282 log("Initialized pool " + poolName);
283 }
284 }
285 }
286
287 private static String propertiesName = "/projects.properties";
288
289 /**
290 * Loads properties and initializes the instance with its values.
291 */
292 private void init() {
293 InputStream is = getClass().getResourceAsStream(propertiesName);
294 Properties dbProps = new Properties();
295 try {
296 dbProps.load(is);
297 }
298 catch (Exception e) {
299 System.err.println("Can't read the properties file. " +
300 "Make sure " + propertiesName +
301 " is in the CLASSPATH. Error: " + e.toString());
302 return;
303 }
304 String logFile = dbProps.getProperty("logfile",
305 "DBConnectionManager.log");
306 uploaddir = dbProps.getProperty("uploaddir","./");
307 debug = "true".equals(dbProps.getProperty("debug", "false"));
308 try {
309 log = new PrintWriter(new FileWriter(logFile, true), true);
310 }
311 catch (IOException e) {
312 System.err.println("Can't open the log file: "
313 + logFile + ": " + e);
314 log = new PrintWriter(System.err);
315
316 }
317 loadDrivers(dbProps);
318 createPools(dbProps);
319 }
320
321 /**
322 * Loads and registers all JDBC drivers. This is done by the
323 * DBConnectionManager, as opposed to the DBConnectionPool,
324 * since many pools may share the same driver.
325 *
326 * @param props The connection pool properties
327 */
328 private void loadDrivers(Properties props) {
329 String driverClasses = props.getProperty("drivers");
330 StringTokenizer st = new StringTokenizer(driverClasses);
331 while (st.hasMoreElements()) {
332 String driverClassName = st.nextToken().trim();
333 try {
334 Driver driver = (Driver)
335 Class.forName(driverClassName).newInstance();
336 DriverManager.registerDriver(driver);
337 drivers.addElement(driver);
338 log("Registered JDBC driver " + driverClassName);
339 }
340 catch (Exception e) {
341 log("Can't register JDBC driver: " +
342 driverClassName + ", Exception: " + e);
343 }
344 }
345 }
346
347 /**
348 * Writes a message to the log file.
349 */
350 private void log(String msg) {
351 log.println(new Date() + ": " + msg);
352 }
353
354 /**
355 * Writes a message with an Exception to the log file.
356 */
357 private void log(Throwable e, String msg) {
358 log.println(new Date() + ": " + msg);
359 e.printStackTrace(log);
360 }
361
362 /**
363 * This inner class represents a connection pool. It creates new
364 * connections on demand, up to a max number if specified, unless
365 * singleconn is true
366 * It also makes sure a connection is still open before it is
367 * returned to a client.
368 */
369 class DBConnectionPool {
370 private int checkedOut;
371 private Vector freeConnections = new Vector();
372 private Hashtable usedConnections = new Hashtable();
373 private int maxConn;
374 boolean singleConn;
375 private String name;
376 private String password;
377 private String URL;
378 private String user;
379 private String timeZone;
380 private int db;
381
382 /**
383 * Creates new connection pool.
384 *
385 * @param name The pool name
386 * @param url The JDBC URL for the database
387 * @param user The database user, or null
388 * @param password The database user password, or null
389 * @param maxConn The maximal number of connections, or 0
390 * for no limit
391 * @param singleConn Whether the same 1 connection should be
392 * returned for each request for a connection
393 */
394 public DBConnectionPool(String name, String url, String user,
395 String password, int maxConn,
396 boolean singleConn, String timezone) {
397 this.name = name;
398 this.URL = url;
399 this.user = user;
400 this.password = password;
401 this.maxConn = maxConn;
402 this.singleConn = singleConn;
403 this.timeZone = timezone;
404 db = getDatabaseEngineType();
405 // need to set the timezone manually
406 //as it doesn't get set for the 1st connection
407 Connection conn = getConnection("DBConnectionPool");
408 // if db mentioned in project.properties AND exits on this machine
409 if (conn != null) {
410 setTimezone(conn);
411 freeConnection(conn);
412 }
413 }
414
415 /**
416 * get the database name
417 *
418 * @param db The name of the database
419 * @return int representing the name of the engine
420 */
421 public int getDatabaseEngineType() {
422 int dbn = 0;
423 Connection conn = getConnection("DBConnectionPool");
424 if (conn != null) {
425 String dbname = "";
426 try {
427 DatabaseMetaData md = conn.getMetaData();
428 dbname = md.getDatabaseProductName();
429 } catch (SQLException e) {
430 log("Can't get Database metadata");
431 }
432 if (dbname.equals("postgresql") || dbname.equals("PostgreSQL")) {
433 dbn = DBConnectionManager.POSTGRES;
434 } else if (dbname.equals("Access")) {
435 dbn = DBConnectionManager.ACCESS;
436 } else if (dbname.equals("MySQL")) {
437 dbn = DBConnectionManager.MYSQL;
438 } else {
439 dbn = DBConnectionManager.UNKNOWN;
440 log("Unknown Database Engine Name: " + name);
441 }
442 freeConnection(conn);
443 }
444 return dbn;
445 }
446
447
448 /**
449 * Checks in a connection to the pool. Notify other Threads that
450 * may be waiting for a connection.
451 *
452 * @param con The connection to check in
453 */
454 public synchronized void freeConnection(Connection con) {
455
456 // If we are using a single connection, we only
457 // keep track of our one free connection and don't
458 // bother tracking used connections
459 if (!singleConn) {
460
461 // Put the connection at the end of the Vector
462 if (usedConnections.remove(con) != null) {
463 freeConnections.addElement(con);
464 checkedOut--;
465 if (debug) {
466 log("freeConnection: " +
467 (String) usedConnections.get(con));
468 String l = name + ": " + usedConnections.size() +
469 " cons in use. " +
470 freeConnections.size() + " free.";
471 if (!usedConnections.isEmpty()) {
472 l += " used by: ";
473 Enumeration usedCon = usedConnections.elements();
474 while (usedCon.hasMoreElements()) {
475 l += (String) usedCon.nextElement() + ", ";
476 }
477 }
478 log (l);
479 }
480 }
481 notifyAll();
482 }
483 }
484
485 /**
486 * Checks out a connection from the pool. If no free connection
487 * is available, a new connection is created unless the max
488 * number of connections has been reached. If a free connection
489 * has been closed by the database, it's removed from the pool
490 * and this method is called again recursively.
491 */
492 public synchronized Connection getConnection(String callingClass) {
493 Connection con = null;
494 if (freeConnections.size() > 0) {
495
496 // Pick the first Connection in the Vector
497 // to get round-robin usage
498 con = (Connection) freeConnections.firstElement();
499 freeConnections.removeElementAt(0);
500
501 try {
502 if (con.isClosed()) {
503 log("Removed bad connection from " + name);
504 // Try again recursively
505 con = getConnection(callingClass);
506 }
507 }
508 catch (SQLException e) {
509 log("Removed bad connection from " + name);
510 // Try again recursively
511 con = getConnection(callingClass);
512 }
513
514 // If we have a single connection, put it straight
515 // back into the pool to be given out again (i.e we
516 // don't wait for it to be released)
517 if (singleConn)
518 freeConnections.addElement(con);
519 }
520 else if (maxConn == 0 || checkedOut < maxConn) {
521 con = newConnection(callingClass);
522 // need to set the timezone for new each connection
523 if (con != null) {
524 setTimezone(con);
525 // Put our new connection straight into the pool
526 // if its the only one
527 if (singleConn)
528 freeConnections.addElement(con);
529 }
530 }
531 if (con != null) {
532 // log("Got Connection: " + callingClass);
533 checkedOut++;
534 if (!singleConn)
535 usedConnections.put(con,callingClass);
536 }
537 return con;
538 }
539
540 /**
541 * Checks out a connection from the pool. If no free connection
542 * is available, a new connection is created unless the max
543 * number of connections has been reached. If a free connection
544 * has been closed by the database, it's removed from the pool
545 * and this method is called again recursively.
546 * <P>
547 * If no connection is available and the max number has been
548 * reached, this method waits the specified time for one to be
549 * checked in.
550 *
551 * @param timeout The timeout value in milliseconds
552 */
553 public synchronized
554 Connection getConnection(String callingClass, long timeout) {
555 long startTime = new Date().getTime();
556 Connection con;
557 while ((con = getConnection(callingClass)) == null) {
558 try {
559 wait(timeout);
560 }
561 catch (InterruptedException e) {
562 ; // Ignore exception
563 }
564 if ((new Date().getTime() - startTime) >= timeout) {
565 // Timeout has expired
566 return null;
567 }
568 }
569 return con;
570 }
571
572 /**
573 * Closes all available connections.
574 */
575 public synchronized void release() {
576 Enumeration allConnections = freeConnections.elements();
577 while (allConnections.hasMoreElements()) {
578 Connection con = (Connection) allConnections.nextElement();
579 try {
580 con.close();
581 log("Closed connection for pool " + name);
582 }
583 catch (SQLException e) {
584 log(e, "Can't close connection for pool " + name);
585 }
586 }
587 freeConnections.removeAllElements();
588 usedConnections.clear();
589 }
590
591 /**
592 * Creates a new connection, using a userid and password
593 * if specified.
594 */
595 private Connection newConnection(String callingClass) {
596 Connection con = null;
597 try {
598 if (user == null)
599 con = DriverManager.getConnection(URL);
600 else
601 con = DriverManager.getConnection(URL, user, password);
602 if (debug) {
603 // += in what follows produces a new string
604 String m = "Created a new connection";
605 if (user != null) {
606 m += " as " + user;
607 }
608 if (password != null) {
609 m += " with password \"" + password + '"';
610 }
611 m += " in pool " + name + " for class " +
612 callingClass + ". Now have " + (checkedOut+1) +
613 " connections checked out.";
614 log(m);
615 }
616 }
617 catch (SQLException e) {
618 log(e, "Can't create a new connection for " + URL);
619 return null;
620 }
621 return con;
622 }
623
624 /**
625 * sets the timezone for this connection (at present only for postgres)
626 *
627 * @param con The connection to check in
628 */
629 public void setTimezone(Connection conn) {
630 if (debug) {
631 log("setting timezone " + db + " " +
632 DBConnectionManager.POSTGRES);
633 }
634 if (db == DBConnectionManager.POSTGRES) {
635 try {
636 Statement s = conn.createStatement();
637 String set = "set timezone='" + timeZone + "'";
638 s.execute(set);
639 } catch (SQLException e) {
640 log(e, "Can't set the timezone: " + timeZone);
641 }
642 }
643 }
644
645 }
646
647
648 //
649 // ===========
650 // Utilities
651 // ===========
652 //
653
654 /**
655 * Database-dependent SQL operator to uppercase a value
656 *
657 * @param db The name of the database
658 * @return Connection The connection or null
659 */
660 public String sqlUppercaseOperator(String db) throws Exception{
661 if (getDatabaseEngineType(db) == ACCESS) {
662 return "UCASE" ;
663 } else {
664 return "UPPER";
665 }
666 }
667
668 /**
669 * Database-dependent SQL operator to insert today's date
670 */
671 public String sqlToday(String db) throws Exception{
672 if (getDatabaseEngineType(db) == ACCESS) {
673 return "today()" ;
674 } else {
675 return "now()";
676 }
677 }
678
679 /**
680 * Database-dependent representation of a 'true' boolean value
681 */
682 public String sqlTrue(String db) {
683 try {
684 if ( getDatabaseEngineType(db) == MYSQL) {
685 return "'t'" ;
686 } else {
687 return "true";
688 }
689 } catch (Exception e) {
690 log("sqlTrue failed");
691 return "true";
692 }
693 }
694
695 /**
696 * Database-dependent representation of a 'false' boolean value
697 */
698 public String sqlFalse(String db) {
699 try {
700 if ( getDatabaseEngineType(db) == MYSQL) {
701 return "'f'" ;
702 } else {
703 return "false";
704 }
705 } catch (Exception e) {
706 log("sqlFalse failed");
707 return "false";
708 }
709 }
710
711 }