java - 最佳实践 - 根据用户选择构建查询

Question

我正在创建一个基于 Web 的小型工具，允许用户以各种方式查询大型查找表。由于它是一个小工具，我正在使用 JSP/Servlets。

查找表有这样的定义:

专栏 1 |专栏2 |专栏3 |日期 |用户 |计数

用户可以按列值或范围进行查询。此外，结果可以按特定列排序。因为该表有几十万条记录，并且增长很快，所以我使用 Oracle 的 ROWNUM 并且只返回一小部分结果。

我有一个从用户那里获取搜索条件的表单页面。我根据一系列条件生成查询，例如:

query = "SELECT * FROM mytable WHERE 1=1 "
if(searchCriteria1 != "")
    query += "AND column1='searchCriteria1' "
if(searchCriteria2 != "")
    query += "AND column2='searchCriteria2' "
if(searchCriteria2 != "")
    query += "AND column2='searchCriteria2' "
if(searchCriteria3 != "")
    query += "AND column3='searchCriteria3' "
if((searchCriteria4 != "") && (searchCriteria5 != ""))
    query += "AND date>='searchCriteria4' AND date<='searchCriteria5' "
etc...

(这只是简化的伪代码)

排序是在第一个结果页面显示后处理的。用户单击该页面的列标题以按该列排序。这将进行回发并查询数据库。基本上，我在上面运行相同的代码，但最后是这样的:

if(sortColumn1)
    query += "ORDER BY column1"
if(sortColumn2)
    query += "ORDER BY column2"
if(sortColumn3)
    query += "ORDER BY column3"

因此，正如您想象的那样，我的查询构建代码对于所有这些不同的条件来说非常长。关于更好的方法有什么建议吗？

Answer 1

字符串连接不是 SQL 的最佳选择。你能做的最好的就是使用 QueryDSL或 JOOQ并以面向对象的方式进行。我对 QueryDSL 比较熟悉。看例子here .

SQLQuery query = new SQLQueryImpl(connection, dialect); 
query.from(myTable);

BooleanBuilder wheres = new BooleanBuilder();

if(notBlank(searchCriteria1))
   wheres.and(myTable.column1.eq(searchCriteria1));
if(notBlank(searchCriteria2))
   wheres.and(myTable.column2.eq(searchCriteria2));
if(notBlank(searchCriteria4) && notBlank(searchCriteria5))
   wheres.andAllOf(myTable.date.goe(searchCriteria4),  myTable.date.loe(searchCriteria5));  //you may want to use myTable.date.between(...)

if (...) {
   query.orderBy(myTable.column1.asc());
} else if (...){
   query.orderBy(myTable.column2.asc());
}

query.limit(100); //it is good to limit a result
query.list(myTable.all());

对于 SQL 注入(inject)，查询引擎会在构造 SQL 时将您的参数包装在命名参数中。