JdbcUtils


1   /*
2    * Copyright 2002-2007 the original author or authors.
3    *
4    * Licensed under the Apache License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    *
8    *      http://www.apache.org/licenses/LICENSE-2.0
9    *
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  
17  package org.springframework.jdbc.support;
18  
19  import java.lang.reflect.InvocationTargetException  ;
20  import java.lang.reflect.Method  ;
21  import java.sql.Blob  ;
22  import java.sql.Clob  ;
23  import java.sql.Connection  ;
24  import java.sql.DatabaseMetaData  ;
25  import java.sql.ResultSet  ;
26  import java.sql.SQLException  ;
27  import java.sql.Statement  ;
28  import java.sql.Types  ;
29  
30  import javax.sql.DataSource  ;
31  
32  import org.apache.commons.logging.Log;
33  import org.apache.commons.logging.LogFactory;
34  
35  import org.springframework.jdbc.CannotGetJdbcConnectionException;
36  import org.springframework.jdbc.datasource.DataSourceUtils;
37  
38  /**
39   * Generic utility methods for working with JDBC. Mainly for internal use
40   * within the framework, but also useful for custom JDBC access code.
41   *
42   * @author Thomas Risberg
43   * @author Juergen Hoeller
44   */
45  public abstract class JdbcUtils {
46  
47      /**
48       * Constant that indicates an unknown (or unspecified) SQL type.
49       * @see java.sql.Types
50       */
51      public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;
52  
53  
54      private static final Log logger = LogFactory.getLog(JdbcUtils.class);
55  
56  
57      /**
58       * Close the given JDBC Connection and ignore any thrown exception.
59       * This is useful for typical finally blocks in manual JDBC code.
60       * @param con the JDBC Connection to close (may be <code>null</code>)
61       */
62      public static void closeConnection(Connection   con) {
63          if (con != null) {
64              try {
65                  con.close();
66              }
67              catch (SQLException   ex) {
68                  logger.debug("Could not close JDBC Connection", ex);
69              }
70              catch (Throwable   ex) {
71                  // We don't trust the JDBC driver: It might throw RuntimeException or Error.
72                  logger.debug("Unexpected exception on closing JDBC Connection", ex);
73              }
74          }
75      }
76  
77      /**
78       * Close the given JDBC Statement and ignore any thrown exception.
79       * This is useful for typical finally blocks in manual JDBC code.
80       * @param stmt the JDBC Statement to close (may be <code>null</code>)
81       */
82      public static void closeStatement(Statement   stmt) {
83          if (stmt != null) {
84              try {
85                  stmt.close();
86              }
87              catch (SQLException   ex) {
88                  logger.debug("Could not close JDBC Statement", ex);
89              }
90              catch (Throwable   ex) {
91                  // We don't trust the JDBC driver: It might throw RuntimeException or Error.
92                  logger.debug("Unexpected exception on closing JDBC Statement", ex);
93              }
94          }
95      }
96  
97      /**
98       * Close the given JDBC ResultSet and ignore any thrown exception.
99       * This is useful for typical finally blocks in manual JDBC code.
100      * @param rs the JDBC ResultSet to close (may be <code>null</code>)
101      */
102     public static void closeResultSet(ResultSet   rs) {
103         if (rs != null) {
104             try {
105                 rs.close();
106             }
107             catch (SQLException   ex) {
108                 logger.debug("Could not close JDBC ResultSet", ex);
109             }
110             catch (Throwable   ex) {
111                 // We don't trust the JDBC driver: It might throw RuntimeException or Error.
112                 logger.debug("Unexpected exception on closing JDBC ResultSet", ex);
113             }
114         }
115     }
116 
117     /**
118      * Retrieve a JDBC column value from a ResultSet, using the most appropriate
119      * value type. The returned value should be a detached value object, not having
120      * any ties to the active ResultSet: in particular, it should not be a Blob or
121      * Clob object but rather a byte array respectively String representation.
122      * <p>Uses the <code>getObject(index)</code> method, but includes additional "hacks"
123      * to get around Oracle 10g returning a non-standard object for its TIMESTAMP
124      * datatype and a <code>java.sql.Date</code> for DATE columns leaving out the
125      * time portion: These columns will explicitly be extracted as standard
126      * <code>java.sql.Timestamp</code> object.
127      * @param rs is the ResultSet holding the data
128      * @param index is the column index
129      * @return the value object
130      * @throws SQLException if thrown by the JDBC API
131      * @see java.sql.Blob
132      * @see java.sql.Clob
133      * @see java.sql.Timestamp
134      */
135     public static Object   getResultSetValue(ResultSet   rs, int index) throws SQLException   {
136         Object   obj = rs.getObject(index);
137         if (obj instanceof Blob  ) {
138             obj = rs.getBytes(index);
139         }
140         else if (obj instanceof Clob  ) {
141             obj = rs.getString(index);
142         }
143         else if (obj != null && obj.getClass().getName().startsWith("oracle.sql.TIMESTAMP")) {
144             obj = rs.getTimestamp(index);
145         }
146         else if (obj != null && obj.getClass().getName().startsWith("oracle.sql.DATE")) {
147             String   metaDataClassName = rs.getMetaData().getColumnClassName(index);
148             if ("java.sql.Timestamp".equals(metaDataClassName) ||
149                     "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
150                 obj = rs.getTimestamp(index);
151             }
152             else {
153                 obj = rs.getDate(index);
154             }
155         }
156         else if (obj != null && obj instanceof java.sql.Date  ) {
157             if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
158                 obj = rs.getTimestamp(index);
159             }
160         }
161         return obj;
162     }
163 
164     /**
165      * Extract database meta data via the given DatabaseMetaDataCallback.
166      * <p>This method will open a connection to the database and retrieve the database metadata.
167      * Since this method is called before the exception translation feature is configured for
168      * a datasource, this method can not rely on the SQLException translation functionality.
169      * <p>Any exceptions will be wrapped in a MetaDataAccessException. This is a checked exception
170      * and any calling code should catch and handle this exception. You can just log the
171      * error and hope for the best, but there is probably a more serious error that will
172      * reappear when you try to access the database again.
173      * @param dataSource the DataSource to extract metadata for
174      * @param action callback that will do the actual work
175      * @return object containing the extracted information, as returned by
176      * the DatabaseMetaDataCallback's <code>processMetaData</code> method
177      * @throws MetaDataAccessException if meta data access failed
178      */
179     public static Object   extractDatabaseMetaData(DataSource   dataSource, DatabaseMetaDataCallback action)
180             throws MetaDataAccessException {
181 
182         Connection   con = null;
183         try {
184             con = DataSourceUtils.getConnection(dataSource);
185             if (con == null) {
186                 // should only happen in test environments
187                 throw new MetaDataAccessException("Connection returned by DataSource [" + dataSource + "] was null");
188             }
189             DatabaseMetaData   metaData = con.getMetaData();
190             if (metaData == null) {
191                 // should only happen in test environments
192                 throw new MetaDataAccessException("DatabaseMetaData returned by Connection [" + con + "] was null");
193             }
194             return action.processMetaData(metaData);
195         }
196         catch (CannotGetJdbcConnectionException ex) {
197             throw new MetaDataAccessException("Could not get Connection for extracting meta data", ex);
198         }
199         catch (SQLException   ex) {
200             throw new MetaDataAccessException("Error while extracting DatabaseMetaData", ex);
201         }
202         catch (AbstractMethodError   err) {
203             throw new MetaDataAccessException(
204                     "JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err);
205         }
206         finally {
207             DataSourceUtils.releaseConnection(con, dataSource);
208         }
209     }
210 
211     /**
212      * Call the specified method on DatabaseMetaData for the given DataSource,
213      * and extract the invocation result.
214      * @param dataSource the DataSource to extract meta data for
215      * @param metaDataMethodName the name of the DatabaseMetaData method to call
216      * @return the object returned by the specified DatabaseMetaData method
217      * @throws MetaDataAccessException if we couldn't access the DatabaseMetaData
218      * or failed to invoke the specified method
219      * @see java.sql.DatabaseMetaData
220      */
221     public static Object   extractDatabaseMetaData(DataSource   dataSource, final String   metaDataMethodName)
222             throws MetaDataAccessException {
223 
224         return extractDatabaseMetaData(dataSource,
225                 new DatabaseMetaDataCallback() {
226                     public Object   processMetaData(DatabaseMetaData   dbmd) throws SQLException  , MetaDataAccessException {
227                         try {
228                             Method   method = dbmd.getClass().getMethod(metaDataMethodName, (Class  []) null);
229                             return method.invoke(dbmd, (Object  []) null);
230                         }
231                         catch (NoSuchMethodException   ex) {
232                             throw new MetaDataAccessException("No method named '" + metaDataMethodName +
233                                     "' found on DatabaseMetaData instance [" + dbmd + "]", ex);
234                         }
235                         catch (IllegalAccessException   ex) {
236                             throw new MetaDataAccessException(
237                                     "Could not access DatabaseMetaData method '" + metaDataMethodName + "'", ex);
238                         }
239                         catch (InvocationTargetException   ex) {
240                             if (ex.getTargetException() instanceof SQLException  ) {
241                                 throw (SQLException  ) ex.getTargetException();
242                             }
243                             throw new MetaDataAccessException(
244                                     "Invocation of DatabaseMetaData method '" + metaDataMethodName + "' failed", ex);
245                         }
246                     }
247                 });
248     }
249 
250     /**
251      * Return whether the given JDBC driver supports JDBC 2.0 batch updates.
252      * <p>Typically invoked right before execution of a given set of statements:
253      * to decide whether the set of SQL statements should be executed through
254      * the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion.
255      * <p>Logs a warning if the "supportsBatchUpdates" methods throws an exception
256      * and simply returns <code>false</code> in that case.
257      * @param con the Connection to check
258      * @return whether JDBC 2.0 batch updates are supported
259      * @see java.sql.DatabaseMetaData#supportsBatchUpdates()
260      */
261     public static boolean supportsBatchUpdates(Connection   con) {
262         try {
263             DatabaseMetaData   dbmd = con.getMetaData();
264             if (dbmd != null) {
265                 if (dbmd.supportsBatchUpdates()) {
266                     logger.debug("JDBC driver supports batch updates");
267                     return true;
268                 }
269                 else {
270                     logger.debug("JDBC driver does not support batch updates");
271                 }
272             }
273         }
274         catch (SQLException   ex) {
275             logger.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
276         }
277         catch (AbstractMethodError   err) {
278             logger.debug("JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method", err);
279         }
280         return false;
281     }
282 
283     /**
284      * Check whether the given SQL type is numeric.
285      * @param sqlType the SQL type to be checked
286      * @return whether the type is numeric
287      */
288     public static boolean isNumeric(int sqlType) {
289         return Types.BIT == sqlType || Types.BIGINT == sqlType || Types.DECIMAL == sqlType ||
290                 Types.DOUBLE == sqlType || Types.FLOAT == sqlType || Types.INTEGER == sqlType ||
291                 Types.NUMERIC == sqlType || Types.REAL == sqlType || Types.SMALLINT == sqlType ||
292                 Types.TINYINT == sqlType;
293     }
294 
295 }
296
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags