Andrey Bobroff Andrey Bobroff - 7 months ago 10
Java Question

Named parameter doesn't work with MySql LIKE statement

I'm trying to organize a search function on the site, using the Spring-jdbc NamedParameterJdbcTemplate.

public List<PhoneEntry> searchPhoneEntries(String search, String username) {

String SQL = "select * from entries, users where users.enabled=true " +
"and entries.username=:username " +
"and concat(secondName, firstName, patronymic, mobile, tel, " +
"address, entries.email) like ('%:search%')";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("username", username);
params.addValue("search", search);
return jdbcTemplate.query(SQL, params, new PhoneEntryMapper());
}


But I get an empty list and have no any error.

When using a simple concatenation:

"...like '%" + search + "%'";


it working properly, but as I understand it is not safe.

I tried also add '%' symbols in parameter:

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("username", username);
params.addValue("search", "'%" + search + "%'");
return jdbcTemplate.query(SQL, params, new PhoneEntryMapper());


But it doesn't work too.

Answer

The solution is to add the parameter without quotes

params.addValue("search", "%" + search + "%");

and in the SQL string write

String sql = "... like :search";

Your first approach ('%:search%') did not work since named parameters are not recognized within string literals.

The second approach params.addValue("search", "'%" + search + "%'"); did not work since now the quotes were part of the like string, therefore asking Mysql to look for strings which start and end with a quote and contain the search term.

Comments