*
The moose likes Servlets and the fly likes any or * Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "any or *" Watch "any or *" New topic
Author

any or *

Annette L'Heureux
Ranch Hand

Joined: Dec 07, 2000
Posts: 135
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
Ranch Hand

Joined: Nov 02, 2000
Posts: 195
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
bcnice@mindspring.com
Annette L'Heureux
Ranch Hand

Joined: Dec 07, 2000
Posts: 135
thanks! I'll try that. I sounds a lot more logical than my way.
thanks again!
annette
Annette L'Heureux
Ranch Hand

Joined: Dec 07, 2000
Posts: 135
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
Ranch Hand

Joined: Nov 02, 2000
Posts: 195
Check out the substring methods of the String class. If:
param = "optionLoc", then
param.substring(6) = "Loc"
HTH
Brian
Annette L'Heureux
Ranch Hand

Joined: Dec 07, 2000
Posts: 135
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
Ranch Hand

Joined: Dec 07, 2000
Posts: 135
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
Ranch Hand

Joined: Nov 02, 2000
Posts: 195
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
Ranch Hand

Joined: Dec 07, 2000
Posts: 135
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: any or *
 
Similar Threads
Selected values
updating drop down list boxes
JSP/JDBC problem...please help!!!!
Many database-queries or one big vector??
Wrapping of select box