如何使用jOOQ从按参数排序的模板生成SQL？

Question

我用jOOQ 3.11.11生成了这样的SQL模板。

DSLContext context = new DefaultDSLContext(conf);
Query query = context.select()
    .from("table1")
    .where(DSL.field("report_date").eq(DSL.param("bizdate")))
    .orderBy(DSL.param("sort"));
String sqlTemp = context.renderNamedParams(query);

SQL模板：

select * 
from table1 
where report_date = :bizdate 
order by :sort

存储SQL模板，并在实时查询条件下确定参数。

ResultQuery resultQuery = context.resultQuery(sqlTemp, DSL.param("bizdate", "20190801"), DSL.param("sort", "id desc"));

实时SQL：

select * 
from table1 
where report_date = '20190801' 
order by 'id desc'

从句顺序有问题。

所以如何将order by param sort替换为“id desc”或“name asc”，并删除引号？

Answer 1

DSL. param（）创建一个绑定变量，在SQL中生成为？，如果您选择使用命名参数，则生成： bizdate，如果您选择内联绑定变量，则生成'20190801'。更多关于绑定变量的信息可以在这里看到。

您不能使用DSL。param（）生成列引用或关键字。列表达式（例如引用）在jOOQ表达式树中由字段类型描述。关键字由关键字类型描述，但您可能不想达到这么低的级别。相反，您希望处理查询表达式中的一些逻辑。例如：

String sortField = "id";
SortOrder sortOrder = SortOrder.ASC;

Query query = context.select()
    .from("table1")
    .where(DSL.field("report_date").eq(DSL.param("bizdate")))
    .orderBy(DSL.field(sortField).sort(sortOrder));

您犯的错误是认为可以使用单个SQL模板进行各种不同的动态SQL查询，但是如果您动态添加另一个谓词呢？还是另一个加入？还是另一个专栏？无论如何，您必须构建一个不同的jOOQ表达式树。就像这里一样。您可以存储两个SQL字符串（每个排序顺序一个），并对每个排序列重复该操作。

但是，与其预先生成单个SQL字符串，我建议您提取一个函数，该函数接受输入参数并每次重新生成查询，例如：

java prettyprint-override">ResultQuery<?> query(String bizDate, Field<?> sortField, SortOrder sortOrder) {
    return context.selectFrom("table1")
                  .where(field("report_date").eq(bizDate))
                  .orderBy(sortField.sort(sortOrder));
}

下面是关于将jOOQ用于动态SQL的进一步阅读：

• https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql
• https://blog.jooq.org/2017/01/16/a-functional-programming-approach-to-dynamic-sql-with-jooq