001: /**
002: * Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, version 2.1, dated February 1999.
003: *
004: * This program is free software; you can redistribute it and/or modify
005: * it under the terms of the latest version of the GNU Lesser General
006: * Public License as published by the Free Software Foundation;
007: *
008: * This program is distributed in the hope that it will be useful,
009: * but WITHOUT ANY WARRANTY; without even the implied warranty of
010: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
011: * GNU Lesser General Public License for more details.
012: *
013: * You should have received a copy of the GNU Lesser General Public License
014: * along with this program (LICENSE.txt); if not, write to the Free Software
015: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
016: */package org.jamwiki.db;
017:
018: import java.lang.reflect.Method;
019: import java.sql.Connection;
020: import java.sql.DriverManager;
021: import java.sql.ResultSet;
022: import java.sql.Statement;
023: import java.util.Properties;
024: import javax.naming.Context;
025: import javax.naming.InitialContext;
026: import org.apache.commons.dbcp.DriverManagerConnectionFactory;
027: import org.apache.commons.dbcp.PoolableConnectionFactory;
028: import org.apache.commons.dbcp.PoolingDriver;
029: import org.apache.commons.lang.StringUtils;
030: import org.apache.commons.pool.impl.GenericObjectPool;
031: import org.jamwiki.Environment;
032: import org.jamwiki.WikiBase;
033: import org.jamwiki.utils.WikiLogger;
034: import org.jamwiki.utils.Encryption;
035:
036: /**
037: * This class provides methods for retrieving database connections, executing queries,
038: * and setting up connection pools.
039: */
040: public class DatabaseConnection {
041:
042: private static final WikiLogger logger = WikiLogger
043: .getLogger(DatabaseConnection.class.getName());
044: /** Any queries that take longer than this value (specified in milliseconds) will print a warning to the log. */
045: protected static final int SLOW_QUERY_LIMIT = 250;
046: private static boolean poolInitialized = false;
047: private static GenericObjectPool connectionPool = null;
048:
049: /**
050: *
051: */
052: private DatabaseConnection() {
053: }
054:
055: /**
056: * Utility method for closing a database connection, a statement and a result set.
057: * This method must ALWAYS be called for any connection retrieved by the
058: * {@link DatabaseConnection#getConnection getConnection()} method, and the
059: * connection SHOULD NOT have already been closed.
060: *
061: * @param conn A database connection, retrieved using DatabaseConnection.getConnection(),
062: * that is to be closed. This connection SHOULD NOT have been previously closed.
063: * @param stmt A statement object that is to be closed. May be <code>null</code>.
064: * @param rs A result set object that is to be closed. May be <code>null</code>.
065: */
066: public static void closeConnection(Connection conn, Statement stmt,
067: ResultSet rs) {
068: if (rs != null) {
069: try {
070: rs.close();
071: } catch (Exception e) {
072: }
073: }
074: DatabaseConnection.closeConnection(conn, stmt);
075: }
076:
077: /**
078: * Utility method for closing a database connection and a statement. This method
079: * must ALWAYS be called for any connection retrieved by the
080: * {@link DatabaseConnection#getConnection getConnection()} method, and the
081: * connection SHOULD NOT have already been closed.
082: *
083: * @param conn A database connection, retrieved using DatabaseConnection.getConnection(),
084: * that is to be closed. This connection SHOULD NOT have been previously closed.
085: * @param stmt A statement object that is to be closed. May be <code>null</code>.
086: */
087: public static void closeConnection(Connection conn, Statement stmt) {
088: if (stmt != null) {
089: try {
090: stmt.close();
091: } catch (Exception e) {
092: }
093: }
094: DatabaseConnection.closeConnection(conn);
095: }
096:
097: /**
098: * Utility method for closing a database connection. This method must ALWAYS be
099: * called for any connection retrieved by the
100: * {@link DatabaseConnection#getConnection getConnection()} method, and the
101: * connection SHOULD NOT have already been closed.
102: *
103: * @param conn A database connection, retrieved using DatabaseConnection.getConnection(),
104: * that is to be closed. This connection SHOULD NOT have been previously closed.
105: */
106: public static void closeConnection(Connection conn) {
107: if (conn == null) {
108: return;
109: }
110: try {
111: conn.close();
112: } catch (Exception e) {
113: logger.severe("Failure while closing connection", e);
114: }
115: }
116:
117: /**
118: * Close the connection pool, to be called for example in the case
119: * of the Servlet being shutdown.
120: */
121: protected static void closeConnectionPool() throws Exception {
122: if (connectionPool == null) {
123: return;
124: }
125: connectionPool.clear();
126: try {
127: connectionPool.close();
128: } catch (Exception e) {
129: logger.severe("Unable to close connection pool", e);
130: throw e;
131: }
132: poolInitialized = false;
133: }
134:
135: /**
136: *
137: */
138: protected static WikiResultSet executeQuery(String sql)
139: throws Exception {
140: Connection conn = null;
141: try {
142: conn = DatabaseConnection.getConnection();
143: return executeQuery(sql, conn);
144: } finally {
145: if (conn != null) {
146: DatabaseConnection.closeConnection(conn);
147: }
148: }
149: }
150:
151: /**
152: *
153: */
154: protected static WikiResultSet executeQuery(String sql,
155: Connection conn) throws Exception {
156: Statement stmt = null;
157: ResultSet rs = null;
158: try {
159: long start = System.currentTimeMillis();
160: stmt = conn.createStatement();
161: rs = stmt.executeQuery(sql);
162: long execution = System.currentTimeMillis() - start;
163: if (execution > DatabaseConnection.SLOW_QUERY_LIMIT) {
164: logger.warning("Slow query: " + sql + " ("
165: + (execution / 1000.000) + " s.)");
166: }
167: logger.fine("Executed " + sql + " ("
168: + (execution / 1000.000) + " s.)");
169: return new WikiResultSet(rs);
170: } catch (Exception e) {
171: throw new Exception("Failure while executing " + sql, e);
172: } finally {
173: if (rs != null) {
174: try {
175: rs.close();
176: } catch (Exception e) {
177: }
178: }
179: if (stmt != null) {
180: try {
181: stmt.close();
182: } catch (Exception e) {
183: }
184: }
185: }
186: }
187:
188: /**
189: *
190: */
191: protected static void executeUpdate(String sql) throws Exception {
192: Connection conn = null;
193: try {
194: conn = DatabaseConnection.getConnection();
195: executeUpdate(sql, conn);
196: } finally {
197: if (conn != null) {
198: DatabaseConnection.closeConnection(conn);
199: }
200: }
201: }
202:
203: /**
204: *
205: */
206: protected static int executeUpdate(String sql, Connection conn)
207: throws Exception {
208: Statement stmt = null;
209: try {
210: long start = System.currentTimeMillis();
211: stmt = conn.createStatement();
212: logger.info("Executing SQL: " + sql);
213: int result = stmt.executeUpdate(sql);
214: long execution = System.currentTimeMillis() - start;
215: if (execution > DatabaseConnection.SLOW_QUERY_LIMIT) {
216: logger.warning("Slow query: " + sql + " ("
217: + (execution / 1000.000) + " s.)");
218: }
219: logger.fine("Executed " + sql + " ("
220: + (execution / 1000.000) + " s.)");
221: return result;
222: } catch (Exception e) {
223: throw new Exception("Failure while executing " + sql, e);
224: } finally {
225: if (stmt != null) {
226: try {
227: stmt.close();
228: } catch (Exception e) {
229: }
230: }
231: }
232: }
233:
234: /**
235: *
236: */
237: protected static Connection getConnection() throws Exception {
238: String url = Environment.getValue(Environment.PROP_DB_URL);
239: String userName = Environment
240: .getValue(Environment.PROP_DB_USERNAME);
241: String password = Encryption.getEncryptedProperty(
242: Environment.PROP_DB_PASSWORD, null);
243: Connection conn = null;
244: if (url.startsWith("jdbc:")) {
245: if (!poolInitialized) {
246: setUpConnectionPool(url, userName, password);
247: }
248: conn = DriverManager
249: .getConnection("jdbc:apache:commons:dbcp:jamwiki");
250: } else {
251: // FIXME - JDK 1.3 is not supported, so update this code
252: // Use Reflection here to avoid a compile time dependency
253: // on the DataSource interface. It's not available by default
254: // on j2se 1.3.
255: Context ctx = new InitialContext();
256: Object dataSource = ctx.lookup(url);
257: Method m;
258: Object args[];
259: if (userName.length() == 0) {
260: Class[] parameterTypes = null;
261: m = dataSource.getClass().getMethod("getConnection",
262: parameterTypes);
263: args = new Object[] {};
264: } else {
265: m = dataSource.getClass().getMethod("getConnection",
266: new Class[] { String.class, String.class });
267: args = new Object[] { userName, password };
268: }
269: conn = (Connection) m.invoke(dataSource, args);
270: }
271: conn.setAutoCommit(true);
272: return conn;
273: }
274:
275: /**
276: *
277: */
278: protected static void handleErrors(Connection conn) {
279: if (conn == null) {
280: return;
281: }
282: try {
283: logger.warning("Rolling back database transactions");
284: conn.rollback();
285: } catch (Exception e) {
286: logger.severe("Unable to rollback connection", e);
287: }
288: }
289:
290: /**
291: *
292: */
293: protected static void setPoolInitialized(boolean poolInitialized) {
294: DatabaseConnection.poolInitialized = poolInitialized;
295: }
296:
297: /**
298: * Set up the database connection.
299: *
300: * @param url The database connection url.
301: * @param userName The user name to use when connecting to the database.
302: * @param password The password to use when connecting to the database.
303: * @throws Exception Thrown if any error occurs while initializing the connection pool.
304: */
305: private static void setUpConnectionPool(String url,
306: String userName, String password) throws Exception {
307: closeConnectionPool();
308: if (!StringUtils.isBlank(Environment
309: .getValue(Environment.PROP_DB_DRIVER))) {
310: Class.forName(Environment
311: .getValue(Environment.PROP_DB_DRIVER), true, Thread
312: .currentThread().getContextClassLoader());
313: }
314: connectionPool = new GenericObjectPool();
315: connectionPool.setMaxActive(Environment
316: .getIntValue(Environment.PROP_DBCP_MAX_ACTIVE));
317: connectionPool.setMaxIdle(Environment
318: .getIntValue(Environment.PROP_DBCP_MAX_IDLE));
319: connectionPool
320: .setMinEvictableIdleTimeMillis(Environment
321: .getIntValue(Environment.PROP_DBCP_MIN_EVICTABLE_IDLE_TIME) * 1000);
322: connectionPool.setTestOnBorrow(Environment
323: .getBooleanValue(Environment.PROP_DBCP_TEST_ON_BORROW));
324: connectionPool.setTestOnReturn(Environment
325: .getBooleanValue(Environment.PROP_DBCP_TEST_ON_RETURN));
326: connectionPool
327: .setTestWhileIdle(Environment
328: .getBooleanValue(Environment.PROP_DBCP_TEST_WHILE_IDLE));
329: connectionPool
330: .setTimeBetweenEvictionRunsMillis(Environment
331: .getIntValue(Environment.PROP_DBCP_TIME_BETWEEN_EVICTION_RUNS) * 1000);
332: connectionPool
333: .setNumTestsPerEvictionRun(Environment
334: .getIntValue(Environment.PROP_DBCP_NUM_TESTS_PER_EVICTION_RUN));
335: connectionPool
336: .setWhenExhaustedAction((byte) Environment
337: .getIntValue(Environment.PROP_DBCP_WHEN_EXHAUSTED_ACTION));
338: Properties properties = new Properties();
339: properties.setProperty("user", userName);
340: properties.setProperty("password", password);
341: if (Environment.getValue(Environment.PROP_DB_TYPE).equals(
342: WikiBase.DATA_HANDLER_ORACLE)) {
343: // handle clobs as strings, Oracle 10g and higher drivers (ojdbc14.jar)
344: properties.setProperty("SetBigStringTryClob", "true");
345: }
346: DriverManagerConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
347: url, properties);
348: new PoolableConnectionFactory(connectionFactory,
349: connectionPool, null, WikiDatabase
350: .getConnectionValidationQuery(), false, true);
351: PoolingDriver driver = new PoolingDriver();
352: driver.registerPool("jamwiki", connectionPool);
353: Connection conn = null;
354: try {
355: // try to get a test connection
356: conn = DriverManager
357: .getConnection("jdbc:apache:commons:dbcp:jamwiki");
358: } finally {
359: if (conn != null) {
360: closeConnection(conn);
361: }
362: }
363: poolInitialized = true;
364: }
365:
366: /**
367: *
368: */
369: public static void testDatabase(String driver, String url,
370: String user, String password, boolean existence)
371: throws Exception {
372: Connection conn = null;
373: try {
374: if (!StringUtils.isBlank(driver)) {
375: Class.forName(driver, true, Thread.currentThread()
376: .getContextClassLoader());
377: if (url.startsWith("jdbc:")) {
378: conn = DriverManager.getConnection(url, user,
379: password);
380: } else {
381: // FIXME - JDK 1.3 is not supported, so update this code
382: // Use Reflection here to avoid a compile time dependency
383: // on the DataSource interface. It's not available by default
384: // on j2se 1.3.
385: Context ctx = new InitialContext();
386: Object dataSource = ctx.lookup(url);
387: Method m;
388: Object args[];
389: if (user.length() == 0) {
390: Class[] parameterTypes = null;
391: m = dataSource.getClass().getMethod(
392: "getConnection", parameterTypes);
393: args = new Object[] {};
394: } else {
395: m = dataSource.getClass().getMethod(
396: "getConnection",
397: new Class[] { String.class,
398: String.class });
399: args = new Object[] { user, password };
400: }
401: conn = (Connection) m.invoke(dataSource, args);
402: }
403: }
404: if (existence) {
405: // test to see if database exists
406: executeQuery(
407: WikiDatabase.getExistenceValidationQuery(),
408: conn);
409: }
410: } finally {
411: if (conn != null) {
412: try {
413: conn.close();
414: } catch (Exception e) {
415: }
416: }
417: }
418: }
419: }
|