working with variable number of parameters in a preparedStatement

Question

I'm creating a search form for my application.
In it user selects fields that should be used in filtering data.
the number fields is variable so I don't know how many ? should be in where clause of SQL query.
How can I use preparedStatement with variable number of conditions in where clause?

Thanks

Answer 1

if you want to add variable number of conditions in where clause use StringBuilder (StringBuffer if its multithreaded env.) and and run time depending upon your conditions concate/append to your string.

like

StringBuilder query = new StringBuilder("Select id, name from Student ");


if(args >0)
{
    query.append(" where "); //and add more args.

and later create prepared statement with this query by converting it to string

PrepareStatement(query.toString());

Answer 2

PrepardStatements don't support variable numbers of conditions. What some frameworks do, is they cache each PreparedStatement in a Map where the key is the query.

So each time you want to run a query, you need to build the string to create the PreparedStatement, check if you have it in the map (and reuse it) or create a new one, and add it to the map.