Prepared statement with some where condition in case of any value

I have prepared statement like

select * from books where author = ? and theme = ?

and i cant figure out what to do, if user select option “any author” or “any theme”, what should i set to prepared statement?

Answer

This is a case for “dynamic SQL”. You can do it manually, or use an ORM.

Let’s see the manual case:

String sql;
if (author == null) {
  if (theme == null) {
     sql = "select * from books";
  } else {
     sql = "select * from books where theme = ?";
  }
} else {
  if (theme == null) {
     sql = "select * from books where author = ?";
  } else {
     sql = "select * from books where author = ? and theme = ?";
  }
}
PreparedStatement ps = con.createStatement(sql);
int param = 1;
if (author != null) {
  ps.setString(param++, author);
}
if (theme != null) {
  ps.setString(param++, theme);
}
// The rest is just running the SQL and read the ResultSet.

Now, if you have 10 parameters, an ORM really helps a lot. Pretty much all of them support dynamic SQL in a really nice way.