Spring Like clause

Question

I am trying to use a MapSqlParameterSource to create a query using a Like clause.

The code is something like this. The function containing it receives 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:

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

 String finalName= nameParam.toLowerCase().trim();

 String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%:pname%' "

;

but this does not work either.

Briarwood

Answer 1

You don't want the quotes around your finalName string. with the named parameters you don't need to specify them. This should work:

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

Southwestwardly

You are my hero. Thanks a lot. I've been working on this for an embarassingly long period of time. – Briarwood

What is the benefit of concatenating the % symbols into the parameter vs directly putting it in your sql query? SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%'+:pname+'%' – Bluefarb

Answer 2

This solution worked for me. I put the "%" on the Object[] parameters list:

    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;

Nicks

Answer 3

Have you tried placing the % wild cards in your sql string (not the bind variable value itself):

String finalName= nameParam.toLowerCase().trim();
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%:finalName%'";

Grainger

Can you suggest a way to modify the existing code so that I can use the '?' placeholder? I don't believe you can use it with a MapSqlParameterSource. Thanks – Briarwood

I've tried that. It gave me similar (possibly same) results. I think there must be something with the single quotes or the '%' signs that interfere with the query. – Briarwood

You can't leave the variable with % though, so you now need: String finalName= nameParam.toLowerCase().trim(); The wild cards are not removed from the finalName variable. Updated my answer. – Grainger

Thank you, I see that my edit was unclear but I meant to reflect that I had essentially tried what you are suggesting but it still does not work. Do you have any other suggestions? – Briarwood

Answer 4

We can use simple JdbcTemplate instead of 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

Marsiella

Answer 5

You can put the pname variable between pipes in your query statement:

String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE '%'||:pname||'%' "

Fiddlehead