jOOQ中的UPDATE-FROM子句引发比较数据类型的异常 [英] UPDATE-FROM clause in jOOQ throws an exception for comparing data types

问题描述

访问jOOQ的UPDATE-FROM子句中的值时遇到问题.我想转换以下PostgreSQL查询:

I have a problem when accessing values in UPDATE-FROM clause in jOOQ. I want to convert following PostgreSQL query:

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 bookIdsAmountMap参数创建的，我正在尝试通过以下方式执行该操作: 到目前为止，这是我在代码中所做的(根据Lukas Eder的回答建议)，是在这个问题中完成的:

VALUES inside of FROM-clause are being created from Map bookIdsAmountMap parameter and I am trying to perform that this way: This is what I have done in my code so far (by Lukas Eder answer suggestion) made in this question:

Row2<Long,Integer> array[] = new Row2[bookIdAmountMap.size()];
int i = 0;
for (Map.Entry<Long, Integer> pair : bookIdAmountMap.entrySet()) {
    array[i] = DSL.row(pair.getKey(), pair.getValue());
    i++;
}
Table<Record2<Long, Integer>> bat = DSL.values(array);
bat = bat.as("bat", "book_id", "amount");
Field<Long> bookIdField = DSL.field(DSL.name("bat", "book_id"), Long.class);
Field<Integer> amountField = DSL.field(DSL.name("bat", "amount"), Integer.class);

ctx.update(BOOK).set(BOOK.AMOUNT, amountField).from(bat) // same result as if I am using in .from(bat.as("bat", "book_id", "amount"))  
                .where(BOOK.BOOK_ID.eq(bookIdField)); 

当我执行Java代码时，出现以下异常:

When I execute Java code I get following exception:

运算符不存在:bigint = text

operator does not exist: bigint = text

对于解决此问题的任何帮助/建议，我们深表感谢. :)

Any help/advice on solving this issue is greatly appreciated. :)

推荐答案

此问题似乎与已知问题，其中PostgreSQL无法通过jOOQ VALUES表达式猜测正确的数据类型.我将需要对此进行进一步调查，并将更新我的答案.

This issue seems related to a known issue where PostgreSQL cannot guess the right data type from a jOOQ VALUES expression. I will need to investigate this further, and will update my answer.

一种解决方法是将绑定值显式转换为适当的数据类型:

A workaround is to cast your bind values to the appropriate data type explicitly:

array[i] = DSL.row(
  DSL.val(pair.getKey()).cast(SQLDataType.BIGINT), 
  DSL.val(pair.getValue()).cast(SQLDataType.INTEGER)
);

这篇关于jOOQ中的UPDATE-FROM子句引发比较数据类型的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！