001: /*
002: * Copyright 2002-2007 the original author or authors.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package org.springframework.jdbc.support;
018:
019: import java.lang.reflect.InvocationTargetException;
020: import java.lang.reflect.Method;
021: import java.sql.Blob;
022: import java.sql.Clob;
023: import java.sql.Connection;
024: import java.sql.DatabaseMetaData;
025: import java.sql.ResultSet;
026: import java.sql.ResultSetMetaData;
027: import java.sql.SQLException;
028: import java.sql.Statement;
029: import java.sql.Types;
030:
031: import javax.sql.DataSource;
032:
033: import org.apache.commons.logging.Log;
034: import org.apache.commons.logging.LogFactory;
035:
036: import org.springframework.jdbc.CannotGetJdbcConnectionException;
037: import org.springframework.jdbc.datasource.DataSourceUtils;
038:
039: /**
040: * Generic utility methods for working with JDBC. Mainly for internal use
041: * within the framework, but also useful for custom JDBC access code.
042: *
043: * @author Thomas Risberg
044: * @author Juergen Hoeller
045: */
046: public abstract class JdbcUtils {
047:
048: /**
049: * Constant that indicates an unknown (or unspecified) SQL type.
050: * @see java.sql.Types
051: */
052: public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;
053:
054: private static final Log logger = LogFactory
055: .getLog(JdbcUtils.class);
056:
057: /**
058: * Close the given JDBC Connection and ignore any thrown exception.
059: * This is useful for typical finally blocks in manual JDBC code.
060: * @param con the JDBC Connection to close (may be <code>null</code>)
061: */
062: public static void closeConnection(Connection con) {
063: if (con != null) {
064: try {
065: con.close();
066: } catch (SQLException ex) {
067: logger.debug("Could not close JDBC Connection", ex);
068: } catch (Throwable ex) {
069: // We don't trust the JDBC driver: It might throw RuntimeException or Error.
070: logger
071: .debug(
072: "Unexpected exception on closing JDBC Connection",
073: ex);
074: }
075: }
076: }
077:
078: /**
079: * Close the given JDBC Statement and ignore any thrown exception.
080: * This is useful for typical finally blocks in manual JDBC code.
081: * @param stmt the JDBC Statement to close (may be <code>null</code>)
082: */
083: public static void closeStatement(Statement stmt) {
084: if (stmt != null) {
085: try {
086: stmt.close();
087: } catch (SQLException ex) {
088: logger.debug("Could not close JDBC Statement", ex);
089: } catch (Throwable ex) {
090: // We don't trust the JDBC driver: It might throw RuntimeException or Error.
091: logger
092: .debug(
093: "Unexpected exception on closing JDBC Statement",
094: ex);
095: }
096: }
097: }
098:
099: /**
100: * Close the given JDBC ResultSet and ignore any thrown exception.
101: * This is useful for typical finally blocks in manual JDBC code.
102: * @param rs the JDBC ResultSet to close (may be <code>null</code>)
103: */
104: public static void closeResultSet(ResultSet rs) {
105: if (rs != null) {
106: try {
107: rs.close();
108: } catch (SQLException ex) {
109: logger.debug("Could not close JDBC ResultSet", ex);
110: } catch (Throwable ex) {
111: // We don't trust the JDBC driver: It might throw RuntimeException or Error.
112: logger
113: .debug(
114: "Unexpected exception on closing JDBC ResultSet",
115: ex);
116: }
117: }
118: }
119:
120: /**
121: * Retrieve a JDBC column value from a ResultSet, using the most appropriate
122: * value type. The returned value should be a detached value object, not having
123: * any ties to the active ResultSet: in particular, it should not be a Blob or
124: * Clob object but rather a byte array respectively String representation.
125: * <p>Uses the <code>getObject(index)</code> method, but includes additional "hacks"
126: * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
127: * datatype and a <code>java.sql.Date</code> for DATE columns leaving out the
128: * time portion: These columns will explicitly be extracted as standard
129: * <code>java.sql.Timestamp</code> object.
130: * @param rs is the ResultSet holding the data
131: * @param index is the column index
132: * @return the value object
133: * @throws SQLException if thrown by the JDBC API
134: * @see java.sql.Blob
135: * @see java.sql.Clob
136: * @see java.sql.Timestamp
137: */
138: public static Object getResultSetValue(ResultSet rs, int index)
139: throws SQLException {
140: Object obj = rs.getObject(index);
141: if (obj instanceof Blob) {
142: obj = rs.getBytes(index);
143: } else if (obj instanceof Clob) {
144: obj = rs.getString(index);
145: } else if (obj != null
146: && obj.getClass().getName().startsWith(
147: "oracle.sql.TIMESTAMP")) {
148: obj = rs.getTimestamp(index);
149: } else if (obj != null
150: && obj.getClass().getName().startsWith(
151: "oracle.sql.DATE")) {
152: String metaDataClassName = rs.getMetaData()
153: .getColumnClassName(index);
154: if ("java.sql.Timestamp".equals(metaDataClassName)
155: || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
156: obj = rs.getTimestamp(index);
157: } else {
158: obj = rs.getDate(index);
159: }
160: } else if (obj != null && obj instanceof java.sql.Date) {
161: if ("java.sql.Timestamp".equals(rs.getMetaData()
162: .getColumnClassName(index))) {
163: obj = rs.getTimestamp(index);
164: }
165: }
166: return obj;
167: }
168:
169: /**
170: * Extract database meta data via the given DatabaseMetaDataCallback.
171: * <p>This method will open a connection to the database and retrieve the database metadata.
172: * Since this method is called before the exception translation feature is configured for
173: * a datasource, this method can not rely on the SQLException translation functionality.
174: * <p>Any exceptions will be wrapped in a MetaDataAccessException. This is a checked exception
175: * and any calling code should catch and handle this exception. You can just log the
176: * error and hope for the best, but there is probably a more serious error that will
177: * reappear when you try to access the database again.
178: * @param dataSource the DataSource to extract metadata for
179: * @param action callback that will do the actual work
180: * @return object containing the extracted information, as returned by
181: * the DatabaseMetaDataCallback's <code>processMetaData</code> method
182: * @throws MetaDataAccessException if meta data access failed
183: */
184: public static Object extractDatabaseMetaData(DataSource dataSource,
185: DatabaseMetaDataCallback action)
186: throws MetaDataAccessException {
187:
188: Connection con = null;
189: try {
190: con = DataSourceUtils.getConnection(dataSource);
191: if (con == null) {
192: // should only happen in test environments
193: throw new MetaDataAccessException(
194: "Connection returned by DataSource ["
195: + dataSource + "] was null");
196: }
197: DatabaseMetaData metaData = con.getMetaData();
198: if (metaData == null) {
199: // should only happen in test environments
200: throw new MetaDataAccessException(
201: "DatabaseMetaData returned by Connection ["
202: + con + "] was null");
203: }
204: return action.processMetaData(metaData);
205: } catch (CannotGetJdbcConnectionException ex) {
206: throw new MetaDataAccessException(
207: "Could not get Connection for extracting meta data",
208: ex);
209: } catch (SQLException ex) {
210: throw new MetaDataAccessException(
211: "Error while extracting DatabaseMetaData", ex);
212: } catch (AbstractMethodError err) {
213: throw new MetaDataAccessException(
214: "JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver",
215: err);
216: } finally {
217: DataSourceUtils.releaseConnection(con, dataSource);
218: }
219: }
220:
221: /**
222: * Call the specified method on DatabaseMetaData for the given DataSource,
223: * and extract the invocation result.
224: * @param dataSource the DataSource to extract meta data for
225: * @param metaDataMethodName the name of the DatabaseMetaData method to call
226: * @return the object returned by the specified DatabaseMetaData method
227: * @throws MetaDataAccessException if we couldn't access the DatabaseMetaData
228: * or failed to invoke the specified method
229: * @see java.sql.DatabaseMetaData
230: */
231: public static Object extractDatabaseMetaData(DataSource dataSource,
232: final String metaDataMethodName)
233: throws MetaDataAccessException {
234:
235: return extractDatabaseMetaData(dataSource,
236: new DatabaseMetaDataCallback() {
237: public Object processMetaData(DatabaseMetaData dbmd)
238: throws SQLException,
239: MetaDataAccessException {
240: try {
241: Method method = dbmd.getClass().getMethod(
242: metaDataMethodName, (Class[]) null);
243: return method.invoke(dbmd, (Object[]) null);
244: } catch (NoSuchMethodException ex) {
245: throw new MetaDataAccessException(
246: "No method named '"
247: + metaDataMethodName
248: + "' found on DatabaseMetaData instance ["
249: + dbmd + "]", ex);
250: } catch (IllegalAccessException ex) {
251: throw new MetaDataAccessException(
252: "Could not access DatabaseMetaData method '"
253: + metaDataMethodName + "'",
254: ex);
255: } catch (InvocationTargetException ex) {
256: if (ex.getTargetException() instanceof SQLException) {
257: throw (SQLException) ex
258: .getTargetException();
259: }
260: throw new MetaDataAccessException(
261: "Invocation of DatabaseMetaData method '"
262: + metaDataMethodName
263: + "' failed", ex);
264: }
265: }
266: });
267: }
268:
269: /**
270: * Return whether the given JDBC driver supports JDBC 2.0 batch updates.
271: * <p>Typically invoked right before execution of a given set of statements:
272: * to decide whether the set of SQL statements should be executed through
273: * the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion.
274: * <p>Logs a warning if the "supportsBatchUpdates" methods throws an exception
275: * and simply returns <code>false</code> in that case.
276: * @param con the Connection to check
277: * @return whether JDBC 2.0 batch updates are supported
278: * @see java.sql.DatabaseMetaData#supportsBatchUpdates()
279: */
280: public static boolean supportsBatchUpdates(Connection con) {
281: try {
282: DatabaseMetaData dbmd = con.getMetaData();
283: if (dbmd != null) {
284: if (dbmd.supportsBatchUpdates()) {
285: logger.debug("JDBC driver supports batch updates");
286: return true;
287: } else {
288: logger
289: .debug("JDBC driver does not support batch updates");
290: }
291: }
292: } catch (SQLException ex) {
293: logger
294: .debug(
295: "JDBC driver 'supportsBatchUpdates' method threw exception",
296: ex);
297: } catch (AbstractMethodError err) {
298: logger
299: .debug(
300: "JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method",
301: err);
302: }
303: return false;
304: }
305:
306: /**
307: * Extract a common name for the database in use even if various drivers/platforms provide varying names.
308: * @param source the name as provided in database metedata
309: * @return the common name to be used
310: */
311: public static String commonDatabaseName(String source) {
312: String name = source;
313: if (source != null && source.startsWith("DB2")) {
314: name = "DB2";
315: } else if ("Sybase SQL Server".equals(source)
316: || "Adaptive Server Enterprise".equals(source)
317: || "sql server".equals(source)) {
318: name = "Sybase";
319: }
320: return name;
321: }
322:
323: /**
324: * Check whether the given SQL type is numeric.
325: * @param sqlType the SQL type to be checked
326: * @return whether the type is numeric
327: */
328: public static boolean isNumeric(int sqlType) {
329: return Types.BIT == sqlType || Types.BIGINT == sqlType
330: || Types.DECIMAL == sqlType || Types.DOUBLE == sqlType
331: || Types.FLOAT == sqlType || Types.INTEGER == sqlType
332: || Types.NUMERIC == sqlType || Types.REAL == sqlType
333: || Types.SMALLINT == sqlType
334: || Types.TINYINT == sqlType;
335: }
336:
337: /**
338: * Determine the column name to use. The column name is determined based on a
339: * lookup using ResultSetMetaData.
340: * <p>This method implementation takes into account recent clarifications
341: * expressed in the JDBC 4.0 specification:
342: * <p><i>columnLabel - the label for the column specified with the SQL AS clause.
343: * If the SQL AS clause was not specified, then the label is the name of the column</i>.
344: * @return the column name to use
345: * @param resultSetMetaData the current meta data to use
346: * @param columnIndex the index of the column for the look up
347: * @throws SQLException in case of lookup failure
348: */
349: public static String lookupColumnName(
350: ResultSetMetaData resultSetMetaData, int columnIndex)
351: throws SQLException {
352: String name = resultSetMetaData.getColumnLabel(columnIndex);
353: if (name == null || name.length() < 1) {
354: name = resultSetMetaData.getColumnName(columnIndex);
355: }
356: return name;
357: }
358:
359: /**
360: * Convert a column name with underscores to the corresponding property name using "camel case". A name
361: * like "customer_number" would match a "customerNumber" property name.
362: * @param name the column name to be converted
363: * @return the name using "camel case"
364: */
365: public static String convertUnderscoreNameToPropertyName(String name) {
366: StringBuffer result = new StringBuffer();
367: boolean nextIsUpper = false;
368: if (name != null && name.length() > 0) {
369: if (name.length() > 1 && name.substring(1, 2).equals("_")) {
370: result.append(name.substring(0, 1).toUpperCase());
371: } else {
372: result.append(name.substring(0, 1).toLowerCase());
373: }
374: for (int i = 1; i < name.length(); i++) {
375: String s = name.substring(i, i + 1);
376: if (s.equals("_")) {
377: nextIsUpper = true;
378: } else {
379: if (nextIsUpper) {
380: result.append(s.toUpperCase());
381: nextIsUpper = false;
382: } else {
383: result.append(s.toLowerCase());
384: }
385: }
386: }
387: }
388: return result.toString();
389: }
390:
391: }
|