001: /*
002: * Copyright (C) 2006 Methodhead Software LLC. All rights reserved.
003: *
004: * This file is part of TransferCM.
005: *
006: * TransferCM is free software; you can redistribute it and/or modify it under the
007: * terms of the GNU General Public License as published by the Free Software
008: * Foundation; either version 2 of the License, or (at your option) any later
009: * version.
010: *
011: * TransferCM is distributed in the hope that it will be useful, but WITHOUT ANY
012: * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
013: * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
014: * details.
015: *
016: * You should have received a copy of the GNU General Public License along with
017: * TransferCM; if not, write to the Free Software Foundation, Inc., 51 Franklin St,
018: * Fifth Floor, Boston, MA 02110-1301 USA
019: */
020:
021: package com.methodhead.persistable;
022:
023: import java.io.IOException;
024: import java.io.LineNumberReader;
025: import java.io.Reader;
026:
027: import java.sql.Connection;
028: import java.sql.ResultSet;
029: import java.sql.SQLException;
030: import java.sql.Statement;
031:
032: import java.util.HashMap;
033: import java.util.Map;
034: import java.util.Properties;
035:
036: import org.apache.commons.dbcp.AbandonedObjectPool;
037: import org.apache.commons.dbcp.AbandonedConfig;
038: import org.apache.commons.dbcp.ConnectionFactory;
039: import org.apache.commons.dbcp.DriverManagerConnectionFactory;
040: import org.apache.commons.dbcp.PoolableConnectionFactory;
041: import org.apache.commons.dbcp.PoolingDataSource;
042: import org.apache.commons.lang.exception.ExceptionUtils;
043:
044: import org.apache.commons.pool.ObjectPool;
045:
046: import org.apache.commons.pool.impl.GenericObjectPool;
047:
048: import org.apache.log4j.Logger;
049:
050: /**
051: * <p>
052: * A singleton that provides JDBC connections using the <a
053: * href="http://jakarta.apache.org/commons/dbcp/">Jakarta Commons DBCP
054: * component</a>. Use {@link
055: * com.methodhead.persistable.ConnectionSingleton#init(java.lang.String,java.util.Properties)
056: * init()} to initialize the singleton. The following properties are
057: * expected:
058: * </p>
059: * <ul>
060: * <li><b>driver</b>: The JDBC driver.</li>
061: * <li><b>uri</b>: The database URI.</li>
062: * <li><b>user</b>: The database login.</li>
063: * <li><b>password</b>: The database password.</li>
064: * <li><b>validQuery</b> (optional): The query used to verify the health of a connection (defaults to <tt>SELECT 1</tt>).</li>
065: * <li><b>evictionPeriodMillis</b> (optional): The time between connection health tests (defaults to <tt>3600000</tt>, 1 hour).</li>
066: * <li><b>maxActive</b> (optional): The maximum number of connections (defaults to <tt>8</tt>).</li>
067: * <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>
068: * </ul>
069: * <p>
070: * Use {@link
071: * com.methodhead.persistable.ConnectionSingleton#getConnection(java.lang.String) getConnection()} anytime after that to
072: * get a connection. In addition, a few convenience methods, {@link
073: * com.methodhead.persistable.ConnectionSingleton#runQuery runQuery()},
074: * {@link com.methodhead.persistable.ConnectionSingleton#runUpdate
075: * runUpdate()}, and {@link
076: * com.methodhead.persistable.ConnectionSingleton#runBatchUpdate
077: * runBatchUpdate()} are available to run queries using the singleton.
078: * </p>
079: * <p>
080: * All of these methods accept a <tt>name</tt> parameter, which enables the
081: * creation of named connection pools. This is useful if you want to
082: * connect to several databases in the same application. Specify
083: * <tt>null</tt> for <tt>name</tt>, or use the methods that don't include the
084: * <tt>name</tt> parameter to use the default connection pool.
085: * </p>
086: * <p>
087: * <strong>Important:</strong> be sure to close connections obtained from
088: * this singleton after you use them as this is how they are returned to
089: * the pool; this includes connections associated with result sets
090: * returned by <tt>runQuery()</tt>.
091: * </p>
092: */
093: public class ConnectionSingleton {
094:
095: // constructors /////////////////////////////////////////////////////////////
096:
097: private ConnectionSingleton() {
098: }
099:
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 extends
113: PoolingDataSource {
114:
115: /*
116: * Constructor.
117: */
118: public CloseablePoolingDataSource(GenericObjectPool pool) {
119:
120: super (pool);
121: pool_ = pool;
122: }
123:
124: /*
125: * Closes the pool associated with this data source.
126: */
127: public void close() throws Exception {
128: pool_.close();
129: }
130:
131: private GenericObjectPool pool_ = null;
132: }
133:
134: // methods //////////////////////////////////////////////////////////////////
135:
136: /**
137: * Initializes the connection pool associated with the name <tt>name</tt>
138: * using the specified properties <tt>dbProps</tt>, returning <tt>true</tt>
139: * if the singleton was successfully initialized, or <tt>false</tt>
140: * otherwise. If a pool already exists for the specified name,
141: * <tt>false</tt> is returned (use {@link
142: * com.methodhead.persistable.ConnectionSingleton#release(java.lang.String)
143: * release()} to release the pool). The following properties are expected
144: * to be defined: <tt>driver</tt> (e.g, <tt>org.postgresql.Driver</tt>),
145: * <tt>uri</tt> (e.g., <tt>jdbc:postgresql:yourdatabase</tt>),
146: * <tt>user</tt>, and <tt>password</tt>. Leading and trailing whitespace is
147: * trimmed from property values.
148: */
149: public static boolean init(String name, Properties dbProps) {
150: try {
151: if (name == null)
152: name = "";
153:
154: Object dataSource = connections_.get(name);
155:
156: if (dataSource != null) {
157: logger_.error("Pool \"" + name
158: + "\" is already initialized.");
159: return false;
160: }
161:
162: //
163: // force default values if optional properties are not specified
164: //
165: if (dbProps.getProperty("validQuery") == null)
166: dbProps.setProperty("validQuery", "SELECT 1");
167: if (dbProps.getProperty("evictionPeriodMillis") == null)
168: dbProps.setProperty("evictionPeriodMillis", "3600000");
169: if (dbProps.getProperty("maxActive") == null)
170: dbProps.setProperty("maxActive", "8");
171: if (dbProps.getProperty("maxWaitMillis") == null)
172: dbProps.setProperty("maxWaitMillis", "2000");
173: if (dbProps.getProperty("logAbandoned") == null)
174: dbProps.setProperty("logAbandoned", "false");
175: if (dbProps.getProperty("abandonedTimeout") == null)
176: dbProps.setProperty("abandonedTimeout", "300");
177:
178: //
179: // trim property values
180: //
181: dbProps.setProperty("driver", dbProps.getProperty("driver")
182: .trim());
183: dbProps.setProperty("uri", dbProps.getProperty("uri")
184: .trim());
185: dbProps.setProperty("user", dbProps.getProperty("user")
186: .trim());
187: dbProps.setProperty("password", dbProps.getProperty(
188: "password").trim());
189: dbProps.setProperty("validQuery", dbProps.getProperty(
190: "validQuery").trim());
191: dbProps.setProperty("evictionPeriodMillis", dbProps
192: .getProperty("evictionPeriodMillis").trim());
193: dbProps.setProperty("maxActive", dbProps.getProperty(
194: "maxActive").trim());
195: dbProps.setProperty("maxWaitMillis", dbProps.getProperty(
196: "maxWaitMillis").trim());
197: dbProps.setProperty("logAbandoned", dbProps.getProperty(
198: "logAbandoned").trim());
199: dbProps.setProperty("abandonedTimeout", dbProps
200: .getProperty("abandonedTimeout").trim());
201:
202: Class.forName(dbProps.getProperty("driver").trim());
203:
204: //
205: // create the right connection pool
206: //
207: GenericObjectPool connectionPool = null;
208:
209: if ("true".equals(dbProps.getProperty("logAbandoned"))) {
210:
211: AbandonedConfig abandonedConfig = new AbandonedConfig();
212: abandonedConfig.setLogAbandoned(true);
213: abandonedConfig.setRemoveAbandoned(true);
214: abandonedConfig.setRemoveAbandonedTimeout(Integer
215: .parseInt(dbProps
216: .getProperty("abandonedTimeout")));
217:
218: connectionPool = new AbandonedObjectPool(null,
219: abandonedConfig);
220: } else {
221: connectionPool = new GenericObjectPool(null);
222: }
223:
224: //
225: // configure pool
226: //
227: connectionPool.setMaxActive(Integer.parseInt(dbProps
228: .getProperty("maxActive")));
229: connectionPool
230: .setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
231: connectionPool.setMaxWait(Integer.parseInt(dbProps
232: .getProperty("maxWaitMillis")));
233: connectionPool.setTestWhileIdle(true);
234: connectionPool.setTimeBetweenEvictionRunsMillis(Integer
235: .parseInt(dbProps
236: .getProperty("evictionPeriodMillis")));
237:
238: DriverManagerConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
239: dbProps.getProperty("uri"), dbProps);
240:
241: PoolableConnectionFactory poolableConnectionFactory = 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: } catch (Exception e) {
258: logger_.error("Unexpected exception while initing pool \""
259: + name + "\": " + e);
260: return false;
261: }
262: }
263:
264: /**
265: * Initalizes the singleton's default connection pool.
266: * @see com.methodhead.persistable.ConnectionSingleton#init(java.lang.String,java.util.Properties) init()
267: */
268: public static boolean init(Properties dbProps) {
269: return init(null, dbProps);
270: }
271:
272: /**
273: * Returns a connection from the connection pool named <tt>name</tt> An
274: * exception is thrown if that pool hasn't been intitalized yet or if it is
275: * not supplying connections (a likely cause of this is bad connection
276: * parameters). If <tt>name</tt> is <tt>null</tt> the default connection
277: * pool is assumed. <strong>Important:</strong> be sure to close the
278: * returned connection, as this is how the connection is returned to the
279: * pool.
280: */
281: public static Connection getConnection(String name)
282: throws SQLException {
283:
284: if (name == null)
285: name = "";
286:
287: CloseablePoolingDataSource dataSource = (CloseablePoolingDataSource) connections_
288: .get(name);
289:
290: if (dataSource == null)
291: throw new SQLException("Pool \"" + name
292: + "\" has not been initialized yet.");
293:
294: return dataSource.getConnection();
295: }
296:
297: /**
298: * Returns a connection from the default connection pool.
299: * @see com.methodhead.persistable.ConnectionSingleton#getConnection(java.lang.String) getConnection()
300: */
301: public static Connection getConnection() throws SQLException {
302: return getConnection(null);
303: }
304:
305: /**
306: * Releases the pool with the specified <tt>name</tt> throwing an exception
307: * if no such pool exists.
308: */
309: public static void release(String name) throws SQLException {
310:
311: if (name == null)
312: name = "";
313:
314: CloseablePoolingDataSource dataSource = (CloseablePoolingDataSource) connections_
315: .get(name);
316:
317: if (dataSource == null)
318: throw new SQLException("Pool \"" + name
319: + "\" has not been initialized yet.");
320:
321: try {
322: dataSource.close();
323: } catch (Exception e) {
324: throw new SQLException(
325: "Unexpected exception while releasing pool \""
326: + name + "\":" + e);
327: }
328:
329: connections_.remove(name);
330: }
331:
332: /**
333: * Releases the default pool.
334: * @see com.methodhead.persistable.ConnectionSingleton#release(java.lang.String) release()
335: */
336: public static void release() throws SQLException {
337:
338: release(null);
339: }
340:
341: /**
342: * A convenience method to close the connection associated with the specified
343: * result set. Any <tt>SQLException</tt> thrown while trying to close the
344: * connection is logged and ignored. Nothing is done if <tt>rs</tt> is
345: * <tt>null</tt>.
346: */
347: public static void close(ResultSet rs) {
348:
349: try {
350: if ((rs != null) && (rs.getStatement() != null)
351: && (rs.getStatement().getConnection() != null)) {
352: rs.getStatement().getConnection().close();
353: }
354: } catch (SQLException e) {
355: logger_.error("Closing connection. "
356: + ExceptionUtils.getStackTrace(e));
357: }
358: }
359:
360: /**
361: * Executes a SQL query using a connection from the pool named
362: * <tt>name</tt>, returning a result set or <tt>null</tt> if an error
363: * occured. If <tt>name</tt> is <tt>null</tt>, the default pool is
364: * assumed. <strong>Important:</strong> be sure to close the connection
365: * associated with the returned result set as this is how the connection is
366: * returned to the pool (try something like
367: * <tt>rs.getStatement().getConnection().close()</tt> or use
368: * <tt>ConnectionSingleton.close()</tt>).
369: */
370: public static ResultSet runQuery(String name, String sql)
371: throws SQLException {
372:
373: Connection conn = null;
374: try {
375: logger_.debug(sql);
376:
377: conn = ConnectionSingleton.getConnection(name);
378: if (conn == null)
379: throw new SQLException(
380: "Pool has not been initialized yet: \"" + name
381: + "\"");
382:
383: ResultSet rs = conn.prepareStatement(sql).executeQuery();
384:
385: if (rs == null) {
386: logger_
387: .error("Couldn't execute sql \""
388: + sql
389: + "\" and database connection may still be open.");
390: }
391:
392: return rs;
393: } catch (SQLException e) {
394: if (conn != null)
395: conn.close();
396: throw new SQLException(e.toString() + " while executing \""
397: + sql + "\"");
398: }
399: }
400:
401: /**
402: * Executes a SQL query using a connection from the default pool.
403: * @see com.methodhead.persistable.ConnectionSingleton#runQuery(java.lang.String,java.lang.String) runQuery()
404: */
405: public static ResultSet runQuery(String sql) throws SQLException {
406: return runQuery(null, sql);
407: }
408:
409: /**
410: * Executes a SQL update using a connection from the pool named <tt>name</tt>.
411: * If <tt>name</tt> is <tt>null</tt> the default pool is assumed.
412: */
413: public static int runUpdate(String name, String sql)
414: throws SQLException {
415: Connection conn = null;
416: try {
417: logger_.debug(sql);
418:
419: conn = ConnectionSingleton.getConnection(name);
420: if (conn == null)
421: throw new SQLException(
422: "Pool has not been initialized yet: \"" + name
423: + "\"");
424:
425: int i = conn.prepareStatement(sql).executeUpdate();
426: conn.close();
427: return i;
428: } catch (SQLException e) {
429: if (conn != null)
430: conn.close();
431: throw new SQLException(e.toString() + " while executing \""
432: + sql + "\"");
433: }
434: }
435:
436: /**
437: * Executes a SQL update using a connection from the default pool. @see
438: * com.methodhead.persistable.ConnectionSingleton#runUpdate(java.lang.String,java.lang.String)
439: * runUpdate()
440: */
441: public static int runUpdate(String sql) throws SQLException {
442: return runUpdate(null, sql);
443: }
444:
445: /**
446: * <p>
447: * Executes a series of SQL statements read from the specified <tt>reader</tt> using the connection with the specified <tt>name</tt>.
448: * The statements are expected in the following format:
449: * </p>
450: * <ul>
451: * <li>
452: * Statements must be separated by a semicolon.
453: * </li>
454: * <li>
455: * Statements may span several lines, but no statement should
456: * begin on the same line another ends.
457: * </li>
458: * <li>
459: * A double dash (<tt>--</tt>) indicates a comment; any text
460: * after the dashes to the end of the line is ignored.
461: * </li>
462: * <li>
463: * SQL keywords should not be mixed case, (e.g., <tt>INSERT</tt> or
464: * <tt>insert</tt>, but not <tt>Insert</tt>).
465: * </li>
466: * <li>
467: * SQL statements should not return results (i.e., no
468: * <tt>SELECT</tt> statements, though <tt>SELECT INTO</tt> is ok).
469: * </li>
470: * </ul>
471: * <p>
472: * <strong>Note:</strong> <tt>reader</tt> is not closed by this method.
473: * </p>
474: */
475: public static void runBatchUpdate(String name, Reader reader)
476: throws IOException, SQLException {
477:
478: LineNumberReader lineReader = new LineNumberReader(reader);
479:
480: String line = null;
481: StringBuffer buf = new StringBuffer();
482:
483: while (true) {
484:
485: //
486: // compose statement
487: //
488: buf.setLength(0);
489: while ((line = lineReader.readLine()) != null) {
490: if (line.startsWith("--"))
491: continue;
492:
493: if (line.indexOf("--") != -1)
494: line = line.substring(0, line.indexOf("--"));
495:
496: if (line.indexOf(";") != -1)
497: buf.append(line.substring(0, line.indexOf(";")));
498: else
499: buf.append(line);
500:
501: buf.append(" ");
502:
503: if (line.indexOf(";") != -1)
504: break;
505: }
506:
507: //
508: // trim statement
509: //
510: String statement = buf.toString().trim();
511:
512: //
513: // execute statement if it's not empty
514: //
515: if (statement.length() > 0)
516: runUpdate(name, buf.toString());
517:
518: if (line == null)
519: break;
520: }
521: }
522:
523: /**
524: * Executes a series SQL statement using a connection from the default pool.
525: * @see com.methodhead.persistable.ConnectionSingleton#runBatchUpdate(java.lang.String,java.io.Reader) runBatchUpdate()
526: */
527: public static void runBatchUpdate(Reader reader)
528: throws IOException, SQLException {
529:
530: runBatchUpdate(null, reader);
531: }
532:
533: /**
534: * Returns one of the <tt>DBTYPE_</tt> constants indicating the type of
535: * database the <tt>name</tt> pool is connected to. The connection's meta
536: * data is examined to determine the database type; if a type cannot be
537: * determined, <tt>null</tt> is returned.
538: */
539: public static String getDatabaseType(String name) {
540:
541: Connection conn = null;
542: try {
543: conn = getConnection(name);
544: String productName = conn.getMetaData()
545: .getDatabaseProductName();
546: conn.close();
547:
548: if ("MySQL".equals(productName))
549: return DBTYPE_MYSQL;
550:
551: if ("PostgreSQL".equals(productName))
552: return DBTYPE_PSQL;
553:
554: if ("Microsoft SQL Server".equals(productName))
555: return DBTYPE_SQLSERVER;
556:
557: throw new PersistableException(
558: "Unexpected database type \"" + productName + "\".");
559: } catch (SQLException e) {
560: throw new RuntimeException("Unexpected SQLException: "
561: + ExceptionUtils.getStackTrace(e));
562: }
563: }
564:
565: /**
566: * Returns one of the <tt>DBTYPE_</tt> constants indicating the type of
567: * database the default pool is connected to. The connection's meta
568: * data is examined to determine the database type; if a type cannot be
569: * determined, <tt>null</tt> is returned.
570: */
571: public static String getDatabaseType() {
572: return getDatabaseType(null);
573: }
574:
575: // properties ///////////////////////////////////////////////////////////////
576:
577: // attributes ///////////////////////////////////////////////////////////////
578:
579: protected static Map connections_ = new HashMap();
580:
581: private static Logger logger_ = Logger
582: .getLogger(ConnectionSingleton.class);
583: }
|