[疑问] 因为要进行联表查询，请问一下怎么实现分页自定义sql？

[MoonBottle]

环境

java: 1.8.0_131
hsweb: 4.0.15

问题说明

因为要进行联表查询，请问一下怎么实现分页自定义sql

[zhou-hao]

参考 https://github.com/zhou-hao/hsweb4-examples/blob/main/hsweb4-webflux-example/src/main/java/org/hswebframework/example/crud/web/TestController.java

[MoonBottle]

Test 里的用法似乎是 4.0.16-SNAPSHOT 版本才有的，4.0.15可以直接升级吗？项目已在生产环境运行了

[zhou-hao]

Test 里的用法似乎是 4.0.16-SNAPSHOT 版本才有的，4.0.15可以直接升级吗？项目已在生产环境运行了

按理说可以，你可以先本地试一试。

[MoonBottle]

okok，多谢大佬

[MoonBottle]

这边测试了下，暂时没发现什么问题，请问下 4.0.16 release 版本的发布计划是什么时间

[zhou-hao]

暂时还没有具体时间。

[MoonBottle]

明白了，多谢！

[MoonBottle]

@zhou-hao 大佬，发现一个问题，使用
lamda 的方式，返回的实体类，有些字段不会被映射，手写sql没有这个问题

sql返回有值

怀疑是驼峰的问题，非驼峰的字段我看有值

[zhou-hao]

完整sql看一下， 原始sql和日志中执行的sql。

[MoonBottle]

日志里捞出来的

select 
       j_0."ota_id"                   as "xxx.otaId",
       alarm_record."source_id"       as "all_1.sourceId",
       alarm_record."target_name"     as "all_1.targetName",
       alarm_record."handle_time"     as "all_1.handleTime",
       alarm_record."target_id"       as "all_1.targetId",
       alarm_record."level"           as "all_1.level",
       alarm_record."alarm_time"      as "all_1.alarmTime",
       alarm_record."description"     as "all_1.description",
       alarm_record."alarm_name"      as "all_1.alarmName",
       alarm_record."target_type"     as "all_1.targetType",
       alarm_record."target_key"      as "all_1.targetKey",
       alarm_record."alarm_config_id" as "all_1.alarmConfigId",
       alarm_record."source_type"     as "all_1.sourceType",
       alarm_record."id"              as "all_1.id",
       alarm_record."source_name"     as "all_1.sourceName",
       alarm_record."state"           as "all_1.state",
       alarm_record."business_type"   as "all_1.businessType"
from public.alarm_record alarm_record
         left join public.xxx j_0 on j_0."device_id" = alarm_record."target_id"
where alarm_record."state" = 'normal'
limit 10 offset 0;

手写sql方式查询用的sql

select * from alarm_record alarm_record " +
                    "left join xxx xxx on xxx.device_id = alarm_record.target_id

表名我因为保密原因，我替换成xxx了

[zhou-hao]

实体类结构看一下

[zhou-hao]

lambda方式是怎么写的

[MoonBottle]

实体类，Xxx 子类字段正常接收

public class AlarmRecordDTO extends GenericEntity<String> {

    @Column
    @Schema(description = "告警配置ID")
    private String alarmConfigId;

    @Schema(description = "告警配置名称")
    private String alarmName;

    @Schema(description = "告警目标类型")
    private String targetType;

    @Schema(description = "告警目标Id")
    private String targetId;

    @Schema(description = "告警目标Key")
    private String targetKey;

    @Schema(description = "告警目标名称")
    private String targetName;

    @Schema(description = "告警源类型")
    private String sourceType;

    @Schema(description = "告警源Id")
    private String sourceId;

    @Schema(description = "告警源名称")
    private String sourceName;

    @Schema(description = "最近一次告警时间")
    private Long alarmTime;

    @Schema(description = "处理时间")
    private Long handleTime;

    @Schema(description = "告警级别")
    private Integer level;

    @Schema(description = "告警记录状态")
    @EnumCodec
    @ColumnType(javaType = String.class)
    @DefaultValue("normal")
    private AlarmRecordState state;

    @Schema(description = "说明")
    private String description;

    @Column
    @Schema(description = "业务告警类型")
    private String businessType;

    @Schema(description = "xxx信息")
    private Xxx xxx;

}

lambda

        return queryHelper
            .select(AlarmRecordDTO.class)
            .all(Xxx.class, AlarmRecordDTO::setXxx)
            .all(AlarmRecordEntity.class)
            .from(AlarmRecordEntity.class)
            .leftJoin(Xxx.class, join -> join.is(Xxx::getDeviceId, AlarmRecordEntity::getTargetId))
            .where(query)
            .fetchPaged();

[zhou-hao]

应该是bug。reimport一下maven再试试

[MoonBottle]

试了下，可以了，大佬🐂啤

[MoonBottle]

关联表我只想查固定几个字段，lambda 可以写么？

[zhou-hao]

.as(Entity::getName,DTO::setName)

[MoonBottle]

多谢多谢

[MoonBottle]

大佬，lambda 模式，分页情况下下，count 的sql，带了order条件，导致sql报错，使用的是 postgresql
参数

{
    "firstPageIndex": 1,
    "sorts": [
        {
            "name": "alarmTime",
            "order": "desc"
        }
    ],
    "terms": [
        {
            "value": "1637633705471975424",
            "column": "xxx.organizationId",
            "termType": "eq"
        }
    ],
    "pageIndex": 1,
    "pageSize": 10
}

column \"alarm_record.alarm_time\" must appear in the GROUP BY clause or be used in an aggregate function

	select count(1) as "_total" from public.alarm_record alarm_record left join public.xxx j_0 on j_0."device_id" = alarm_record."target_id" where j_0."organization_id" = '1637633705471975424' order by alarm_record."alarm_time" desc limit 10 offset 0

[zhou-hao]

好的，我看一下，应该是count条件构造的问题。

[zhou-hao]

在reimport一下maven试试

[MoonBottle]

可以了，不过我看控制台好像不打印 count 的 sql 了，是我的问题么

[zhou-hao]

最新的sql日志名做出了调整。日志名 默认是实体类名了。

[MoonBottle]

我调整下日志级别试试

[MoonBottle]

似乎不对啊，select 的日志还是 R2dbcReactiveSqlExecutor 打印的，难道是只调整了 count 的

[MoonBottle]

调整了下日志级别，可以看到 count 的日志了，是用来接收查询结构的实体类打印的