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