Examples with SqlParameter org.springframework.jdbc.core.SqlParameter used on opensource projects

Example 6 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class CallMetaDataContext method matchInParameterValuesWithCallParameters.

/**
	 * 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<>(this.callParameters.size());
    Map<String, Object> matchedParameters = new HashMap<>(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;
}

Example 7 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class CallMetaDataContext method reconcileParameters.

/**
	 * 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<>();
    final Map<String, SqlParameter> declaredParams = new LinkedHashMap<>();
    boolean returnDeclared = false;
    List<String> outParamNames = new ArrayList<>();
    List<String> metaDataParamNames = new ArrayList<>();
    // 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<>();
    workParams.addAll(declaredReturnParams);
    if (!this.metaDataProvider.isProcedureColumnMetaDataUsed()) {
        workParams.addAll(declaredParams.values());
        return workParams;
    }
    Map<String, String> limitedInParamNamesMap = new HashMap<>(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;
}

Example 8 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class CallMetaDataContext method createCallString.

/**
	 * 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;
    // 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;
}

Example 9 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class CallMetaDataContextTests method testMatchParameterValuesAndSqlInOutParameters.

@Test
public void testMatchParameterValuesAndSqlInOutParameters() throws Exception {
    final String TABLE = "customers";
    final String USER = "me";
    given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
    given(databaseMetaData.getUserName()).willReturn(USER);
    given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
    List<SqlParameter> parameters = new ArrayList<>();
    parameters.add(new SqlParameter("id", Types.NUMERIC));
    parameters.add(new SqlInOutParameter("name", Types.NUMERIC));
    parameters.add(new SqlOutParameter("customer_no", Types.NUMERIC));
    MapSqlParameterSource parameterSource = new MapSqlParameterSource();
    parameterSource.addValue("id", 1);
    parameterSource.addValue("name", "Sven");
    parameterSource.addValue("customer_no", "12345XYZ");
    context.setProcedureName(TABLE);
    context.initializeMetaData(dataSource);
    context.processParameters(parameters);
    Map<String, Object> inParameters = context.matchInParameterValuesWithCallParameters(parameterSource);
    assertEquals("Wrong number of matched in parameter values", 2, inParameters.size());
    assertTrue("in parameter value missing", inParameters.containsKey("id"));
    assertTrue("in out parameter value missing", inParameters.containsKey("name"));
    assertTrue("out parameter value matched", !inParameters.containsKey("customer_no"));
    List<String> names = context.getOutParameterNames();
    assertEquals("Wrong number of out parameters", 2, names.size());
    List<SqlParameter> callParameters = context.getCallParameters();
    assertEquals("Wrong number of call parameters", 3, callParameters.size());
}

Example 10 with SqlParameter

use of org.springframework.jdbc.core.SqlParameter in project spring-framework by spring-projects.

the class SqlUpdateTests method doTestNamedParameterUpdate.

private void doTestNamedParameterUpdate(final boolean namedDeclarations) throws SQLException {
    given(preparedStatement.executeUpdate()).willReturn(1);
    given(connection.prepareStatement(UPDATE_INT_INT)).willReturn(preparedStatement);
    class NamedParameterUpdater extends SqlUpdate {

        public NamedParameterUpdater() {
            setSql(UPDATE_NAMED_PARAMETERS);
            setDataSource(dataSource);
            if (namedDeclarations) {
                declareParameter(new SqlParameter("priceId", Types.DECIMAL));
                declareParameter(new SqlParameter("perfId", Types.NUMERIC));
            } else {
                declareParameter(new SqlParameter(Types.NUMERIC));
                declareParameter(new SqlParameter(Types.DECIMAL));
            }
            compile();
        }

        public int run(int performanceId, int type) {
            Map<String, Integer> params = new HashMap<>();
            params.put("perfId", performanceId);
            params.put("priceId", type);
            return updateByNamedParam(params);
        }
    }
    NamedParameterUpdater pc = new NamedParameterUpdater();
    int rowsAffected = pc.run(1, 1);
    assertEquals(1, rowsAffected);
    verify(preparedStatement).setObject(1, 1, Types.NUMERIC);
    verify(preparedStatement).setObject(2, 1, Types.DECIMAL);
}