jOOQ中的UPDATE-FROM子句引发CTE字段的期望 [英] UPDATE-FROM clause in jOOQ throws an expecption for CTE field

问题描述

我正在尝试将以下PostgreSQL查询转换为jOOQ:

I am trying to convert following PostgreSQL query into jOOQ:

UPDATE book
SET amount = bat.amount
FROM (
    VALUES (2, 136),(5, 75)
) AS bat(book_id, amount)
WHERE book.book_id = bat.book_id;

FROM子句中的

VALUES是通过Map<Long, Integer> bookIdsAmountMap参数创建的，我正在尝试通过以下方式执行该操作:

VALUES inside of FROM-clause are being created from Map<Long, Integer> bookIdsAmountMap parameter and I am trying to perform that this way:

class BookUtilHelper {

    @SuppressWarnings("unchecked")
    static Table<Record2<Long, Integer>> batTmp(DSLContext dsl, Map<Long, Integer> bookIdAmountMapUpdated) {
        Row2<Long,Integer> array[] = new Row2[bookIdAmountMapUpdated.size()];
        int i = 0;
        for (Map.Entry<Long, Integer> pair : bookIdAmountMapUpdated.entrySet()) {
            array[i]=DSL.row(pair.getKey(), pair.getValue());
            i++;
        }
        Table<Record2<Long, Integer>> batTmp = DSL.values(array);
        batTmp.fields("book_id", "amount");         
        return batTmp;
    } 
}

然后，我还尝试创建可以像此示例

Then, I try to also create fields which can be accessed like in this example

Field<Long> bookIdField = DSL.field(DSL.name("bat", "book_id"), Long.class);
Field<Integer> amountField = DSL.field(DSL.name("bat", "amount"), Integer.class);
Table<Record2<Long, Integer>> batTmp = BookUtilHelper.batTmp(dsl, bookIdAmountMapUpdated);
// ctx variable is of type DSLContext
ctx.update(BOOK).set(BOOK.AMOUNT, amountField).from(batTmp.as("bat")) 
 .where(BOOK.BOOK_ID.eq(bookIdField));

当我尝试更新图书时，出现以下异常:

When I try to update book I get following exception:

bat.book_id栏不存在

column bat.book_id does not exist

任何有关如何解决此问题的建议将不胜感激. :)

Any advice on how to solve this issue would be greatly appreciated. :)

推荐答案

这没有任何作用:

batTmp.fields("book_id", "amount");

这只会重命名表，而不会重命名列:

Whereas this only renames the table, not the columns:

batTmp.as("bat")

改为写这个:

batTmp.as("bat", "book_id", "amount")

这篇关于jOOQ中的UPDATE-FROM子句引发CTE字段的期望的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！