Spring jdbcTemplate dynamic where clause

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 ent开发者_开发百科ers 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.

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

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

Spring Data and Hibernate have that kind of functionality. Though it might not be worth dragging in such big framework for your app.

You can try to check out SimpleJdbcInsert http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html

Edit: Alternatively you can try to fix it in SQL with checking on empty, but if you have lots of data to go through, this technique will slow down your request.

"select * from address 
where (shopname = ? or shopname = null)
 and (city = ? or city = null)";

If Scala is an option to you, the query could be constructed with something like this:

case class Search(shopname:String, city:String = None) {
 def sql = "select * from address where shopname = '"+shopname+"'" + city.map(" and city = '"+
      _ +"'").getOrElse("")  
}

Example usage:

Search("lloh").sql
Search("lloh", Some("Austin")).sql