How to search string LIKE 'something%' with Java Spring Framework?

Question

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%';

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);
}

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

I could be brutal and enter it as

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

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

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

it work for me if i use : "select * from FOO where CODE in (:codes) and NAME like "+namePart+" '%' "; — Navrattan Yadav, Jan 20 '17 at 08:21

score 42 · Accepted Answer · answered Jun 19 '12 at 15:08

42

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);
}

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?

answered Jun 19 '12 at 15:08

ZeroOne

3,041
3
31
52

Nerf, that was the solution I was about to give :D That's the way I was doing it here, before I moved to MyBatis, but that's another story :p – Olivier Coilland Jun 19 '12 at 15:10
AFAIK Spring does escape but only as far as preventing SQL injection. Otherwise, LIKE expects an expression, and % is part of the expression, so I see this more or less logical this way. – Istvan Devai Jun 19 '12 at 15:13
@OlivierCoilland Ha, sorry for stealing your answer. ;) Thanks for the confirmation though, at least I know somebody else uses this technique as well. And thanks for the pointer to MyBatis; I've never heard about it before but it looks interesting. – ZeroOne Jun 19 '12 at 15:16
Will Spring escape the codes list to multiple question marks? I don't think so. – Michael-O Jun 20 '12 at 21:42

score 5 · Answer 2 · edited May 27 '20 at 09:56

5

For named parameters to work, you need to use NamedParameterJdbcTemplate

params.put("name", "Joe%");

jdbcTemplate.query("select * from FOO where CODE in (:codes) and NAME like :name"

edited May 27 '20 at 09:56

djm.im

3,295
4
30
45

answered Jun 19 '12 at 15:09

Istvan Devai

3,962
23
21

score 4 · Answer 3 · answered Apr 26 '18 at 14:13

In another form, I encountered the same problem, and I tried to solve it via this manner:

public List<MyEntity> getMyEntityValuesBySearchText(String searchText) {

    String query = "SELECT * FROM MY_ENTITY_TABLE WHERE NAME LIKE ?";
    return this.getJdbcTemplate().query(query, new String[] { "%" + searchText + "%" },
                (rs, rowNum) -> new MyEntity(rs.getLong("PK"), rs.getString("NAME")));
}

score 0 · Answer 4 · edited Jan 03 '22 at 16:22

0

There is a problem with the code above. The code structure is correct but there is a problem in mapping the variable. You will get the error message as "Index Out of Bound" SQL Exception error.

To avoid that error we map our variable properly using the class "MySqlParameterSource". We needed to create an object for that class and pass our variable inside to map out variables.

Follow this as an example.

 public List<Products> getParticular2(@RequestParam String charc){

        String sql ="select * from products where name like :name";

        Map<String, Object> params = new HashMap<String, Object>();
                params.put("name", charc+"%");
        
        MapSqlParameterSource param = new MapSqlParameterSource(params);
        
        List <Products> list = template.query(sql, param, new 
                                BeanPropertyRowMapper<>(Products.class));
        return list;
    }

edited Jan 03 '22 at 16:22

Laurel

5,965
14
31
57

answered Jan 03 '22 at 05:45

Abishek.K

1
2

Which code are you referring to? @MMKarami's? – ZeroOne Jan 03 '22 at 06:48
Actually your code does not have any mistake...But in spring boot application ...To map the parameter we need that class..before I am using your method but I got an error an index out of bound... And I googled to find the class sqlparametersource... – Abishek.K Jan 03 '22 at 10:06
Which version of Spring Boot are you using? – ZeroOne Jan 03 '22 at 11:42
Spring Boot version 2.6.2 – Abishek.K Jan 03 '22 at 12:22
Ok, well, my solution worked for me at the time, i.e. in 2012, so something may certainly have changed since then. I'm happy you found a solution to your problem, thanks for sharing it. – ZeroOne Jan 03 '22 at 13:04
Yes .. Thank You..I belive that your code will work...Due to the change in the maven dependency file, its not working in latest version..Actucally Before I don't have an idea to map the variables...By seeing your code I got an idea to map the variables as like query and I got solution..Your code helped me a lot ...Thanks a Lot..bro.. – Abishek.K Jan 03 '22 at 13:16

How to search string LIKE 'something%' with Java Spring Framework?

4 Answers4

Linked