How to fill a query sql with multiple optional parameter in PreparedStatement?

Question

I explain myself...

I have a form with fill the query (eg.):

SELECT * 
FROM table 
WHERE id=? AND name=? AND sex=? AND year=? AND class=?

but only the "id" is mandatory, all the other parameter are optional. How can I fill (or re-create) the prerared statement for that query ???

Answer 1

You'd either have to use multiple prepared statements or just create a statement on the fly, checking which parameters you have.

Like this:

String query = "SELECT * FROM table WHERE id=?";
if( nameParameter != null ) {
  query += " AND name=?"; //don't never ever directly add the value here
}
...

Update/Warning: Don't directly add the parameter values to the query string but use PreparedStatement and the like instead. As displayed above the query string should only contain placeholders for the values (eg. ?) in order to prevent SQL-injection attacks.

What I mean is, do NOT do the following:

if( nameParameter != null ) {
  //NEVER EVER, REALLY I MEAN IT, DON'T DO THIS
  query += " AND name='" + nameParameter + "'"; 
}