Home > Java > Searching Using Fragment Of A String Using Spring

Searching Using Fragment Of A String Using Spring

July 23rd, 2009

I encountered something rather annoying using Spring. I was query something similar to

SELECT last_name FROM users WHERE email_address LIKE ?

Spring execute the query but returned 0 results when I expected at least 1 (depending on how many characters of the last_name I had passed as a parameter to this query.

Only when I changed the relevant part of the code from

Object[] parameters = new Object[1];
parameters[0] = searchStr;

to

Object[] parameters = new Object[1];
parameters[0] = searchStr + "%";

did I get the correct number of records.

Here’s the complete code (parts modified for this post)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

public List<String> retrieveLastNamesBasedOnEmailAddress(String searchStr)
{ // Constructing the query
  String sqlStr = "SELECT last_name FROM users WHERE email_address LIKE ?";
  // Constructing the parameter array
  Object[] parameters = new Object[1];
  parameters[0] = searchStr + "%";
  // Executing the query
  if(this.jdbcTemplate == null) 
    this.jdbcTemplate = new JdbcTemplate(getDataSource());
  List<String> list = this.jdbcTemplate.query(
      sqlStr, 
      parameters, 
      new SingleColumnRowMapper(String.class)
      );
  return list;
}

Depending on the permissible fragment of the the search parameter, perhaps adding in the % in front and back of the search parameter might be a better idea.

6

  parameters[0] = "%" + searchStr + "%";
Categories: Java Tags:
Comments are closed.