如何使用Java Spring Framework搜索字符串LIKE'something％'? [英] How to search string LIKE 'something%' with Java Spring Framework?

问题描述

我有一个带Foos的MySQL表.每个Foo都有一个数字非唯一代码和一个名称.现在，我需要查找具有某些特定代码之一的Foo是否恰好具有以给定字符串开头的名称.在普通的SQL中，这是微不足道的:

I've got a MySQL table with Foos. Each Foo has a numeric non-unique code and a name. Now I need to find if any Foo with one of certain codes happens to have a name that starts with a given string. In normal SQL this would be trivial:

select * from FOO where CODE in (2,3,5) and NAME like 'bar%';

但是我现在如何在春季正确地做到这一点?不需要'like'运算符，我可以这样做:

But how would I properly do this in Spring now? Without the need for the 'like' operator I'd do it like this:

public List<Foo> getByName(List<Integer> codes, String namePart) {
    String sql = "select * from FOO where CODE in (:codes) and NAME=:name"
    Map<String,Object> params = new HashMap<String,Object>();
    params.put("codes", codes);
    params.put("name", namePart);
    return getSimpleJdbcTemplate().query(sql, new FooRowMapper(), params);
}

但是，使用'like'似乎无效:NAME like :name%，NAME like ':name%'或NAME like ?%，当使用占位符而不是命名参数时.

However, with 'like' nothing seems to work: NAME like :name%, NAME like ':name%', or NAME like ?% when using the placeholders instead of named parameters.

我可能很残酷，请输入

String sql = "select * from FOO where CODE in (:codes) and NAME like '"+namePart+"%'";` 

但是显然，如果Spring正确地清理输入参数等，那就更好了.

but obviously it would be more than nice if Spring would sanitize the input parameters properly etc, you know...

您可能认为Spring会以某种方式支持此操作，但我无法弄清楚.

You'd think Spring would support this somehow but I cannot figure it out.

推荐答案

当然，我不得不在一天结束之前再尝试一件事"，瞧瞧，我所有的单元测试都突然通过了:/p>

Wait, of course I had to "try one more final thing" before calling it a day, and lo and behold, all my unit tests suddenly pass:

public List<Foo> getByName(List<Integer> codes, String namePart) {
    String sql = "select * from FOO where CODE in (:codes) and NAME like :name"
    Map<String,Object> params = new HashMap<String,Object>();
    params.put("codes", codes);
    params.put("name", namePart+"%");
    return getSimpleJdbcTemplate().query(sql, new FooRowMapper(), params);
}

我没想到要在参数中输入％"，我确定Spring会自动对其进行转义.我想知道我做对了吗?

I didn't think of entering the "%" in the parameter, I was certain Spring would automatically escape it. I wonder if I'm doing it right?

这篇关于如何使用Java Spring Framework搜索字符串LIKE'something％'?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！