KickJava   Java API By Example, From Geeks To Geeks.

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 JavaDoc;
24 import java.io.LineNumberReader JavaDoc;
25 import java.io.Reader JavaDoc;
26
27 import java.sql.Connection JavaDoc;
28 import java.sql.ResultSet JavaDoc;
29 import java.sql.SQLException JavaDoc;
30 import java.sql.Statement JavaDoc;
31
32 import java.util.HashMap JavaDoc;
33 import java.util.Map JavaDoc;
34 import java.util.Properties JavaDoc;
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 JavaDoc DBTYPE_MYSQL = "MYSQL";
103   public static final String JavaDoc DBTYPE_PSQL = "PSQL";
104   public static final String JavaDoc 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 JavaDoc {
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 JavaDoc name, Properties JavaDoc dbProps ) {
155     try {
156       if ( name == null )
157         name = "";
158
159       Object JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc getConnection(
284     String JavaDoc name )
285   throws
286     SQLException JavaDoc {
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 JavaDoc(
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 JavaDoc getConnection()
306   throws
307     SQLException JavaDoc {
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 JavaDoc name )
317   throws
318     SQLException JavaDoc {
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 JavaDoc(
328         "Pool \"" + name + "\" has not been initialized yet." );
329     
330     try {
331       dataSource.close();
332     }
333     catch ( Exception JavaDoc e ) {
334       throw new SQLException JavaDoc(
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 JavaDoc {
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 JavaDoc 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 JavaDoc 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 JavaDoc runQuery(
382     String JavaDoc name,
383     String JavaDoc sql )
384   throws
385     SQLException JavaDoc {
386
387     Connection JavaDoc conn = null;
388     try {
389       logger_.debug( sql );
390
391       conn = ConnectionSingleton.getConnection( name );
392       if ( conn == null )
393         throw
394           new SQLException JavaDoc(
395             "Pool has not been initialized yet: \"" + name + "\"" );
396
397       ResultSet JavaDoc 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 JavaDoc e ) {
406       if ( conn != null )
407         conn.close();
408       throw new SQLException JavaDoc(
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 JavaDoc runQuery(
418     String JavaDoc sql )
419   throws
420     SQLException JavaDoc {
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 JavaDoc name,
430     String JavaDoc sql )
431   throws
432     SQLException JavaDoc {
433     Connection JavaDoc conn = null;
434     try {
435       logger_.debug( sql );
436
437       conn = ConnectionSingleton.getConnection( name );
438       if ( conn == null )
439         throw
440           new SQLException JavaDoc(
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 JavaDoc e ) {
448       if ( conn != null )
449         conn.close();
450       throw new SQLException JavaDoc(
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 JavaDoc sql )
462   throws
463     SQLException JavaDoc {
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 JavaDoc name,
499     Reader JavaDoc reader )
500   throws
501     IOException JavaDoc,
502     SQLException JavaDoc {
503
504     LineNumberReader JavaDoc lineReader =
505       new LineNumberReader JavaDoc( reader );
506
507     String JavaDoc line = null;
508     StringBuffer JavaDoc buf = new StringBuffer JavaDoc();
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 JavaDoc 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 JavaDoc reader )
556   throws
557     IOException JavaDoc,
558     SQLException JavaDoc {
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 JavaDoc getDatabaseType(
570     String JavaDoc name ) {
571
572     Connection JavaDoc conn = null;
573     try {
574       conn = getConnection( name );
575       String JavaDoc 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 JavaDoc e ) {
591       throw new RuntimeException JavaDoc(
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 JavaDoc getDatabaseType() {
603     return getDatabaseType( null );
604   }
605   
606
607   // properties ///////////////////////////////////////////////////////////////
608

609   // attributes ///////////////////////////////////////////////////////////////
610

611   protected static Map JavaDoc connections_ = new HashMap JavaDoc();
612
613   private static Logger logger_ = Logger.getLogger( ConnectionSingleton.class );
614 }
615
Popular Tags