Java > Open Source Codes > com > methodhead > persistable > ConnectionSingleton


1 /*
2  * Copyright (C) 2006 Methodhead Software LLC. All rights reserved.
3  *
4  * This file is part of TransferCM.
5  *
6  * TransferCM is free software; you can redistribute it and/or modify it under the
7  * terms of the GNU General Public License as published by the Free Software
8  * Foundation; either version 2 of the License, or (at your option) any later
9  * version.
10  *
11  * TransferCM is distributed in the hope that it will be useful, but WITHOUT ANY
12  * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
13  * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
14  * details.
15  *
16  * You should have received a copy of the GNU General Public License along with
17  * TransferCM; if not, write to the Free Software Foundation, Inc., 51 Franklin St,
18  * Fifth Floor, Boston, MA 02110-1301 USA
19  */
20
21 package com.methodhead.persistable;
22
23 import java.io.IOException ;
24 import java.io.LineNumberReader ;
25 import java.io.Reader ;
26
27 import java.sql.Connection ;
28 import java.sql.ResultSet ;
29 import java.sql.SQLException ;
30 import java.sql.Statement ;
31
32 import java.util.HashMap ;
33 import java.util.Map ;
34 import java.util.Properties ;
35
36 import org.apache.commons.dbcp.AbandonedObjectPool;
37 import org.apache.commons.dbcp.AbandonedConfig;
38 import org.apache.commons.dbcp.ConnectionFactory;
39 import org.apache.commons.dbcp.DriverManagerConnectionFactory;
40 import org.apache.commons.dbcp.PoolableConnectionFactory;
41 import org.apache.commons.dbcp.PoolingDataSource;
42 import org.apache.commons.lang.exception.ExceptionUtils;
43
44 import org.apache.commons.pool.ObjectPool;
45
46 import org.apache.commons.pool.impl.GenericObjectPool;
47
48 import org.apache.log4j.Logger;
49
50 /**
51  * <p>
52  * A singleton that provides JDBC connections using the <a
53  * HREF="http://jakarta.apache.org/commons/dbcp/">Jakarta Commons DBCP
54  * component</a>. Use {@link
55  * com.methodhead.persistable.ConnectionSingleton#init(java.lang.String,java.util.Properties)
56  * init()} to initialize the singleton. The following properties are
57  * expected:
58  * </p>
59  * <ul>
60  * <li><b>driver</b>: The JDBC driver.</li>
61  * <li><b>uri</b>: The database URI.</li>
62  * <li><b>user</b>: The database login.</li>
63  * <li><b>password</b>: The database password.</li>
64  * <li><b>validQuery</b> (optional): The query used to verify the health of a connection (defaults to <tt>SELECT 1</tt>).</li>
65  * <li><b>evictionPeriodMillis</b> (optional): The time between connection health tests (defaults to <tt>3600000</tt>, 1 hour).</li>
66  * <li><b>maxActive</b> (optional): The maximum number of connections (defaults to <tt>8</tt>).</li>
67  * <li><b>maxWaitMillis</b> (optional): The amount of time spent waiting for a free connection when all connections in use (defaults to <tt>2000</tt>).</li>
68  * </ul>
69  * <p>
70  * Use {@link
71  * com.methodhead.persistable.ConnectionSingleton#getConnection(java.lang.String) getConnection()} anytime after that to
72  * get a connection. In addition, a few convenience methods, {@link
73  * com.methodhead.persistable.ConnectionSingleton#runQuery runQuery()},
74  * {@link com.methodhead.persistable.ConnectionSingleton#runUpdate
75  * runUpdate()}, and {@link
76  * com.methodhead.persistable.ConnectionSingleton#runBatchUpdate
77  * runBatchUpdate()} are available to run queries using the singleton.
78  * </p>
79  * <p>
80  * All of these methods accept a <tt>name</tt> parameter, which enables the
81  * creation of named connection pools. This is useful if you want to
82  * connect to several databases in the same application. Specify
83  * <tt>null</tt> for <tt>name</tt>, or use the methods that don't include the
84  * <tt>name</tt> parameter to use the default connection pool.
85  * </p>
86  * <p>
87  * <strong>Important:</strong> be sure to close connections obtained from
88  * this singleton after you use them as this is how they are returned to
89  * the pool; this includes connections associated with result sets
90  * returned by <tt>runQuery()</tt>.
91  * </p>
92  */
93 public class ConnectionSingleton {
94
95   // constructors /////////////////////////////////////////////////////////////
96
97   private ConnectionSingleton() {
98   }
99
100   // constants ////////////////////////////////////////////////////////////////
101
102   public static final String DBTYPE_MYSQL = "MYSQL";
103   public static final String DBTYPE_PSQL = "PSQL";
104   public static final String DBTYPE_SQLSERVER = "SQLSERVER";
105
106   // classes //////////////////////////////////////////////////////////////////
107
108   /**
109    * Extends <tt>PoolingDataSource</tt> to keep track of the pool so that it
110    * may explicitly close the pool.
111    */
112   private static class CloseablePoolingDataSource
113   extends
114     PoolingDataSource {
115
116     /*
117      * Constructor.
118      */
119     public CloseablePoolingDataSource(
120       GenericObjectPool pool ) {
121
122       super( pool );
123       pool_ = pool;
124     }
125
126     /*
127      * Closes the pool associated with this data source.
128      */
129     public void close()
130     throws
131       Exception {
132       pool_.close();
133     }
134
135     private GenericObjectPool pool_ = null;
136   }
137
138   // methods //////////////////////////////////////////////////////////////////
139
140   /**
141    * Initializes the connection pool associated with the name <tt>name</tt>
142    * using the specified properties <tt>dbProps</tt>, returning <tt>true</tt>
143    * if the singleton was successfully initialized, or <tt>false</tt>
144    * otherwise. If a pool already exists for the specified name,
145    * <tt>false</tt> is returned (use {@link
146    * com.methodhead.persistable.ConnectionSingleton#release(java.lang.String)
147    * release()} to release the pool). The following properties are expected
148    * to be defined: <tt>driver</tt> (e.g, <tt>org.postgresql.Driver</tt>),
149    * <tt>uri</tt> (e.g., <tt>jdbc:postgresql:yourdatabase</tt>),
150    * <tt>user</tt>, and <tt>password</tt>. Leading and trailing whitespace is
151    * trimmed from property values.
152    */
153   public static boolean init(
154     String name, Properties dbProps ) {
155     try {
156       if ( name == null )
157         name = "";
158
159       Object dataSource = connections_.get( name );
160
161       if ( dataSource != null ) {
162         logger_.error( "Pool \"" + name + "\" is already initialized." );
163         return false;
164       }
165
166       //
167 // force default values if optional properties are not specified
168 //
169 if ( dbProps.getProperty( "validQuery" ) == null )
170         dbProps.setProperty( "validQuery", "SELECT 1" );
171       if ( dbProps.getProperty( "evictionPeriodMillis" ) == null )
172         dbProps.setProperty( "evictionPeriodMillis", "3600000" );
173       if ( dbProps.getProperty( "maxActive" ) == null )
174         dbProps.setProperty( "maxActive", "8" );
175       if ( dbProps.getProperty( "maxWaitMillis" ) == null )
176         dbProps.setProperty( "maxWaitMillis", "2000" );
177       if ( dbProps.getProperty( "logAbandoned" ) == null )
178         dbProps.setProperty( "logAbandoned", "false" );
179       if ( dbProps.getProperty( "abandonedTimeout" ) == null )
180         dbProps.setProperty( "abandonedTimeout", "300" );
181
182       //
183 // trim property values
184 //
185 dbProps.setProperty(
186         "driver", dbProps.getProperty( "driver" ).trim() );
187       dbProps.setProperty(
188         "uri", dbProps.getProperty( "uri" ).trim() );
189       dbProps.setProperty(
190         "user", dbProps.getProperty( "user" ).trim() );
191       dbProps.setProperty(
192         "password", dbProps.getProperty( "password" ).trim() );
193       dbProps.setProperty(
194         "validQuery", dbProps.getProperty( "validQuery" ).trim() );
195       dbProps.setProperty(
196         "evictionPeriodMillis",
197         dbProps.getProperty( "evictionPeriodMillis" ).trim() );
198       dbProps.setProperty(
199         "maxActive", dbProps.getProperty( "maxActive" ).trim() );
200       dbProps.setProperty(
201         "maxWaitMillis", dbProps.getProperty( "maxWaitMillis" ).trim() );
202       dbProps.setProperty(
203         "logAbandoned", dbProps.getProperty( "logAbandoned" ).trim() );
204       dbProps.setProperty(
205         "abandonedTimeout", dbProps.getProperty( "abandonedTimeout" ).trim() );
206
207       Class.forName( dbProps.getProperty( "driver" ).trim() );
208
209       //
210 // create the right connection pool
211 //
212 GenericObjectPool connectionPool = null;
213     
214       if ( "true".equals( dbProps.getProperty( "logAbandoned" ) ) ) {
215
216         AbandonedConfig abandonedConfig = new AbandonedConfig();
217         abandonedConfig.setLogAbandoned( true );
218         abandonedConfig.setRemoveAbandoned( true );
219         abandonedConfig.setRemoveAbandonedTimeout( Integer.parseInt( dbProps.getProperty( "abandonedTimeout" ) ) );
220
221         connectionPool = new AbandonedObjectPool( null, abandonedConfig );
222       }
223       else {
224         connectionPool = new GenericObjectPool( null );
225       }
226
227       //
228 // configure pool
229 //
230 connectionPool.setMaxActive( Integer.parseInt( dbProps.getProperty( "maxActive" ) ) );
231       connectionPool.setWhenExhaustedAction( GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
232       connectionPool.setMaxWait( Integer.parseInt( dbProps.getProperty( "maxWaitMillis" ) ) );
233       connectionPool.setTestWhileIdle( true );
234       connectionPool.setTimeBetweenEvictionRunsMillis( Integer.parseInt( dbProps.getProperty( "evictionPeriodMillis" ) ) );
235
236       DriverManagerConnectionFactory connectionFactory =
237         new DriverManagerConnectionFactory(
238           dbProps.getProperty( "uri" ), dbProps );
239
240       PoolableConnectionFactory poolableConnectionFactory =
241         new PoolableConnectionFactory(
242           connectionFactory, // factory
243 connectionPool, // pool
244 null, // factory for prepared statement pooling
245 dbProps.getProperty( "validQuery" ), // validation query
246 false, // default "read only" setting
247 true ); // default "auto commit" setting
248
249       dataSource = new CloseablePoolingDataSource(connectionPool);
250
251       //
252 // add the data source to the map
253 //
254 connections_.put( name, dataSource );
255
256       return true;
257     }
258     catch ( Exception e ) {
259       logger_.error(
260         "Unexpected exception while initing pool \"" + name + "\": " + e );
261       return false;
262     }
263   }
264
265   /**
266    * Initalizes the singleton's default connection pool.
267    * @see com.methodhead.persistable.ConnectionSingleton#init(java.lang.String,java.util.Properties) init()
268    */
269   public static boolean init(
270     Properties dbProps ) {
271     return init( null, dbProps );
272   }
273
274   /**
275    * Returns a connection from the connection pool named <tt>name</tt> An
276    * exception is thrown if that pool hasn't been intitalized yet or if it is
277    * not supplying connections (a likely cause of this is bad connection
278    * parameters). If <tt>name</tt> is <tt>null</tt> the default connection
279    * pool is assumed. <strong>Important:</strong> be sure to close the
280    * returned connection, as this is how the connection is returned to the
281    * pool.
282    */
283   public static Connection getConnection(
284     String name )
285   throws
286     SQLException {
287
288     if ( name == null )
289       name = "";
290
291     CloseablePoolingDataSource dataSource =
292       ( CloseablePoolingDataSource )connections_.get( name );
293
294     if ( dataSource == null )
295       throw new SQLException (
296         "Pool \"" + name + "\" has not been initialized yet." );
297
298     return dataSource.getConnection();
299   }
300
301   /**
302    * Returns a connection from the default connection pool.
303    * @see com.methodhead.persistable.ConnectionSingleton#getConnection(java.lang.String) getConnection()
304    */
305   public static Connection getConnection()
306   throws
307     SQLException {
308     return getConnection( null );
309   }
310
311   /**
312    * Releases the pool with the specified <tt>name</tt> throwing an exception
313    * if no such pool exists.
314    */
315   public static void release(
316     String name )
317   throws
318     SQLException {
319
320     if ( name == null )
321       name = "";
322
323     CloseablePoolingDataSource dataSource =
324       ( CloseablePoolingDataSource )connections_.get( name );
325
326     if ( dataSource == null )
327       throw new SQLException (
328         "Pool \"" + name + "\" has not been initialized yet." );
329     
330     try {
331       dataSource.close();
332     }
333     catch ( Exception e ) {
334       throw new SQLException (
335         "Unexpected exception while releasing pool \"" + name + "\":" + e );
336     }
337
338     connections_.remove( name );
339   }
340
341   /**
342    * Releases the default pool.
343    * @see com.methodhead.persistable.ConnectionSingleton#release(java.lang.String) release()
344    */
345   public static void release()
346   throws
347     SQLException {
348
349     release( null );
350   }
351
352   /**
353    * A convenience method to close the connection associated with the specified
354    * result set. Any <tt>SQLException</tt> thrown while trying to close the
355    * connection is logged and ignored. Nothing is done if <tt>rs</tt> is
356    * <tt>null</tt>.
357    */
358   public static void close(
359     ResultSet rs ) {
360
361     try {
362       if ( ( rs != null ) && ( rs.getStatement() != null ) && ( rs.getStatement().getConnection() != null ) ) {
363         rs.getStatement().getConnection().close();
364       }
365     }
366     catch ( SQLException e ) {
367       logger_.error( "Closing connection. " + ExceptionUtils.getStackTrace( e ) );
368     }
369   }
370
371   /**
372    * Executes a SQL query using a connection from the pool named
373    * <tt>name</tt>, returning a result set or <tt>null</tt> if an error
374    * occured. If <tt>name</tt> is <tt>null</tt>, the default pool is
375    * assumed. <strong>Important:</strong> be sure to close the connection
376    * associated with the returned result set as this is how the connection is
377    * returned to the pool (try something like
378    * <tt>rs.getStatement().getConnection().close()</tt> or use
379    * <tt>ConnectionSingleton.close()</tt>).
380    */
381   public static ResultSet runQuery(
382     String name,
383     String sql )
384   throws
385     SQLException {
386
387     Connection conn = null;
388     try {
389       logger_.debug( sql );
390
391       conn = ConnectionSingleton.getConnection( name );
392       if ( conn == null )
393         throw
394           new SQLException (
395             "Pool has not been initialized yet: \"" + name + "\"" );
396
397       ResultSet rs = conn.prepareStatement( sql ).executeQuery();
398
399       if ( rs == null ) {
400         logger_.error( "Couldn't execute sql \"" + sql + "\" and database connection may still be open." );
401       }
402
403       return rs;
404     }
405     catch ( SQLException e ) {
406       if ( conn != null )
407         conn.close();
408       throw new SQLException (
409         e.toString() + " while executing \"" + sql + "\"" );
410     }
411   }
412
413   /**
414    * Executes a SQL query using a connection from the default pool.
415    * @see com.methodhead.persistable.ConnectionSingleton#runQuery(java.lang.String,java.lang.String) runQuery()
416    */
417   public static ResultSet runQuery(
418     String sql )
419   throws
420     SQLException {
421     return runQuery( null, sql );
422   }
423
424   /**
425    * Executes a SQL update using a connection from the pool named <tt>name</tt>.
426    * If <tt>name</tt> is <tt>null</tt> the default pool is assumed.
427    */
428   public static int runUpdate(
429     String name,
430     String sql )
431   throws
432     SQLException {
433     Connection conn = null;
434     try {
435       logger_.debug( sql );
436
437       conn = ConnectionSingleton.getConnection( name );
438       if ( conn == null )
439         throw
440           new SQLException (
441             "Pool has not been initialized yet: \"" + name + "\"" );
442
443       int i = conn.prepareStatement( sql ).executeUpdate();
444       conn.close();
445       return i;
446     }
447     catch ( SQLException e ) {
448       if ( conn != null )
449         conn.close();
450       throw new SQLException (
451         e.toString() + " while executing \"" + sql + "\"" );
452     }
453   }
454
455   /**
456    * Executes a SQL update using a connection from the default pool. @see
457    * com.methodhead.persistable.ConnectionSingleton#runUpdate(java.lang.String,java.lang.String)
458    * runUpdate()
459    */
460   public static int runUpdate(
461     String sql )
462   throws
463     SQLException {
464     return runUpdate( null, sql );
465   }
466
467   /**
468    * <p>
469    * Executes a series of SQL statements read from the specified <tt>reader</tt> using the connection with the specified <tt>name</tt>.
470    * The statements are expected in the following format:
471    * </p>
472    * <ul>
473    * <li>
474    * Statements must be separated by a semicolon.
475    * </li>
476    * <li>
477    * Statements may span several lines, but no statement should
478    * begin on the same line another ends.
479    * </li>
480    * <li>
481    * A double dash (<tt>--</tt>) indicates a comment; any text
482    * after the dashes to the end of the line is ignored.
483    * </li>
484    * <li>
485    * SQL keywords should not be mixed case, (e.g., <tt>INSERT</tt> or
486    * <tt>insert</tt>, but not <tt>Insert</tt>).
487    * </li>
488    * <li>
489    * SQL statements should not return results (i.e., no
490    * <tt>SELECT</tt> statements, though <tt>SELECT INTO</tt> is ok).
491    * </li>
492    * </ul>
493    * <p>
494    * <strong>Note:</strong> <tt>reader</tt> is not closed by this method.
495    * </p>
496    */
497   public static void runBatchUpdate(
498     String name,
499     Reader reader )
500   throws
501     IOException ,
502     SQLException {
503
504     LineNumberReader lineReader =
505       new LineNumberReader ( reader );
506
507     String line = null;
508     StringBuffer buf = new StringBuffer ();
509
510     while ( true ) {
511
512       //
513 // compose statement
514 //
515 buf.setLength( 0 );
516       while ( ( line = lineReader.readLine() ) != null ) {
517         if ( line.startsWith( "--" ) )
518           continue;
519
520         if ( line.indexOf( "--" ) != -1 )
521           line = line.substring( 0, line.indexOf( "--" ) );
522
523         if ( line.indexOf( ";" ) != -1 )
524           buf.append( line.substring( 0, line.indexOf( ";" ) ) );
525         else
526           buf.append( line );
527
528         buf.append( " " );
529
530         if ( line.indexOf( ";" ) != -1 )
531           break;
532       }
533
534       //
535 // trim statement
536 //
537 String statement = buf.toString().trim();
538
539       //
540 // execute statement if it's not empty
541 //
542 if ( statement.length() > 0 )
543         runUpdate( name, buf.toString() );
544
545       if ( line == null )
546         break;
547     }
548   }
549
550   /**
551    * Executes a series SQL statement using a connection from the default pool.
552    * @see com.methodhead.persistable.ConnectionSingleton#runBatchUpdate(java.lang.String,java.io.Reader) runBatchUpdate()
553    */
554   public static void runBatchUpdate(
555     Reader reader )
556   throws
557     IOException ,
558     SQLException {
559
560     runBatchUpdate( null, reader );
561   }
562
563   /**
564    * Returns one of the <tt>DBTYPE_</tt> constants indicating the type of
565    * database the <tt>name</tt> pool is connected to. The connection's meta
566    * data is examined to determine the database type; if a type cannot be
567    * determined, <tt>null</tt> is returned.
568    */
569   public static String getDatabaseType(
570     String name ) {
571
572     Connection conn = null;
573     try {
574       conn = getConnection( name );
575       String productName = conn.getMetaData().getDatabaseProductName();
576       conn.close();
577
578       if ( "MySQL".equals( productName ) )
579         return DBTYPE_MYSQL;
580
581       if ( "PostgreSQL".equals( productName ) )
582         return DBTYPE_PSQL;
583
584       if ( "Microsoft SQL Server".equals( productName ) )
585         return DBTYPE_SQLSERVER;
586
587       throw new PersistableException(
588         "Unexpected database type \"" + productName + "\"." );
589     }
590     catch ( SQLException e ) {
591       throw new RuntimeException (
592         "Unexpected SQLException: " + ExceptionUtils.getStackTrace( e ) );
593     }
594   }
595
596   /**
597    * Returns one of the <tt>DBTYPE_</tt> constants indicating the type of
598    * database the default pool is connected to. The connection's meta
599    * data is examined to determine the database type; if a type cannot be
600    * determined, <tt>null</tt> is returned.
601    */
602   public static String getDatabaseType() {
603     return getDatabaseType( null );
604   }
605   
606
607   // properties ///////////////////////////////////////////////////////////////
608
609   // attributes ///////////////////////////////////////////////////////////////
610
611   protected static Map connections_ = new HashMap ();
612
613   private static Logger logger_ = Logger.getLogger( ConnectionSingleton.class );
614 }
615

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags