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.core; 018 019import java.io.StringReader; 020import java.io.StringWriter; 021import java.math.BigDecimal; 022import java.math.BigInteger; 023import java.sql.Blob; 024import java.sql.Clob; 025import java.sql.DatabaseMetaData; 026import java.sql.PreparedStatement; 027import java.sql.SQLException; 028import java.sql.SQLFeatureNotSupportedException; 029import java.sql.Types; 030import java.util.Arrays; 031import java.util.Calendar; 032import java.util.Collection; 033import java.util.Collections; 034import java.util.HashMap; 035import java.util.Map; 036import java.util.Set; 037import java.util.concurrent.ConcurrentHashMap; 038 039import org.apache.commons.logging.Log; 040import org.apache.commons.logging.LogFactory; 041 042import org.springframework.core.SpringProperties; 043import org.springframework.jdbc.support.SqlValue; 044 045/** 046 * Utility methods for PreparedStatementSetter/Creator and CallableStatementCreator 047 * implementations, providing sophisticated parameter management (including support 048 * for LOB values). 049 * 050 * <p>Used by PreparedStatementCreatorFactory and CallableStatementCreatorFactory, 051 * but also available for direct use in custom setter/creator implementations. 052 * 053 * @author Thomas Risberg 054 * @author Juergen Hoeller 055 * @since 1.1 056 * @see PreparedStatementSetter 057 * @see PreparedStatementCreator 058 * @see CallableStatementCreator 059 * @see PreparedStatementCreatorFactory 060 * @see CallableStatementCreatorFactory 061 * @see SqlParameter 062 * @see SqlTypeValue 063 * @see org.springframework.jdbc.core.support.SqlLobValue 064 */ 065public abstract class StatementCreatorUtils { 066 067 /** 068 * System property that instructs Spring to ignore {@link java.sql.ParameterMetaData#getParameterType} 069 * completely, i.e. to never even attempt to retrieve {@link PreparedStatement#getParameterMetaData()} 070 * for {@link StatementCreatorUtils#setNull} calls. 071 * <p>The effective default is "false", trying {@code getParameterType} calls first and falling back to 072 * {@link PreparedStatement#setNull} / {@link PreparedStatement#setObject} calls based on well-known 073 * behavior of common databases. Spring records JDBC drivers with non-working {@code getParameterType} 074 * implementations and won't attempt to call that method for that driver again, always falling back. 075 * <p>Consider switching this flag to "true" if you experience misbehavior at runtime, 076 * e.g. with connection pool issues in case of an exception thrown from {@code getParameterType} 077 * (as reported on JBoss AS 7) or in case of performance problems (as reported on PostgreSQL). 078 * <p>Note that this flag is "true" by default on Oracle 12c since there can be leaks created by 079 * {@code getParameterType} calls in such a scenario. You need to explicitly set the flag to 080 * "false" in order to enforce the use of {@code getParameterType} against Oracle drivers. 081 */ 082 public static final String IGNORE_GETPARAMETERTYPE_PROPERTY_NAME = "spring.jdbc.getParameterType.ignore"; 083 084 085 static final Boolean shouldIgnoreGetParameterType; 086 087 static final Set<String> driversWithNoSupportForGetParameterType = 088 Collections.newSetFromMap(new ConcurrentHashMap<String, Boolean>(1)); 089 090 private static final Log logger = LogFactory.getLog(StatementCreatorUtils.class); 091 092 private static final Map<Class<?>, Integer> javaTypeToSqlTypeMap = new HashMap<Class<?>, Integer>(32); 093 094 static { 095 String propVal = SpringProperties.getProperty(IGNORE_GETPARAMETERTYPE_PROPERTY_NAME); 096 shouldIgnoreGetParameterType = (propVal != null ? Boolean.valueOf(propVal) : null); 097 098 javaTypeToSqlTypeMap.put(boolean.class, Types.BOOLEAN); 099 javaTypeToSqlTypeMap.put(Boolean.class, Types.BOOLEAN); 100 javaTypeToSqlTypeMap.put(byte.class, Types.TINYINT); 101 javaTypeToSqlTypeMap.put(Byte.class, Types.TINYINT); 102 javaTypeToSqlTypeMap.put(short.class, Types.SMALLINT); 103 javaTypeToSqlTypeMap.put(Short.class, Types.SMALLINT); 104 javaTypeToSqlTypeMap.put(int.class, Types.INTEGER); 105 javaTypeToSqlTypeMap.put(Integer.class, Types.INTEGER); 106 javaTypeToSqlTypeMap.put(long.class, Types.BIGINT); 107 javaTypeToSqlTypeMap.put(Long.class, Types.BIGINT); 108 javaTypeToSqlTypeMap.put(BigInteger.class, Types.BIGINT); 109 javaTypeToSqlTypeMap.put(float.class, Types.FLOAT); 110 javaTypeToSqlTypeMap.put(Float.class, Types.FLOAT); 111 javaTypeToSqlTypeMap.put(double.class, Types.DOUBLE); 112 javaTypeToSqlTypeMap.put(Double.class, Types.DOUBLE); 113 javaTypeToSqlTypeMap.put(BigDecimal.class, Types.DECIMAL); 114 javaTypeToSqlTypeMap.put(java.sql.Date.class, Types.DATE); 115 javaTypeToSqlTypeMap.put(java.sql.Time.class, Types.TIME); 116 javaTypeToSqlTypeMap.put(java.sql.Timestamp.class, Types.TIMESTAMP); 117 javaTypeToSqlTypeMap.put(Blob.class, Types.BLOB); 118 javaTypeToSqlTypeMap.put(Clob.class, Types.CLOB); 119 } 120 121 122 /** 123 * Derive a default SQL type from the given Java type. 124 * @param javaType the Java type to translate 125 * @return the corresponding SQL type, or {@link SqlTypeValue#TYPE_UNKNOWN} if none found 126 */ 127 public static int javaTypeToSqlParameterType(Class<?> javaType) { 128 Integer sqlType = javaTypeToSqlTypeMap.get(javaType); 129 if (sqlType != null) { 130 return sqlType; 131 } 132 if (Number.class.isAssignableFrom(javaType)) { 133 return Types.NUMERIC; 134 } 135 if (isStringValue(javaType)) { 136 return Types.VARCHAR; 137 } 138 if (isDateValue(javaType) || Calendar.class.isAssignableFrom(javaType)) { 139 return Types.TIMESTAMP; 140 } 141 return SqlTypeValue.TYPE_UNKNOWN; 142 } 143 144 /** 145 * Set the value for a parameter. The method used is based on the SQL type 146 * of the parameter and we can handle complex types like arrays and LOBs. 147 * @param ps the prepared statement or callable statement 148 * @param paramIndex index of the parameter we are setting 149 * @param param the parameter as it is declared including type 150 * @param inValue the value to set 151 * @throws SQLException if thrown by PreparedStatement methods 152 */ 153 public static void setParameterValue(PreparedStatement ps, int paramIndex, SqlParameter param, Object inValue) 154 throws SQLException { 155 156 setParameterValueInternal(ps, paramIndex, param.getSqlType(), param.getTypeName(), param.getScale(), inValue); 157 } 158 159 /** 160 * Set the value for a parameter. The method used is based on the SQL type 161 * of the parameter and we can handle complex types like arrays and LOBs. 162 * @param ps the prepared statement or callable statement 163 * @param paramIndex index of the parameter we are setting 164 * @param sqlType the SQL type of the parameter 165 * @param inValue the value to set (plain value or a SqlTypeValue) 166 * @throws SQLException if thrown by PreparedStatement methods 167 * @see SqlTypeValue 168 */ 169 public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType, Object inValue) 170 throws SQLException { 171 172 setParameterValueInternal(ps, paramIndex, sqlType, null, null, inValue); 173 } 174 175 /** 176 * Set the value for a parameter. The method used is based on the SQL type 177 * of the parameter and we can handle complex types like arrays and LOBs. 178 * @param ps the prepared statement or callable statement 179 * @param paramIndex index of the parameter we are setting 180 * @param sqlType the SQL type of the parameter 181 * @param typeName the type name of the parameter 182 * (optional, only used for SQL NULL and SqlTypeValue) 183 * @param inValue the value to set (plain value or a SqlTypeValue) 184 * @throws SQLException if thrown by PreparedStatement methods 185 * @see SqlTypeValue 186 */ 187 public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, 188 Object inValue) throws SQLException { 189 190 setParameterValueInternal(ps, paramIndex, sqlType, typeName, null, inValue); 191 } 192 193 /** 194 * Set the value for a parameter. The method used is based on the SQL type 195 * of the parameter and we can handle complex types like arrays and LOBs. 196 * @param ps the prepared statement or callable statement 197 * @param paramIndex index of the parameter we are setting 198 * @param sqlType the SQL type of the parameter 199 * @param typeName the type name of the parameter 200 * (optional, only used for SQL NULL and SqlTypeValue) 201 * @param scale the number of digits after the decimal point 202 * (for DECIMAL and NUMERIC types) 203 * @param inValue the value to set (plain value or a SqlTypeValue) 204 * @throws SQLException if thrown by PreparedStatement methods 205 * @see SqlTypeValue 206 */ 207 private static void setParameterValueInternal(PreparedStatement ps, int paramIndex, int sqlType, 208 String typeName, Integer scale, Object inValue) throws SQLException { 209 210 String typeNameToUse = typeName; 211 int sqlTypeToUse = sqlType; 212 Object inValueToUse = inValue; 213 214 // override type info? 215 if (inValue instanceof SqlParameterValue) { 216 SqlParameterValue parameterValue = (SqlParameterValue) inValue; 217 if (logger.isDebugEnabled()) { 218 logger.debug("Overriding type info with runtime info from SqlParameterValue: column index " + paramIndex + 219 ", SQL type " + parameterValue.getSqlType() + ", type name " + parameterValue.getTypeName()); 220 } 221 if (parameterValue.getSqlType() != SqlTypeValue.TYPE_UNKNOWN) { 222 sqlTypeToUse = parameterValue.getSqlType(); 223 } 224 if (parameterValue.getTypeName() != null) { 225 typeNameToUse = parameterValue.getTypeName(); 226 } 227 inValueToUse = parameterValue.getValue(); 228 } 229 230 if (logger.isTraceEnabled()) { 231 logger.trace("Setting SQL statement parameter value: column index " + paramIndex + 232 ", parameter value [" + inValueToUse + 233 "], value class [" + (inValueToUse != null ? inValueToUse.getClass().getName() : "null") + 234 "], SQL type " + (sqlTypeToUse == SqlTypeValue.TYPE_UNKNOWN ? "unknown" : Integer.toString(sqlTypeToUse))); 235 } 236 237 if (inValueToUse == null) { 238 setNull(ps, paramIndex, sqlTypeToUse, typeNameToUse); 239 } 240 else { 241 setValue(ps, paramIndex, sqlTypeToUse, typeNameToUse, scale, inValueToUse); 242 } 243 } 244 245 /** 246 * Set the specified PreparedStatement parameter to null, 247 * respecting database-specific peculiarities. 248 */ 249 private static void setNull(PreparedStatement ps, int paramIndex, int sqlType, String typeName) throws SQLException { 250 if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER && typeName == null)) { 251 boolean useSetObject = false; 252 Integer sqlTypeToUse = null; 253 DatabaseMetaData dbmd = null; 254 String jdbcDriverName = null; 255 boolean tryGetParameterType = true; 256 257 if (shouldIgnoreGetParameterType == null) { 258 try { 259 dbmd = ps.getConnection().getMetaData(); 260 jdbcDriverName = dbmd.getDriverName(); 261 tryGetParameterType = !driversWithNoSupportForGetParameterType.contains(jdbcDriverName); 262 if (tryGetParameterType && jdbcDriverName.startsWith("Oracle")) { 263 // Avoid getParameterType use with Oracle 12c driver by default: 264 // needs to be explicitly activated through spring.jdbc.getParameterType.ignore=false 265 tryGetParameterType = false; 266 driversWithNoSupportForGetParameterType.add(jdbcDriverName); 267 } 268 } 269 catch (Throwable ex) { 270 logger.debug("Could not check connection metadata", ex); 271 } 272 } 273 else { 274 tryGetParameterType = !shouldIgnoreGetParameterType; 275 } 276 277 if (tryGetParameterType) { 278 try { 279 sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex); 280 } 281 catch (Throwable ex) { 282 if (logger.isDebugEnabled()) { 283 logger.debug("JDBC 3.0 getParameterType call not supported - using fallback method instead: " + ex); 284 } 285 } 286 } 287 288 if (sqlTypeToUse == null) { 289 // JDBC driver not compliant with JDBC 3.0 -> proceed with database-specific checks 290 sqlTypeToUse = Types.NULL; 291 try { 292 if (dbmd == null) { 293 dbmd = ps.getConnection().getMetaData(); 294 } 295 if (jdbcDriverName == null) { 296 jdbcDriverName = dbmd.getDriverName(); 297 } 298 if (shouldIgnoreGetParameterType == null) { 299 // Register JDBC driver with no support for getParameterType, except for the 300 // Oracle 12c driver where getParameterType fails for specific statements only 301 // (so an exception thrown above does not indicate general lack of support). 302 driversWithNoSupportForGetParameterType.add(jdbcDriverName); 303 } 304 String databaseProductName = dbmd.getDatabaseProductName(); 305 if (databaseProductName.startsWith("Informix") || 306 (jdbcDriverName.startsWith("Microsoft") && jdbcDriverName.contains("SQL Server"))) { 307 // "Microsoft SQL Server JDBC Driver 3.0" versus "Microsoft JDBC Driver 4.0 for SQL Server" 308 useSetObject = true; 309 } 310 else if (databaseProductName.startsWith("DB2") || 311 jdbcDriverName.startsWith("jConnect") || 312 jdbcDriverName.startsWith("SQLServer") || 313 jdbcDriverName.startsWith("Apache Derby")) { 314 sqlTypeToUse = Types.VARCHAR; 315 } 316 } 317 catch (Throwable ex) { 318 logger.debug("Could not check connection metadata", ex); 319 } 320 } 321 if (useSetObject) { 322 ps.setObject(paramIndex, null); 323 } 324 else { 325 ps.setNull(paramIndex, sqlTypeToUse); 326 } 327 } 328 else if (typeName != null) { 329 ps.setNull(paramIndex, sqlType, typeName); 330 } 331 else { 332 ps.setNull(paramIndex, sqlType); 333 } 334 } 335 336 private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, 337 Integer scale, Object inValue) throws SQLException { 338 339 if (inValue instanceof SqlTypeValue) { 340 ((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName); 341 } 342 else if (inValue instanceof SqlValue) { 343 ((SqlValue) inValue).setValue(ps, paramIndex); 344 } 345 else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR ) { 346 ps.setString(paramIndex, inValue.toString()); 347 } 348 else if (sqlType == Types.NVARCHAR || sqlType == Types.LONGNVARCHAR) { 349 ps.setNString(paramIndex, inValue.toString()); 350 } 351 else if ((sqlType == Types.CLOB || sqlType == Types.NCLOB) && isStringValue(inValue.getClass())) { 352 String strVal = inValue.toString(); 353 if (strVal.length() > 4000) { 354 // Necessary for older Oracle drivers, in particular when running against an Oracle 10 database. 355 // Should also work fine against other drivers/databases since it uses standard JDBC 4.0 API. 356 try { 357 if (sqlType == Types.NCLOB) { 358 ps.setNClob(paramIndex, new StringReader(strVal), strVal.length()); 359 } 360 else { 361 ps.setClob(paramIndex, new StringReader(strVal), strVal.length()); 362 } 363 return; 364 } 365 catch (AbstractMethodError err) { 366 logger.debug("JDBC driver does not implement JDBC 4.0 'setClob(int, Reader, long)' method", err); 367 } 368 catch (SQLFeatureNotSupportedException ex) { 369 logger.debug("JDBC driver does not support JDBC 4.0 'setClob(int, Reader, long)' method", ex); 370 } 371 } 372 // Fallback: setString or setNString binding 373 if (sqlType == Types.NCLOB) { 374 ps.setNString(paramIndex, strVal); 375 } 376 else { 377 ps.setString(paramIndex, strVal); 378 } 379 } 380 else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) { 381 if (inValue instanceof BigDecimal) { 382 ps.setBigDecimal(paramIndex, (BigDecimal) inValue); 383 } 384 else if (scale != null) { 385 ps.setObject(paramIndex, inValue, sqlType, scale); 386 } 387 else { 388 ps.setObject(paramIndex, inValue, sqlType); 389 } 390 } 391 else if (sqlType == Types.BOOLEAN) { 392 if (inValue instanceof Boolean) { 393 ps.setBoolean(paramIndex, (Boolean) inValue); 394 } 395 else { 396 ps.setObject(paramIndex, inValue, Types.BOOLEAN); 397 } 398 } 399 else if (sqlType == Types.DATE) { 400 if (inValue instanceof java.util.Date) { 401 if (inValue instanceof java.sql.Date) { 402 ps.setDate(paramIndex, (java.sql.Date) inValue); 403 } 404 else { 405 ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime())); 406 } 407 } 408 else if (inValue instanceof Calendar) { 409 Calendar cal = (Calendar) inValue; 410 ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal); 411 } 412 else { 413 ps.setObject(paramIndex, inValue, Types.DATE); 414 } 415 } 416 else if (sqlType == Types.TIME) { 417 if (inValue instanceof java.util.Date) { 418 if (inValue instanceof java.sql.Time) { 419 ps.setTime(paramIndex, (java.sql.Time) inValue); 420 } 421 else { 422 ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime())); 423 } 424 } 425 else if (inValue instanceof Calendar) { 426 Calendar cal = (Calendar) inValue; 427 ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal); 428 } 429 else { 430 ps.setObject(paramIndex, inValue, Types.TIME); 431 } 432 } 433 else if (sqlType == Types.TIMESTAMP) { 434 if (inValue instanceof java.util.Date) { 435 if (inValue instanceof java.sql.Timestamp) { 436 ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue); 437 } 438 else { 439 ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime())); 440 } 441 } 442 else if (inValue instanceof Calendar) { 443 Calendar cal = (Calendar) inValue; 444 ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal); 445 } 446 else { 447 ps.setObject(paramIndex, inValue, Types.TIMESTAMP); 448 } 449 } 450 else if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER && 451 "Oracle".equals(ps.getConnection().getMetaData().getDatabaseProductName()))) { 452 if (isStringValue(inValue.getClass())) { 453 ps.setString(paramIndex, inValue.toString()); 454 } 455 else if (isDateValue(inValue.getClass())) { 456 ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime())); 457 } 458 else if (inValue instanceof Calendar) { 459 Calendar cal = (Calendar) inValue; 460 ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal); 461 } 462 else { 463 // Fall back to generic setObject call without SQL type specified. 464 ps.setObject(paramIndex, inValue); 465 } 466 } 467 else { 468 // Fall back to generic setObject call with SQL type specified. 469 ps.setObject(paramIndex, inValue, sqlType); 470 } 471 } 472 473 /** 474 * Check whether the given value can be treated as a String value. 475 */ 476 private static boolean isStringValue(Class<?> inValueType) { 477 // Consider any CharSequence (including StringBuffer and StringBuilder) as a String. 478 return (CharSequence.class.isAssignableFrom(inValueType) || 479 StringWriter.class.isAssignableFrom(inValueType)); 480 } 481 482 /** 483 * Check whether the given value is a {@code java.util.Date} 484 * (but not one of the JDBC-specific subclasses). 485 */ 486 private static boolean isDateValue(Class<?> inValueType) { 487 return (java.util.Date.class.isAssignableFrom(inValueType) && 488 !(java.sql.Date.class.isAssignableFrom(inValueType) || 489 java.sql.Time.class.isAssignableFrom(inValueType) || 490 java.sql.Timestamp.class.isAssignableFrom(inValueType))); 491 } 492 493 /** 494 * Clean up all resources held by parameter values which were passed to an 495 * execute method. This is for example important for closing LOB values. 496 * @param paramValues parameter values supplied. May be {@code null}. 497 * @see DisposableSqlTypeValue#cleanup() 498 * @see org.springframework.jdbc.core.support.SqlLobValue#cleanup() 499 */ 500 public static void cleanupParameters(Object... paramValues) { 501 if (paramValues != null) { 502 cleanupParameters(Arrays.asList(paramValues)); 503 } 504 } 505 506 /** 507 * Clean up all resources held by parameter values which were passed to an 508 * execute method. This is for example important for closing LOB values. 509 * @param paramValues parameter values supplied. May be {@code null}. 510 * @see DisposableSqlTypeValue#cleanup() 511 * @see org.springframework.jdbc.core.support.SqlLobValue#cleanup() 512 */ 513 public static void cleanupParameters(Collection<?> paramValues) { 514 if (paramValues != null) { 515 for (Object inValue : paramValues) { 516 // Unwrap SqlParameterValue first... 517 if (inValue instanceof SqlParameterValue) { 518 inValue = ((SqlParameterValue) inValue).getValue(); 519 } 520 // Check for disposable value types 521 if (inValue instanceof SqlValue) { 522 ((SqlValue) inValue).cleanup(); 523 } 524 else if (inValue instanceof DisposableSqlTypeValue) { 525 ((DisposableSqlTypeValue) inValue).cleanup(); 526 } 527 } 528 } 529 } 530 531}