GeeCON Prague 2014*
The moose likes JDBC and the fly likes how to minimize the search Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to minimize the search" Watch "how to minimize the search" New topic
Author

how to minimize the search

badri nath
Ranch Hand

Joined: Dec 21, 2005
Posts: 57
Hi

I guess my subject line makes my requirement clear, its easy but can any one suggest good SQL Query.I am using MySql.

To explain in detail, my requirement is something like this..

I have four fields to make the search
1)typeissue 2)priority 3)status 4)issuedate

Requirements:
A)If the user dont enter any thing to make search then all records should be displayed

B)If the user enters typeissue/priority/status/issuedate then search condition should contain one or all the fields respectively.
i.e., If user enters more fields search should be minimized.

I have tried with some thing like this but it is not working fine(my logic is not fine ;) )

StringBuffer sbQuery = new StringBuffer();
sbQuery.append("SELECT * FROM AddDetails ");
if(SelectedIssue != "" || SelectedPriority != "" || SelectedStatus != "" || OpenedOn != "")
{
sbQuery.append("WHERE ");
sbQuery.append("typeissue=? ");
if(typeissue != "")
{
sbQuery.append("AND ");
}else
{
sbQuery.append("OR ");
}
sbQuery.append("priority=? ");

if(priority != "" )
{
sbQuery.append("AND ");
}else
{
sbQuery.append("OR ");
}

sbQuery.append("status=? ");

if(status != "")
{
sbQuery.append("AND ");
}else
{
sbQuery.append("OR ");
}
sbQuery.append("issuedate=? ");
}

/* Prepared Statement followed by this */


It is working fine if immediate fields are not null...but if i try on various conditions it is blunder...

Can any suggest better SQL syntax or good java code.
Thnx in advance..
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

Badri,
The "or" part is making things more complicated than necessary and I think is what is causing the logic problem.

Think about what you want the query to look like:
No fields selected: SELECT * FROM AddDetails
Only type issue selected: SELECT * FROM AddDetails where typeissue = ?
First two fields selected: SELECT * FROM AddDetails where typeissue = ? and priority = ?
All fields selected: SELECT * FROM AddDetails where typeissue = ? and priority = ? and status = ? and issuedate = ?

So the idea would be something like:

[ September 08, 2006: Message edited by: Jeanne Boyarsky ]

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
badri nath
Ranch Hand

Joined: Dec 21, 2005
Posts: 57
Thanks Jeanne
 
GeeCON Prague 2014
 
subject: how to minimize the search