001: /*
002: * $Header: /cvsroot/mvnforum/myvietnam/src/net/myvietnam/mvncore/db/DBUtils.java,v 1.34 2008/01/15 11:10:06 minhnn Exp $
003: * $Author: minhnn $
004: * $Revision: 1.34 $
005: * $Date: 2008/01/15 11:10:06 $
006: *
007: * ====================================================================
008: *
009: * Copyright (C) 2002-2007 by MyVietnam.net
010: *
011: * All copyright notices regarding MyVietnam and MyVietnam CoreLib
012: * MUST remain intact in the scripts and source code.
013: *
014: * This library is free software; you can redistribute it and/or
015: * modify it under the terms of the GNU Lesser General Public
016: * License as published by the Free Software Foundation; either
017: * version 2.1 of the License, or (at your option) any later version.
018: *
019: * This library is distributed in the hope that it will be useful,
020: * but WITHOUT ANY WARRANTY; without even the implied warranty of
021: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
022: * Lesser General Public License for more details.
023: *
024: * You should have received a copy of the GNU Lesser General Public
025: * License along with this library; if not, write to the Free Software
026: * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
027: *
028: * Correspondence and Marketing Questions can be sent to:
029: * info at MyVietnam net
030: *
031: * @author: Minh Nguyen
032: * @author: Mai Nguyen
033: */
034: package net.myvietnam.mvncore.db;
035:
036: import java.sql.*;
037:
038: import javax.sql.DataSource;
039:
040: import net.myvietnam.mvncore.MVNCoreConfig;
041: import net.myvietnam.mvncore.info.DatabaseInfo;
042: import net.myvietnam.mvncore.service.MvnCoreInfoService;
043: import net.myvietnam.mvncore.service.MvnCoreServiceFactory;
044: import net.myvietnam.mvncore.util.DateUtil;
045: import org.apache.commons.logging.Log;
046: import org.apache.commons.logging.LogFactory;
047:
048: /**
049: * A database utility class to handle all the database stuffs
050: * in the MyVietnam framework
051: */
052: public final class DBUtils {
053:
054: private static Log log = LogFactory.getLog(DBUtils.class);
055:
056: public static final int DATABASE_UNKNOWN = 0;
057: public static final int DATABASE_GENERAL = 1;
058: public static final int DATABASE_NOSCROLL = 2;
059:
060: public static final int DATABASE_ORACLE = 10;
061: public static final int DATABASE_SQLSERVER = 11;
062: public static final int DATABASE_DB2 = 12;
063: public static final int DATABASE_SYBASE = 13;
064: public static final int DATABASE_IMFORMIX = 14;
065: public static final int DATABASE_MYSQL = 15;
066: public static final int DATABASE_POSTGRESQL = 16;
067: public static final int DATABASE_HSQLDB = 17;
068: public static final int DATABASE_ACCESS = 18;
069: public static final int DATABASE_SAPDB = 19;
070: public static final int DATABASE_INTERBASE = 20;
071: public static final int DATABASE_FIREBIRD = 21;
072:
073: public static final int DATABASE_DERBY = 22;
074:
075: public static final int MAX_FETCH_SIZE = 100;
076:
077: private static int databaseType = DATABASE_UNKNOWN;
078:
079: private static boolean useDatasource = false;
080:
081: private static int maxTimeToWait = 2000;// 2 seconds
082:
083: private static int minutesBetweenRefresh = 30;// 30 minutes
084:
085: private static DBConnectionManager connectionManager = null;
086:
087: private static DataSource dataSource = null;
088:
089: private static long lastGetConnectionTime = 0;
090:
091: private static long lastCloseAllConnectionsTime = 0;
092:
093: private static MvnCoreInfoService mvnCoreInfo = MvnCoreServiceFactory
094: .getMvnCoreService().getMvnCoreInfoService();
095:
096: // static init of the class
097: static {
098: databaseType = MVNCoreConfig.getDatabaseType();
099: if (databaseType != DATABASE_UNKNOWN) {
100: log.info("Set DATABASE_TYPE = " + databaseType);
101: }
102: useDatasource = MVNCoreConfig.isUseDataSource();
103: if (useDatasource) {
104: String dataSourceName = "";
105: try {
106: javax.naming.Context context = new javax.naming.InitialContext();
107: // sample data source = java:comp/env/jdbc/MysqlDataSource
108: dataSourceName = MVNCoreConfig.getDataSourceName();
109: dataSource = (DataSource) context
110: .lookup(dataSourceName);
111: log
112: .info("DBUtils : use datasource = "
113: + dataSourceName);
114: } catch (javax.naming.NamingException e) {
115: //log.error("Cannot get DataSource: datasource name = " + option.datasourceName, e);
116: log.error("Cannot get DataSource: datasource name = "
117: + dataSourceName, e);
118: }
119: } else {
120: //maxTimeToWait = option.maxTimeToWait;
121: maxTimeToWait = MVNCoreConfig.getMaxTimeToWait();
122: //minutesBetweenRefresh = option.minutesBetweenRefresh;
123: minutesBetweenRefresh = MVNCoreConfig
124: .getMinutesBetweenRefresh();
125: connectionManager = DBConnectionManager
126: .getInstance(true/* a new method*/);
127: log
128: .info("DBUtils : use built-in DBConnectionManager (MAX_TIME_TO_WAIT = "
129: + maxTimeToWait
130: + ", MINUTES_BETWEEN_REFRESH = "
131: + minutesBetweenRefresh + ")");
132: }
133: log.info("DBUtils inited. Detailed info: "
134: + mvnCoreInfo.getProductVersion() + " (Build: "
135: + mvnCoreInfo.getProductReleaseDate() + ")");
136: }
137:
138: private DBUtils() {// so cannot new an instance
139: }
140:
141: /**
142: * Use this method to get the database type. This method will automatically
143: * detect the database type. You could override this value by modifying
144: * the value in mvncore_db_DBOptions.properties
145: * @return : the database type
146: */
147: public static int getDatabaseType() {
148: if (databaseType == DATABASE_UNKNOWN) {
149: Connection connection = null;
150: try {
151: connection = DBUtils.getConnection();
152: DatabaseMetaData dbmd = connection.getMetaData();
153: String databaseName = dbmd.getDatabaseProductName()
154: .toLowerCase();
155: if (databaseName.indexOf("oracle") != -1) {
156: databaseType = DATABASE_ORACLE;
157: } else if (databaseName.indexOf("sql server") != -1) {
158: databaseType = DATABASE_SQLSERVER;
159: } else if (databaseName.indexOf("mysql") != -1) {// "MySQL"
160: databaseType = DATABASE_MYSQL;
161: } else if (databaseName.indexOf("derby") != -1) {
162: databaseType = DATABASE_DERBY;
163: } else if (databaseName.indexOf("postgresql") != -1) {
164: databaseType = DATABASE_POSTGRESQL;
165: } else if (databaseName.indexOf("hsql") != -1) {
166: databaseType = DATABASE_HSQLDB;
167: } else if (databaseName.indexOf("sap") != -1) {// "SAP DB"
168: databaseType = DATABASE_SAPDB;
169: } else if (databaseName.indexOf("firebird") != -1) {//"firebird"
170: databaseType = DATABASE_FIREBIRD;
171: } else {
172: databaseType = DATABASE_GENERAL;
173: }
174: DatabaseInfo databaseInfo = new DatabaseInfo();
175: log.info("Auto detect DATABASE_TYPE = " + databaseType
176: + " (" + getDatabaseTypeName(databaseType)
177: + ")");
178: log.info("Database Name: "
179: + databaseInfo.getDatabaseProductName());
180: log.info("Database Version: "
181: + databaseInfo.getDatabaseProductVersion());
182: log.info("Database Url: "
183: + databaseInfo.getDatabaseUrl());
184: log.info("Database Username: "
185: + databaseInfo.getDatabaseUsername());
186: log.info("JDBC Driver Name: "
187: + databaseInfo.getDriverName());
188: log.info("JDBC Driver Version: "
189: + databaseInfo.getDriverVersion());
190: } catch (Exception ex) {
191: log.error("Error when running getDatabaseType", ex);
192: } finally {
193: DBUtils.closeConnection(connection);
194: }
195: }
196: return databaseType;
197: }
198:
199: public static String getDatabaseTypeName(int databaseType) {
200: String databaseTypeName = "Cannot find databaseType = "
201: + databaseType;
202: switch (databaseType) {
203: case DATABASE_UNKNOWN:
204: databaseTypeName = "DATABASE_UNKNOWN";
205: break;
206: case DATABASE_GENERAL:
207: databaseTypeName = "DATABASE_GENERAL";
208: break;
209: case DATABASE_NOSCROLL:
210: databaseTypeName = "DATABASE_NOSCROLL";
211: break;
212: case DATABASE_ORACLE:
213: databaseTypeName = "DATABASE_ORACLE";
214: break;
215: case DATABASE_SQLSERVER:
216: databaseTypeName = "DATABASE_SQLSERVER";
217: break;
218: case DATABASE_DB2:
219: databaseTypeName = "DATABASE_DB2";
220: break;
221: case DATABASE_SYBASE:
222: databaseTypeName = "DATABASE_SYBASE";
223: break;
224: case DATABASE_IMFORMIX:
225: databaseTypeName = "DATABASE_IMFORMIX";
226: break;
227: case DATABASE_MYSQL:
228: databaseTypeName = "DATABASE_MYSQL";
229: break;
230: case DATABASE_DERBY:
231: databaseTypeName = "DATABASE_DERBY";
232: break;
233: case DATABASE_POSTGRESQL:
234: databaseTypeName = "DATABASE_POSTGRESQL";
235: break;
236: case DATABASE_HSQLDB:
237: databaseTypeName = "DATABASE_HSQLDB";
238: break;
239: case DATABASE_ACCESS:
240: databaseTypeName = "DATABASE_ACCESS";
241: break;
242: case DATABASE_SAPDB:
243: databaseTypeName = "DATABASE_SAPDB";
244: break;
245: case DATABASE_INTERBASE:
246: databaseTypeName = "DATABASE_INTERBASE";
247: break;
248: case DATABASE_FIREBIRD:
249: databaseTypeName = "DATABASE_FIREBIRD";
250: break;
251: }
252: return databaseTypeName;
253: }
254:
255: /**
256: * Get a connection from the connection pool. The returned connection
257: * must be closed by calling DBUtils.closeConnection()
258: * @return : a new connection from the pool if succeed
259: * @throws SQLException : if cannot get a connection from the pool
260: */
261: public static Connection getConnection() throws SQLException {
262:
263: long now = System.currentTimeMillis();
264: lastGetConnectionTime = now;
265: // now check if we have not close all connections to refresh
266: // after MINUTES_BETWEEN_REFRESH minutes, then will do it now
267: if (now - lastCloseAllConnectionsTime > DateUtil.MINUTE
268: * minutesBetweenRefresh) {
269: boolean isBalance = closeAllConnections();
270: if (isBalance == false) {
271: try {
272: // wait for the checked-out connections to be returned and closed
273: Thread.sleep(2000);
274: log
275: .debug("DBUtils: sleep 2 seconds for checked-out connections to returned and closed.");
276: } catch (Exception ex) {
277: }
278: }
279: }
280:
281: Connection conection = null;
282:
283: if (useDatasource) {
284: if (dataSource != null) {
285: conection = dataSource.getConnection();
286: }
287: } else {
288: if (connectionManager != null) {
289: conection = connectionManager
290: .getConnection(maxTimeToWait);
291: } else {
292: log
293: .fatal("Assertion: DBUtils.connectionManager == null");
294: }
295: }
296:
297: if (conection == null) {
298: throw new SQLException(
299: "DBUtils: Cannot get connection from Connection Pool.");
300: }
301: return conection;
302: }
303:
304: /**
305: * Close all the connections that currently in the pool
306: * This method could be used to refresh the database connection
307: * @return true if the pool is empty and balance
308: * false if the pool has returned some connection to outside
309: */
310: public static boolean closeAllConnections() {
311: log.debug("DBUtils.closeAllConnections is called.");
312: boolean retValue = true;// balance (default)
313: lastCloseAllConnectionsTime = System.currentTimeMillis();
314: if (useDatasource) {
315: if (dataSource != null) {
316: // do nothing here now
317: }
318: } else {
319: if (connectionManager != null) {
320: retValue = connectionManager.release();
321: } else {
322: log
323: .fatal("Assertion: DBUtils.connectionManager == null");
324: }
325: }
326: return retValue;
327: }
328:
329: /**
330: * Use this method to return the connection to the connection pool
331: * Do not use this method to close connection that is not from
332: * the connection pool
333: * @param connection : the connection that needs to be returned to the pool
334: */
335: public static void closeConnection(Connection connection) {
336: if (connection == null)
337: return;
338:
339: if (useDatasource) {
340: try {
341: connection.close();
342: } catch (SQLException e) {
343: log.error("DBUtils: Cannot close connection.", e);
344: }
345: } else {
346: //connectionManager.freeConnection(connection);
347: try {
348: connection.close();
349: } catch (SQLException e) {
350: log
351: .error("Assertion: this should not happen when release connection.");
352: }
353: }
354: }
355:
356: /**
357: * Use this method to reset the MaxRows and FetchSize of the Statement
358: * to the default values
359: * @param statement : the statement that needs to be reseted
360: */
361: public static void resetStatement(Statement statement) {
362: if (statement != null) {
363: try {
364: statement.setMaxRows(0); //reset to the default value
365: } catch (SQLException e) {
366: log
367: .error(
368: "DBUtils: Cannot reset statement MaxRows.",
369: e);
370: }
371:
372: try {
373: statement.setFetchSize(0); //reset to the default value
374: } catch (SQLException sqle) {
375: //do nothing, postgreSQL does not support this method
376: }
377: }
378: }
379:
380: /**
381: * Use this method to close the Statement
382: * @param statement : the statement that needs to be closed
383: */
384: public static void closeStatement(Statement statement) {
385: try {
386: if (statement != null)
387: statement.close();
388: } catch (SQLException e) {
389: log.error("DBUtils: Cannot close statement.", e);
390: }
391: }
392:
393: /**
394: * Use this method to close the ResultSet
395: * @param rs : the resultset that needs to be closed
396: */
397: public static void closeResultSet(ResultSet rs) {
398: try {
399: if (rs != null)
400: rs.close();
401: } catch (SQLException e) {
402: log.error("DBUtils: Cannot close resultset.", e);
403: }
404: }
405:
406: public static boolean isCaseSensitiveDatebase() {
407: if ((DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE)
408: || (DBUtils.getDatabaseType() == DBUtils.DATABASE_SAPDB)
409: || (DBUtils.getDatabaseType() == DBUtils.DATABASE_POSTGRESQL)
410: || (DBUtils.getDatabaseType() == DBUtils.DATABASE_DERBY)) {
411: return true;
412: }
413: return false;
414: }
415: }
|