在jOOQ中限定一个临时表列名 [英] Qualifying a temporary table column name in jOOQ

问题描述

我正在使用jOOQ和一个临时表:

I am using jOOQ with a temporary table:

Table<Record> TMP = DSL.table("tmp");
Field<String> TYPE = DSL.field("type", String.class);
Field<String> TOKEN = DSL.field("token", String.class);

这使我可以编写简单的查询: DSL.select(TYPE，TOKEN).from(TMP)...

This allows me to write simple queries: DSL.select(TYPE, TOKEN).from(TMP)...

但是，当我尝试联接到另一个表时，它会产生歧义，因为列名TYPE和TOKEN不符合表名的限制(即，我需要生成的代码看起来像SELECT tmp.type, tmp.token ...).是否有办法做到这一点?是通过让Jooq理解temp表具有某些列，还是通过使用具有限定名称的Field来实现的?

However, when I try to join against another table, it creates ambiguities because the column names TYPE and TOKEN are not qualified with a table name (i.e. I need the generated code to look like SELECT tmp.type, tmp.token ...). Is there a way to make this happen, either by making Jooq understand that the temp table has certain columns, or by making a Field with a qualified name?

当然，对于查询的这些部分，我总是可以使用原始SQL，这是我到目前为止所做的.

Of course I can always drop to raw SQL for these parts of the query, which is what I've been doing so far.

推荐答案

在jOOQ中有两种与表/列动态交互的方式(即，无需使用代码生成器):

There are two ways to interact with tables / columns dynamically (i.e. without using the code generator) in jOOQ:

这就是您正在做的.显然，您可以通过两种方式直接在普通的SQL Field引用中对列进行限定:

That's what you're doing. You can obviously qualify the columns directly in your plain SQL Field references in two ways:

通过在每个字段中重复"tmp"字符串:

By repeating the "tmp" string in each field:

Table<Record> TMP = DSL.table("tmp");
Field<String> TYPE = DSL.field("tmp.type", String.class);
Field<String> TOKEN = DSL.field("tmp.token", String.class);

通过将"tmp"引用嵌入到普通的SQL模板中:

By embedding the "tmp" reference in the plain SQL template:

Table<Record> TMP = DSL.table("tmp");
Field<String> TYPE = DSL.field("{0}.type", String.class, TMP);
Field<String> TOKEN = DSL.field("{0}.token", String.class, TMP);

手册中此处记录了纯SQL功能

这可能就是您要执行的操作.您会写:

That's probably what you want to be doing instead. You'll write:

Table<Record> TMP = DSL.table(DSL.name("tmp"));
Field<String> TYPE = DSL.field(DSL.name("tmp", "type"), String.class);
Field<String> TOKEN = DSL.field(DSL.name("tmp", "token"), String.class);

手册中的命名功能在此处进行了描述

这种方法的优点是:

• 没有SQL注入风险
• 区分大小写
• 表映射和其他AST转换也将起作用

• No SQL injection risk
• Case-sensitivity is taken care of
• Table mapping and other AST transformations will work, too

这篇关于在jOOQ中限定一个临时表列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！