Named parameter not replaced inside single quotation marks

Question

I'm trying to use a named parameter but when I have them inside of single quotation marks they don't get replaced for the query and it returns 0 results. How do I use my parameter inside the single quotation marks?

protected static final String SQL_QUERY =
        "    SELECT * \n" +
        "    FROM example \n" +
        "    WHERE UPPER(name) LIKE UPPER('%:query%') \n";

@Autowired
private NamedParameterJdbcTemplate template;

public List<Item> getResultFromQuery(String query) throws Exception {
return (List<Item>) template.query(SQL_QUERY,
                    new MapSqlParameterSource().addValue("query", query), resultSetExtractor);
}

You don't need the quotation marks. The prepared statement will add them for you. — Michael, Feb 07 '19 at 18:34

score 0 · Answer 1 · answered Feb 07 '19 at 18:35

0

May be you are using \n inside single quotations, you need to encapsulate \n and for the expression '%:query%' you can write it as... '+%:query%+' inside single qutations or may be ' "+%:query%+" '

answered Feb 07 '19 at 18:35

Ali Haider

41
4

Siva Bhargava · Answer 2 · 2022-09-22T15:21:14.683

MapSqlParameterSource().addValue accepts parametername & value as inputs. Here value type is Object So, mention the type of the parameter you are giving. In your case it is string so give something like String.format(query) and change '%:query%' to :query

    protected static final String SQL_QUERY =
        "    SELECT * \n" +
        "    FROM example \n" +
        "    WHERE UPPER(name) LIKE UPPER(:query) \n";

@Autowired
private NamedParameterJdbcTemplate template;

public List<Item> getResultFromQuery(String query) throws Exception {
return (List<Item>) template.query(SQL_QUERY,
                    new MapSqlParameterSource().addValue("query", String.format(query)), resultSetExtractor);
}

Named parameter not replaced inside single quotation marks

2 Answers2