JOOQ从表中获取记录并将其插入到另一个表中 [英] JOOQ fetch record from table and insert it into another table

问题描述

我想从一个表中选择所有字段，提取到另一个表的记录中，然后将记录插入该表中.但是，我得到一个错误，说值是空的.这是相关的代码段:

I want to select all fields from one table, fetch into a record of another table, and insert the record into that table. However, I am getting an error saying that the values are null. Here is the relevant piece of code:

    // Fetch
    PersonDeactivatedRecord person = getContext().selectFrom(PERSON)
            .where(PERSON.ID.eq(id))
            .fetchOneInto(PersonDeactivatedRecord.class);

    // Insert
    person.setDeactivatedOn(new Timestamp(System.currentTimeMillis()));
    getContext().insertInto(PERSON_DEACTIVATED)
            .set(person)
            .execute();

person_deactivated表是person表的副本，外加一列(deactivated_on).我得到的错误是这样的:

The person_deactivated table is a copy of person table plus one more column (deactivated_on). The error I'm getting is this:

org.jooq.exception.DataAccessException: SQL [insert into "xxx"."person_deactivated" ("deactivated_on") values (cast(? as timestamp))]; ERROR: null value in column "id" violates not-null constraint
  Detail: Failing row contains (null, null, null, null, null, null, null, null, null, ...)

请注意，当我调试此代码时，我看到person对象已按预期完全填充.知道我在这里缺少什么吗?

Note that when I debug this code I see that person object is fully populated as expected. Any idea what I'm missing here?

推荐答案

The InsertSetStep.set(Record) method will not insert the entire person record into the target table, but consider only those fields whose value is "changed". See the Javadoc:

这与调用

This is the same as calling set(Map) with the argument record treated as a Map<Field<?>, Object>, except that the Record.changed() flags are taken into consideration in order to update only changed values.

因此，您有几种方法可以解决此问题(当然，我列出的还不止这些):

So, you have several possibilities to fix this issue (there are more than the ones I list here, of course):

这将是最简单的解决方法:

This will be the simplest fix:

// Insert
person.setDeactivatedOn(new Timestamp(System.currentTimeMillis()));
person.changed(true); // Sets all flags to true
getContext().insertInto(PERSON_DEACTIVATED)
        .set(person)
        .execute();

2.写一个查询

此解决方案的优点是您只有一个查询，这意味着:

2. Write a single query

The advantage of this solution is that you have only a single query, which means:

1. 单服务器往返(减少延迟)
2. 更好的锁定语义(数据库知道您在做什么，因此出现竞争状况的机会更少)

因此，您可以编写:

getContext()
    .insertInto(PERSON_DEACTIVATED)
    .columns(PERSON_DEACTIVATED.fields())
    .select(
        select(Arrays
           .stream(PERSON_DEACTIVATED.fields())
           .map(f -> f.equals(PERSON_DEACTIVATED.DEACTIVATED_ON)
                   ? currentTimestamp()
                   : PERSON.field(f))
           .collect(Collectors.toList()))
       .from(PERSON)
       .where(PERSON.ID.eq(id))
    )
    .execute();

和往常一样，此答案假定您的代码中具有以下静态导入:

As always, this answer assumes you have the following static import in your code:

import static org.jooq.impl.DSL.*;

上面的查询会将PERSON中的所有列插入PERSON_DEACTIVATED中，除了DEACTIVATED_ON列将被当前时间戳替换.

The above query will insert all columns from PERSON into PERSON_DEACTIVATED, except the DEACTIVATED_ON column will be replaced by the current timestamp.

这篇关于JOOQ从表中获取记录并将其插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！