/** * 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; } }
/** * 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); }
/** * 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; } }
/** * 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); }
/** * 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); }
@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 parseSqlStatementWithLogicalAnd() { String expectedSql = "xxx & yyyy"; ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(expectedSql); assertEquals(expectedSql, NamedParameterUtils.substituteNamedParameters(parsedSql, null)); }
@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]); }
@Test // SPR-8280 public void parseSqlStatementWithQuotedSingleQuote() { String sql = "SELECT ':foo'':doo', :xxx FROM DUAL"; ParsedSql psql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(1, psql.getTotalParameterCount()); assertEquals("xxx", psql.getParameterNames().get(0)); }
@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 parseSqlStatementWithQuotesAndCommentBefore() { String sql = "SELECT /*:doo*/':foo', :xxx FROM DUAL"; ParsedSql psql = NamedParameterUtils.parseSqlStatement(sql); assertEquals(1, psql.getTotalParameterCount()); assertEquals("xxx", psql.getParameterNames().get(0)); }
@Test public void parseSqlStatementWithQuotesAndCommentAfter() { String sql2 = "SELECT ':foo'/*:doo*/, :xxx FROM DUAL"; ParsedSql psql2 = NamedParameterUtils.parseSqlStatement(sql2); assertEquals(1, psql2.getTotalParameterCount()); assertEquals("xxx", psql2.getParameterNames().get(0)); }
@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)); }