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.

Answer 1

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

Answer 2

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