如何使用jOOQ从模板按参数生成SQL? [英] How to generate SQL from template with order by parameter using jOOQ?

问题描述

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

I generate the SQL template like this with jOOQ 3.11.11.

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模板，并根据实时查询条件确定参数.

The SQL template is stored and the params are decided at realtime query condition.

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子句有问题.

There is something wrong with the order by clause.

所以.如何用参数 sort 替换为"id desc" 或"name asc" 并消除引号?

So. How to replace the order by param sort with "id desc" or "name asc" and eliminate the quotes?

推荐答案

有关绑定变量的更多信息，请参见此处.

DSL.param() creates a bind variable, which is generated as ? in SQL, or :bizdate if you choose to use named parameters, or '20190801' if you choose to inline the bind variables. More about bind variables can be seen here.

您不能使用DSL.param()生成列引用或关键字. jOOQ表达式树中的 Field 类型.关键字由 Keyword 类型描述，但是您可以可能不想走到如此低的水平.相反，您想处理查询表达式中的某些逻辑.例如:

You cannot use DSL.param() to generate column references or keywords. A column expression (e.g. a reference) is described in the jOOQ expression tree by the Field type. Keywords are described by the Keyword type, but you probably do not want to go this low level. Instead you want to handle some of the logic in your query expression. For example:

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字符串(每个排序顺序一个)，然后对每个排序列重复一次.

The mistake you're making is to think that you can use a single SQL template for all sorts of different dynamic SQL queries, but what if you're dynamically adding another predicate? Or another join? Or another column? You'd have to build a different jOOQ expression tree anyway. Just like here. You could store two SQL strings (one for each sort order), and repeat that for each sort column.

但是，建议您提取一个采用输入参数并每次重新生成查询的函数，而不是预先生成单个SQL字符串，例如:

But, instead of pre-generating a single SQL string, I recommend you extract a function that takes the input parameters and generates the query every time afresh, e.g.:

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

这里有一些关于将jOOQ用于动态SQL的进一步阅读:

Here is some further reading about using jOOQ for dynamic 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

• 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

这篇关于如何使用jOOQ从模板按参数生成SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！