How to dynamically generate SQL query based on user selection?

Question

This is the same question as:

How to dynamically generate SQL query based on user selection?

The only difference is that I am also interested in solutions that also use Java / JPA (possibly EclipseLink or Hibernate extensions).

I need to create a graphical interface with which users can select several attributes that will be used to query the database to find suitable people. I am looking for ideas on how to dynamically generate a database query according to user choices.

Query will contain several fields, but to get this idea, I will give only three of the following:

• Profession - can be from 0 to n lines of employment. If busy lines are specified, one of them must match.

• Age - age can be set as:

  • exact match (30)
  • (e.g. 30-40)
  • less value (-40)
  • greater than value (30 -)

An age parameter is optional in the request. In addition, the user can indicate whether age is a desired parameter. If this is not required, and the person has no age, this is his profile, age criteria are ignored for this person.

• Height - Similar to Age

Request example:

No criteria were given:

select * from persons

Given only lesson:

select * from persons where occupation = 'dentist'

Several classes were provided:

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

Age was set as greater than the value, and it must exist in the personal profile:

select * from persons where age >= 30

, :

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

:

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

, , , , . , .

Answer 1

Hibernate Criteria .

Toplink Expression ExpressionBuilder.

Answer 2

, querydsl, torpedoquery e Object Query, , api, jpa 2, JPA2 Query,

!

Answer 3

JPA 1.X, , http://rrusin.blogspot.com/2010/02/jpa-query-builder.html. , :

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")
                }
            )

Answer 4

Querydsl, JPA 1.0, API , , . Querydsl , API . Querydsl .

Answer 5

In my code I use ANDand objects for this OR. They take lists as parameters (look great with Java 5 variable arguments) and combine them in Strings with the necessary spaces and parentheses. Pseudocode:

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

where it join()converts an array of objects into a string array and then connects the elements to the parameter.