Supplying variable number of arguments to a stored procedure / SQL

I have some database table, let's say CarTb, and a front end that lets you browse second hand cars available for sale.

In the front end I can filter on as little or as much as I like - show me all blue cars, show me all red cars with 4 doors, show me all cars with rego number XXX123 OR ipod capability and a five speaker stereo. etc.

What I want to be able to do on the Java back end is build a system开发者_高级运维 that queries the database dynamically based upon what the UI sends me. I'm wondering if it is possible to dynamically inject a whole string of arguments into a stored proc... Obviously I know you can map individual arguments like doors=? and colour=? and then substitute in the values... But what about variable arguements, almost like Java vargs I suppose.

You'd always be doing a select * from CarTb, it's just the where clause could be any of the following...

• WHERE doors=4
• WHERE doors=5 AND colour=RED AND MP3=Y
• WHERE turbo=Y OR blown=Y

You get the idea.

Any suggestions? We do have access to hibernate if that helps, and the sybase database (ie we can write another stored proc to do this if necessary).

--Cheers, Dave

---

You can dynamically build your query but still have doors=:param. For example:

if (doorsSelected) {
    clauses.add("doors=:doors");
    paramNames.add("doors");
    values.add(doors);
}

if (engineSelected) {
    clauses.add("engine=:engine");
    params.put("engine", engine);
}

Then simply join the list of clauses (for example, using guava's Joiner):

String queryString = Joiner.on(" AND ").join(clauses);
Query query = session.createQuery(queryString);

Then iterate params and query.setParameter(key, value)

---

I can think of one kind of cheating way, though I imagine/hope the query would be optimized. You could add extra args in your prepared statement for the conditions you would like to add which would evaluate to true only if the field was present.

select ... 
  from ...
    where var1=? and <expression associated with that var1>  
    or var2=? and <expression associated with that var2>  
    or etc