File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes check out this SQL statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "check out this SQL statement" Watch "check out this SQL statement" New topic

check out this SQL statement

peter moss

Joined: Aug 22, 2003
Posts: 9
I'm trying to make an advanced search page in JSP where the user can search based on multiple criteria...
But I want the user to be able to leave some of the criteria blank (i.e. under gender, if they went male and female results, then they just don't change the select box)
So I've done something like this, leaving the option value of the first/default selection blank in hopes that it will pass on a blank entry to the next page and when i select * from cases where gender = '', I want it to show all the males and females. BUT this isn't working.
out.print("<select name=\"gender\">");
out.print("<option value =\"\"></option>");
out.print("<option value=\"Male\">Male</option>");
out.print("<option value=\"Female\">Female</option>");

String query = "SELECT * FROM Cases WHERE Diagnosis = '" + diagnosis + "' AND Age > " + age1 + " AND Age < " + age2 + " AND Gender = '" + gender + "' AND Category = '" + category + "' AND Study = '" + study + "'";
I'm pretty new to this stuff so I'm probably just making a major but simple error. What is the best approach to building SQL search statements?
Nagendra Prasad
Ranch Hand

Joined: Jul 11, 2002
Posts: 219
what you probably need is the ability to append the where clauses to the select statement programmatically.
In your example the condition "gender = null" would give u incorrect results. That is not desirable.
You need to append clauses based on whether the values are entered in the first place. Have an if/else conditional setup for the simplest illustration.
if value in param is null
append "gender is null" to where clause
append "gender = Parameter"

that should see u thru!

Best Regards,<br />Nagendra Prasad.
Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
You don't want to use in your while criteria. That would give you results for only those cases where the patiend was neither male or female. This will result in a very small result set
What you really want is - this will return cases without regard to gender.

Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
Better yet, if they don't select a gender [for instance], just omit the "And Gender = ..." from the clause entirely. Keep in mind that you must code around your specific database. Can the "Gender" field be null in the database? If so, then doing a "where Gender in ('Male', 'Female') will not return records where the gender is null.
What about case-sensitivity [eg. Sybase is normally case-insensitive, Oracle is case-sensitive]. Do you have entries like "MALE", "male", and "Male" in the database? If so, then you have to code for that.
Anyway, I'd do something like:
if (value in param is not null) then
append "And Gender = <param>"
Just keep in mind that the first clause needs to be "Where ...", and all subsequent clauses need to be "And ...".
I agree. Here's the link:
subject: check out this SQL statement
It's not a secret anymore!