我有一个带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%'?
发布评论