How can I handle a search by a dynamic set of variables in JDBC?

Question

I'm making a dynamic query to pass to mysql based on which variables I'm passing are not null.

I can build the query without a problem, but I can't understand how to work on the right group of variables.

for example, in a situation where everything I pass is null I'll just have a "SELECT * FROM mytable", but if I'm passing (null, int, null string, null) or (float, int, null, null, null), with a query like "SELECT * FROM mytable WHERE x=int AND..." I'm not sure how to work on the right combination.

I could open tons of if for every combination, but I feel the wouldn't be the right move.

Answer 1

If I undestood it correctly you need to build a query and apply only those values in the where conditions which are not null.

You use StringBuilder to build your query, like below.

StringBuilder fixedQuery = new StringBuilder("SELECT * FROM mytable");

StringBuilder conditions = new StringBuilder();
if(myIntMember != null)
  conditions.append("x=:myIntPlaceHolder")
if(myStringMember != null)
  conditions.append(" AND y=:myStringPlaceHolder")

 ....so on 

 if(conditions.length()>0)
   fixedQuery.append(" WHERE ").append(conditions);
 //Later on just palce the named parameter value accordingly using the similar if conditions.
 //You dont have to check all permutation combinations.

Answer 2

You can do it ways mentioned below:

1. dynamically build the query string based on values of vars. Like if x is not null, add its query portion:

 String query= "select * from myTable where 1=1 ";
    if(!Strings.isNullOrEmpty(x)) //string type
      {
         query+=" and columnName="+x; //or add your condition
       }
    if(y>0) //for integer type
       {
          query+=" and columnName="+y;// or add your condition you need
       }

This way you can build your entire query. Be careful with the syntax, spaces

2.

as @PM 77-1 suggested. Suppose, let's say your variable x can be null or not null. For both cases, you can handle it this way:

select * from myTable where (x is null or x<10) AND (Y is null or y<=6); //just add your conditions after `or` operator