Conditional 'where' condition

[meera rao]

I have a situation , where there are 5 text boxes and the User can enter values in any number of the text boxes and Query for the result.

What could be a possible solution in this case. Is it possible to have selective 'where' conditions.

[Paul Sturrock]

Is it possible to have selective 'where' conditions.

Absolutely. Its just a matter of manipulating the SQL string you use (i.e. append more to it if a checkbox is checked).

[meera rao]

Can you explain more clearly, how can we append conditions

[Paul Sturrock]

Well this depends on whether you are doing this as a PreparedStatement or a Statement, but below is some partial pseudo-code to build sql for a Statement, using conditional where clause entries:

StringBuffer sql = new StringBuffer("select * from foo");
 
if (anyCheckboxIsChecked) {
   sql.append(" where " );
   
   if (checkbox1Checked) {
       sql.append(" a = whatever and ");
   }
    ....
   if (checkbox5Checked) {
       sql.append(" e = whatever and ");
   }
   // remember to get rid of the trailing " and " here
}

[meera rao]

I am using a callable Statement. Can you help me with that?

[David Ulicny]

You just need to pass parameters to stored procedure, depends on definition of SP, but in general it will be the same work as Paul mentioned above.

[meera rao]

I didn't understand how to implement it . Canyou give me an example

[Ulf Dittmer]

A callable statement is not really applicable in this case, as the number of parameters is not fixed. Construct your SQL directly like in the example above (and of course take care of SQL injection vulnerabilities).

[meera rao]

My Sql statement is a combination of many left joins. And I shouldn't be using sql stmt in my java code " according to the rules".

Is there any other way?

[Paul Sturrock]

Originally posted by meera rao:
My Sql statement is a combination of many left joins. And I shouldn't be using sql stmt in my java code " according to the rules".

Is there any other way?

Unless it makes sense to use a Statement - for example when the sql is mutable.

[David Ulicny]

Now I'm confused what you are trying to do. Are you using stored procedures? If not, why you are using CallableStatement?

You cannot use Statement or PreparedStatement?

[Padma Lalwani]

You can implement your stored procedure with 5 input params (assuming you have max of 5 checkboxes), and pass null or 0, when the corresponding checkbox is not checked
In the SP, you would tweak the query as Paul has illustrated, appending condition to query if input param is not null. You need to be careful with the 'where' and 'and' clauses

If you need variable number of params, a standard way would be to use delimited input string, and parse the string in SP to retrieve individual values, unless your database provides support for arrays or collections

Padma

[meera rao]

since I am using a stored procedure. I wrote a function that returns a variable that is needed to append to the where clause.

say the variable 'qry' returns ' where txtbx1 = I_bx1 and txtbx2 = I_bx2'
where I_bx1 and I_bx2 are variables that will be passed to the stored procedure.


I am not able to append it to my sql query . How should i do it.

I tired " select abc from tblabc || qry ".

[meera rao]

can anybody help me out plz

[Paul Sturrock]

I don't think any of us understand why you are using a Stored Procedure to do what you are trying to do. Typically, Stored Procedures will represent a repeatable bit of query logic where only the input parameters are allowed to change. You seem to be trying to write a Stored Procedure which itself changes, if that is the case, a Statement makes more sense. Is there a specific reason you are using a procedure?

[Bryan Scarbrough]

Paul Sturrock said:

StringBuffer sql = new StringBuffer("select * from foo");
 
if (anyCheckboxIsChecked) {
   sql.append(" where " );
   
   if (checkbox1Checked) {
       sql.append(" a = whatever and ");
   }
    ....
   if (checkbox5Checked) {
       sql.append(" e = whatever and ");
   }
   // remember to get rid of the trailing " and " here
}

How can you remove the trailing " and "? Actually I want to remove a trailing comma from a StringBuffer, but I am sure the principle is the same.

[Jeanne Boyarsky]

Bryan,
You can use the delete() method on StringBuffer. It takes the indexes you want to delete as parameters, but you can find those using lastIndexOf().

[Bryan Scarbrough]

Thanks for the reply! I figured out a solution - I am taking the count() of the StringBuffer value and then using the deleteCharAt(countValue) and removing the last character, which happens to be the comma value.

I will definitely look into your post however since I will probably need to do this function more often.

Thanks again.