命名参数不适用于MySql LIKE语句 [英] Named parameter doesn't work with MySql LIKE statement

问题描述

我正在尝试使用Spring-jdbc NamedParameterJdbcTemplate在网站上组织搜索功能.

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.

使用简单串联时:

"...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());

但是它也不起作用.

推荐答案

解决方案是添加不带引号的参数

The solution is to add the parameter without quotes

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

，并在SQL字符串中写入

and in the SQL string write

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

---

您的第一种方法('%:search%')不起作用，因为在字符串文字中无法识别命名参数.

---

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

第二种方法params.addValue("search", "'%" + search + "%'");无效，因为现在引号是like字符串的一部分，因此要求Mysql查找以引号开头和结尾并包含搜索项的字符串.

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.

这篇关于命名参数不适用于MySql LIKE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！