View Javadoc

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      * &lt;poolname&gt;.url          The JDBC URL for the database
244      * &lt;poolname&gt;.user         A database user (optional)
245      * &lt;poolname&gt;.password     A database user password (if user specified)
246      * &lt;poolname&gt;.maxconn      The maximal number of connections (optional)
247      * &lt;poolname&gt;.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 }