/** * 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); }
/** * Parse the SQL statement and locate any placeholders or named parameters. * Named parameters are substituted for a JDBC placeholder and any select list * is expanded to the required number of placeholders. * <p>This is a shortcut version of * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}. * @param sql the SQL statement * @param paramSource the source for named parameters * @return the SQL statement with substituted parameters */ public static String substituteNamedParameters(String sql, SqlParameterSource paramSource) { ParsedSql parsedSql = parseSqlStatement(sql); return substituteNamedParameters(parsedSql, paramSource); }
/** * 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); }
/** * 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); }
@Test public void testParseSqlStatementWithStringContainingQuotes() { String expectedSql = "select 'first name' from artists where id = ? and quote = 'exsqueeze me?'"; String sql = "select 'first name' from artists where id = :id and quote = 'exsqueeze me?'"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }
@Test // SPR-4612 public void parseSqlStatementWithPostgresCasting() { String expectedSql = "select 'first name' from artists where id = ? and birth_date=?::timestamp"; String sql = "select 'first name' from artists where id = :id and birth_date=:birthDate::timestamp"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }
@Test // SPR-2544 public void substituteNamedParametersWithLogicalAnd() { String expectedSql = "xxx & yyyy"; String newSql = NamedParameterUtils.substituteNamedParameters(expectedSql, new MapSqlParameterSource()); assertEquals(expectedSql, newSql); }
@Test // SPR-3173 public void variableAssignmentOperator() { String expectedSql = "x := 1"; String newSql = NamedParameterUtils.substituteNamedParameters(expectedSql, new MapSqlParameterSource()); assertEquals(expectedSql, newSql); }
/** * Generic method to execute the update given named parameters. * All other update methods invoke this method. * @param paramMap a Map of parameter name to parameter object, * matching named parameters specified in the SQL statement * @return the number of rows affected by the update */ public int updateByNamedParam(Map<String, ?> paramMap) 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()); int rowsAffected = getJdbcTemplate().update(newPreparedStatementCreator(sqlToUse, params)); checkRowsAffected(rowsAffected); return rowsAffected; }
@Test public void substituteNamedParametersWithStringContainingQuotes() { String expectedSql = "select 'first name' from artists where id = ? and quote = 'exsqueeze me?'"; String sql = "select 'first name' from artists where id = :id and quote = 'exsqueeze me?'"; String newSql = NamedParameterUtils.substituteNamedParameters(sql, new MapSqlParameterSource()); assertEquals(expectedSql, newSql); }
@Test // SPR-2544 public void parseSqlStatementWithLogicalAnd() { String expectedSql = "xxx & yyyy"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(expectedSql); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }
/** * Method to execute the update given arguments and * retrieve the generated keys using a KeyHolder. * @param paramMap a Map of parameter name to parameter object, * matching named parameters specified in the SQL statement * @param generatedKeyHolder the KeyHolder that will hold the generated keys * @return the number of rows affected by the update */ public int updateByNamedParam(Map<String, ?> paramMap, KeyHolder generatedKeyHolder) 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()); int rowsAffected = getJdbcTemplate().update(newPreparedStatementCreator(sqlToUse, params), generatedKeyHolder); checkRowsAffected(rowsAffected); return rowsAffected; }
@Test public void substituteNamedParameters() { MapSqlParameterSource namedParams = new MapSqlParameterSource(); namedParams.addValue("a", "a").addValue("b", "b").addValue("c", "c"); assertEquals("xxx ? ? ?", NamedParameterUtils.substituteNamedParameters("xxx :a :b :c", namedParams)); assertEquals("xxx ? ? ? xx ? ?", NamedParameterUtils.substituteNamedParameters("xxx :a :b :c xx :a :a", namedParams)); }
@Test // SPR-7476 public void parseSqlStatementWithEmptyBracketsOrBracketsInQuotes() { String expectedSql = "select foo from bar where baz = b:{}z"; String sql = "select foo from bar where baz = b:{}z"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(0, parsedSql.getParameterNames().size()); String finalSql = NamedParameterUtils.substituteNamedParameters(parsedSql, null); assertEquals(expectedSql, finalSql); String expectedSql2 = "select foo from bar where baz = 'b:{p1}z'"; String sql2 = "select foo from bar where baz = 'b:{p1}z'"; ParsedSql parsedSql2 = NamedParameterUtils.parseSqlStatement(sql2); assertEquals(0, parsedSql2.getParameterNames().size()); String finalSql2 = NamedParameterUtils.substituteNamedParameters(parsedSql2, null); assertEquals(expectedSql2, finalSql2); }
@Test public void parseSqlStatementWithSingleLetterInBrackets() { String expectedSql = "select foo from bar where baz = b?z"; String sql = "select foo from bar where baz = b:{p}z"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(1, parsedSql.getParameterNames().size()); assertEquals("p", parsedSql.getParameterNames().get(0)); String finalSql = NamedParameterUtils.substituteNamedParameters(parsedSql, null); assertEquals(expectedSql, finalSql); }
@Test // SPR-7476 public void parseSqlStatementWithEscapedColon() { String expectedSql = "select '0\\:0' as a, foo from bar where baz < DATE(? 23:59:59) and baz = ?"; String sql = "select '0\\:0' as a, foo from bar where baz < DATE(:p1 23\\:59\\:59) and baz = :p2"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(2, parsedSql.getParameterNames().size()); assertEquals("p1", parsedSql.getParameterNames().get(0)); assertEquals("p2", parsedSql.getParameterNames().get(1)); String finalSql = NamedParameterUtils.substituteNamedParameters(parsedSql, null); assertEquals(expectedSql, finalSql); }
@Test // SPR-7476 public void parseSqlStatementWithBracketDelimitedParameterNames() { String expectedSql = "select foo from bar where baz = b??z"; String sql = "select foo from bar where baz = b:{p1}:{p2}z"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(2, parsedSql.getParameterNames().size()); assertEquals("p1", parsedSql.getParameterNames().get(0)); assertEquals("p2", parsedSql.getParameterNames().get(1)); String finalSql = NamedParameterUtils.substituteNamedParameters(parsedSql, null); assertEquals(expectedSql, finalSql); }
@Test // SPR-15382 public void parseSqlStatementWithPostgresAllArrayStringsExistsOperator() { String expectedSql = "select '[\"3\", \"11\"]'::jsonb ?& '{1,3,11,12,17}'::text[] AND ? = 'Back in Black'"; String sql = "select '[\"3\", \"11\"]'::jsonb ?& '{1,3,11,12,17}'::text[] AND :album = 'Back in Black'"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(1, parsedSql.getTotalParameterCount()); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }
@Test // SPR-15382 public void parseSqlStatementWithPostgresAnyArrayStringsExistsOperator() { String expectedSql = "select '[\"3\", \"11\"]'::jsonb ?| '{1,3,11,12,17}'::text[]"; String sql = "select '[\"3\", \"11\"]'::jsonb ?| '{1,3,11,12,17}'::text[]"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(0, parsedSql.getTotalParameterCount()); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }
@Test // SPR-13582 public void parseSqlStatementWithPostgresContainedOperator() { String expectedSql = "select 'first name' from artists where info->'stat'->'albums' = ?? ? and '[\"1\",\"2\",\"3\"]'::jsonb ?? '4'"; String sql = "select 'first name' from artists where info->'stat'->'albums' = ?? :album and '[\"1\",\"2\",\"3\"]'::jsonb ?? '4'"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(1, parsedSql.getTotalParameterCount()); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }