any or *

[Annette L'Heureux]

I've got a little sql question.
Let's say you have 4 drop-down boxes, and the user can select different options in all 4 boxes, then hit submit. The values get passed to a servlet which does a query to a database with the restrictions specified in the box.
ex:
Select system_names
from tb__systems
where option1=box1
and option2=box2
and option3=box3
and option4=box4;
(of course option and box are replaced by real values, I just can't be bothered writing the whole thing).
I have managed to get this to work fine, but what if you don't want to narrow it down that much. What if you only want to select 2 options, and leave the other 2 at "any".
How do you write a piece of code (I was thinking it would have to be an "if" statement) that checks for this, and makes the query string accordingly?
Here's what I had with just 2 boxes:
if(env != "any")
{
query = querybase + "env_identifier=" + env;
if(loc != "any")
query = query + "and loc_identifier=" + loc;
else
query = query;
}else
{
if(loc != "any")
query = querybase + "loc_identifier = " + loc;
else
query = "select system_name from tb__systems";
}

(I hope the code came out ok)
Any ideas? what am I doing wrong?
Thanks,
Annette

[Brian Nice]

What if you named each of the drop down boxes something like optionBox1, optionBox2, optionBox3, and optionBOx4. Then you could use :
Enumeration enum = request.getParameterNames();
StringBuffer query = new StringBuffer("select system_names from tb_systems where 1=1"); // 1=1 is so you can append all additional criteria with an ' and ' rather then having to decide if this is the first criteria in the where clause or not

while (enum.hasMoreElements() ) {
String param = (String)enum.nextElement();
if (param.startsWith("optionBox")) {
String val = request.getParameter(param);
if (!val.equals("defualtvalue"))
query.append(" and " + param + " = '" + val + "' ");
}
}
This way you can have as many option boxes as you want as long as they follow the naming scheme and as long as their default value is called the same thing.
HTH
Brian
[e-mail]

[Annette L'Heureux]

thanks! I'll try that. I sounds a lot more logical than my way.
thanks again!
annette

[Annette L'Heureux]

Ok, I've run into one small problem. The above code given by Brian would work great, except for the "param".
param ends up being whatever the option box is called.
ex: optionbox1, optionbox2, etc
when you append that to the query, it ends up being
select system_names from tb__systems where 1=1 and optionbox1=vl and......
is there a way to chop off parts of words? I mean, if I were to call my option boxes things like:
optionEnv, optionLoc, optionOwner
is there a way to cut off the option part?
Kind of like query.append(), only I would be removing the first 6 letters or something.
Annette
[This message has been edited by Annette L'Heureux (edited February 01, 2001).]

[Brian Nice]

Check out the substring methods of the String class. If:
param = "optionLoc", then
param.substring(6) = "Loc"
HTH
Brian

[Annette L'Heureux]

That's exactly what I was looking for. I knew it must exist, because I had seen similar stuff in c++, but I wasn't sure what it was. I spent the entire afternoon yesterday looking for it, but I guess I wasn't looking in the right place.
Thanks!
Annette

[Annette L'Heureux]

Ok, one last problem and I think I've got it solved.
Right now I'm getting an error that the method "executeQuery(java.lang.StringBuffer)" is not found in the interface java.sql.Statement
because I use stmt.executeQuery(query)
(where query is now a StringBuffer)
How do I solve this?
What is the statements purpose and do I have to use it?

[Brian Nice]

executeQuery expects a String as a parameter so you need to convert the StringBuffer to a string. Try:
stmt.executeQuery(query.toString() );
Brian

[Annette L'Heureux]

thanks! I thought of that too!
(I'm actually amazed that I found it on my own!)
Everything seems to be working fine now, I'm just working on the formatting!
Thanks for all your help Brian!
Annette