DBUtils


1   /*
2    * $Header: /cvsroot/mvnforum/myvietnam/src/net/myvietnam/mvncore/db/DBUtils.java,v 1.25.2.1 2006/04/15 15:06:34 minhnn Exp $
3    * $Author: minhnn $
4    * $Revision: 1.25.2.1 $
5    * $Date: 2006/04/15 15:06:34 $
6    *
7    * ====================================================================
8    *
9    * Copyright (C) 2002-2006 by MyVietnam.net
10   *
11   * All copyright notices regarding MyVietnam and MyVietnam CoreLib
12   * MUST remain intact in the scripts and source code.
13   *
14   * This library is free software; you can redistribute it and/or
15   * modify it under the terms of the GNU Lesser General Public
16   * License as published by the Free Software Foundation; either
17   * version 2.1 of the License, or (at your option) any later version.
18   *
19   * This library is distributed in the hope that it will be useful,
20   * but WITHOUT ANY WARRANTY; without even the implied warranty of
21   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
22   * Lesser General Public License for more details.
23   *
24   * You should have received a copy of the GNU Lesser General Public
25   * License along with this library; if not, write to the Free Software
26   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
27   *
28   * Correspondence and Marketing Questions can be sent to:
29   * info at MyVietnam net
30   *
31   * @author: Minh Nguyen
32   * @author: Mai  Nguyen
33   */
34  package net.myvietnam.mvncore.db;
35  
36  import java.sql.*;
37  
38  import javax.sql.DataSource  ;
39  
40  import net.myvietnam.mvncore.MVNCoreConfig;
41  import net.myvietnam.mvncore.MVNCoreInfo;
42  import net.myvietnam.mvncore.util.DateUtil;
43  import org.apache.commons.logging.Log;
44  import org.apache.commons.logging.LogFactory;
45  
46  /**
47   * A  database utility class to handle all the database stuffs
48   * in the MyVietnam framework
49   */
50  public final class DBUtils {
51  
52      private static Log log = LogFactory.getLog(DBUtils.class);
53  
54      public static final int DATABASE_UNKNOWN     = 0;
55      public static final int DATABASE_GENERAL     = 1;
56      public static final int DATABASE_NOSCROLL    = 2;
57  
58      public static final int DATABASE_ORACLE      = 10;
59      public static final int DATABASE_SQLSERVER   = 11;
60      public static final int DATABASE_DB2         = 12;
61      public static final int DATABASE_SYBASE      = 13;
62      public static final int DATABASE_IMFORMIX    = 14;
63      public static final int DATABASE_MYSQL       = 15;
64      public static final int DATABASE_POSTGRESQL  = 16;
65      public static final int DATABASE_HSQLDB      = 17;
66      public static final int DATABASE_ACCESS      = 18;
67      public static final int DATABASE_SAPDB       = 19;
68      public static final int DATABASE_INTERBASE   = 20;
69      public static final int DATABASE_FIREBIRD    = 21;
70  
71      private static int databaseType = DATABASE_UNKNOWN;
72  
73      private static boolean useDatasource = false;
74  
75      private static int maxTimeToWait = 2000;// 2 seconds
76  
77      private static int minutesBetweenRefresh = 30;// 30 minutes
78  
79      private static DBConnectionManager connectionManager = null;
80  
81      private static DataSource   dataSource = null;
82  
83      private static long lastGetConnectionTime = 0;
84  
85      private static long lastCloseAllConnectionsTime = 0;
86  
87      // static init of the class
88      static {
89          databaseType = MVNCoreConfig.getDatabaseType();
90          if (databaseType != DATABASE_UNKNOWN) {
91              log.info("Set DATABASE_TYPE = " + databaseType);
92          }
93          useDatasource = MVNCoreConfig.isUseDataSource();
94          if (useDatasource) {
95              String   dataSourceName = "";
96              try {
97                  javax.naming.Context   context = new javax.naming.InitialContext  ();
98                  // sample data source = java:comp/env/jdbc/MysqlDataSource
99                  dataSourceName = MVNCoreConfig.getDataSourceName();
100                 dataSource = (DataSource  ) context.lookup(dataSourceName);
101                 log.info("DBUtils : use datasource = " + dataSourceName);
102             } catch (javax.naming.NamingException   e) {
103                 //log.error("Cannot get DataSource: datasource name = " + option.datasourceName, e);
104                 log.error("Cannot get DataSource: datasource name = " + dataSourceName, e);
105             }
106         } else {
107             //maxTimeToWait = option.maxTimeToWait;
108             maxTimeToWait = MVNCoreConfig.getMaxTimeToWait();
109             //minutesBetweenRefresh = option.minutesBetweenRefresh;
110             minutesBetweenRefresh = MVNCoreConfig.getMinutesBetweenRefresh();
111             connectionManager = DBConnectionManager.getInstance(true/* a new method*/);
112             log.info("DBUtils : use built-in DBConnectionManager (MAX_TIME_TO_WAIT = " + maxTimeToWait + ", MINUTES_BETWEEN_REFRESH = " + minutesBetweenRefresh + ")");
113         }
114         log.info("DBUtils inited. Detailed info: " + MVNCoreInfo.getProductVersion() + " (Build: " + MVNCoreInfo.getProductReleaseDate() + ")");
115     }
116 
117     private DBUtils() {// so cannot new an instance
118     }
119 
120     /**
121      * Use this method to get the database type. This method will automatically
122      * detect the database type. You could override this value by modifying
123      * the value in mvncore_db_DBOptions.properties
124      * @return : the database type
125      */
126     public static int getDatabaseType() {
127         if (databaseType == DATABASE_UNKNOWN) {
128             Connection connection = null;
129             try {
130                 connection = DBUtils.getConnection();
131                 DatabaseMetaData dbmd = connection.getMetaData();
132                 String   databaseName = dbmd.getDatabaseProductName().toLowerCase();
133                 if (databaseName.indexOf("oracle") != -1) {
134                     databaseType = DATABASE_ORACLE;
135                 } else if (databaseName.indexOf("sql server") != -1) {
136                     databaseType = DATABASE_SQLSERVER;
137                 } else if (databaseName.indexOf("mysql") != -1) {// "MySQL"
138                     databaseType = DATABASE_MYSQL;
139                 } else if (databaseName.indexOf("postgresql") != -1) {
140                     databaseType = DATABASE_POSTGRESQL;
141                 } else if (databaseName.indexOf("hsql") != -1) {
142                     databaseType = DATABASE_HSQLDB;
143                 } else if (databaseName.indexOf("sap") != -1) {// "SAP DB"
144                     databaseType = DATABASE_SAPDB;
145                 } else if (databaseName.indexOf("firebird") != -1) {//"firebird"
146                     databaseType = DATABASE_FIREBIRD;
147                 } else {
148                     databaseType = DATABASE_GENERAL;
149                 }
150                 log.info("Auto detect DATABASE_TYPE = " + databaseType + " (" + getDatabaseTypeName(databaseType) + ")");
151             } catch (Exception   ex) {
152                 log.error("Error when running getDatabaseType", ex);
153             } finally {
154                 DBUtils.closeConnection(connection);
155             }
156         }
157         return databaseType;
158     }
159 
160     public static String   getDatabaseTypeName(int databaseType) {
161         String   databaseTypeName = "Cannot find databaseType = " + databaseType;
162         switch (databaseType) {
163         case DATABASE_UNKNOWN:
164             databaseTypeName = "DATABASE_UNKNOWN";
165             break;
166         case DATABASE_GENERAL:
167             databaseTypeName = "DATABASE_GENERAL";
168             break;
169         case DATABASE_NOSCROLL:
170             databaseTypeName = "DATABASE_NOSCROLL";
171             break;
172         case DATABASE_ORACLE:
173             databaseTypeName = "DATABASE_ORACLE";
174             break;
175         case DATABASE_SQLSERVER:
176             databaseTypeName = "DATABASE_SQLSERVER";
177             break;
178         case DATABASE_DB2:
179             databaseTypeName = "DATABASE_DB2";
180             break;
181         case DATABASE_SYBASE:
182             databaseTypeName = "DATABASE_SYBASE";
183             break;
184         case DATABASE_IMFORMIX:
185             databaseTypeName = "DATABASE_IMFORMIX";
186             break;
187         case DATABASE_MYSQL:
188             databaseTypeName = "DATABASE_MYSQL";
189             break;
190         case DATABASE_POSTGRESQL:
191             databaseTypeName = "DATABASE_POSTGRESQL";
192             break;
193         case DATABASE_HSQLDB:
194             databaseTypeName = "DATABASE_HSQLDB";
195             break;
196         case DATABASE_ACCESS:
197             databaseTypeName = "DATABASE_ACCESS";
198             break;
199         case DATABASE_SAPDB:
200             databaseTypeName = "DATABASE_SAPDB";
201             break;
202         case DATABASE_INTERBASE:
203             databaseTypeName = "DATABASE_INTERBASE";
204             break;
205         case DATABASE_FIREBIRD:
206             databaseTypeName = "DATABASE_FIREBIRD";
207             break;
208         }
209         return databaseTypeName;
210     }
211 
212     /**
213      * Get a connection from the connection pool. The returned connection
214      * must be closed by calling DBUtils.closeConnection()
215      * @return : a new connection from the pool if succeed
216      * @throws SQLException : if cannot get a connection from the pool
217      */
218     public static Connection getConnection() throws SQLException {
219 
220         long now = System.currentTimeMillis();
221         lastGetConnectionTime = now;
222         // now check if we have not close all connections to refresh
223         // after MINUTES_BETWEEN_REFRESH minutes, then will do it now
224         if (now - lastCloseAllConnectionsTime > DateUtil.MINUTE * minutesBetweenRefresh) {
225             boolean isBalance = closeAllConnections();
226             if (isBalance == false) {
227                 try {
228                     // wait for the checked-out connections to be returned and closed
229                     Thread.sleep(2000);
230                     log.debug("DBUtils: sleep 2 seconds for checked-out connections to returned and closed.");
231                 } catch (Exception   ex) { }
232             }
233         }
234 
235         Connection conection = null;
236 
237         if (useDatasource) {
238             if (dataSource != null) {
239                 conection = dataSource.getConnection();
240             }
241         } else {
242             if (connectionManager != null) {
243                 conection = connectionManager.getConnection(maxTimeToWait);
244             } else {
245                 log.fatal("Assertion: DBUtils.connectionManager == null");
246             }
247         }
248 
249         if (conection == null) {
250             throw new SQLException("DBUtils: Cannot get connection from Connection Pool.");
251         }
252         return conection;
253     }
254 
255     /**
256      * Close all the connections that currently in the pool
257      * This method could be used to refresh the database connection
258      * @return true if the pool is empty and balance
259      *         false if the pool has returned some connection to outside
260      */
261     public static boolean closeAllConnections() {
262         log.debug("DBUtils.closeAllConnections is called.");
263         boolean retValue = true;// balance (default)
264         lastCloseAllConnectionsTime = System.currentTimeMillis();
265         if (useDatasource) {
266             if (dataSource != null) {
267                 // do nothing here now
268             }
269         } else {
270             if (connectionManager != null) {
271                 retValue = connectionManager.release();
272             } else {
273                 log.fatal("Assertion: DBUtils.connectionManager == null");
274             }
275         }
276         return retValue;
277     }
278 
279     /**
280      * Use this method to return the connection to the connection pool
281      * Do not use this method to close connection that is not from
282      * the connection pool
283      * @param connection : the connection that needs to be returned to the pool
284      */
285     public static void closeConnection(Connection connection) {
286         if (connection == null) return;
287 
288         if (useDatasource) {
289             try {
290                 connection.close();
291             } catch (SQLException e) {
292                 log.error("DBUtils: Cannot close connection.", e);
293             }
294         } else {
295             connectionManager.freeConnection(connection);
296         }
297     }
298 
299     /**
300      * Use this method to reset the MaxRows and FetchSize of the Statement
301      * to the default values
302      * @param statement : the statement that needs to be reseted
303      */
304     public static void resetStatement(Statement statement) {
305         if (statement != null) {
306             try {
307                 statement.setMaxRows(0); //reset to the default value
308             } catch (SQLException e) {
309                 log.error("DBUtils: Cannot reset statement MaxRows.", e);
310             }
311 
312             try {
313                 statement.setFetchSize(0); //reset to the default value
314             } catch (SQLException sqle) {
315                 //do nothing, postgreSQL doesnt support this method
316             }
317         }
318     }
319 
320     /**
321      * Use this method to close the Statement
322      * @param statement : the statement that needs to be closed
323      */
324     public static void closeStatement(Statement statement) {
325         try {
326             if (statement != null) statement.close();
327         } catch (SQLException e) {
328             log.error("DBUtils: Cannot close statement.", e);
329         }
330     }
331 
332     /**
333      * Use this method to close the ResultSet
334      * @param rs : the resultset that needs to be closed
335      */
336     public static void closeResultSet(ResultSet rs) {
337         try {
338             if (rs != null) rs.close();
339         } catch (SQLException e) {
340             log.error("DBUtils: Cannot close resultset.", e);
341         }
342     }
343 
344     /*
345     public static void main(String[] args) {
346         //DBUtils DBUtils1 = new DBUtils();
347         //log.info("i = " + dataSource1);
348     }*/
349 }
350
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags