Best ways to construct Dynamic Search Conditions for Sql

Question

I have always wondered what's the best way to achieve this task. In most web based applications you have to provide search options on many different criteria. Based on what criteria is chosen behind the scene you modify your SQL. Generally, this is how I tend to go about it:-

1. Have a base SQL template.

2. In the base template have conditions like this WHERE [#PRE_COND1] AND [#PRE_COND2] .. so on and so forth. So an example SQL might look something like SELECT NAME,AGE FROM PERSONS [,#TABLE2] [,#TABLE3] WHERE [#PRE_COND1] AND [#PRE_COND2] ORDER BY [#ORD_COND1] AND [#ORD_COND2] etc.

3. During run time after figuring out the all the search criteria user has entered, I replace the [#PRE_COND1]s and [#ORD_COND1]s with the appropriate SQLs and then execute the query.

I personally do not like this brute force method. However, I never came across a better approach either. How do you accomplish such tasks generally given you are either using native JDBC or Spring JDBC?

It is almost like I need a C MACRO like functionality in Java to do this.

Answer 1

if you were using Hibernate, the Criteria API excels at this. If not, I guess somebody has written some similar API for pure SQL

I remembered the H2 guy had some links, see here in Similar Projects, some of these tools might be what you need.

Answer 2

With search, the retrieved columns are usually fixed: usually the where clause is the one that changes.

I tend to use

select ... from ...
where true

as the basis and simply expand the where clause with and column = value criteria as necessary. Ordering is a simple extension of this principle.

This approach also works well with the hibernate query language.

Answer 3

For java, I use SqlBuilder (which is like the Hibernate Criteria API for straight JDBC). Of course, i'm biased. :)