如何根据用户的选择动态生成SQL查询？ [英] How to dynamically generate SQL query based on user’s selections?

问题描述

这是同样的问题：

唯一的区别是，我我很想看到使用Java / JPA的解决方案（可能是EclipseLink或Hibernate特定扩展）。

我需要创建一个GUI，使用它可以选择多个属性这将用于查询数据库以找到合适的人员。我正在寻找如何根据用户的选择动态生成数据库查询的想法。

查询将包含几个字段，但为了得到这个想法，我将只包含三个以下为例：

• 占领 - 可以有0到n个职业字符串。如果给出职业字符串，其中一个必须匹配。


$ b


1. 完全匹配（30）


2. 范围（例如30-40）
   
3. 小于值（-40）


4. 超过值（30-）

查询中的年龄参数是可选的。另外，用户可以指定年龄是否是必需的参数。如果没有要求，并且一个人没有年龄是他/她的个人资料，那么这个人的年龄标准将被忽略。

• 身高 - 与年龄相似

示例查询：

没有给出任何标准：

  select * from persons 
  
 
 
 
只有占用了： 
 
 
  select * from occupies ='dentist'
  
已经给了几个职业：
  select * from（where ='dentist'or occupation ='engineer'）
  / pre> 
 
 
年龄被视为大于价值，并且需要在个人资料中存在： 
 
 <$从年龄> = 30 
  
的人员选择* p> 已经作为一个范围给出，并不需要存在于人的个人资料中：  
 
 
  select from *其中（height为null或（height> = 30且height = 40））
  
不同标准的组合： 
 
 
 从职业='牙医'和年龄> = 30且（身高为空或（身高> = 30且身高<= 40））的人选择* b $ b  
我已经实现了能够以字符串形式生成查询的代码，但它肯定不太漂亮。我正在寻找想法什么是最有效率和最漂亮的方式来实现这一目标。
 标准查询。
 
 
 
在Toplink中，我们获得了 Expression和ExpressionBuilder 。
 
This is the same question as:

How to dynamically generate SQL query based on user's 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:

exact match (30)
range (e.g. 30-40)
less than a value (-40)
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.
解决方案
In Hibernate, you can use Criteria queries.

In Toplink, we got Expression, and ExpressionBuilder.

                        
这篇关于如何根据用户的选择动态生成SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！