001: /**
002: * $RCSfile$
003: * $Revision: 9355 $
004: * $Date: 2007-10-25 12:56:35 -0700 (Thu, 25 Oct 2007) $
005: *
006: * Copyright (C) 2004 Jive Software. All rights reserved.
007: *
008: * This software is published under the terms of the GNU Public License (GPL),
009: * a copy of which is included in this distribution.
010: */package org.jivesoftware.database;
011:
012: import org.jivesoftware.util.ClassUtils;
013: import org.jivesoftware.util.JiveGlobals;
014: import org.jivesoftware.util.Log;
015:
016: import java.io.*;
017: import java.sql.*;
018:
019: /**
020: * Central manager of database connections. All methods are static so that they
021: * can be easily accessed throughout the classes in the database package.<p>
022: *
023: * This class also provides a set of utility methods that abstract out
024: * operations that may not work on all databases such as setting the max number
025: * or rows that a query should return.
026: *
027: * @author Jive Software
028: * @see ConnectionProvider
029: */
030: public class DbConnectionManager {
031:
032: private static ConnectionProvider connectionProvider;
033: private static final Object providerLock = new Object();
034:
035: // True if connection profiling is turned on. Always false by default.
036: private static boolean profilingEnabled = false;
037:
038: // True if the database support transactions.
039: private static boolean transactionsSupported;
040: // True if the database requires large text fields to be streamed.
041: private static boolean streamTextRequired;
042: // True if the database supports the Statement.setMaxRows() method.
043: private static boolean maxRowsSupported;
044: // True if the database supports the Statement.setFetchSize() method.
045: private static boolean fetchSizeSupported;
046: // True if the database supports correlated subqueries.
047: private static boolean subqueriesSupported;
048: // True if the database supports scroll-insensitive results.
049: private static boolean scrollResultsSupported;
050: // True if the database supports batch updates.
051: private static boolean batchUpdatesSupported;
052:
053: private static DatabaseType databaseType = DatabaseType.unknown;
054:
055: private static SchemaManager schemaManager = new SchemaManager();
056:
057: /**
058: * Returns a database connection from the currently active connection
059: * provider. (auto commit is set to true).
060: *
061: * @return a connection.
062: * @throws SQLException if a SQL exception occurs.
063: */
064: public static Connection getConnection() throws SQLException {
065: if (connectionProvider == null) {
066: synchronized (providerLock) {
067: if (connectionProvider == null) {
068: // Attempt to load the connection provider classname as
069: // a Jive property.
070: String className = JiveGlobals
071: .getXMLProperty("connectionProvider.className");
072: if (className != null) {
073: // Attempt to load the class.
074: try {
075: Class conClass = ClassUtils
076: .forName(className);
077: setConnectionProvider((ConnectionProvider) conClass
078: .newInstance());
079: } catch (Exception e) {
080: Log
081: .error(
082: "Warning: failed to create the "
083: + "connection provider specified by connection"
084: + "Provider.className. Using the default pool.",
085: e);
086: setConnectionProvider(new DefaultConnectionProvider());
087: }
088: } else {
089: setConnectionProvider(new DefaultConnectionProvider());
090: }
091: }
092: }
093: }
094: Connection con = connectionProvider.getConnection();
095:
096: if (con == null) {
097: Log.error("WARNING: ConnectionManager.getConnection() "
098: + "failed to obtain a connection.");
099: }
100: // See if profiling is enabled. If yes, wrap the connection with a
101: // profiled connection.
102: if (profilingEnabled) {
103: return new ProfiledConnection(con);
104: } else {
105: return con;
106: }
107: }
108:
109: /**
110: * Returns a Connection from the currently active connection provider that
111: * is ready to participate in transactions (auto commit is set to false).
112: *
113: * @return a connection with transactions enabled.
114: * @throws SQLException if a SQL exception occurs.
115: */
116: public static Connection getTransactionConnection()
117: throws SQLException {
118: Connection con = getConnection();
119: if (isTransactionsSupported()) {
120: con.setAutoCommit(false);
121: }
122: return con;
123: }
124:
125: /**
126: * Closes a PreparedStatement and Connection. However, it first rolls back the transaction or
127: * commits it depending on the value of <code>abortTransaction</code>.
128: *
129: * @param pstmt the prepared statement to close.
130: * @param con the connection to close.
131: * @param abortTransaction true if the transaction should be rolled back.
132: */
133: public static void closeTransactionConnection(
134: PreparedStatement pstmt, Connection con,
135: boolean abortTransaction) {
136: try {
137: if (pstmt != null) {
138: pstmt.close();
139: }
140: } catch (Exception e) {
141: Log.error(e);
142: }
143: closeTransactionConnection(con, abortTransaction);
144: }
145:
146: /**
147: * Closes a Connection. However, it first rolls back the transaction or
148: * commits it depending on the value of <code>abortTransaction</code>.
149: *
150: * @param con the connection to close.
151: * @param abortTransaction true if the transaction should be rolled back.
152: */
153: public static void closeTransactionConnection(Connection con,
154: boolean abortTransaction) {
155: // test to see if the connection passed in is null
156: if (con == null) {
157: return;
158: }
159:
160: // Rollback or commit the transaction
161: if (isTransactionsSupported()) {
162: try {
163: if (abortTransaction) {
164: con.rollback();
165: } else {
166: con.commit();
167: }
168: } catch (Exception e) {
169: Log.error(e);
170: }
171: }
172: try {
173: // Reset the connection to auto-commit mode.
174: if (isTransactionsSupported()) {
175: con.setAutoCommit(true);
176: }
177: } catch (Exception e) {
178: Log.error(e);
179: }
180: try {
181: // Close the db connection.
182: con.close();
183: } catch (Exception e) {
184: Log.error(e);
185: }
186: }
187:
188: /**
189: * Closes a result set. This method should be called within the finally section of
190: * your database logic, as in the following example:
191: *
192: * <pre>
193: * public void doSomething(Connection con) {
194: * ResultSet rs = null;
195: * PreparedStatement pstmt = null;
196: * try {
197: * pstmt = con.prepareStatement("select * from blah");
198: * rs = pstmt.executeQuery();
199: * ....
200: * }
201: * catch (SQLException sqle) {
202: * Log.error(sqle);
203: * }
204: * finally {
205: * ConnectionManager.closeResultSet(rs);
206: * ConnectionManager.closePreparedStatement(pstmt);
207: * }
208: * } </pre>
209: *
210: * @param rs the result set to close.
211: */
212: public static void closeResultSet(ResultSet rs) {
213: try {
214: if (rs != null) {
215: rs.close();
216: }
217: } catch (SQLException e) {
218: Log.error(e);
219: }
220: }
221:
222: /**
223: * Closes a statement. This method should be called within the finally section of
224: * your database logic, as in the following example:
225: *
226: * <pre>
227: * public void doSomething(Connection con) {
228: * PreparedStatement pstmt = null;
229: * try {
230: * pstmt = con.prepareStatement("select * from blah");
231: * ....
232: * }
233: * catch (SQLException sqle) {
234: * Log.error(sqle);
235: * }
236: * finally {
237: * ConnectionManager.closePreparedStatement(pstmt);
238: * }
239: * } </pre>
240: *
241: * @param stmt the statement.
242: */
243: public static void closeStatement(Statement stmt) {
244: try {
245: if (stmt != null) {
246: stmt.close();
247: }
248: } catch (Exception e) {
249: Log.error(e);
250: }
251: }
252:
253: /**
254: * Closes a result set, statement and database connection (returning the connection to
255: * the connection pool). This method should be called within the finally section of
256: * your database logic, as in the following example:
257: *
258: * <pre>
259: * Connection con = null;
260: * PrepatedStatment pstmt = null;
261: * ResultSet rs = null;
262: * try {
263: * con = ConnectionManager.getConnection();
264: * pstmt = con.prepareStatement("select * from blah");
265: * rs = psmt.executeQuery();
266: * ....
267: * }
268: * catch (SQLException sqle) {
269: * Log.error(sqle);
270: * }
271: * finally {
272: * ConnectionManager.closeConnection(rs, pstmt, con);
273: * }</pre>
274: *
275: * @param rs the result set.
276: * @param stmt the statement.
277: * @param con the connection.
278: */
279: public static void closeConnection(ResultSet rs, Statement stmt,
280: Connection con) {
281: closeResultSet(rs);
282: closeStatement(stmt);
283: closeConnection(con);
284: }
285:
286: /**
287: * Closes a statement and database connection (returning the connection to
288: * the connection pool). This method should be called within the finally section of
289: * your database logic, as in the following example:
290: * <p/>
291: * <pre>
292: * Connection con = null;
293: * PrepatedStatment pstmt = null;
294: * try {
295: * con = ConnectionManager.getConnection();
296: * pstmt = con.prepareStatement("select * from blah");
297: * ....
298: * }
299: * catch (SQLException sqle) {
300: * Log.error(sqle);
301: * }
302: * finally {
303: * DbConnectionManager.closeConnection(pstmt, con);
304: * }</pre>
305: *
306: * @param stmt the statement.
307: * @param con the connection.
308: */
309: public static void closeConnection(Statement stmt, Connection con) {
310: try {
311: if (stmt != null) {
312: stmt.close();
313: }
314: } catch (Exception e) {
315: Log.error(e);
316: }
317: closeConnection(con);
318: }
319:
320: /**
321: * Closes a database connection (returning the connection to the connection pool). Any
322: * statements associated with the connection should be closed before calling this method.
323: * This method should be called within the finally section of your database logic, as
324: * in the following example:
325: * <p/>
326: * <pre>
327: * Connection con = null;
328: * try {
329: * con = ConnectionManager.getConnection();
330: * ....
331: * }
332: * catch (SQLException sqle) {
333: * Log.error(sqle);
334: * }
335: * finally {
336: * DbConnectionManager.closeConnection(con);
337: * }</pre>
338: *
339: * @param con the connection.
340: */
341: public static void closeConnection(Connection con) {
342: try {
343: if (con != null) {
344: con.close();
345: }
346: } catch (Exception e) {
347: Log.error(e);
348: }
349: }
350:
351: /**
352: * Creates a scroll insensitive Statement if the JDBC driver supports it, or a normal
353: * Statement otherwise.
354: *
355: * @param con the database connection.
356: * @return a Statement
357: * @throws SQLException if an error occurs.
358: */
359: public static Statement createScrollableStatement(Connection con)
360: throws SQLException {
361: if (isScrollResultsSupported()) {
362: return con.createStatement(
363: ResultSet.TYPE_SCROLL_INSENSITIVE,
364: ResultSet.CONCUR_READ_ONLY);
365: } else {
366: return con.createStatement();
367: }
368: }
369:
370: /**
371: * Creates a scroll insensitive PreparedStatement if the JDBC driver supports it, or a normal
372: * PreparedStatement otherwise.
373: *
374: * @param con the database connection.
375: * @param sql the SQL to create the PreparedStatement with.
376: * @return a PreparedStatement
377: * @throws java.sql.SQLException if an error occurs.
378: */
379: public static PreparedStatement createScrollablePreparedStatement(
380: Connection con, String sql) throws SQLException {
381: if (isScrollResultsSupported()) {
382: return con.prepareStatement(sql,
383: ResultSet.TYPE_SCROLL_INSENSITIVE,
384: ResultSet.CONCUR_READ_ONLY);
385: } else {
386: return con.prepareStatement(sql);
387: }
388: }
389:
390: /**
391: * Scrolls forward in a result set the specified number of rows. If the JDBC driver
392: * supports the feature, the cursor will be moved directly. Otherwise, we scroll
393: * through results one by one manually by calling <tt>rs.next()</tt>.
394: *
395: * @param rs the ResultSet object to scroll.
396: * @param rowNumber the row number to scroll forward to.
397: * @throws SQLException if an error occurs.
398: */
399: public static void scrollResultSet(ResultSet rs, int rowNumber)
400: throws SQLException {
401: // If the driver supports scrollable result sets, use that feature.
402: if (isScrollResultsSupported()) {
403: if (rowNumber > 0) {
404: rs.setFetchDirection(ResultSet.FETCH_FORWARD);
405:
406: // We will attempt to do a relative fetch. This may fail in SQL Server if
407: // <resultset-navigation-strategy> is set to absolute. It would need to be
408: // set to looping to work correctly.
409: // If so, manually scroll to the correct row.
410: try {
411: rs.relative(rowNumber);
412: } catch (SQLException e) {
413: for (int i = 0; i < rowNumber; i++) {
414: rs.next();
415: }
416: }
417: }
418: }
419: // Otherwise, manually scroll to the correct row.
420: else {
421: for (int i = 0; i < rowNumber; i++) {
422: rs.next();
423: }
424: }
425: }
426:
427: /**
428: * Returns the current connection provider. The only case in which this
429: * method should be called is if more information about the current
430: * connection provider is needed. Database connections should always be
431: * obtained by calling the getConnection method of this class.
432: *
433: * @return the connection provider.
434: */
435: public static ConnectionProvider getConnectionProvider() {
436: return connectionProvider;
437: }
438:
439: /**
440: * Sets the connection provider. The old provider (if it exists) is shut
441: * down before the new one is started. A connection provider <b>should
442: * not</b> be started before being passed to the connection manager
443: * because the manager will call the start() method automatically.
444: *
445: * @param provider the ConnectionProvider that the manager should obtain
446: * connections from.
447: */
448: public static void setConnectionProvider(ConnectionProvider provider) {
449: synchronized (providerLock) {
450: if (connectionProvider != null) {
451: connectionProvider.destroy();
452: connectionProvider = null;
453: }
454: connectionProvider = provider;
455: connectionProvider.start();
456: // Now, get a connection to determine meta data.
457: Connection con = null;
458: try {
459: con = connectionProvider.getConnection();
460: setMetaData(con);
461:
462: // Check to see if the database schema needs to be upgraded.
463: schemaManager.checkOpenfireSchema(con);
464: } catch (Exception e) {
465: Log.error(e);
466: } finally {
467: try {
468: if (con != null) {
469: con.close();
470: }
471: } catch (Exception e) {
472: Log.error(e);
473: }
474: }
475: }
476: // Remember what connection provider we want to use for restarts.
477: JiveGlobals.setXMLProperty("connectionProvider.className",
478: provider.getClass().getName());
479: }
480:
481: /**
482: * Destroys the currennt connection provider. Future calls to
483: * {@link #getConnectionProvider()} will return <tt>null</tt> until a new
484: * ConnectionProvider is set, or one is automatically loaded by a call to
485: * {@link #getConnection()}.
486: */
487: public static void destroyConnectionProvider() {
488: synchronized (providerLock) {
489: if (connectionProvider != null) {
490: connectionProvider.destroy();
491: connectionProvider = null;
492: }
493: }
494: }
495:
496: /**
497: * Retrives a large text column from a result set, automatically performing
498: * streaming if the JDBC driver requires it. This is necessary because
499: * different JDBC drivers have different capabilities and methods for
500: * retrieving large text values.
501: *
502: * @param rs the ResultSet to retrieve the text field from.
503: * @param columnIndex the column in the ResultSet of the text field.
504: * @return the String value of the text field.
505: * @throws SQLException if an SQL exception occurs.
506: */
507: public static String getLargeTextField(ResultSet rs, int columnIndex)
508: throws SQLException {
509: if (isStreamTextRequired()) {
510: Reader bodyReader = null;
511: String value = null;
512: try {
513: bodyReader = rs.getCharacterStream(columnIndex);
514: if (bodyReader == null) {
515: return null;
516: }
517: char[] buf = new char[256];
518: int len;
519: StringWriter out = new StringWriter(256);
520: while ((len = bodyReader.read(buf)) >= 0) {
521: out.write(buf, 0, len);
522: }
523: value = out.toString();
524: out.close();
525: } catch (Exception e) {
526: Log.error(e);
527: throw new SQLException("Failed to load text field");
528: } finally {
529: try {
530: if (bodyReader != null) {
531: bodyReader.close();
532: }
533: } catch (Exception e) {
534: // Ignore.
535: }
536: }
537: return value;
538: } else {
539: return rs.getString(columnIndex);
540: }
541: }
542:
543: /**
544: * Sets a large text column in a result set, automatically performing
545: * streaming if the JDBC driver requires it. This is necessary because
546: * different JDBC drivers have different capabilities and methods for
547: * setting large text values.
548: *
549: * @param pstmt the PreparedStatement to set the text field in.
550: * @param parameterIndex the index corresponding to the text field.
551: * @param value the String to set.
552: * @throws SQLException if an SQL exception occurs.
553: */
554: public static void setLargeTextField(PreparedStatement pstmt,
555: int parameterIndex, String value) throws SQLException {
556: if (isStreamTextRequired()) {
557: Reader bodyReader;
558: try {
559: bodyReader = new StringReader(value);
560: pstmt.setCharacterStream(parameterIndex, bodyReader,
561: value.length());
562: } catch (Exception e) {
563: Log.error(e);
564: throw new SQLException("Failed to set text field.");
565: }
566: // Leave bodyReader open so that the db can read from it. It *should*
567: // be garbage collected after it's done without needing to call close.
568: } else {
569: pstmt.setString(parameterIndex, value);
570: }
571: }
572:
573: /**
574: * Sets the max number of rows that should be returned from executing a
575: * statement. The operation is automatically bypassed if Jive knows that the
576: * the JDBC driver or database doesn't support it.
577: *
578: * @param stmt the Statement to set the max number of rows for.
579: * @param maxRows the max number of rows to return.
580: */
581: public static void setMaxRows(Statement stmt, int maxRows) {
582: if (isMaxRowsSupported()) {
583: try {
584: stmt.setMaxRows(maxRows);
585: } catch (Throwable t) {
586: // Ignore. Exception may happen if the driver doesn't support
587: // this operation and we didn't set meta-data correctly.
588: // However, it is a good idea to update the meta-data so that
589: // we don't have to incur the cost of catching an exception
590: // each time.
591: maxRowsSupported = false;
592: }
593: }
594: }
595:
596: /**
597: * Sets the number of rows that the JDBC driver should buffer at a time.
598: * The operation is automatically bypassed if Jive knows that the
599: * the JDBC driver or database doesn't support it.
600: *
601: * @param rs the ResultSet to set the fetch size for.
602: * @param fetchSize the fetchSize.
603: */
604: public static void setFetchSize(ResultSet rs, int fetchSize) {
605: if (isFetchSizeSupported()) {
606: try {
607: rs.setFetchSize(fetchSize);
608: } catch (Throwable t) {
609: // Ignore. Exception may happen if the driver doesn't support
610: // this operation and we didn't set meta-data correctly.
611: // However, it is a good idea to update the meta-data so that
612: // we don't have to incur the cost of catching an exception
613: // each time.
614: fetchSizeSupported = false;
615: }
616: }
617: }
618:
619: /**
620: * Returns a SchemaManager instance, which can be used to manage the database
621: * schema information for Openfire and plugins.
622: *
623: * @return a SchemaManager instance.
624: */
625: public static SchemaManager getSchemaManager() {
626: return schemaManager;
627: }
628:
629: /**
630: * Uses a connection from the database to set meta data information about
631: * what different JDBC drivers and databases support.
632: *
633: * @param con the connection.
634: * @throws SQLException if an SQL exception occurs.
635: */
636: private static void setMetaData(Connection con) throws SQLException {
637: DatabaseMetaData metaData = con.getMetaData();
638: // Supports transactions?
639: transactionsSupported = metaData.supportsTransactions();
640: // Supports subqueries?
641: subqueriesSupported = metaData.supportsCorrelatedSubqueries();
642: // Supports scroll insensitive result sets? Try/catch block is a
643: // workaround for DB2 JDBC driver, which throws an exception on
644: // the method call.
645: try {
646: scrollResultsSupported = metaData
647: .supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
648: } catch (Exception e) {
649: scrollResultsSupported = false;
650: }
651: // Supports batch updates
652: batchUpdatesSupported = metaData.supportsBatchUpdates();
653:
654: // Set defaults for other meta properties
655: streamTextRequired = false;
656: maxRowsSupported = true;
657: fetchSizeSupported = true;
658:
659: // Get the database name so that we can perform meta data settings.
660: String dbName = metaData.getDatabaseProductName().toLowerCase();
661: String driverName = metaData.getDriverName().toLowerCase();
662:
663: // Oracle properties.
664: if (dbName.indexOf("oracle") != -1) {
665: databaseType = DatabaseType.oracle;
666: streamTextRequired = true;
667: scrollResultsSupported = false;
668: // The i-net AUGURO JDBC driver
669: if (driverName.indexOf("auguro") != -1) {
670: streamTextRequired = false;
671: fetchSizeSupported = true;
672: maxRowsSupported = false;
673: }
674: }
675: // Postgres properties
676: else if (dbName.indexOf("postgres") != -1) {
677: databaseType = DatabaseType.postgresql;
678: // Postgres blows, so disable scrolling result sets.
679: scrollResultsSupported = false;
680: fetchSizeSupported = false;
681: }
682: // Interbase properties
683: else if (dbName.indexOf("interbase") != -1) {
684: databaseType = DatabaseType.interbase;
685: fetchSizeSupported = false;
686: maxRowsSupported = false;
687: }
688: // SQLServer
689: else if (dbName.indexOf("sql server") != -1) {
690: databaseType = DatabaseType.sqlserver;
691: // JDBC driver i-net UNA properties
692: if (driverName.indexOf("una") != -1) {
693: fetchSizeSupported = true;
694: maxRowsSupported = false;
695: }
696: }
697: // MySQL properties
698: else if (dbName.indexOf("mysql") != -1) {
699: databaseType = DatabaseType.mysql;
700: transactionsSupported = false;
701: }
702: // HSQL properties
703: else if (dbName.indexOf("hsql") != -1) {
704: databaseType = DatabaseType.hsqldb;
705: scrollResultsSupported = false;
706: }
707: // DB2 properties.
708: else if (dbName.indexOf("db2") != 1) {
709: databaseType = DatabaseType.db2;
710: }
711: }
712:
713: /**
714: * Returns the database type. The possible types are constants of the
715: * DatabaseType class. Any database that doesn't have its own constant
716: * falls into the "Other" category.
717: *
718: * @return the database type.
719: */
720: public static DatabaseType getDatabaseType() {
721: return databaseType;
722: }
723:
724: /**
725: * Returns true if connection profiling is turned on. You can collect
726: * profiling statistics by using the static methods of the ProfiledConnection
727: * class.
728: *
729: * @return true if connection profiling is enabled.
730: */
731: public static boolean isProfilingEnabled() {
732: return profilingEnabled;
733: }
734:
735: /**
736: * Turns connection profiling on or off. You can collect profiling
737: * statistics by using the static methods of the ProfiledConnection
738: * class.
739: *
740: * @param enable true to enable profiling; false to disable.
741: */
742: public static void setProfilingEnabled(boolean enable) {
743: // If enabling profiling, call the start method on ProfiledConnection
744: if (!profilingEnabled && enable) {
745: ProfiledConnection.start();
746: }
747: // Otherwise, if turning off, call stop method.
748: else if (profilingEnabled && !enable) {
749: ProfiledConnection.stop();
750: }
751: profilingEnabled = enable;
752: }
753:
754: public static boolean isTransactionsSupported() {
755: return transactionsSupported;
756: }
757:
758: public static boolean isStreamTextRequired() {
759: return streamTextRequired;
760: }
761:
762: public static boolean isMaxRowsSupported() {
763: return maxRowsSupported;
764: }
765:
766: public static boolean isFetchSizeSupported() {
767:
768: return fetchSizeSupported;
769: }
770:
771: public static boolean isSubqueriesSupported() {
772: return subqueriesSupported;
773: }
774:
775: public static boolean isScrollResultsSupported() {
776: return scrollResultsSupported;
777: }
778:
779: public static boolean isBatchUpdatesSupported() {
780: return batchUpdatesSupported;
781: }
782:
783: public static boolean isEmbeddedDB() {
784: return connectionProvider != null
785: && connectionProvider instanceof EmbeddedConnectionProvider;
786: }
787:
788: /**
789: * A class that identifies the type of the database that Jive is connected
790: * to. In most cases, we don't want to make any database specific calls
791: * and have no need to know the type of database we're using. However,
792: * there are certain cases where it's critical to know the database for
793: * performance reasons.
794: */
795: @SuppressWarnings({"UnnecessarySemicolon"})
796: // Support for QDox parsing
797: public static enum DatabaseType {
798:
799: oracle,
800:
801: postgresql,
802:
803: mysql,
804:
805: hsqldb,
806:
807: db2,
808:
809: sqlserver,
810:
811: interbase,
812:
813: unknown;
814: }
815:
816: private DbConnectionManager() {
817: // Not instantiable.
818: }
819: }
|