Dynamic create WHERE clause from user Search criteria

Question

I have what I'm sure is a fairly common issue, and I don't want to re-invent the wheel. I have a search form where users can specify search criteria and type of search (AND OR Ext..).

The form passes back id's that map to column names and values. Currently, I'm using server side Java to glue the strings together into a where clause. It works in some cases, but it's clunky, error prone and will not scale well.

Any suggestions?

Thanks,

David

Answer 1

If you were using an ORM (I use Hibernate), you could use the Criteria API: it lets you agregate conditions (using a loop for example), and builds the resulting query.

In native SQL, I don't know a library that would do that. Maybe you could write your own, getting some inspiration from the documentation and code for Hibernate Criteria ?

---

OR:

If you do something sufficiently complex on the client (say you manage priorities between AND and OR, you can nest conditions using parenthesis ...), then you're probably already building a data structure that handles this on the client.

In that case, I suggest you send that data structure to the server, and use it via loops to build your query.

Answer 2

I'd use prepare and ? for parameters anyway, if only to avoid injection and other misconversion risks.

If your search criteria are limited in size, you can statically list all possible queries to prepare.

If not, you can maintain a pool of prepared queries dynamically. This is not as useful for a web app, where you probably won't get to reuse any of the queries you prepare.