mariadb 如何将下面的SQL翻译成jOOQ

ijxebb2r  于 2023-06-22  发布在  其他
关注(0)|答案(2)|浏览(134)

我有以下(对我来说有点复杂)MariaDB 10.6更新:

update survey_definition sd
    join (select si.survey_def_id, si.status, si.end_date
          from survey_instance si
          where si.survey_def_id = ?1
            and si.status not in (14)
          order by si.end_date desc
          limit 1) latest on sd.id = latest.survey_def_id
set sd.latest_result=latest.status,
    sd.latest_survey_end_date=latest.end_date;

如何将其转换成jOOQ DSL?这可能吗?
我最好的(未完成的)尝试是:

jOOQ版本:3.12.1
DSL文本:

dslContext.update(SURVEY_DEFINITION
    .join(dslContext.select (SURVEY._INSTANCE.SURVEY_DEFINITION_ID, SURVEY_INSTANCE.STATUS, SURVEY_INSTANCE.END_TIME_MILLIS)
        . from(SURVEY_INSTANCE) SelectJoinStep<Record3<String, ScanStatus, Long>>
        . where(SURVEY_INSTANCE.SURVEY_DEFINITION_ID.eq(definitionId)) SelectConditionStep<Record3<Sting, ScanStatus, Long>>
        . limit(1) SelectLimitPercentStep<Record3<Sting, ScanStatus, Long>>
        .on (SURVEY_INSTANCE.ID.eq(SURVEY_DEFINITION.ID))
    )

.on()触发:“Cannot resolve method 'on' in '"SelectLimitPercentStep'”。

**UPD:**我提出了以下解决方案:

final Table<Record3<String, ScanStatus, Long>> latest = dslContext
            .select(SURVEY_INSTANCE.SURVEY_DEFINITION_ID, SURVEY_INSTANCE.STATUS, SURVEY_INSTANCE.END_TIME_MILLIS)
            .from(SURVEY_INSTANCE)
            .where(DSL.and(
                SURVEY_INSTANCE.SURVEY_DEFINITION_ID.eq(definitionId),
                SURVEY_INSTANCE.STATUS.notEqual(ScanStatus.DELETED))
            )
            .orderBy(SURVEY_INSTANCE.END_TIME_MILLIS.desc())
            .limit(1)
            .asTable("latest");
        dslContext.update(SURVEY_DEFINITION
                .join(latest)
                .on(latest.field(SURVEY_INSTANCE.SURVEY_DEFINITION_ID).eq(SURVEY_DEFINITION.ID)))
            .set(SURVEY_DEFINITION.LATEST_SCAN_STATUS, latest.field(SURVEY_INSTANCE.STATUS))
            .where(SURVEY_DEFINITION.ID.eq(definitionId))
            .execute();

感谢@LukasEder和@VonC的帮助和想法。

lnxxn5zx

lnxxn5zx1#

您所提供的SQL查询是一个复杂的UPDATE语句,其中包含一个JOIN子句作为子查询。在jOOQ中,可以使用DSLContextupdate()方法,结合join子句的join()on()方法以及update子句的set()方法来构建这样的查询。
为了避免任何可读性问题(比如放错地方的括号),您可以分解查询。
我将假设:

  • 您的survey_definitionsurvey_instance表将分别生成为SURVEY_DEFINITIONSURVEY_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_RESULTLATEST_SURVEY_END_DATESURVEY_DEFINITION表中列的确切名称,并且它们的类型与子查询中的类型匹配。如果不是这种情况,您可能需要更改这些名称或添加类型转换。
如您所见,我使用的是on()方法(TableOnStep objectON clause的一部分,由join(latest)调用返回)。
我没有在SelectLimitPercentStep对象上使用on()(它没有on()方法,因此您最初的错误消息为“Cannot resolve method 'on' in '"SelectLimitPercentStep'”)。

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

在这里,latest是一个Table对象,表示一个派生表。
所以在这个上下文中调用on()应该不会触发编译错误。
由于某种原因,我不能在context.update(SD)之后执行join,因为根本没有这样的方法。可能是因为jOOQ版本比较老。
另一种变通方法是先执行子查询,存储其结果,然后在update语句中使用这些结果。
例如:

// 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();
}

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

bq3bfh9z

bq3bfh9z2#

截图中的具体问题只是你放错了一个右括号:

.join(select(...).limit(1)).on(SURVEY_INSTANCE.ID.eq(SURVEY_DEFINITION.ID))

而不是:

.join(select(...).limit(1).on(SURVEY_INSTANCE.ID.eq(SURVEY_DEFINITION.ID))

相关问题