java Spring Like 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3246807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring Like clause
提问by Chris
I am trying to use a MapSqlParameterSource to create a query using a Like clause.
我正在尝试使用 MapSqlParameterSource 创建使用 Like 子句的查询。
The code is something like this. The function containing it receives nameParam:
代码是这样的。包含它的函数接收 nameParam：
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE :pname ";
String finalName= "'%" +nameParam.toLowerCase().trim() + "%'";
MapSqlParameterSource namedParams= new MapSqlParameterSource();
namedParams.addValue("pname", finalName);
int count= this.namedParamJdbcTemplate.queryForInt(namecount, namedParams);
This does not work correctly, giving me somewhere between 0-10 results when I should be receiving thousands. I essentially want the final query to look like:
这不能正常工作，当我应该收到数千个结果时，给了我 0-10 个结果。我基本上希望最终的查询看起来像：
SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%name%'
but this is evidently not happening. Any help would be appreciated.
但这显然不会发生。任何帮助，将不胜感激。
Edit:
编辑：
I have also tried putting the '%'s in the SQL, like
我也试过把 '%' 放在 SQL 中，比如
String finalName= nameParam.toLowerCase().trim();
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%:pname%' "
;
;
but this does not work either.
但这也不起作用。
回答by Jeff
You don't want the quotes around your finalName string. with the named parameters you don't need to specify them. This should work:
您不希望 finalName 字符串周围有引号。使用命名参数，您无需指定它们。这应该有效：
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE :pname ";
String finalName= "%" + nameParam.toLowerCase().trim() + "%";
MapSqlParameterSource namedParams= new MapSqlParameterSource();
namedParams.addValue("pname", finalName);
int count= this.namedParamJdbcTemplate.queryForInt(namecount, namedParams);
回答by Andrea Girardi
This solution worked for me. I put the "%" on the Object[] parameters list:
这个解决方案对我有用。我将“%”放在 Object[] 参数列表中：
String sqlCommand = "SELECT customer_id, customer_identifier_short, CONCAT(RTRIM(customer_identifier_a),' ', RTRIM(customer_identifier_b)) customerFullName "
+ " FROM Customer "
+ " WHERE customer_identifier_short LIKE ? OR customer_identifier_a LIKE ? "
+ " LIMIT 10";
List<Customer> customers = getJdbcTemplate().query(sqlCommand, new Object[] { query + "%", query + "%"}, new RowMapper<Customer>() {
public Customer mapRow(ResultSet rs, int i) throws SQLException {
Customer customer = new Customer();
customer.setCustomerFullName(rs.getString("customerFullName"));
customer.setCustomerIdentifier(rs.getString("customer_identifier_short"));
customer.setCustomerID(rs.getInt("customer_id"));
return customer;
}
});
return customers;
回答by Brian
Have you tried placing the % wild cards in your sql string (not the bind variable value itself):
您是否尝试在 sql 字符串中放置 % 通配符（不是绑定变量值本身）：
String finalName= nameParam.toLowerCase().trim();
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%:finalName%'";
回答by bnguyen82
We can use simple JdbcTemplate instead of NamedParamJdbcTemplate
我们可以使用简单的 JdbcTemplate 而不是 NamedParamJdbcTemplate
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE ? ";
String finalName= "%" +nameParam.toLowerCase().trim() + "%"; //Notes: no quote
getJdbcTemplate().queryForInt(namecount, new Object[] {finalName});
Hope it helpful for someone using JdbcTemplate
希望对使用 JdbcTemplate 的人有所帮助
