/** * Central execution method. All named parameter execution goes through this method. * @param paramMap parameters associated with the name specified while declaring * the SqlParameters. Primitive parameters must be represented by their Object wrapper * type. The ordering of parameters is not significant since they are supplied in a * SqlParameterMap which is an implementation of the Map interface. * @param context contextual information passed to the {@code mapRow} * callback method. The JDBC operation itself doesn't rely on this parameter, * but it can be useful for creating the objects of the result list. * @return a List of objects, one per row of the ResultSet. Normally all these * will be of the same class, although it is possible to use different types. */ public List<T> executeByNamedParam(Map<String, ?> paramMap, @Nullable Map<?, ?> context) throws DataAccessException { validateNamedParameters(paramMap); ParsedSql parsedSql = getParsedSql(); MapSqlParameterSource paramSource = new MapSqlParameterSource(paramMap); String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource); Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, getDeclaredParameters()); RowMapper<T> rowMapper = newRowMapper(params, context); return getJdbcTemplate().query(newPreparedStatementCreator(sqlToUse, params), rowMapper); }
/** * Parse the SQL statement and locate any placeholders or named parameters. * Named parameters are substituted for a JDBC placeholder. * <p>This is a shortcut version of * {@link #parseSqlStatement(String)} in combination with * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}. * @param sql the SQL statement * @return the actual (parsed) SQL statement */ public static String parseSqlStatementIntoString(String sql) { ParsedSql parsedSql = parseSqlStatement(sql); return substituteNamedParameters(parsedSql, null); }
/** * Build a {@link PreparedStatementCreatorFactory} based on the given SQL and named parameters. * @param parsedSql parsed representation of the given SQL statement * @param paramSource container of arguments to bind * @return the corresponding {@link PreparedStatementCreatorFactory} * @since 5.1.3 * @see #getPreparedStatementCreator(String, SqlParameterSource, Consumer) * @see #getParsedSql(String) */ protected PreparedStatementCreatorFactory getPreparedStatementCreatorFactory( ParsedSql parsedSql, SqlParameterSource paramSource) { String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource); List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource); return new PreparedStatementCreatorFactory(sqlToUse, declaredParameters); }
@Override public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null); int[] columnTypes = NamedParameterUtils.buildSqlTypeArray(parsedSql, batchArgs[i]); setStatementParameters(values, ps, columnTypes); } @Override
/** * Convert a Map of named parameter values to a corresponding array. * <p>This is a shortcut version of * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}. * @param sql the SQL statement * @param paramMap the Map of parameters * @return the array of values */ public static Object[] buildValueArray(String sql, Map<String, ?> paramMap) { ParsedSql parsedSql = parseSqlStatement(sql); return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap), null); }
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) { ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource); Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null); List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource); PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters); return pscf.newPreparedStatementCreator(params); }
/** * Build a PreparedStatementCreator based on the given SQL and named parameters. * <p>Note: Not used for the {@code update} variant with generated key handling. * @param sql SQL to execute * @param paramSource container of arguments to bind * @return the corresponding PreparedStatementCreator */ protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) { ParsedSql parsedSql = getParsedSql(sql); String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource); Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null); List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource); PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters); return pscf.newPreparedStatementCreator(params); }
@Test // SPR-4789 public void parseSqlContainingComments() { String sql1 = "/*+ HINT */ xxx /* comment ? */ :a yyyy :b :c :a zzzzz -- :xx XX\n"; ParsedSql psql1 = NamedParameterUtils.parseSqlStatement(sql1); assertEquals("/*+ HINT */ xxx /* comment ? */ ? yyyy ? ? ? zzzzz -- :xx XX\n", NamedParameterUtils.substituteNamedParameters(psql1, null)); MapSqlParameterSource paramMap = new MapSqlParameterSource(); paramMap.addValue("a", "a"); paramMap.addValue("b", "b"); paramMap.addValue("c", "c"); Object[] params = NamedParameterUtils.buildValueArray(psql1, paramMap, null); assertEquals(4, params.length); assertEquals("a", params[0]); assertEquals("b", params[1]); assertEquals("c", params[2]); assertEquals("a", params[3]); String sql2 = "/*+ HINT */ xxx /* comment ? */ :a yyyy :b :c :a zzzzz -- :xx XX"; ParsedSql psql2 = NamedParameterUtils.parseSqlStatement(sql2); assertEquals("/*+ HINT */ xxx /* comment ? */ ? yyyy ? ? ? zzzzz -- :xx XX", NamedParameterUtils.substituteNamedParameters(psql2, null)); String sql3 = "/*+ HINT */ xxx /* comment ? */ :a yyyy :b :c :a zzzzz /* :xx XX*"; ParsedSql psql3 = NamedParameterUtils.parseSqlStatement(sql3); assertEquals("/*+ HINT */ xxx /* comment ? */ ? yyyy ? ? ? zzzzz /* :xx XX*", NamedParameterUtils.substituteNamedParameters(psql3, null)); String sql4 = "/*+ HINT */ xxx /* comment :a ? */ :a yyyy :b :c :a zzzzz /* :xx XX*"; ParsedSql psql4 = NamedParameterUtils.parseSqlStatement(sql4); Map<String, String> parameters = Collections.singletonMap("a", "0"); assertEquals("/*+ HINT */ xxx /* comment :a ? */ ? yyyy ? ? ? zzzzz /* :xx XX*", NamedParameterUtils.substituteNamedParameters(psql4, new MapSqlParameterSource(parameters))); }
/** * Obtain a parsed representation of this operation's SQL statement. * <p>Typically used for named parameter parsing. */ protected ParsedSql getParsedSql() { synchronized (this.parsedSqlMonitor) { if (this.cachedSql == null) { this.cachedSql = NamedParameterUtils.parseSqlStatement(resolveSql()); } return this.cachedSql; } }
public static int[] executeBatchUpdateWithNamedParameters( final ParsedSql parsedSql, final SqlParameterSource[] batchArgs, JdbcOperations jdbcOperations) { if (batchArgs.length == 0) { return new int[0]; } String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, batchArgs[0]); return jdbcOperations.batchUpdate( sqlToUse, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null); int[] columnTypes = NamedParameterUtils.buildSqlTypeArray(parsedSql, batchArgs[i]); setStatementParameters(values, ps, columnTypes); } @Override public int getBatchSize() { return batchArgs.length; } }); }
int skipToPosition = i; while (i < statement.length) { skipToPosition = skipCommentsAndQuotes(statement, i); if (i == skipToPosition) { break; namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter); totalParameterCount = addNamedParameter( parameterList, totalParameterCount, escapes, i, j + 1, parameter); while (j < statement.length && !isParameterSeparator(statement[j])) { j++; namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter); totalParameterCount = addNamedParameter( parameterList, totalParameterCount, escapes, i, j, parameter);
@Override public void setValues(PreparedStatement ps, int i) throws SQLException { Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null); pscf.newPreparedStatementSetter(values).setValues(ps); } @Override
@Test public void convertTypeMapToSqlParameterList() { MapSqlParameterSource namedParams = new MapSqlParameterSource(); namedParams.addValue("a", "a", 1).addValue("b", "b", 2).addValue("c", "c", 3, "SQL_TYPE"); assertSame(3, NamedParameterUtils .buildSqlParameterList(NamedParameterUtils.parseSqlStatement("xxx :a :b :c"), namedParams).size()); assertSame(5, NamedParameterUtils .buildSqlParameterList(NamedParameterUtils.parseSqlStatement("xxx :a :b :c xx :a :b"), namedParams).size()); assertSame(5, NamedParameterUtils .buildSqlParameterList(NamedParameterUtils.parseSqlStatement("xxx :a :a :a xx :a :a"), namedParams).size()); assertEquals(2, NamedParameterUtils .buildSqlParameterList(NamedParameterUtils.parseSqlStatement("xxx :a :b :c xx :a :b"), namedParams).get(4).getSqlType()); assertEquals("SQL_TYPE", NamedParameterUtils .buildSqlParameterList(NamedParameterUtils.parseSqlStatement("xxx :a :b :c"), namedParams).get(2).getTypeName()); }
@Test public void convertTypeMapToArray() { MapSqlParameterSource namedParams = new MapSqlParameterSource(); namedParams.addValue("a", "a", 1).addValue("b", "b", 2).addValue("c", "c", 3); assertSame(3, NamedParameterUtils .buildSqlTypeArray(NamedParameterUtils.parseSqlStatement("xxx :a :b :c"), namedParams).length); assertSame(5, NamedParameterUtils .buildSqlTypeArray(NamedParameterUtils.parseSqlStatement("xxx :a :b :c xx :a :b"), namedParams).length); assertSame(5, NamedParameterUtils .buildSqlTypeArray(NamedParameterUtils.parseSqlStatement("xxx :a :a :a xx :a :a"), namedParams).length); assertEquals(2, NamedParameterUtils .buildSqlTypeArray(NamedParameterUtils.parseSqlStatement("xxx :a :b :c xx :a :b"), namedParams)[4]); }
try { Object value = paramSource.getValue(paramName); SqlParameter param = findParameter(declaredParams, paramName, i); paramArray[i] = (param != null ? new SqlParameterValue(param, value) : value);
protected void processStreamList(final Exchange exchange, final String sql, final SqlParameterSource param) throws Exception { // spring JDBC to parse the SQL and build the prepared statement creator // this is what NamedJdbcTemplate does internally final ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); final String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, param); final Object[] params = NamedParameterUtils.buildValueArray(parsedSql, param, null); final List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, param); final PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters); final PreparedStatementCreator statementCreator = pscf.newPreparedStatementCreator(params); processStreamList(exchange, statementCreator, sqlToUse); }
public int update( String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames) throws DataAccessException { ParsedSql parsedSql = getParsedSql(sql); String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource); Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null); List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource); PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters); if (keyColumnNames != null) { pscf.setGeneratedKeysColumnNames(keyColumnNames); } else { pscf.setReturnGeneratedKeys(true); } return getJdbcOperations().update(pscf.newPreparedStatementCreator(params), generatedKeyHolder); }
/** * Convert a Map of named parameter values to a corresponding array. * <p>This is a shortcut version of * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}. * @param sql the SQL statement * @param paramMap the Map of parameters * @return the array of values */ public static Object[] buildValueArray(String sql, Map<String, ?> paramMap) { ParsedSql parsedSql = parseSqlStatement(sql); return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap), null); }
/** * Obtain a parsed representation of the given SQL statement. * <p>The default implementation uses an LRU cache with an upper limit of 256 entries. * @param sql the original SQL statement * @return a representation of the parsed SQL statement */ protected ParsedSql getParsedSql(String sql) { if (getCacheLimit() <= 0) { return NamedParameterUtils.parseSqlStatement(sql); } synchronized (this.parsedSqlCache) { ParsedSql parsedSql = this.parsedSqlCache.get(sql); if (parsedSql == null) { parsedSql = NamedParameterUtils.parseSqlStatement(sql); this.parsedSqlCache.put(sql, parsedSql); } return parsedSql; } }
@Test // SPR-2544 public void substituteNamedParametersWithLogicalAnd() { String expectedSql = "xxx & yyyy"; String newSql = NamedParameterUtils.substituteNamedParameters(expectedSql, new MapSqlParameterSource()); assertEquals(expectedSql, newSql); }