001: /*
002: * This file is part of the WfMOpen project.
003: * Copyright (C) 2001-2003 Danet GmbH (www.danet.de), GS-AN.
004: * All rights reserved.
005: *
006: * This program is free software; you can redistribute it and/or modify
007: * it under the terms of the GNU General Public License as published by
008: * the Free Software Foundation; either version 2 of the License, or
009: * (at your option) any later version.
010: *
011: * This program is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: * GNU General Public License for more details.
015: *
016: * You should have received a copy of the GNU General Public License
017: * along with this program; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: *
020: * $Id: JDBCUtil.java,v 1.4 2007/03/27 21:59:42 mlipp Exp $
021: *
022: * $Log: JDBCUtil.java,v $
023: * Revision 1.4 2007/03/27 21:59:42 mlipp
024: * Fixed lots of checkstyle warnings.
025: *
026: * Revision 1.3 2006/09/29 12:32:08 drmlipp
027: * Consistently using WfMOpen as projct name now.
028: *
029: * Revision 1.2 2005/04/22 15:10:48 drmlipp
030: * Merged changes from 1.3 branch up to 1.3p15.
031: *
032: * Revision 1.1.1.1.6.1 2005/04/13 16:14:05 drmlipp
033: * Optimized db access.
034: *
035: * Revision 1.1.1.1 2003/06/30 20:05:12 drmlipp
036: * Initial import
037: *
038: * Revision 1.33 2003/06/29 19:50:07 lipp
039: * Moved primary key generator fropm JDBCUtil to EJBUtil and made some
040: * fixes.
041: *
042: * Revision 1.32 2003/06/27 08:51:47 lipp
043: * Fixed copyright/license information.
044: *
045: * Revision 1.31 2003/05/22 09:17:15 lipp
046: * Better default for KeyGen EJB name.
047: *
048: * Revision 1.30 2003/04/25 14:50:59 lipp
049: * Fixed javadoc errors and warnings.
050: *
051: * Revision 1.29 2003/03/28 10:09:20 lipp
052: * Intriduced logging using commons-logging.
053: *
054: * Revision 1.28 2003/03/06 13:50:11 lipp
055: * Added method.
056: *
057: * Revision 1.27 2003/02/24 10:46:13 lipp
058: * Added missing null test.
059: *
060: * Revision 1.26 2003/02/21 16:38:51 lipp
061: * Introduced UniversalPrepStmt.
062: *
063: * Revision 1.25 2003/02/19 12:23:14 lipp
064: * Adapted parameter sequence for setBinary to usual style.
065: *
066: * Revision 1.24 2003/02/18 15:07:59 lipp
067: * New method getString to get around some driver problems.
068: *
069: * Revision 1.23 2003/02/14 21:57:12 lipp
070: * Fixed problem with reserved identifier.
071: *
072: * Revision 1.22 2003/02/14 16:48:07 lipp
073: * New primary key generator.
074: *
075: * Revision 1.21 2003/02/05 14:23:22 lipp
076: * Removed unnecessary exception.
077: *
078: * Revision 1.20 2003/01/15 17:10:16 robert
079: * Using default value for column length, if not meta data available.
080: *
081: * Revision 1.19 2002/08/26 14:17:07 lipp
082: * JavaDoc fixes.
083: *
084: * Revision 1.18 2002/08/26 09:43:31 schlue
085: * Code optimizations.
086: *
087: * Revision 1.17 2002/08/23 13:17:09 schlue
088: * Modifications and fixes acc. to code review.
089: *
090: * Revision 1.16 2002/08/22 17:17:26 schlue
091: * Testing of threshold for svalue against DB schema implemented.
092: * New utility operation for determining column length added.
093: * Optional oracle test environment.
094: *
095: * Revision 1.15 2002/08/22 10:58:53 schlue
096: * Utility method to determine column length added.
097: *
098: * Revision 1.14 2002/05/22 15:17:04 lipp
099: * Fixed setNullable for Integer.
100: *
101: * Revision 1.13 2002/05/21 13:23:31 huaiyang
102: * Add new method for writing Integer in database.
103: *
104: * Revision 1.12 2002/03/22 08:46:08 lipp
105: * Changed set/getBinary to support Oracle.
106: *
107: * Revision 1.11 2002/02/03 21:01:18 lipp
108: * Added support for minimum key.
109: *
110: * Revision 1.10 2002/01/22 08:48:14 lipp
111: * Javadoc fixes.
112: *
113: * Revision 1.9 2002/01/14 12:04:01 robert
114: * replace parameter DataSource with Connection (JDBCUtil.getFreePrimaryKey)
115: *
116: * Revision 1.8 2002/01/11 18:27:38 robert
117: * add getFreePrimaryKey method
118: *
119: * Revision 1.7 2001/12/19 21:07:50 lipp
120: * Added resource selection object.
121: *
122: * Revision 1.6 2001/12/07 12:33:17 schlue
123: * Implementation of persistent map finished
124: *
125: * Revision 1.5 2001/11/22 14:45:58 robert
126: * javadoc
127: *
128: * Revision 1.4 2001/11/21 17:05:18 robert
129: * add setBnary and getBinary methods for load and save of blob's
130: *
131: * Revision 1.3 2001/11/09 10:11:51 montag
132: * different jndi names for jdbc resource of workflow and staffmgmt
133: *
134: * Revision 1.2 2001/10/25 17:02:13 robert
135: * javadoc
136: *
137: * Revision 1.1 2001/10/25 07:38:05 lipp
138: * Moved JDBCUtil to de.danet.an.util
139: *
140: * Revision 1.10 2001/10/15 11:17:34 robert
141: * modify closeAll for a PreparedStatement and Statement
142: *
143: * Revision 1.9 2001/10/12 19:05:41 lipp
144: * Connection cleanup cleaned up.
145: *
146: * Revision 1.8 2001/10/10 12:57:23 schlue
147: * Errors found by "ant doccheck" fixed
148: *
149: * Revision 1.7 2001/08/24 12:58:30 lipp
150: * CR/NL fixes
151: *
152: * Revision 1.6 2001/08/23 22:44:45 robert
153: * temporary status of exceptions handling
154: *
155: * Revision 1.5 2001/08/23 10:49:19 robert
156: * refreshDS throws EJBException including NamingException
157: *
158: * Revision 1.4 2001/08/23 09:19:51 robert
159: * rename makeConnection to refreshDS
160: *
161: * Revision 1.3 2001/08/14 12:07:52 robert
162: * modify lookup name for the database to DefaultDS
163: *
164: * Revision 1.2 2001/08/13 16:58:01 robert
165: * modify link name to the database
166: *
167: * Revision 1.1 2001/08/13 12:44:07 robert
168: * initial
169: *
170: *
171: */
172:
173: package de.danet.an.util;
174:
175: import java.io.IOException;
176: import java.io.InputStream;
177: import java.io.ObjectInputStream;
178: import java.io.Reader;
179: import java.io.Serializable;
180:
181: import java.util.HashMap;
182: import java.util.Iterator;
183: import java.util.LinkedList;
184: import java.util.List;
185: import java.util.Map;
186:
187: import java.sql.Connection;
188: import java.sql.DatabaseMetaData;
189: import java.sql.ResultSet;
190: import java.sql.SQLException;
191: import java.sql.Statement;
192:
193: import javax.naming.InitialContext;
194: import javax.naming.NamingException;
195: import javax.sql.DataSource;
196:
197: /**
198: * Collection of JDBC utilities.
199: * This class adds common utilitites for a JDBC environment.
200: */
201: public class JDBCUtil {
202:
203: private static final org.apache.commons.logging.Log logger = org.apache.commons.logging.LogFactory
204: .getLog(JDBCUtil.class);
205:
206: private static final String ORACLE_DRIVER = "Oracle JDBC driver";
207: private static final String HSQL_DRIVER = "HSQL Database Engine Driver";
208: private static final String MAXDB_DRIVER = "SAP DB";
209:
210: /** List of cached column length value. */
211: private static Map columnLengthCache = new HashMap();
212:
213: /**
214: * This class holds some database properties derived from the
215: * driver and database type and version.
216: */
217: public static final class DBProperties implements Serializable {
218: /** The driver name. */
219: private String drvName;
220: /** The driver major version. */
221: private int drvMajVersion;
222: /** Flag to pass through */
223: private boolean mayPassThrough = true;
224: /** Flag indicating HSQLDB */
225: private boolean isHsqldb;
226: /** Flag indicating Oracle */
227: private boolean isOracle;
228: /** Flag indicating MaxDB */
229: private boolean isMaxDb;
230: /** Work around PointBase's string bug [Incident: 050218-000002]. */
231: private boolean fixPointBaseSetString = false;
232: /** Supports batch updates. */
233: private boolean withBatch = false;
234: /** Supports select for update. */
235: private boolean withSelectForUpdate = false;
236:
237: /**
238: * Create a new instance.
239: */
240: private DBProperties(DatabaseMetaData meta) throws SQLException {
241: drvName = meta.getDriverName();
242: if (drvName == null) {
243: drvName = "(unknown)";
244: }
245: drvMajVersion = meta.getDriverMajorVersion();
246: mayPassThrough = !drvName.equals(ORACLE_DRIVER);
247: fixPointBaseSetString = drvName.startsWith("PointBase");
248: isHsqldb = drvName.equals(HSQL_DRIVER);
249: isOracle = drvName.equals(ORACLE_DRIVER);
250: isMaxDb = drvName.equals(MAXDB_DRIVER);
251: withBatch = (mayPassThrough && meta.supportsBatchUpdates());
252: withSelectForUpdate = meta.supportsSelectForUpdate();
253: }
254:
255: /**
256: * Return the driver name.
257: * @return the driver name
258: */
259: public final String driverName() {
260: return drvName;
261: }
262:
263: /**
264: * Return the driver major version.
265: * @return driver major version
266: */
267: public final int driverMajorVersion() {
268: return drvMajVersion;
269: }
270:
271: /**
272: * Return <code>true</code> if statements may simply be
273: * passed trough without special workarounds.
274: * @return pass through flag
275: */
276: public final boolean mayPassThrough() {
277: return mayPassThrough;
278: }
279:
280: /**
281: * Return <code>true</code> if database is HSQLDB.
282: * @return HSQLDB flag
283: */
284: public final boolean isHsqldb() {
285: return isHsqldb;
286: }
287:
288: /**
289: * Return <code>true</code> if database is Oracle.
290: * @return Oracle flag
291: */
292: public final boolean isOracle() {
293: return isOracle;
294: }
295:
296: /**
297: * Return <code>true</code> if database is MaxDb.
298: * @return Oracle flag
299: */
300: public final boolean isMaxDb() {
301: return isMaxDb;
302: }
303:
304: /**
305: * Return <code>true</code> if database needs work around
306: * PointBase's string bug [Incident: 050218-000002].
307: * @return flag
308: */
309: public final boolean fixPointBaseSetString() {
310: return fixPointBaseSetString;
311: }
312:
313: /**
314: * Return <code>true</code> if driver supports batch updates.
315: * @return batch updates flag
316: */
317: public final boolean supportsBatchUpdates() {
318: return withBatch;
319: }
320:
321: /**
322: * Return <code>true</code> if driver supports getString for
323: * strings of arbitrary length.
324: * @return support indicator
325: */
326: public final boolean supportsGetString() {
327: return !(isOracle || isMaxDb);
328: }
329:
330: /**
331: * Return <code>true</code> if driver supports select for update.
332: * @return support indicator
333: */
334: public final boolean supportsSelectForUpdate() {
335: return withSelectForUpdate;
336: }
337: }
338:
339: /** Store for DB properties. */
340: private static Map dbPropsStore = new HashMap();
341: /** Helper for LRU algorithm of dbPropsStore. */
342: private static List dbPropsUse = new LinkedList();
343:
344: /**
345: * Return the database properties for the given data source and
346: * connection.
347: * @param dataSource the data source
348: * @return the database properties
349: * @throws SQLException if an error occurs while obtaining the
350: * required information
351: */
352: public static DBProperties dbProperties(DataSource dataSource)
353: throws SQLException {
354: DBProperties dbProps = (DBProperties) dbPropsStore
355: .get(dataSource);
356: if (dbProps != null) {
357: return dbProps;
358: }
359: Connection con = null;
360: try {
361: con = dataSource.getConnection();
362: return dbProperties(dataSource, con);
363: } finally {
364: JDBCUtil.closeAll(null, null, con);
365: }
366: }
367:
368: /**
369: * Return the database properties for the given data source and
370: * connection.
371: * @param dataSource the data source
372: * @param con the connection
373: * @return the database properties
374: * @throws SQLException if an error occurs while obtaining the
375: * required information
376: */
377: public static DBProperties dbProperties(DataSource dataSource,
378: Connection con) throws SQLException {
379: DBProperties dbProps = null;
380: if (dataSource != null) {
381: dbProps = (DBProperties) dbPropsStore.get(dataSource);
382: if (logger.isDebugEnabled() && dbProps != null) {
383: logger
384: .debug("Re-using db properties for "
385: + dataSource);
386: }
387: if (dbProps != null && dbProps != dbPropsUse.get(0)) {
388: synchronized (dbPropsStore) {
389: dbPropsUse.remove(dbProps);
390: dbPropsUse.add(0, dbProps);
391: }
392: }
393: }
394: if (dbProps != null) {
395: return dbProps;
396: }
397: dbProps = new DBProperties(con.getMetaData());
398: if (logger.isDebugEnabled()) {
399: logger.debug("Got db properties for "
400: + (dataSource != null ? dataSource.toString() : con
401: .toString()));
402: logger.debug("DB driver is "
403: + dbProps.driverName()
404: + ", major version "
405: + dbProps.driverMajorVersion()
406: + ", "
407: + (dbProps.supportsBatchUpdates() ? "supports"
408: : "does not support") + " batch mode.");
409: }
410: if (dataSource != null) {
411: if (logger.isDebugEnabled()) {
412: logger.debug("Saving db properties for " + dataSource);
413: }
414: synchronized (dbPropsStore) {
415: if (!dbPropsStore.containsKey(dataSource)) {
416: dbPropsStore.put(dataSource, dbProps);
417: dbPropsUse.add(0, dbProps);
418: }
419: while (dbPropsUse.size() > 10) {
420: DBProperties lruProps = (DBProperties) dbPropsUse
421: .remove(dbPropsUse.size() - 1);
422: if (logger.isDebugEnabled()) {
423: DataSource ds = null;
424: for (Iterator i = dbPropsStore.entrySet()
425: .iterator(); i.hasNext();) {
426: Map.Entry entry = (Map.Entry) i.next();
427: if (entry.getValue() == lruProps) {
428: ds = (DataSource) entry.getKey();
429: break;
430: }
431: }
432: logger.debug("Removing lru db properties for "
433: + ds);
434: }
435: dbPropsStore.remove(lruProps);
436: }
437: }
438: }
439: return dbProps;
440: }
441:
442: /**
443: * Refresh the Data Source of the database.
444: * @param dsToTest Data source to test
445: * @param dbName jndi name of the database
446: * @return the data source
447: * @throws NamingException if the resource is not present.
448: */
449: public static DataSource refreshDS(DataSource dsToTest,
450: String dbName) throws NamingException {
451: if (dsToTest != null) {
452: return dsToTest;
453: } else {
454: InitialContext ic = new InitialContext();
455: dsToTest = (DataSource) ic.lookup(dbName);
456: return dsToTest;
457: }
458: }
459:
460: /**
461: * Close a connection, a prepared statement or a statement and a
462: * result set, if allocated, i.e. if != null.
463: * @param rs a result set or null.
464: * @param st a prepared statement, statement or null
465: * @param con a connection or null.
466: * @throws SQLException if a sql error occurs.
467: */
468: public static void closeAll(ResultSet rs, Statement st,
469: Connection con) throws SQLException {
470: if (rs != null) {
471: rs.close();
472: }
473: if (st != null) {
474: st.close();
475: }
476: if (con != null) {
477: con.close();
478: }
479: }
480:
481: /**
482: * Retrieves the byte array from the ResultSet, create an object
483: * and returns it.
484: * @param rs a result set.
485: * @param offset an int offset for the ResultSet
486: * @return an Object. On the client side the object must be casted.
487: * @throws IOException any of the usual Input/Output related exceptions.
488: * @throws ClassNotFoundException if class of a serialized object
489: * cannot be found.
490: * @throws SQLException if a sql error occurs.
491: */
492: public static Object getBinary(ResultSet rs, int offset)
493: throws IOException, ClassNotFoundException, SQLException {
494: // byte[] st = rs.getBytes(offset);
495: // if (st == null ) {
496: // return null;
497: // }
498: // ByteArrayInputStream baip = new ByteArrayInputStream(st);
499: // ObjectInputStream ois = new ObjectInputStream(baip);
500: InputStream is = null;
501: try {
502: is = rs.getBinaryStream(offset);
503: } catch (NullPointerException npe) {
504: // this circumvents that hsqldb throws an NullPointerException
505: // while trying to getBinaryStream from a null-column.
506: if (rs.wasNull()) {
507: return null;
508: }
509: // oops, different error.
510: throw npe;
511: }
512: if (rs.wasNull()) {
513: return null;
514: }
515: ObjectInputStream ois = new ObjectInputStream(is);
516: return ois.readObject();
517: }
518:
519: /**
520: * Retrieves a string from the result set. For drivers that are
521: * known not to support <code>getString</code> for all string
522: * lengths, <code>getCharacterStream</code> will be used. This
523: * method should be used instead of
524: * <code>ResultSet.getString</code> if the field is of type
525: * <code>LONGVARCHAR</code>.
526: * @param dataSource the data soure that had been used to obtain
527: * the connection.
528: * @param rs a result set.
529: * @param offset an int offset for the ResultSet
530: * @return the <code>String</code> or <code>null</code>.
531: * @throws IOException any of the usual Input/Output related exceptions.
532: * @throws SQLException if a sql error occurs.
533: */
534: public static String getString(DataSource dataSource, ResultSet rs,
535: int offset) throws IOException, SQLException {
536: DBProperties dbProps = dbProperties(dataSource, rs
537: .getStatement().getConnection());
538: // The SAP DB driver is known to return only a partial
539: // string with getString in some system configurations.
540: // With oracle, we have to use getCharacterStream to access
541: // the CLOB anyway. Note that this requires 8.1.6 or higher for
542: // both the database and the jdbc driver.
543: if (!dbProps.supportsGetString()) {
544: Reader rdr = rs.getCharacterStream(offset);
545: if (rdr == null) {
546: return null;
547: }
548: StringBuffer res = new StringBuffer();
549: char[] buf = new char[1024];
550: while (true) {
551: int rc = rdr.read(buf);
552: if (rc < 0) {
553: return res.toString();
554: }
555: res.append(buf, 0, rc);
556: }
557: }
558:
559: // The default is to simply use getString.
560: return rs.getString(offset);
561: }
562:
563: /**
564: * Retrieves the Integer from the ResultSet, create an object
565: * and returns it.
566: * @param rs a result set.
567: * @param offset an int offset for the ResultSet
568: * @return an Integer or <code>null</code>.
569: * @throws SQLException if a sql error occurs.
570: */
571: public static Integer getInteger(ResultSet rs, int offset)
572: throws SQLException {
573: int i = rs.getInt(offset);
574: if (rs.wasNull()) {
575: return null;
576: }
577: return new Integer(i);
578: }
579:
580: /**
581: * Retrieves the Long from the ResultSet, create an object
582: * and returns it.
583: * @param rs a result set.
584: * @param offset an int offset for the ResultSet
585: * @return a Long or <code>null</code>.
586: * @throws SQLException if a sql error occurs.
587: */
588: public static Long getLong(ResultSet rs, int offset)
589: throws SQLException {
590: long l = rs.getLong(offset);
591: if (rs.wasNull()) {
592: return null;
593: }
594: return new Long(l);
595: }
596:
597: /**
598: * Retrieves the defined size of a table column by using
599: * the database meta schema.
600: * @param dbConnection the connection to be used for requesting the
601: * meta schema
602: * @param tableName the table name where the column is located
603: * @param columnName the name of the column for the request
604: * @param defaultValue if the database driver delivers an illegal
605: * length (0) for a varchar type column, this value is returned.
606: * @return the column size.
607: * @throws SQLException if a database error occurs.
608: */
609: public static int getTableColumnSize(Connection dbConnection,
610: String tableName, String columnName, int defaultValue)
611: throws SQLException {
612: DatabaseMetaData dmd = dbConnection.getMetaData();
613: final String dbURL = dmd.getURL();
614: final String hashValue = dbURL + "[" + tableName + "]" + "["
615: + columnName + "]";
616: // Try to retrieve stored column length from cache
617: Integer cachedCLength = (Integer) columnLengthCache
618: .get(hashValue);
619: if (cachedCLength != null) {
620: int cachedLength = cachedCLength.intValue();
621: if (cachedLength != 0) {
622: return cachedLength;
623: } else {
624: return defaultValue;
625: }
626: }
627: // Determine column length value from database schema
628: ResultSet rs = dmd
629: .getColumns(null, null, tableName, columnName);
630: int cLength = 0;
631: short dataType = 0;
632: if (rs.next()) {
633: cLength = rs.getInt("COLUMN_SIZE");
634: dataType = rs.getShort("DATA_TYPE");
635: } else {
636: cLength = defaultValue;
637: logger.warn("Schema query not supported. "
638: + "Using default value for column length.");
639: }
640: rs.close();
641: // Save column length value in cache
642: columnLengthCache.put(hashValue, new Integer(cLength));
643: // Test if information is not supplied (e.g. Hypersonic DB)
644: if ((dataType == java.sql.Types.VARCHAR) && (cLength == 0)) {
645: cLength = defaultValue;
646: logger.warn("Illegal maximum varchar length (0) received "
647: + "from schema. Using default value instead.");
648: }
649: return cLength;
650: }
651: }
|