Java 如何将以下SQL翻译成jOOQ

您提供的SQL查询是一个复杂的UPDATE语句，其中包含一个子查询的JOIN子句.在jOOQ中，您可以使用DSLContext的update()个方法，结合用于JOIN子句的join()和on()个方法以及用于UPDATE子句的set()个方法来构建这样的查询.

为了避免任何可读性问题(如放错位置的圆括号)，您可以分解查询.

我将假设:

• 您的survey_definition和survey_instance表分别生成为SURVEY_DEFINITION和SURVEY_INSTANCE.您需要导入生成的表.如果没有生成它们，则需要手动创建它们.
• 你有你的DSLContext作为context可用.
• 原始SQL中的?1是稍后提供的参数.在jOOQ DSL中，我将其替换为param("?1", Integer.class).

然后，代码将是:

import static org.jooq.impl.DSL.*;
import static com.example.generated.Tables.*;

// Assuming the DSLContext is available as "context"

SurveyDefinition SD = SURVEY_DEFINITION.as("sd");
SurveyInstance SI = SURVEY_INSTANCE.as("si");

Field<Integer> surveyDefId = field(name("survey_def_id"), Integer.class);
Field<Integer> status = field(name("status"), Integer.class);
Field<Timestamp> endDate = field(name("end_date"), Timestamp.class);

Table<?> latest = context
    .select(SI.SURVEY_DEF_ID.as(surveyDefId), SI.STATUS.as(status), SI.END_DATE.as(endDate))
    .from(SI)
    .where(SI.SURVEY_DEF_ID.eq(param("?1", Integer.class)), SI.STATUS.notIn(14))
    .orderBy(SI.END_DATE.desc())
    .limit(1)
    .asTable("latest");

context.update(SD)
    .join(latest)
    .on(SD.ID.eq(latest.field(surveyDefId)))
    .set(SD.LATEST_RESULT, latest.field(status))
    .set(SD.LATEST_SURVEY_END_DATE, latest.field(endDate))
    .execute();

注意:代码假设LATEST_RESULT和LATEST_SURVEY_END_DATE与SURVEY_DEFINITION表中的列的名称完全相同，并且它们的类型与子查询中的类型匹配.如果不是这样，您可能需要更改这些名称或添加类型转换.

As you can see, I am using the on() method (part of the ON clause on a TableOnStep object, which is returned by the join(latest) call.
I am not using on() not on a SelectLimitPercentStep object (which does not have an on() method, hence your original error message "Cannot resolve method 'on' in '"SelectLimitPercentStep'").

context.update(SD)
    .join(latest)
    .on(SD.ID.eq(latest.field(surveyDefId)))
    ...

Here, latest is a Table object that represents a derived table.
So calling on() in this context should not trigger a compilation error.

由于某些原因，我不能在context.update(SD)之后进行联接，根本没有这样的方法.可能是因为jOOQ版本较旧.

An alternative workaround would be to perform the subquery first, store its results, and then use these results in the update statement.
As an example:

// First, execute the subquery and fetch the results
Record3<String, Integer, Date> record = dslContext.select(SURVEY_INSTANCE.SURVEY_DEF_ID, SURVEY_INSTANCE.STATUS, SURVEY_INSTANCE.END_DATE)
    .from(SURVEY_INSTANCE)
    .where(SURVEY_INSTANCE.SURVEY_DEF_ID.eq(definitionId))
    .and(SURVEY_INSTANCE.STATUS.notIn(14))
    .orderBy(SURVEY_INSTANCE.END_DATE.desc())
    .limit(1)
    .fetchOne();

// Next, use the results of the subquery in the UPDATE statement
if (record != null) {
    dslContext.update(SURVEY_DEFINITION)
        .set(SURVEY_DEFINITION.LATEST_RESULT, record.get(SURVEY_INSTANCE.STATUS))
        .set(SURVEY_DEFINITION.LATEST_SURVEY_END_DATE, record.get(SURVEY_INSTANCE.END_DATE))
        .where(SURVEY_DEFINITION.ID.eq(record.get(SURVEY_INSTANCE.SURVEY_DEF_ID)))
        .execute();
}

此解决方法将操作分为两个部分，它首先从子查询中获取记录，然后使用该数据执行更新.