Spring jdbcTemplate dynamic where clause

Question

Is it possible to generate arbitrary where condtions SQL query through Jdbc template:

example:

If i pass value for 1 parameter (only name) : search by name

"select * from address where shopname = ?";

If i pass value for 2 parameter (name and city) - search by shopname and city:

"select * from address where shopname = ? and city = ?";

I have mupliple search fields. 7 fields. If user enters any combination. i have search only based on parameter. How to dynamically pass the parameters to the sql. Need snippet/Example how to achieve this.

Answer 1

What you want is some sort of criteria building api, which Hibernate has. Unfortunately, I don't think Spring's JdbcTemplate has any such facility. Others will correct me if I'm wrong...

Answer 2

Though as some guys already suggested that Hibernate is the best way of doing this, but still i think you can try this approach-

String sql = "select * from address where 1 = 1";

if(shopname != null)
  sql += "and shopname = :shopname";

if(city!= null)
  sql += "and city = :city";

and so on..and use NamedParameterJdbcTemplate