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.

Solution 1

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?

Solution 2

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

Solution 3

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" 

Comments

[Navrattan Yadav]

it work for me if i use : "select * from FOO where CODE in (:codes) and NAME like "+namePart+" '%' ";

[Olivier Coilland]

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

[Istvan Devai]

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.

[ZeroOne]

@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.

[Michael-O]

Will Spring escape the codes list to multiple question marks? I don't think so.

[ZeroOne]

Which code are you referring to? @MMKarami's?

[Abishek.K]

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...

[ZeroOne]

Which version of Spring Boot are you using?

[Abishek.K]

Spring Boot version 2.6.2

[ZeroOne]

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. 👍

[Abishek.K]

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..