CODEWITHSUNDEEP
javasqljdbcprepared-statementwhere-clause
ayan ahmedov
ayan ahmedov

Reputation: 391

prepared statement varying number of where clauses

I need to implement a method to query with varying number of conditions. I don't want to make a new prepared statement one every single time , since that is why I'm using prepared statements in the first place. Basically what I have is this with 3 conditions.

String sql = "select * from t where attr1 = ? and attr2 = ? and attr3 = ?;";

But depending on the parameter I get ,I might only need a subset of conditions.Say in a method like this void (SomeWrapper filter){..} where any not null field of filter is used in a condition.

So I might end up needing queries like below

String sql1 = "select * from t where attr1 = ? and attr3 = ?;";
String sql2 = "select * from t where attr2 = ? and attr3 = ?;";
String sql3 = "select * from t where attr2 = ?;"; 
etc..

That may be ok for only 3 conditions but I have many attributes in the table and obviously I can not hard code every possible combination of where clauses.

I thought it might be possible to do something like this by producing a query like this one

select * from t where attr1 = value1 and attr2 = attr2 and attr3 = value3

Now I don't know how to make such a query with prepared statements. Since I don't have a way to set attr2 = attr2. Any suggestion to accomplish that?

Upvotes: 0

Views: 3501

Answers (3)

straville
straville

Reputation: 1016

See this question

I've found following 2 ways of achieving multi-can-be-null-parameters into SQL implementations convenient:

  1. Check whether the column matches parameter or is null (lighter to implement)

    "SELECT * FROM Courses WHERE " 
 + "(product = ? or ? is null) " 
 + "AND (location = ? or ? is null) " 
 + "AND (courseType = ? or ? is null) " 
 + "AND (category = ? or ? is null);

  2. Dynamic addition of WHERE/AND clauses based on parameters (more flexible)

    <begin of the SELECT statement, initialization of StringBuilder or StringBuffer sb>
for (T param: parameters) {
    if (param != null) {
         sb.append("<parameter specific WHERE/AND clause>");
    }
}

Upvotes: -1

Jhenry
Jhenry

Reputation: 151

I had a same situation. Where in the where clause is built dynamically depending on the search criteria such as search first name, last name, age, etc with values ranging from String, Integer and Boolean. On top of it, i cannot have default values set. I was able to accomplish like this below and hope it will be of help and reference in future. I have used counter in this example, however, this can also be accomplished by using map.

int paramIndex =1
int lnIdx = 0,fnIdx = 0,mnIdx = 0, iaIdx = 0;

StringBuffer queryString = new StringBuffer("select IsActive, FirstName, MiddleName, LastName, Userid");
StringBuffer fromClause = new StringBuffer("FROM table1, table2, table3");
STringBuffer whereClause = new StringBuffer(" table1.Userid = table2.

if(StringUtil.isNotBlank(firstName){
 whereClause.append(" and table3.firstName like ?");
 fnIdx = paramIndex++;
}


if(StringUtil.isNotBlank(firstName){
 whereClause.append(" and table3.lastName like ?");
 lnIdx = paramIndex++;
}


if(StringUtil.isNotBlank(firstName){
 whereClause.append(" and table3.middleName like ?");
 mnIdx = paramIndex++;
}

if(StringUtil.isNotBlank(firstName){
 whereClause.append(" and table3.isActive = ?);
 iaIdx = paramIndex++;
}

try{

queryString.append(fromClause);
queryString.append(whereClause);

PreparedStatement stmt = getPreparedStatement(queryString.toString());

if(fnIdx >0 {
 stmt.setString(fnIdx,firstName+"%");
}
if(mnIdx >0 {
 stmt.setString(mnIdx,middleName+"%");
}
if(lnIdx >0 {
 stmt.setString(lnIdx,lastName+"%");
}
if(iaIdx >0 {
 stmt.setInt(iaIdx,isActive);
}

}catch(Exception e){}

Upvotes: 0

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

If there's some value that you'll never want to use as a criterion (such as empty string), then you could use

select * from t where ? in (attr1, '') and ? in (attr2, '') and ? in (attr3,'')

and just set each parameter to empty string if you don't want the corresponding column to be checked.

Upvotes: 2

Related Questions