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.metadata; 018 019import java.sql.DatabaseMetaData; 020import java.util.ArrayList; 021import java.util.HashMap; 022import java.util.HashSet; 023import java.util.LinkedHashMap; 024import java.util.List; 025import java.util.Locale; 026import java.util.Map; 027import java.util.Set; 028 029import javax.sql.DataSource; 030 031import org.apache.commons.logging.Log; 032import org.apache.commons.logging.LogFactory; 033 034import org.springframework.dao.InvalidDataAccessApiUsageException; 035import org.springframework.jdbc.core.RowMapper; 036import org.springframework.jdbc.core.SqlOutParameter; 037import org.springframework.jdbc.core.SqlParameter; 038import org.springframework.jdbc.core.SqlParameterValue; 039import org.springframework.jdbc.core.SqlReturnResultSet; 040import org.springframework.jdbc.core.namedparam.SqlParameterSource; 041import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils; 042import org.springframework.jdbc.support.JdbcUtils; 043import org.springframework.lang.Nullable; 044import org.springframework.util.Assert; 045import org.springframework.util.StringUtils; 046 047/** 048 * Class to manage context meta-data used for the configuration 049 * and execution of a stored procedure call. 050 * 051 * @author Thomas Risberg 052 * @author Juergen Hoeller 053 * @author Kiril Nugmanov 054 * @since 2.5 055 */ 056public class CallMetaDataContext { 057 058 // Logger available to subclasses 059 protected final Log logger = LogFactory.getLog(getClass()); 060 061 // Name of procedure to call 062 @Nullable 063 private String procedureName; 064 065 // Name of catalog for call 066 @Nullable 067 private String catalogName; 068 069 // Name of schema for call 070 @Nullable 071 private String schemaName; 072 073 // List of SqlParameter objects to be used in call execution 074 private List<SqlParameter> callParameters = new ArrayList<>(); 075 076 // Actual name to use for the return value in the output map 077 @Nullable 078 private String actualFunctionReturnName; 079 080 // Set of in parameter names to exclude use for any not listed 081 private Set<String> limitedInParameterNames = new HashSet<>(); 082 083 // List of SqlParameter names for out parameters 084 private List<String> outParameterNames = new ArrayList<>(); 085 086 // Indicates whether this is a procedure or a function 087 private boolean function = false; 088 089 // Indicates whether this procedure's return value should be included 090 private boolean returnValueRequired = false; 091 092 // Should we access call parameter meta-data info or not 093 private boolean accessCallParameterMetaData = true; 094 095 // Should we bind parameter by name 096 private boolean namedBinding; 097 098 // The provider of call meta-data 099 @Nullable 100 private CallMetaDataProvider metaDataProvider; 101 102 103 /** 104 * Specify the name used for the return value of the function. 105 */ 106 public void setFunctionReturnName(String functionReturnName) { 107 this.actualFunctionReturnName = functionReturnName; 108 } 109 110 /** 111 * Get the name used for the return value of the function. 112 */ 113 public String getFunctionReturnName() { 114 return (this.actualFunctionReturnName != null ? this.actualFunctionReturnName : "return"); 115 } 116 117 /** 118 * Specify a limited set of in parameters to be used. 119 */ 120 public void setLimitedInParameterNames(Set<String> limitedInParameterNames) { 121 this.limitedInParameterNames = limitedInParameterNames; 122 } 123 124 /** 125 * Get a limited set of in parameters to be used. 126 */ 127 public Set<String> getLimitedInParameterNames() { 128 return this.limitedInParameterNames; 129 } 130 131 /** 132 * Specify the names of the out parameters. 133 */ 134 public void setOutParameterNames(List<String> outParameterNames) { 135 this.outParameterNames = outParameterNames; 136 } 137 138 /** 139 * Get a list of the out parameter names. 140 */ 141 public List<String> getOutParameterNames() { 142 return this.outParameterNames; 143 } 144 145 /** 146 * Specify the name of the procedure. 147 */ 148 public void setProcedureName(@Nullable String procedureName) { 149 this.procedureName = procedureName; 150 } 151 152 /** 153 * Get the name of the procedure. 154 */ 155 @Nullable 156 public String getProcedureName() { 157 return this.procedureName; 158 } 159 160 /** 161 * Specify the name of the catalog. 162 */ 163 public void setCatalogName(@Nullable String catalogName) { 164 this.catalogName = catalogName; 165 } 166 167 /** 168 * Get the name of the catalog. 169 */ 170 @Nullable 171 public String getCatalogName() { 172 return this.catalogName; 173 } 174 175 /** 176 * Specify the name of the schema. 177 */ 178 public void setSchemaName(@Nullable String schemaName) { 179 this.schemaName = schemaName; 180 } 181 182 /** 183 * Get the name of the schema. 184 */ 185 @Nullable 186 public String getSchemaName() { 187 return this.schemaName; 188 } 189 190 /** 191 * Specify whether this call is a function call. 192 */ 193 public void setFunction(boolean function) { 194 this.function = function; 195 } 196 197 /** 198 * Check whether this call is a function call. 199 */ 200 public boolean isFunction() { 201 return this.function; 202 } 203 204 /** 205 * Specify whether a return value is required. 206 */ 207 public void setReturnValueRequired(boolean returnValueRequired) { 208 this.returnValueRequired = returnValueRequired; 209 } 210 211 /** 212 * Check whether a return value is required. 213 */ 214 public boolean isReturnValueRequired() { 215 return this.returnValueRequired; 216 } 217 218 /** 219 * Specify whether call parameter meta-data should be accessed. 220 */ 221 public void setAccessCallParameterMetaData(boolean accessCallParameterMetaData) { 222 this.accessCallParameterMetaData = accessCallParameterMetaData; 223 } 224 225 /** 226 * Check whether call parameter meta-data should be accessed. 227 */ 228 public boolean isAccessCallParameterMetaData() { 229 return this.accessCallParameterMetaData; 230 } 231 232 /** 233 * Specify whether parameters should be bound by name. 234 * @since 4.2 235 */ 236 public void setNamedBinding(boolean namedBinding) { 237 this.namedBinding = namedBinding; 238 } 239 240 /** 241 * Check whether parameters should be bound by name. 242 * @since 4.2 243 */ 244 public boolean isNamedBinding() { 245 return this.namedBinding; 246 } 247 248 249 /** 250 * Initialize this class with meta-data from the database. 251 * @param dataSource the DataSource used to retrieve meta-data 252 */ 253 public void initializeMetaData(DataSource dataSource) { 254 this.metaDataProvider = CallMetaDataProviderFactory.createMetaDataProvider(dataSource, this); 255 } 256 257 private CallMetaDataProvider obtainMetaDataProvider() { 258 Assert.state(this.metaDataProvider != null, "No CallMetaDataProvider - call initializeMetaData first"); 259 return this.metaDataProvider; 260 } 261 262 /** 263 * Create a ReturnResultSetParameter/SqlOutParameter depending on the support provided 264 * by the JDBC driver used for the database in use. 265 * @param parameterName the name of the parameter (also used as the name of the List returned in the output) 266 * @param rowMapper a RowMapper implementation used to map the data returned in the result set 267 * @return the appropriate SqlParameter 268 */ 269 public SqlParameter createReturnResultSetParameter(String parameterName, RowMapper<?> rowMapper) { 270 CallMetaDataProvider provider = obtainMetaDataProvider(); 271 if (provider.isReturnResultSetSupported()) { 272 return new SqlReturnResultSet(parameterName, rowMapper); 273 } 274 else { 275 if (provider.isRefCursorSupported()) { 276 return new SqlOutParameter(parameterName, provider.getRefCursorSqlType(), rowMapper); 277 } 278 else { 279 throw new InvalidDataAccessApiUsageException( 280 "Return of a ResultSet from a stored procedure is not supported"); 281 } 282 } 283 } 284 285 /** 286 * Get the name of the single out parameter for this call. 287 * If there are multiple parameters, the name of the first one will be returned. 288 */ 289 @Nullable 290 public String getScalarOutParameterName() { 291 if (isFunction()) { 292 return getFunctionReturnName(); 293 } 294 else { 295 if (this.outParameterNames.size() > 1) { 296 logger.info("Accessing single output value when procedure has more than one output parameter"); 297 } 298 return (!this.outParameterNames.isEmpty() ? this.outParameterNames.get(0) : null); 299 } 300 } 301 302 /** 303 * Get the List of SqlParameter objects to be used in call execution. 304 */ 305 public List<SqlParameter> getCallParameters() { 306 return this.callParameters; 307 } 308 309 /** 310 * Process the list of parameters provided, and if procedure column meta-data is used, 311 * the parameters will be matched against the meta-data information and any missing 312 * ones will be automatically included. 313 * @param parameters the list of parameters to use as a base 314 */ 315 public void processParameters(List<SqlParameter> parameters) { 316 this.callParameters = reconcileParameters(parameters); 317 } 318 319 /** 320 * Reconcile the provided parameters with available meta-data and add new ones where appropriate. 321 */ 322 protected List<SqlParameter> reconcileParameters(List<SqlParameter> parameters) { 323 CallMetaDataProvider provider = obtainMetaDataProvider(); 324 325 final List<SqlParameter> declaredReturnParams = new ArrayList<>(); 326 final Map<String, SqlParameter> declaredParams = new LinkedHashMap<>(); 327 boolean returnDeclared = false; 328 List<String> outParamNames = new ArrayList<>(); 329 List<String> metaDataParamNames = new ArrayList<>(); 330 331 // Get the names of the meta-data parameters 332 for (CallParameterMetaData meta : provider.getCallParameterMetaData()) { 333 if (!meta.isReturnParameter()) { 334 metaDataParamNames.add(lowerCase(meta.getParameterName())); 335 } 336 } 337 338 // Separate implicit return parameters from explicit parameters... 339 for (SqlParameter param : parameters) { 340 if (param.isResultsParameter()) { 341 declaredReturnParams.add(param); 342 } 343 else { 344 String paramName = param.getName(); 345 if (paramName == null) { 346 throw new IllegalArgumentException("Anonymous parameters not supported for calls - " + 347 "please specify a name for the parameter of SQL type " + param.getSqlType()); 348 } 349 String paramNameToMatch = lowerCase(provider.parameterNameToUse(paramName)); 350 declaredParams.put(paramNameToMatch, param); 351 if (param instanceof SqlOutParameter) { 352 outParamNames.add(paramName); 353 if (isFunction() && !metaDataParamNames.contains(paramNameToMatch) && !returnDeclared) { 354 if (logger.isDebugEnabled()) { 355 logger.debug("Using declared out parameter '" + paramName + 356 "' for function return value"); 357 } 358 this.actualFunctionReturnName = paramName; 359 returnDeclared = true; 360 } 361 } 362 } 363 } 364 setOutParameterNames(outParamNames); 365 366 List<SqlParameter> workParams = new ArrayList<>(declaredReturnParams); 367 if (!provider.isProcedureColumnMetaDataUsed()) { 368 workParams.addAll(declaredParams.values()); 369 return workParams; 370 } 371 372 Map<String, String> limitedInParamNamesMap = new HashMap<>(this.limitedInParameterNames.size()); 373 for (String limitedParamName : this.limitedInParameterNames) { 374 limitedInParamNamesMap.put(lowerCase(provider.parameterNameToUse(limitedParamName)), limitedParamName); 375 } 376 377 for (CallParameterMetaData meta : provider.getCallParameterMetaData()) { 378 String paramName = meta.getParameterName(); 379 String paramNameToCheck = null; 380 if (paramName != null) { 381 paramNameToCheck = lowerCase(provider.parameterNameToUse(paramName)); 382 } 383 String paramNameToUse = provider.parameterNameToUse(paramName); 384 if (declaredParams.containsKey(paramNameToCheck) || (meta.isReturnParameter() && returnDeclared)) { 385 SqlParameter param; 386 if (meta.isReturnParameter()) { 387 param = declaredParams.get(getFunctionReturnName()); 388 if (param == null && !getOutParameterNames().isEmpty()) { 389 param = declaredParams.get(getOutParameterNames().get(0).toLowerCase()); 390 } 391 if (param == null) { 392 throw new InvalidDataAccessApiUsageException( 393 "Unable to locate declared parameter for function return value - " + 394 " add an SqlOutParameter with name '" + getFunctionReturnName() + "'"); 395 } 396 else { 397 this.actualFunctionReturnName = param.getName(); 398 } 399 } 400 else { 401 param = declaredParams.get(paramNameToCheck); 402 } 403 if (param != null) { 404 workParams.add(param); 405 if (logger.isDebugEnabled()) { 406 logger.debug("Using declared parameter for '" + 407 (paramNameToUse != null ? paramNameToUse : getFunctionReturnName()) + "'"); 408 } 409 } 410 } 411 else { 412 if (meta.isReturnParameter()) { 413 // DatabaseMetaData.procedureColumnReturn or possibly procedureColumnResult 414 if (!isFunction() && !isReturnValueRequired() && paramName != null && 415 provider.byPassReturnParameter(paramName)) { 416 if (logger.isDebugEnabled()) { 417 logger.debug("Bypassing meta-data return parameter for '" + paramName + "'"); 418 } 419 } 420 else { 421 String returnNameToUse = 422 (StringUtils.hasLength(paramNameToUse) ? paramNameToUse : getFunctionReturnName()); 423 workParams.add(provider.createDefaultOutParameter(returnNameToUse, meta)); 424 if (isFunction()) { 425 this.actualFunctionReturnName = returnNameToUse; 426 outParamNames.add(returnNameToUse); 427 } 428 if (logger.isDebugEnabled()) { 429 logger.debug("Added meta-data return parameter for '" + returnNameToUse + "'"); 430 } 431 } 432 } 433 else { 434 if (paramNameToUse == null) { 435 paramNameToUse = ""; 436 } 437 if (meta.getParameterType() == DatabaseMetaData.procedureColumnOut) { 438 workParams.add(provider.createDefaultOutParameter(paramNameToUse, meta)); 439 outParamNames.add(paramNameToUse); 440 if (logger.isDebugEnabled()) { 441 logger.debug("Added meta-data out parameter for '" + paramNameToUse + "'"); 442 } 443 } 444 else if (meta.getParameterType() == DatabaseMetaData.procedureColumnInOut) { 445 workParams.add(provider.createDefaultInOutParameter(paramNameToUse, meta)); 446 outParamNames.add(paramNameToUse); 447 if (logger.isDebugEnabled()) { 448 logger.debug("Added meta-data in-out parameter for '" + paramNameToUse + "'"); 449 } 450 } 451 else { 452 // DatabaseMetaData.procedureColumnIn or possibly procedureColumnUnknown 453 if (this.limitedInParameterNames.isEmpty() || 454 limitedInParamNamesMap.containsKey(lowerCase(paramNameToUse))) { 455 workParams.add(provider.createDefaultInParameter(paramNameToUse, meta)); 456 if (logger.isDebugEnabled()) { 457 logger.debug("Added meta-data in parameter for '" + paramNameToUse + "'"); 458 } 459 } 460 else { 461 if (logger.isDebugEnabled()) { 462 logger.debug("Limited set of parameters " + limitedInParamNamesMap.keySet() + 463 " skipped parameter for '" + paramNameToUse + "'"); 464 } 465 } 466 } 467 } 468 } 469 } 470 471 return workParams; 472 } 473 474 /** 475 * Match input parameter values with the parameters declared to be used in the call. 476 * @param parameterSource the input values 477 * @return a Map containing the matched parameter names with the value taken from the input 478 */ 479 public Map<String, Object> matchInParameterValuesWithCallParameters(SqlParameterSource parameterSource) { 480 // For parameter source lookups we need to provide case-insensitive lookup support 481 // since the database meta-data is not necessarily providing case sensitive parameter names. 482 Map<String, String> caseInsensitiveParameterNames = 483 SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource); 484 485 Map<String, String> callParameterNames = new HashMap<>(this.callParameters.size()); 486 Map<String, Object> matchedParameters = new HashMap<>(this.callParameters.size()); 487 for (SqlParameter parameter : this.callParameters) { 488 if (parameter.isInputValueProvided()) { 489 String parameterName = parameter.getName(); 490 String parameterNameToMatch = obtainMetaDataProvider().parameterNameToUse(parameterName); 491 if (parameterNameToMatch != null) { 492 callParameterNames.put(parameterNameToMatch.toLowerCase(), parameterName); 493 } 494 if (parameterName != null) { 495 if (parameterSource.hasValue(parameterName)) { 496 matchedParameters.put(parameterName, 497 SqlParameterSourceUtils.getTypedValue(parameterSource, parameterName)); 498 } 499 else { 500 String lowerCaseName = parameterName.toLowerCase(); 501 if (parameterSource.hasValue(lowerCaseName)) { 502 matchedParameters.put(parameterName, 503 SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName)); 504 } 505 else { 506 String englishLowerCaseName = parameterName.toLowerCase(Locale.ENGLISH); 507 if (parameterSource.hasValue(englishLowerCaseName)) { 508 matchedParameters.put(parameterName, 509 SqlParameterSourceUtils.getTypedValue(parameterSource, englishLowerCaseName)); 510 } 511 else { 512 String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(parameterName); 513 if (parameterSource.hasValue(propertyName)) { 514 matchedParameters.put(parameterName, 515 SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName)); 516 } 517 else { 518 if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) { 519 String sourceName = caseInsensitiveParameterNames.get(lowerCaseName); 520 matchedParameters.put(parameterName, 521 SqlParameterSourceUtils.getTypedValue(parameterSource, sourceName)); 522 } 523 else if (logger.isInfoEnabled()) { 524 logger.info("Unable to locate the corresponding parameter value for '" + 525 parameterName + "' within the parameter values provided: " + 526 caseInsensitiveParameterNames.values()); 527 } 528 } 529 } 530 } 531 } 532 } 533 } 534 } 535 536 if (logger.isDebugEnabled()) { 537 logger.debug("Matching " + caseInsensitiveParameterNames.values() + " with " + callParameterNames.values()); 538 logger.debug("Found match for " + matchedParameters.keySet()); 539 } 540 return matchedParameters; 541 } 542 543 /** 544 * Match input parameter values with the parameters declared to be used in the call. 545 * @param inParameters the input values 546 * @return a Map containing the matched parameter names with the value taken from the input 547 */ 548 public Map<String, ?> matchInParameterValuesWithCallParameters(Map<String, ?> inParameters) { 549 CallMetaDataProvider provider = obtainMetaDataProvider(); 550 if (!provider.isProcedureColumnMetaDataUsed()) { 551 return inParameters; 552 } 553 554 Map<String, String> callParameterNames = new HashMap<>(this.callParameters.size()); 555 for (SqlParameter parameter : this.callParameters) { 556 if (parameter.isInputValueProvided()) { 557 String parameterName = parameter.getName(); 558 String parameterNameToMatch = provider.parameterNameToUse(parameterName); 559 if (parameterNameToMatch != null) { 560 callParameterNames.put(parameterNameToMatch.toLowerCase(), parameterName); 561 } 562 } 563 } 564 565 Map<String, Object> matchedParameters = new HashMap<>(inParameters.size()); 566 inParameters.forEach((parameterName, parameterValue) -> { 567 String parameterNameToMatch = provider.parameterNameToUse(parameterName); 568 String callParameterName = callParameterNames.get(lowerCase(parameterNameToMatch)); 569 if (callParameterName == null) { 570 if (logger.isDebugEnabled()) { 571 Object value = parameterValue; 572 if (value instanceof SqlParameterValue) { 573 value = ((SqlParameterValue) value).getValue(); 574 } 575 if (value != null) { 576 logger.debug("Unable to locate the corresponding IN or IN-OUT parameter for \"" + 577 parameterName + "\" in the parameters used: " + callParameterNames.keySet()); 578 } 579 } 580 } 581 else { 582 matchedParameters.put(callParameterName, parameterValue); 583 } 584 }); 585 586 if (matchedParameters.size() < callParameterNames.size()) { 587 for (String parameterName : callParameterNames.keySet()) { 588 String parameterNameToMatch = provider.parameterNameToUse(parameterName); 589 String callParameterName = callParameterNames.get(lowerCase(parameterNameToMatch)); 590 if (!matchedParameters.containsKey(callParameterName) && logger.isInfoEnabled()) { 591 logger.info("Unable to locate the corresponding parameter value for '" + parameterName + 592 "' within the parameter values provided: " + inParameters.keySet()); 593 } 594 } 595 } 596 597 if (logger.isDebugEnabled()) { 598 logger.debug("Matching " + inParameters.keySet() + " with " + callParameterNames.values()); 599 logger.debug("Found match for " + matchedParameters.keySet()); 600 } 601 return matchedParameters; 602 } 603 604 public Map<String, ?> matchInParameterValuesWithCallParameters(Object[] parameterValues) { 605 Map<String, Object> matchedParameters = new HashMap<>(parameterValues.length); 606 int i = 0; 607 for (SqlParameter parameter : this.callParameters) { 608 if (parameter.isInputValueProvided()) { 609 String parameterName = parameter.getName(); 610 matchedParameters.put(parameterName, parameterValues[i++]); 611 } 612 } 613 return matchedParameters; 614 } 615 616 /** 617 * Build the call string based on configuration and meta-data information. 618 * @return the call string to be used 619 */ 620 public String createCallString() { 621 Assert.state(this.metaDataProvider != null, "No CallMetaDataProvider available"); 622 623 StringBuilder callString; 624 int parameterCount = 0; 625 String catalogNameToUse; 626 String schemaNameToUse; 627 628 // For Oracle where catalogs are not supported we need to reverse the schema name 629 // and the catalog name since the catalog is used for the package name 630 if (this.metaDataProvider.isSupportsSchemasInProcedureCalls() && 631 !this.metaDataProvider.isSupportsCatalogsInProcedureCalls()) { 632 schemaNameToUse = this.metaDataProvider.catalogNameToUse(getCatalogName()); 633 catalogNameToUse = this.metaDataProvider.schemaNameToUse(getSchemaName()); 634 } 635 else { 636 catalogNameToUse = this.metaDataProvider.catalogNameToUse(getCatalogName()); 637 schemaNameToUse = this.metaDataProvider.schemaNameToUse(getSchemaName()); 638 } 639 640 if (isFunction() || isReturnValueRequired()) { 641 callString = new StringBuilder("{? = call "); 642 parameterCount = -1; 643 } 644 else { 645 callString = new StringBuilder("{call "); 646 } 647 648 if (StringUtils.hasLength(catalogNameToUse)) { 649 callString.append(catalogNameToUse).append("."); 650 } 651 if (StringUtils.hasLength(schemaNameToUse)) { 652 callString.append(schemaNameToUse).append("."); 653 } 654 callString.append(this.metaDataProvider.procedureNameToUse(getProcedureName())); 655 callString.append("("); 656 657 for (SqlParameter parameter : this.callParameters) { 658 if (!parameter.isResultsParameter()) { 659 if (parameterCount > 0) { 660 callString.append(", "); 661 } 662 if (parameterCount >= 0) { 663 callString.append(createParameterBinding(parameter)); 664 } 665 parameterCount++; 666 } 667 } 668 callString.append(")}"); 669 670 return callString.toString(); 671 } 672 673 /** 674 * Build the parameter binding fragment. 675 * @param parameter call parameter 676 * @return parameter binding fragment 677 * @since 4.2 678 */ 679 protected String createParameterBinding(SqlParameter parameter) { 680 return (isNamedBinding() ? parameter.getName() + " => ?" : "?"); 681 } 682 683 private static String lowerCase(@Nullable String paramName) { 684 return (paramName != null ? paramName.toLowerCase() : ""); 685 } 686 687}