CODEWITHSUNDEEP
springjdbctemplate
minil
minil

Reputation: 7135

Spring jdbcTemplate dynamic where clause

Is it possible to generate arbitrary where conditions 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 multiple 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.

Upvotes: 9

Views: 18194

Answers (5)

Thiago Nogueira
Thiago Nogueira

Reputation: 1

If you are using Kotlin, the query could be constructed like this:

fun optParamStr(param: Any?, queryParamName: String): String? =
        param?.let { "$queryParamName = :$queryParamName" }


val params = listOf(
                value1 to filed1,
                value2 to field2,
                value3 to field3
            ).mapNotNull { optParamStr(it.first, it.second) }.joinToString(" AND ")

Upvotes: 0

Jainesh kumar
Jainesh kumar

Reputation: 93

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

Upvotes: 3

Tzen
Tzen

Reputation: 1436

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

Upvotes: 0

hvgotcodes
hvgotcodes

Reputation: 120188

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

Upvotes: 11

eptx
eptx

Reputation: 801

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

Upvotes: -3

Related Questions