001/* 002 * Copyright 2002-2020 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 * https://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 017package org.springframework.jdbc.support; 018 019import java.lang.reflect.InvocationTargetException; 020import java.math.BigDecimal; 021import java.sql.Blob; 022import java.sql.Clob; 023import java.sql.Connection; 024import java.sql.DatabaseMetaData; 025import java.sql.ResultSet; 026import java.sql.ResultSetMetaData; 027import java.sql.SQLException; 028import java.sql.SQLFeatureNotSupportedException; 029import java.sql.Statement; 030import java.sql.Types; 031import javax.sql.DataSource; 032 033import org.apache.commons.logging.Log; 034import org.apache.commons.logging.LogFactory; 035 036import org.springframework.jdbc.CannotGetJdbcConnectionException; 037import org.springframework.jdbc.datasource.DataSourceUtils; 038import org.springframework.lang.UsesJava7; 039import org.springframework.util.ClassUtils; 040import org.springframework.util.NumberUtils; 041import org.springframework.util.StringUtils; 042 043/** 044 * Generic utility methods for working with JDBC. Mainly for internal use 045 * within the framework, but also useful for custom JDBC access code. 046 * 047 * @author Thomas Risberg 048 * @author Juergen Hoeller 049 */ 050public abstract class JdbcUtils { 051 052 /** 053 * Constant that indicates an unknown (or unspecified) SQL type. 054 * @see java.sql.Types 055 */ 056 public static final int TYPE_UNKNOWN = Integer.MIN_VALUE; 057 058 059 // Check for JDBC 4.1 getObject(int, Class) method - available on JDK 7 and higher 060 private static final boolean getObjectWithTypeAvailable = 061 ClassUtils.hasMethod(ResultSet.class, "getObject", int.class, Class.class); 062 063 private static final Log logger = LogFactory.getLog(JdbcUtils.class); 064 065 066 /** 067 * Close the given JDBC Connection and ignore any thrown exception. 068 * This is useful for typical finally blocks in manual JDBC code. 069 * @param con the JDBC Connection to close (may be {@code null}) 070 */ 071 public static void closeConnection(Connection con) { 072 if (con != null) { 073 try { 074 con.close(); 075 } 076 catch (SQLException ex) { 077 logger.debug("Could not close JDBC Connection", ex); 078 } 079 catch (Throwable ex) { 080 // We don't trust the JDBC driver: It might throw RuntimeException or Error. 081 logger.debug("Unexpected exception on closing JDBC Connection", ex); 082 } 083 } 084 } 085 086 /** 087 * Close the given JDBC Statement and ignore any thrown exception. 088 * This is useful for typical finally blocks in manual JDBC code. 089 * @param stmt the JDBC Statement to close (may be {@code null}) 090 */ 091 public static void closeStatement(Statement stmt) { 092 if (stmt != null) { 093 try { 094 stmt.close(); 095 } 096 catch (SQLException ex) { 097 logger.trace("Could not close JDBC Statement", ex); 098 } 099 catch (Throwable ex) { 100 // We don't trust the JDBC driver: It might throw RuntimeException or Error. 101 logger.trace("Unexpected exception on closing JDBC Statement", ex); 102 } 103 } 104 } 105 106 /** 107 * Close the given JDBC ResultSet and ignore any thrown exception. 108 * This is useful for typical finally blocks in manual JDBC code. 109 * @param rs the JDBC ResultSet to close (may be {@code null}) 110 */ 111 public static void closeResultSet(ResultSet rs) { 112 if (rs != null) { 113 try { 114 rs.close(); 115 } 116 catch (SQLException ex) { 117 logger.trace("Could not close JDBC ResultSet", ex); 118 } 119 catch (Throwable ex) { 120 // We don't trust the JDBC driver: It might throw RuntimeException or Error. 121 logger.trace("Unexpected exception on closing JDBC ResultSet", ex); 122 } 123 } 124 } 125 126 /** 127 * Retrieve a JDBC column value from a ResultSet, using the specified value type. 128 * <p>Uses the specifically typed ResultSet accessor methods, falling back to 129 * {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types. 130 * <p>Note that the returned value may not be assignable to the specified 131 * required type, in case of an unknown type. Calling code needs to deal 132 * with this case appropriately, e.g. throwing a corresponding exception. 133 * @param rs is the ResultSet holding the data 134 * @param index is the column index 135 * @param requiredType the required value type (may be {@code null}) 136 * @return the value object (possibly not of the specified required type, 137 * with further conversion steps necessary) 138 * @throws SQLException if thrown by the JDBC API 139 * @see #getResultSetValue(ResultSet, int) 140 */ 141 @UsesJava7 // guard optional use of JDBC 4.1 (safe with 1.6 due to getObjectWithTypeAvailable check) 142 public static Object getResultSetValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException { 143 if (requiredType == null) { 144 return getResultSetValue(rs, index); 145 } 146 147 Object value; 148 149 // Explicitly extract typed value, as far as possible. 150 if (String.class == requiredType) { 151 return rs.getString(index); 152 } 153 else if (boolean.class == requiredType || Boolean.class == requiredType) { 154 value = rs.getBoolean(index); 155 } 156 else if (byte.class == requiredType || Byte.class == requiredType) { 157 value = rs.getByte(index); 158 } 159 else if (short.class == requiredType || Short.class == requiredType) { 160 value = rs.getShort(index); 161 } 162 else if (int.class == requiredType || Integer.class == requiredType) { 163 value = rs.getInt(index); 164 } 165 else if (long.class == requiredType || Long.class == requiredType) { 166 value = rs.getLong(index); 167 } 168 else if (float.class == requiredType || Float.class == requiredType) { 169 value = rs.getFloat(index); 170 } 171 else if (double.class == requiredType || Double.class == requiredType || 172 Number.class == requiredType) { 173 value = rs.getDouble(index); 174 } 175 else if (BigDecimal.class == requiredType) { 176 return rs.getBigDecimal(index); 177 } 178 else if (java.sql.Date.class == requiredType) { 179 return rs.getDate(index); 180 } 181 else if (java.sql.Time.class == requiredType) { 182 return rs.getTime(index); 183 } 184 else if (java.sql.Timestamp.class == requiredType || java.util.Date.class == requiredType) { 185 return rs.getTimestamp(index); 186 } 187 else if (byte[].class == requiredType) { 188 return rs.getBytes(index); 189 } 190 else if (Blob.class == requiredType) { 191 return rs.getBlob(index); 192 } 193 else if (Clob.class == requiredType) { 194 return rs.getClob(index); 195 } 196 else if (requiredType.isEnum()) { 197 // Enums can either be represented through a String or an enum index value: 198 // leave enum type conversion up to the caller (e.g. a ConversionService) 199 // but make sure that we return nothing other than a String or an Integer. 200 Object obj = rs.getObject(index); 201 if (obj instanceof String) { 202 return obj; 203 } 204 else if (obj instanceof Number) { 205 // Defensively convert any Number to an Integer (as needed by our 206 // ConversionService's IntegerToEnumConverterFactory) for use as index 207 return NumberUtils.convertNumberToTargetClass((Number) obj, Integer.class); 208 } 209 else { 210 // e.g. on Postgres: getObject returns a PGObject but we need a String 211 return rs.getString(index); 212 } 213 } 214 215 else { 216 // Some unknown type desired -> rely on getObject. 217 if (getObjectWithTypeAvailable) { 218 try { 219 return rs.getObject(index, requiredType); 220 } 221 catch (AbstractMethodError err) { 222 logger.debug("JDBC driver does not implement JDBC 4.1 'getObject(int, Class)' method", err); 223 } 224 catch (SQLFeatureNotSupportedException ex) { 225 logger.debug("JDBC driver does not support JDBC 4.1 'getObject(int, Class)' method", ex); 226 } 227 catch (SQLException ex) { 228 logger.debug("JDBC driver has limited support for JDBC 4.1 'getObject(int, Class)' method", ex); 229 } 230 } 231 232 // Corresponding SQL types for JSR-310 / Joda-Time types, left up 233 // to the caller to convert them (e.g. through a ConversionService). 234 String typeName = requiredType.getSimpleName(); 235 if ("LocalDate".equals(typeName)) { 236 return rs.getDate(index); 237 } 238 else if ("LocalTime".equals(typeName)) { 239 return rs.getTime(index); 240 } 241 else if ("LocalDateTime".equals(typeName)) { 242 return rs.getTimestamp(index); 243 } 244 245 // Fall back to getObject without type specification, again 246 // left up to the caller to convert the value if necessary. 247 return getResultSetValue(rs, index); 248 } 249 250 // Perform was-null check if necessary (for results that the JDBC driver returns as primitives). 251 return (rs.wasNull() ? null : value); 252 } 253 254 /** 255 * Retrieve a JDBC column value from a ResultSet, using the most appropriate 256 * value type. The returned value should be a detached value object, not having 257 * any ties to the active ResultSet: in particular, it should not be a Blob or 258 * Clob object but rather a byte array or String representation, respectively. 259 * <p>Uses the {@code getObject(index)} method, but includes additional "hacks" 260 * to get around Oracle 10g returning a non-standard object for its TIMESTAMP 261 * datatype and a {@code java.sql.Date} for DATE columns leaving out the 262 * time portion: These columns will explicitly be extracted as standard 263 * {@code java.sql.Timestamp} object. 264 * @param rs is the ResultSet holding the data 265 * @param index is the column index 266 * @return the value object 267 * @throws SQLException if thrown by the JDBC API 268 * @see java.sql.Blob 269 * @see java.sql.Clob 270 * @see java.sql.Timestamp 271 */ 272 public static Object getResultSetValue(ResultSet rs, int index) throws SQLException { 273 Object obj = rs.getObject(index); 274 String className = null; 275 if (obj != null) { 276 className = obj.getClass().getName(); 277 } 278 if (obj instanceof Blob) { 279 Blob blob = (Blob) obj; 280 obj = blob.getBytes(1, (int) blob.length()); 281 } 282 else if (obj instanceof Clob) { 283 Clob clob = (Clob) obj; 284 obj = clob.getSubString(1, (int) clob.length()); 285 } 286 else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) { 287 obj = rs.getTimestamp(index); 288 } 289 else if (className != null && className.startsWith("oracle.sql.DATE")) { 290 String metaDataClassName = rs.getMetaData().getColumnClassName(index); 291 if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) { 292 obj = rs.getTimestamp(index); 293 } 294 else { 295 obj = rs.getDate(index); 296 } 297 } 298 else if (obj instanceof java.sql.Date) { 299 if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) { 300 obj = rs.getTimestamp(index); 301 } 302 } 303 return obj; 304 } 305 306 /** 307 * Extract database meta-data via the given DatabaseMetaDataCallback. 308 * <p>This method will open a connection to the database and retrieve its meta-data. 309 * Since this method is called before the exception translation feature is configured 310 * for a DataSource, this method can not rely on SQLException translation itself. 311 * <p>Any exceptions will be wrapped in a MetaDataAccessException. This is a checked 312 * exception and any calling code should catch and handle this exception. You can just 313 * log the error and hope for the best, but there is probably a more serious error that 314 * will reappear when you try to access the database again. 315 * @param dataSource the DataSource to extract meta-data for 316 * @param action callback that will do the actual work 317 * @return object containing the extracted information, as returned by 318 * the DatabaseMetaDataCallback's {@code processMetaData} method 319 * @throws MetaDataAccessException if meta-data access failed 320 * @see java.sql.DatabaseMetaData 321 */ 322 public static Object extractDatabaseMetaData(DataSource dataSource, DatabaseMetaDataCallback action) 323 throws MetaDataAccessException { 324 325 Connection con = null; 326 try { 327 con = DataSourceUtils.getConnection(dataSource); 328 DatabaseMetaData metaData; 329 try { 330 metaData = con.getMetaData(); 331 } 332 catch (SQLException ex) { 333 if (DataSourceUtils.isConnectionTransactional(con, dataSource)) { 334 // Probably a closed thread-bound Connection - retry against fresh Connection 335 DataSourceUtils.releaseConnection(con, dataSource); 336 con = null; 337 logger.debug("Failed to obtain DatabaseMetaData from transactional Connection - " + 338 "retrying against fresh Connection", ex); 339 con = dataSource.getConnection(); 340 metaData = con.getMetaData(); 341 } 342 else { 343 throw ex; 344 } 345 } 346 if (metaData == null) { 347 // should only happen in test environments 348 throw new MetaDataAccessException("DatabaseMetaData returned by Connection [" + con + "] was null"); 349 } 350 return action.processMetaData(metaData); 351 } 352 catch (CannotGetJdbcConnectionException ex) { 353 throw new MetaDataAccessException("Could not get Connection for extracting meta-data", ex); 354 } 355 catch (SQLException ex) { 356 throw new MetaDataAccessException("Error while extracting DatabaseMetaData", ex); 357 } 358 catch (AbstractMethodError err) { 359 throw new MetaDataAccessException( 360 "JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err); 361 } 362 finally { 363 DataSourceUtils.releaseConnection(con, dataSource); 364 } 365 } 366 367 /** 368 * Call the specified method on DatabaseMetaData for the given DataSource, 369 * and extract the invocation result. 370 * @param dataSource the DataSource to extract meta-data for 371 * @param metaDataMethodName the name of the DatabaseMetaData method to call 372 * @return the object returned by the specified DatabaseMetaData method 373 * @throws MetaDataAccessException if we couldn't access the DatabaseMetaData 374 * or failed to invoke the specified method 375 * @see java.sql.DatabaseMetaData 376 */ 377 public static Object extractDatabaseMetaData(DataSource dataSource, final String metaDataMethodName) 378 throws MetaDataAccessException { 379 380 return extractDatabaseMetaData(dataSource, 381 new DatabaseMetaDataCallback() { 382 @Override 383 public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException { 384 try { 385 return DatabaseMetaData.class.getMethod(metaDataMethodName).invoke(dbmd); 386 } 387 catch (NoSuchMethodException ex) { 388 throw new MetaDataAccessException("No method named '" + metaDataMethodName + 389 "' found on DatabaseMetaData instance [" + dbmd + "]", ex); 390 } 391 catch (IllegalAccessException ex) { 392 throw new MetaDataAccessException( 393 "Could not access DatabaseMetaData method '" + metaDataMethodName + "'", ex); 394 } 395 catch (InvocationTargetException ex) { 396 if (ex.getTargetException() instanceof SQLException) { 397 throw (SQLException) ex.getTargetException(); 398 } 399 throw new MetaDataAccessException( 400 "Invocation of DatabaseMetaData method '" + metaDataMethodName + "' failed", ex); 401 } 402 } 403 }); 404 } 405 406 /** 407 * Return whether the given JDBC driver supports JDBC 2.0 batch updates. 408 * <p>Typically invoked right before execution of a given set of statements: 409 * to decide whether the set of SQL statements should be executed through 410 * the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion. 411 * <p>Logs a warning if the "supportsBatchUpdates" methods throws an exception 412 * and simply returns {@code false} in that case. 413 * @param con the Connection to check 414 * @return whether JDBC 2.0 batch updates are supported 415 * @see java.sql.DatabaseMetaData#supportsBatchUpdates() 416 */ 417 public static boolean supportsBatchUpdates(Connection con) { 418 try { 419 DatabaseMetaData dbmd = con.getMetaData(); 420 if (dbmd != null) { 421 if (dbmd.supportsBatchUpdates()) { 422 logger.debug("JDBC driver supports batch updates"); 423 return true; 424 } 425 else { 426 logger.debug("JDBC driver does not support batch updates"); 427 } 428 } 429 } 430 catch (SQLException ex) { 431 logger.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex); 432 } 433 return false; 434 } 435 436 /** 437 * Extract a common name for the target database in use even if 438 * various drivers/platforms provide varying names at runtime. 439 * @param source the name as provided in database meta-data 440 * @return the common name to be used (e.g. "DB2" or "Sybase") 441 */ 442 public static String commonDatabaseName(String source) { 443 String name = source; 444 if (source != null && source.startsWith("DB2")) { 445 name = "DB2"; 446 } 447 else if ("Sybase SQL Server".equals(source) || 448 "Adaptive Server Enterprise".equals(source) || 449 "ASE".equals(source) || 450 "sql server".equalsIgnoreCase(source) ) { 451 name = "Sybase"; 452 } 453 return name; 454 } 455 456 /** 457 * Check whether the given SQL type is numeric. 458 * @param sqlType the SQL type to be checked 459 * @return whether the type is numeric 460 */ 461 public static boolean isNumeric(int sqlType) { 462 return (Types.BIT == sqlType || Types.BIGINT == sqlType || Types.DECIMAL == sqlType || 463 Types.DOUBLE == sqlType || Types.FLOAT == sqlType || Types.INTEGER == sqlType || 464 Types.NUMERIC == sqlType || Types.REAL == sqlType || Types.SMALLINT == sqlType || 465 Types.TINYINT == sqlType); 466 } 467 468 /** 469 * Determine the column name to use. The column name is determined based on a 470 * lookup using ResultSetMetaData. 471 * <p>This method implementation takes into account recent clarifications 472 * expressed in the JDBC 4.0 specification: 473 * <p><i>columnLabel - the label for the column specified with the SQL AS clause. 474 * If the SQL AS clause was not specified, then the label is the name of the column</i>. 475 * @param resultSetMetaData the current meta-data to use 476 * @param columnIndex the index of the column for the look up 477 * @return the column name to use 478 * @throws SQLException in case of lookup failure 479 */ 480 public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException { 481 String name = resultSetMetaData.getColumnLabel(columnIndex); 482 if (!StringUtils.hasLength(name)) { 483 name = resultSetMetaData.getColumnName(columnIndex); 484 } 485 return name; 486 } 487 488 /** 489 * Convert a column name with underscores to the corresponding property name using "camel case". 490 * A name like "customer_number" would match a "customerNumber" property name. 491 * @param name the column name to be converted 492 * @return the name using "camel case" 493 */ 494 public static String convertUnderscoreNameToPropertyName(String name) { 495 StringBuilder result = new StringBuilder(); 496 boolean nextIsUpper = false; 497 if (name != null && name.length() > 0) { 498 if (name.length() > 1 && name.charAt(1) == '_') { 499 result.append(Character.toUpperCase(name.charAt(0))); 500 } 501 else { 502 result.append(Character.toLowerCase(name.charAt(0))); 503 } 504 for (int i = 1; i < name.length(); i++) { 505 char c = name.charAt(i); 506 if (c == '_') { 507 nextIsUpper = true; 508 } 509 else { 510 if (nextIsUpper) { 511 result.append(Character.toUpperCase(c)); 512 nextIsUpper = false; 513 } 514 else { 515 result.append(Character.toLowerCase(c)); 516 } 517 } 518 } 519 } 520 return result.toString(); 521 } 522 523}