How can I inject "variables" WHERE clauses into a query?

Question

I have a query:

SELECT * FROM table_name WHERE name=? surname=? gender=?;

and I have to inject the strings into the query (I use the prepared statement) but the problem is that these 3 clauses are variable; I can have 0, 1, or also all 3 clauses active based on the input that the user do.

How can I "ignore" each of them if the user doesn't input anything for it?

Thanks to all!

EDIT: it's also good if anyone know how to setStrin() to a preparedStatement without let him to put ' ' around the string.

Answer 1

The simplest solution is going to be to build your prepared statement dynamically, something like this:

String sql = "SELECT * FROM table_name";

if (nameInput != null || surnameInput != null || genderInput != null)
{
    sql += " WHERE ";
}

if (nameInput != null)
{
    sql += "name=? ";
}

I think you get the idea from there for the other inputs.

You can then pass the final value of sql into your prepare call along with the input values.

Answer 2

Easy to do without any complex or expensive logic, in a single line...

Assuming that your three variables are @name, @surname, and @gender.

Also assuming that a zero-length string will be provided when a filter is not required.

Then, your Select statement is simply:

    select * from table_name where (name = @name or @name = '') and (surname = @surname or @surname = '') and (gender = @gender or @gender = '')

That's all there is to it! No complex or expensive logic.