/**
* Match input parameter values with the parameters declared to be used in the call.
*
* @param inParameters the input values
* @return a Map containing the matched parameter names with the value taken from the input
*/
public Map<String, ?> matchInParameterValuesWithCallParameters(Map<String, ?> inParameters) {
if (!this.metaDataProvider.isProcedureColumnMetaDataUsed()) {
return inParameters;
}
Map<String, String> callParameterNames =
new HashMap<String, String>(this.callParameters.size());
for (SqlParameter parameter : this.callParameters) {
if (parameter.isInputValueProvided()) {
String parameterName = parameter.getName();
String parameterNameToMatch = this.metaDataProvider.parameterNameToUse(parameterName);
if (parameterNameToMatch != null) {
callParameterNames.put(parameterNameToMatch.toLowerCase(), parameterName);
}
}
}
Map<String, Object> matchedParameters = new HashMap<String, Object>(inParameters.size());
for (String parameterName : inParameters.keySet()) {
String parameterNameToMatch = this.metaDataProvider.parameterNameToUse(parameterName);
String callParameterName = callParameterNames.get(parameterNameToMatch.toLowerCase());
if (callParameterName == null) {
if (logger.isDebugEnabled()) {
Object value = inParameters.get(parameterName);
if (value instanceof SqlParameterValue) {
value = ((SqlParameterValue) value).getValue();
}
if (value != null) {
logger.debug(
"Unable to locate the corresponding IN or IN-OUT parameter for \""
+ parameterName
+ "\" in the parameters used: "
+ callParameterNames.keySet());
}
}
} else {
matchedParameters.put(callParameterName, inParameters.get(parameterName));
}
}
if (matchedParameters.size() < callParameterNames.size()) {
for (String parameterName : callParameterNames.keySet()) {
String parameterNameToMatch = this.metaDataProvider.parameterNameToUse(parameterName);
String callParameterName = callParameterNames.get(parameterNameToMatch.toLowerCase());
if (!matchedParameters.containsKey(callParameterName)) {
logger.warn(
"Unable to locate the corresponding parameter value for '"
+ parameterName
+ "' within the parameter values provided: "
+ inParameters.keySet());
}
}
}
if (logger.isDebugEnabled()) {
logger.debug("Matching " + inParameters.keySet() + " with " + callParameterNames.values());
logger.debug("Found match for " + matchedParameters.keySet());
}
return matchedParameters;
}
public Map<String, ?> matchInParameterValuesWithCallParameters(Object[] parameterValues) {
Map<String, Object> matchedParameters = new HashMap<String, Object>(parameterValues.length);
int i = 0;
for (SqlParameter parameter : this.callParameters) {
if (parameter.isInputValueProvided()) {
String parameterName = parameter.getName();
matchedParameters.put(parameterName, parameterValues[i++]);
}
}
return matchedParameters;
}
/**
* Build the call string based on configuration and metadata information.
*
* @return the call string to be used
*/
public String createCallString() {
String callString;
int parameterCount = 0;
String catalogNameToUse;
String schemaNameToUse;
// For Oracle where catalogs are not supported we need to reverse the schema name
// and the catalog name since the cataog is used for the package name
if (this.metaDataProvider.isSupportsSchemasInProcedureCalls()
&& !this.metaDataProvider.isSupportsCatalogsInProcedureCalls()) {
schemaNameToUse = this.metaDataProvider.catalogNameToUse(getCatalogName());
catalogNameToUse = this.metaDataProvider.schemaNameToUse(getSchemaName());
} else {
catalogNameToUse = this.metaDataProvider.catalogNameToUse(getCatalogName());
schemaNameToUse = this.metaDataProvider.schemaNameToUse(getSchemaName());
}
String procedureNameToUse = this.metaDataProvider.procedureNameToUse(getProcedureName());
if (isFunction() || isReturnValueRequired()) {
callString =
"{? = call "
+ (StringUtils.hasLength(catalogNameToUse) ? catalogNameToUse + "." : "")
+ (StringUtils.hasLength(schemaNameToUse) ? schemaNameToUse + "." : "")
+ procedureNameToUse
+ "(";
parameterCount = -1;
} else {
callString =
"{call "
+ (StringUtils.hasLength(catalogNameToUse) ? catalogNameToUse + "." : "")
+ (StringUtils.hasLength(schemaNameToUse) ? schemaNameToUse + "." : "")
+ procedureNameToUse
+ "(";
}
for (SqlParameter parameter : this.callParameters) {
if (!(parameter.isResultsParameter())) {
if (parameterCount > 0) {
callString += ", ";
}
if (parameterCount >= 0) {
callString += createParameterBinding(parameter);
}
parameterCount++;
}
}
callString += ")}";
return callString;
}
/**
* Build the parameter binding fragment.
*
* @param parameter call parameter
* @return parameter binding fragment
* @since 4.2
*/
protected String createParameterBinding(SqlParameter parameter) {
if (isNamedBinding()) {
return parameter.getName() + " => ?";
} else {
return "?";
}
}
/**
* Add a declared parameter to teh list of parameters for the call
*
* @param parameter the {@link SqlParameter} to add
*/
public void addDeclaredParameter(SqlParameter parameter) {
this.declaredParameters.add(parameter);
if (logger.isDebugEnabled()) {
logger.debug(
"Added declared parameter for [" + getProcedureName() + "]: " + parameter.getName());
}
}
/**
* Overridden method to configure the CallableStatementCreatorFactory based on our declared
* parameters.
*
* @see RdbmsOperation#compileInternal()
*/
@Override
protected final void compileInternal() {
if (isSqlReadyForUse()) {
this.callString = getSql();
} else {
List<SqlParameter> parameters = getDeclaredParameters();
int parameterCount = 0;
if (isFunction()) {
this.callString = "{? = call " + getSql() + "(";
parameterCount = -1;
} else {
this.callString = "{call " + getSql() + "(";
}
for (SqlParameter parameter : parameters) {
if (!(parameter.isResultsParameter())) {
if (parameterCount > 0) {
this.callString += ", ";
}
if (parameterCount >= 0) {
this.callString += "?";
}
parameterCount++;
}
}
this.callString += ")}";
}
if (logger.isDebugEnabled()) {
logger.debug("Compiled stored procedure. Call string is [" + getCallString() + "]");
}
this.callableStatementFactory =
new CallableStatementCreatorFactory(getCallString(), getDeclaredParameters());
this.callableStatementFactory.setResultSetType(getResultSetType());
this.callableStatementFactory.setUpdatableResults(isUpdatableResults());
this.callableStatementFactory.setNativeJdbcExtractor(
getJdbcTemplate().getNativeJdbcExtractor());
onCompileInternal();
}
/** Mathod to perform the actual call processing */
private Map<String, Object> executeCallInternal(Map params) {
CallableStatementCreator csc =
getCallableStatementFactory().newCallableStatementCreator(params);
if (logger.isDebugEnabled()) {
logger.debug(
"The following parameters are used for call " + getCallString() + " with: " + params);
int i = 1;
for (SqlParameter p : getCallParameters()) {
logger.debug(
i++
+ ": "
+ p.getName()
+ " SQL Type "
+ p.getSqlType()
+ " Type Name "
+ p.getTypeName()
+ " "
+ p.getClass().getName());
}
}
Map<String, Object> result = getJdbcTemplate().call(csc, getCallParameters());
return result;
}
/**
* Match input parameter values with the parameters declared to be used in the call.
*
* @param parameterSource the input values
* @return a Map containing the matched parameter names with the value taken from the input
*/
public Map<String, Object> matchInParameterValuesWithCallParameters(
SqlParameterSource parameterSource) {
// For parameter source lookups we need to provide case-insensitive lookup support
// since the database metadata is not necessarily providing case sensitive parameter names.
Map<String, String> caseInsensitiveParameterNames =
SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);
Map<String, String> callParameterNames =
new HashMap<String, String>(this.callParameters.size());
Map<String, Object> matchedParameters = new HashMap<String, Object>(this.callParameters.size());
for (SqlParameter parameter : this.callParameters) {
if (parameter.isInputValueProvided()) {
String parameterName = parameter.getName();
String parameterNameToMatch = this.metaDataProvider.parameterNameToUse(parameterName);
if (parameterNameToMatch != null) {
callParameterNames.put(parameterNameToMatch.toLowerCase(), parameterName);
}
if (parameterName != null) {
if (parameterSource.hasValue(parameterName)) {
matchedParameters.put(
parameterName,
SqlParameterSourceUtils.getTypedValue(parameterSource, parameterName));
} else {
String lowerCaseName = parameterName.toLowerCase();
if (parameterSource.hasValue(lowerCaseName)) {
matchedParameters.put(
parameterName,
SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
} else {
String englishLowerCaseName = parameterName.toLowerCase(Locale.ENGLISH);
if (parameterSource.hasValue(englishLowerCaseName)) {
matchedParameters.put(
parameterName,
SqlParameterSourceUtils.getTypedValue(parameterSource, englishLowerCaseName));
} else {
String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(parameterName);
if (parameterSource.hasValue(propertyName)) {
matchedParameters.put(
parameterName,
SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
} else {
if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
String sourceName = caseInsensitiveParameterNames.get(lowerCaseName);
matchedParameters.put(
parameterName,
SqlParameterSourceUtils.getTypedValue(parameterSource, sourceName));
} else {
logger.warn(
"Unable to locate the corresponding parameter value for '"
+ parameterName
+ "' within the parameter values provided: "
+ caseInsensitiveParameterNames.values());
}
}
}
}
}
}
}
}
if (logger.isDebugEnabled()) {
logger.debug(
"Matching "
+ caseInsensitiveParameterNames.values()
+ " with "
+ callParameterNames.values());
logger.debug("Found match for " + matchedParameters.keySet());
}
return matchedParameters;
}
/**
* Reconcile the provided parameters with available metadata and add new ones where appropriate.
*/
protected List<SqlParameter> reconcileParameters(List<SqlParameter> parameters) {
final List<SqlParameter> declaredReturnParams = new ArrayList<SqlParameter>();
final Map<String, SqlParameter> declaredParams = new LinkedHashMap<String, SqlParameter>();
boolean returnDeclared = false;
List<String> outParamNames = new ArrayList<String>();
List<String> metaDataParamNames = new ArrayList<String>();
// Get the names of the meta data parameters
for (CallParameterMetaData meta : this.metaDataProvider.getCallParameterMetaData()) {
if (meta.getParameterType() != DatabaseMetaData.procedureColumnReturn) {
metaDataParamNames.add(meta.getParameterName().toLowerCase());
}
}
// Separate implicit return parameters from explicit parameters...
for (SqlParameter param : parameters) {
if (param.isResultsParameter()) {
declaredReturnParams.add(param);
} else {
String paramName = param.getName();
if (paramName == null) {
throw new IllegalArgumentException(
"Anonymous parameters not supported for calls - "
+ "please specify a name for the parameter of SQL type "
+ param.getSqlType());
}
String paramNameToMatch = this.metaDataProvider.parameterNameToUse(paramName).toLowerCase();
declaredParams.put(paramNameToMatch, param);
if (param instanceof SqlOutParameter) {
outParamNames.add(paramName);
if (isFunction() && !metaDataParamNames.contains(paramNameToMatch)) {
if (!returnDeclared) {
if (logger.isDebugEnabled()) {
logger.debug(
"Using declared out parameter '" + paramName + "' for function return value");
}
setFunctionReturnName(paramName);
returnDeclared = true;
}
}
}
}
}
setOutParameterNames(outParamNames);
List<SqlParameter> workParams = new ArrayList<SqlParameter>();
workParams.addAll(declaredReturnParams);
if (!this.metaDataProvider.isProcedureColumnMetaDataUsed()) {
workParams.addAll(declaredParams.values());
return workParams;
}
Map<String, String> limitedInParamNamesMap =
new HashMap<String, String>(this.limitedInParameterNames.size());
for (String limitedParamName : this.limitedInParameterNames) {
limitedInParamNamesMap.put(
this.metaDataProvider.parameterNameToUse(limitedParamName).toLowerCase(),
limitedParamName);
}
for (CallParameterMetaData meta : this.metaDataProvider.getCallParameterMetaData()) {
String paramNameToCheck = null;
if (meta.getParameterName() != null) {
paramNameToCheck =
this.metaDataProvider.parameterNameToUse(meta.getParameterName()).toLowerCase();
}
String paramNameToUse = this.metaDataProvider.parameterNameToUse(meta.getParameterName());
if (declaredParams.containsKey(paramNameToCheck)
|| (meta.getParameterType() == DatabaseMetaData.procedureColumnReturn
&& returnDeclared)) {
SqlParameter param;
if (meta.getParameterType() == DatabaseMetaData.procedureColumnReturn) {
param = declaredParams.get(getFunctionReturnName());
if (param == null && getOutParameterNames().size() > 0) {
param = declaredParams.get(getOutParameterNames().get(0).toLowerCase());
}
if (param == null) {
throw new InvalidDataAccessApiUsageException(
"Unable to locate declared parameter for function return value - "
+ " add a SqlOutParameter with name '"
+ getFunctionReturnName()
+ "'");
} else {
setFunctionReturnName(param.getName());
}
} else {
param = declaredParams.get(paramNameToCheck);
}
if (param != null) {
workParams.add(param);
if (logger.isDebugEnabled()) {
logger.debug(
"Using declared parameter for '"
+ (paramNameToUse != null ? paramNameToUse : getFunctionReturnName())
+ "'");
}
}
} else {
if (meta.getParameterType() == DatabaseMetaData.procedureColumnReturn) {
if (!isFunction()
&& !isReturnValueRequired()
&& this.metaDataProvider.byPassReturnParameter(meta.getParameterName())) {
if (logger.isDebugEnabled()) {
logger.debug(
"Bypassing metadata return parameter for '" + meta.getParameterName() + "'");
}
} else {
String returnNameToUse =
(StringUtils.hasLength(meta.getParameterName())
? paramNameToUse
: getFunctionReturnName());
workParams.add(this.metaDataProvider.createDefaultOutParameter(returnNameToUse, meta));
if (isFunction()) {
setFunctionReturnName(returnNameToUse);
outParamNames.add(returnNameToUse);
}
if (logger.isDebugEnabled()) {
logger.debug("Added metadata return parameter for '" + returnNameToUse + "'");
}
}
} else {
if (meta.getParameterType() == DatabaseMetaData.procedureColumnOut) {
workParams.add(this.metaDataProvider.createDefaultOutParameter(paramNameToUse, meta));
outParamNames.add(paramNameToUse);
if (logger.isDebugEnabled()) {
logger.debug("Added metadata out parameter for '" + paramNameToUse + "'");
}
} else if (meta.getParameterType() == DatabaseMetaData.procedureColumnInOut) {
workParams.add(this.metaDataProvider.createDefaultInOutParameter(paramNameToUse, meta));
outParamNames.add(paramNameToUse);
if (logger.isDebugEnabled()) {
logger.debug("Added metadata in out parameter for '" + paramNameToUse + "'");
}
} else {
if (this.limitedInParameterNames.isEmpty()
|| limitedInParamNamesMap.containsKey(paramNameToUse.toLowerCase())) {
workParams.add(this.metaDataProvider.createDefaultInParameter(paramNameToUse, meta));
if (logger.isDebugEnabled()) {
logger.debug("Added metadata in parameter for '" + paramNameToUse + "'");
}
} else {
if (logger.isDebugEnabled()) {
logger.debug(
"Limited set of parameters "
+ limitedInParamNamesMap.keySet()
+ " skipped parameter for '"
+ paramNameToUse
+ "'");
}
}
}
}
}
}
return workParams;
}
@SuppressWarnings("unchecked")
@Test
public void testSqlParametersAreSet() throws Exception {
setUp("basicStoredProcOutboundChannelAdapterTest.xml", getClass());
DirectFieldAccessor accessor = new DirectFieldAccessor(this.consumer);
Object handler = accessor.getPropertyValue("handler");
accessor = new DirectFieldAccessor(handler);
Object executor = accessor.getPropertyValue("executor");
DirectFieldAccessor executorAccessor = new DirectFieldAccessor(executor);
Object sqlParameters = executorAccessor.getPropertyValue("sqlParameters");
assertNotNull(sqlParameters);
assertTrue(sqlParameters instanceof List);
List<SqlParameter> sqlParametersAsList = (List<SqlParameter>) sqlParameters;
assertTrue(sqlParametersAsList.size() == 4);
SqlParameter parameter1 = sqlParametersAsList.get(0);
SqlParameter parameter2 = sqlParametersAsList.get(1);
SqlParameter parameter3 = sqlParametersAsList.get(2);
SqlParameter parameter4 = sqlParametersAsList.get(3);
assertEquals("username", parameter1.getName());
assertEquals("password", parameter2.getName());
assertEquals("age", parameter3.getName());
assertEquals("description", parameter4.getName());
assertNull("Expect that the scale is null.", parameter1.getScale());
assertNull("Expect that the scale is null.", parameter2.getScale());
assertEquals("Expect that the scale is 5.", Integer.valueOf(5), parameter3.getScale());
assertNull("Expect that the scale is null.", parameter4.getScale());
assertEquals("SqlType is ", Types.VARCHAR, parameter1.getSqlType());
assertEquals("SqlType is ", Types.VARCHAR, parameter2.getSqlType());
assertEquals("SqlType is ", Types.INTEGER, parameter3.getSqlType());
assertEquals("SqlType is ", Types.VARCHAR, parameter4.getSqlType());
assertTrue(parameter1 instanceof SqlParameter);
assertTrue(parameter2 instanceof SqlOutParameter);
assertTrue(parameter3 instanceof SqlInOutParameter);
assertTrue(parameter4 instanceof SqlParameter);
}
