Spring jdbcTemplate dynamic where clause

Question

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.

hvgotcodes · Accepted Answer · 2015-03-17T13:30:01.353

11

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

edited Mar 17 '15 at 13:30

answered May 22 '11 at 15:45

hvgotcodes

118,147
33
203
236

score 3 · Answer 2 · answered May 26 '18 at 21:21

3

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

answered May 26 '18 at 21:21

Jainesh kumar

93
1
9

2

... and use StringBuilder() instead of string concatenation :) – Slava Semushin Oct 12 '18 at 20:58

score 0 · Answer 3 · answered Mar 17 '15 at 13:49

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

Thiago Nogueira · Answer 4 · 2023-03-13T13:39:58.163

0

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

edited Mar 13 '23 at 13:39

answered Mar 13 '23 at 13:37

Thiago Nogueira

1
1

I fail to see the connection to Spring JDBCtemplate. – greybeard Mar 18 '23 at 18:52

score -3 · Answer 5 · answered May 26 '11 at 20:44

-3

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

answered May 26 '11 at 20:44

eptx

801
7
17

1

I'm not familiar with Scala, but from the String concatenation in this suggested approach I'd suggest avoiding this approach to avoid the possibility of SQL Injection attacks. – Kevin Hooke Nov 07 '12 at 23:44
1

That would be very unsafe due to SQL Injection risks. – BrianC May 22 '14 at 23:39

Spring jdbcTemplate dynamic where clause

5 Answers5