mysql contain和like,命名参数不适用于MySql LIKE语句

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

public List 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.

解决方案

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.