在 Java 中动态组装 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2684994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamically assembling SQL queries in Java
提问by kd.
What is the best way to assemble an SQL query with join conditions dynamically? I don't want to hard code the query for each different condition on a webpage or a set of webpages. Is it even feasible?
用连接条件动态组装 SQL 查询的最佳方法是什么？我不想对网页或一组网页上的每个不同条件的查询进行硬编码。它甚至可行吗？
I got as far as being able to assemble simple queries but i got stumped when i needed to insert join conditions, i.e. how to figure out dependencies for joins etc.
我已经能够组装简单的查询，但是当我需要插入连接条件时我被难住了，即如何找出连接的依赖关系等。
回答by Enrique
You can use iBatis. It has a feature called Dynamic SQL. With this you can create dynamic queries at runtime based on several conditions.
您可以使用 iBatis。它有一个称为动态 SQL的功能。有了这个，您可以在运行时根据几个条件创建动态查询。
回答by kd.
public String AssembleSimpleSelectQuery(String TableName,Hashtable<String,String> criteria) {
Hashtable<String,String> columnlist = ReturnColumnList(TableName);
Iterator<String> iter = columnlist.keySet().iterator();
int count = 0;
query=new StringBuilder();
query.append("SELECT ");
while(iter.hasNext())
{
count++;
query.append(iter.next());
if(count < (columnlist.size()))
{
query.append(",");
}
}
query.append(" From " + TableName );
Iterator<String> crit = criteria.keySet().iterator();
if(criteria.size()>0)
{
query.append(" where ");
}
count = 0;
while(crit.hasNext())
{
count++;
String temp = crit.next();
query.append(temp + "=");
if(columnlist.get(temp).equals("String") || columnlist.get(temp).equals("Id"))
{
query.append("'" + criteria.get(temp) + "'");
}
else if(columnlist.get(temp).equals("Date"))
{
query.append("to_date('"+criteria.get(temp)+"','mm-dd-yyyy')");
}
if(count < criteria.size())
{
query.append(" and ");
}
}
return query.toString();
}
/this is the sample function that creates a simple select query --i call returncolumnlist that reads off from an xml file that stores tablenames and their columns and returns a hashtable of column names and their datatypes.... --i havent yet figured out how to fit in a join condition (maybe more than 1) into this code...so looking for ideas for that...im not looking for code.../
/这是创建一个简单选择查询的示例函数——我调用 returncolumnlist，它从存储表名及其列的 xml 文件中读取并返回列名及其数据类型的哈希表.... --我还没有想到找出如何将连接条件（可能超过 1 个）放入此代码中......所以寻找想法......我不是在寻找代码....../
回答by Jeffrey Kemp
My preferred approach to building complex dynamic queries is:
我构建复杂动态查询的首选方法是：
- Enumerate all the possible queries (i.e. query patterns) that are required. This allows me to see commonalities and find patterns for which generic code can be written.
- Generate each part of the SQL statement separately, then concatenate at the very end. e.g. (this is not meant to be working code, just a sketch of an idea):
- 枚举所需的所有可能的查询（即查询模式）。这使我能够看到共性并找到可以编写通用代码的模式。
- 分别生成 SQL 语句的每个部分，然后在最后连接。例如（这不是工作代码，只是一个想法的草图）：
<pseudocode>
<伪代码>
select_clause = 'SELECT '
from_clause = 'FROM '
where_clause = 'WHERE '
orderby_clause = 'ORDER BY '
if [query on person] then
select_clause += 'p.name, p.dob '
from_clause += 'person p '
orderby_clause += 'p.name '
if [query on address] then
select_clause += 'a.address_text '
from_clause += ', address a '
where_clause += 'p.address_id = a.id AND a.id=:p1 '
else
where_clause += 'p.id=:p1'
end if
end if
sql_stmt = select_clause + from_clause + where_clause + orderby_clause + ';'
</pseudocode>
</伪代码>
So, the above code might produce the following statements:
因此，上面的代码可能会产生以下语句：
SELECT p.name, p.dob
FROM person p
WHERE p.id=:p1
ORDER BY p.name;
SELECT p.name, p.dob, a.address_text
FROM person p, address a
WHERE p.address_id = a.id AND a.id=:p1
ORDER BY p.name;
With more parameters, this approach means that I don't have to deal with an exponentially-increasing number of possible combinations of criteria.
有了更多参数，这种方法意味着我不必处理数量呈指数增长的可能标准组合。
回答by Juraj
Hibernate has API to construct queries without having to concatenate strings at all, see here:
Hibernate 有 API 来构造查询，而无需连接字符串，请参见此处：
http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/Criteria.html
http://docs.jboss.org/hibernate/core/3.5/api/org/hibernate/Criteria.html
