How to dynamically generate SQL query based on user’s selections?

Question

This is the same question as:

http://stackoverflow.com/questions/2040680/how-to-dynamically-generate-sql-query-based-on-users-selections

The only difference is, that I'm interested in seeing solutions also using Java/JPA (+possibly EclipseLink or Hibernate specific extensions).

I need to create a GUI, using which users can select several attributes which will be used to query the database to find suitable persons. I'm looking for ideas how to dynamically generate the database query according to user's choices.

Query will contain several fields, but to get the idea I will include only three of those below as an example:

• Occupation - there can be 0 to n occupation strings. If occupation strings are given, one of them have to match.

• Age - age can be given as:

  1. exact match (30)
  2. range (e.g. 30-40)
  3. less than a value (-40)
  4. more than a value (30-)

Age parameter is optional in the query. In addition, user can specify whether age is a required parameter. If it's not required, and a person does not have age is his/her profile, age criteria is ignored for this person.

• Height - similar as age

Example queries:

No criteria has been given:

select * from persons

Only occupation has been given:

select * from persons where occupation = 'dentist'

Several occupations have been given:

select * from persons where (occupation = 'dentist' or occupation = 'engineer')

Age has been given as a greater than value, and it's required to exist on person's profile:

select * from persons where age >= 30

Height has been given as a range, and it's not required to exist on person's profile:

select * from persons where (height is null or (height >= 30 and height <= 40))

Combination of different criteria:

select * from persons where occupation = 'dentist' and age >= 30 and (height is null or (height >= 30 and height <= 40))

I have already implemented code which is capable of generating queries as strings, but it certainly is not too pretty. I'm looking for ideas what would be the most efficient and prettiest way to achieve this.

Answer 1

In Hibernate, you can use Criteria queries.

In Toplink, we got Expression, and ExpressionBuilder.

Answer 2

In my code, I'm using AND and OR objects for this. They take lists as parameters (looks nice with Java 5's variadic arguments) and concatenate them in Strings with the necessary spaces and parentheses. Pseudo code:

AND(WhereCond ... conds) { this.conds = conds; }
toString() { return conds.length == 0 ? "" : "(" + join(conds, " AND ") + ")" };

where join() converts the object array to string array and then joins the elements with the parameter.

Answer 3

If you want to do this in JPA 1.X, you can use custom query builder as described here http://rrusin.blogspot.com/2010/02/jpa-query-builder.html. This enables creating queries like this:

return new JpaQueryBuilder().buildQuery(em,
                new Object[] {
                    "select c from Car c where c.name is not null",
                    new JQBParam("name", name, " and c.name = :name"),
                    new JQBParam("type", type, " and c.type = :type")
                }
            )