SQL statement for easier db a search

[dangari]

Hi.
I have a web application whereby I perform a search through 4 fields, namely:book serial no.,tel no.,gender and publishing date.In my jsp page variables picking the values respectively are:serial,tel,gender and datez.These are submitted to the java class.Currently, I perform my searches as follows(in java class):
/***********************************************/

//......after connecting to db and all that


if(serial.equals("") )
 {
 if(!tel.equals("") )//
 {
    query = "SELECT * FROM tbooks WHERE tbooks.tel=' "+tel+" ' ";
 }
  else if(gender.equals(""))
 {
   query = "SELECT * FROM tbooks WHERE tbooks.gender=' "+gender+" ' ";
 }
      }//end if
else if (!serial.equals(""))
{
query = "SELECT * FROM tbooks WHERE tbooks.serial=' "+serial+" ' ";
}

//query is the statement to be executed later as  
//ResultSet rs = statement.executeQuery(query);
// more code goes here.......

/********************************************/
The main question here is, Is there an SQL statement that works well in place of my nested if statements? (i.e if serial no is present ,tel is absent, gender is present and date is absent, then it brings results considering the present values only)....Kind of bringing results of ..

SELECT * FROM tbooks WHERE serial = ' "+serial+" ' AND tel= ' "+tel+" ' AND gender= ' "+gender+" ' AND datez= ' "+datez+" '

....even if one variable is null it considers only the non-empty variables.Thank you so much in advance.

[Atli]

Hey.

How about something like:

// Create a list object for the conditions, and the query string.
List conditions = new List();
String query = "SELECT * FROM tbooks";

// Add each condition to the list if it is present.
if(!serial.equals("")) {
    conditions.add("serial='" + serial +"'");
}
if(!tel.equals("")) {
    conditions.add("tel='" + tel +"'");
}
if(!gender.equals("")) {
    conditions.add("gender='" + gender +"'");
}
if(!date.equals("")) {
    conditions.add("date='" + date +"'");
}

// If any conditions were present, compile a WHERE clause.
if(conditions.size() > 0) {
    query += " WHERE ";
    Iterator i = conditions.iterator();
    int cLength = conditions.size();
    while(i.hasNext())
    {
        query += i.next().toString();
        if(cLength > 1) { // We want to skip the last loop, so > 1
            query += " AND ";
        }
        --cLength;
    }
}

// And you should have your query.

My Java is a bit rusty, but you get the idea.

Is that what you are trying to do?

[dangari]

Atli!You are the kind of people that I personally refer to as programming genious.This has to be the best idea that I have seen since I joined Daniweb.Fantastic stuff!!