• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Conditional 'where' condition

 
meera rao
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you explain more clearly, how can we append conditions
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
meera rao
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using a callable Statement. Can you help me with that?
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I didn't understand how to implement it . Canyou give me an example
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can anybody help me out plz
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock said:


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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic