Geek Answers Handbook

Spring jdbcTemplate dynamic where where

Is it possible to generate an arbitrary value where it computes the SQL query through the Jdbc template:

Example:

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

"select * from address where shopname = ?";

If I pass the value for 2 parameters (name and city) - search by store name and city:

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

I have mupliple search fields. 7 fields. If the user enters any combination. I have a search only by parameter. How to dynamically pass parameters in sql. Need a snippet / An example of how to do this.

+7
source share
3 answers

What you want is a kind of api criteria building that Hibernate has. Unfortunately, I do not think Spring JdbcTemplate has such an option. Others will correct me if I am wrong ...

+9
source

Spring Data and Hibernate have this functionality. Although it may not be worth dragging and dropping such large frames for your application.

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

Edit: Alternatively, you can try to fix this in SQL with checking for empty, but if you have a lot of data to go through, this technique will slow down your query.

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

If Scala is an option for you, the query can be constructed using the following:

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

Usage example:

 Search("lloh").sql Search("lloh", Some("Austin")).sql
-3
source

More articles:


All Articles