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