Spring Webflux下R2DBC多表查询的排序与分页实现问题

针对你遇到的Spring Webflux + R2DBC多表动态查询、分页排序问题，给你几个可行的实现思路：

1. 使用DatabaseClient动态构建查询 #

DatabaseClient是R2DBC提供的原生客户端，支持灵活的动态SQL拼接，能完美处理可选条件和排序方向的问题：

public Flux<YourDto> queryWithDynamicParams(Optional<Long> userCountryId, Optional<Long> customerId, 
                                            String sortColumn, String sortDirection, int offset, int limit) {
    // 基础SQL
    StringBuilder sql = new StringBuilder("""
        SELECT u.id, u.catalog_id, u.amount, u.currency, u.created_date, cc.customer_id, c.country
        FROM user u 
        JOIN catalog c ON u.catalog_id = c.id 
        JOIN catalog_contract cc ON cc.id = c.catalog_contract_id 
        WHERE 1=1
        """);

    // 拼接可选WHERE条件
    userCountryId.ifPresent(id -> sql.append(" AND u.user_country_id = :userCountryId"));
    customerId.ifPresent(id -> sql.append(" AND cc.customer_id = :customerId"));

    // 拼接排序（注意：要限制sortColumn和sortDirection的可选值，避免SQL注入）
    sql.append(String.format(" ORDER BY %s %s", sortColumn, sortDirection.toUpperCase()));
    // 拼接分页
    sql.append(" OFFSET :offset LIMIT :limit");

    // 构建查询并绑定参数
    DatabaseClient.GenericExecuteSpec spec = databaseClient.sql(sql.toString())
            .bind("offset", offset)
            .bind("limit", limit);

    userCountryId.ifPresent(id -> spec.bind("userCountryId", id));
    customerId.ifPresent(id -> spec.bind("customerId", id));

    return spec.map((row, metadata) -> {
        // 映射Row到你的DTO对象
        YourDto dto = new YourDto();
        dto.setId(row.get("id", Long.class));
        // 其他字段映射...
        return dto;
    }).all();
}

注意：必须对sortColumn和sortDirection做合法性校验，比如只允许传入预定义的字段（如u.created_date、c.country）和ASC/DESC，防止SQL注入风险。

2. 结合Spring Data R2DBC的@Query与SpEL表达式 #

如果想继续用Spring Data的Repository接口，可以通过SpEL表达式动态注入排序逻辑，同时处理可选条件：

public interface YourRepository extends ReactiveCrudRepository<YourEntity, Long> {

    @Query("""
        SELECT u.id, u.catalog_id, u.amount, u.currency, u.created_date, cc.customer_id, c.country
        FROM user u 
        JOIN catalog c ON u.catalog_id = c.id 
        JOIN catalog_contract cc ON cc.id = c.catalog_contract_id 
        WHERE (:userCountryId IS NULL OR u.user_country_id = :userCountryId)
        AND (:customerId IS NULL OR cc.customer_id = :customerId)
        ORDER BY :#{#sort}
        OFFSET :offset LIMIT :limit
        """)
    Flux<YourDto> findByDynamicParams(@Param("userCountryId") Long userCountryId,
                                      @Param("customerId") Long customerId,
                                      @Param("sort") String sort,
                                      @Param("offset") int offset,
                                      @Param("limit") int limit);
}

调用时，把排序字段和方向拼接成一个字符串传入，比如"u.created_date DESC"，同时同样要对这个参数做合法性校验。

如果想用Pageable，由于R2DBC的Pageable支持需要手动处理总条数查询，可以额外写一个查询总记录数的方法：

@Query("""
    SELECT COUNT(*)
    FROM user u 
    JOIN catalog c ON u.catalog_id = c.id 
    JOIN catalog_contract cc ON cc.id = c.catalog_contract_id 
    WHERE (:userCountryId IS NULL OR u.user_country_id = :userCountryId)
    AND (:customerId IS NULL OR cc.customer_id = :customerId)
    """)
Mono<Long> countByDynamicParams(@Param("userCountryId") Long userCountryId,
                                @Param("customerId") Long customerId);

然后在Service层组合分页数据：

public Mono<Page<YourDto>> getPage(Long userCountryId, Long customerId, Pageable pageable) {
    // 校验排序字段合法性
    String sortStr = pageable.getSort().stream()
            .map(order -> order.getProperty() + " " + order.getDirection())
            .collect(Collectors.joining(", "));

    return yourRepository.countByDynamicParams(userCountryId, customerId)
            .flatMap(total -> yourRepository.findByDynamicParams(userCountryId, customerId, sortStr, 
                            pageable.getPageNumber() * pageable.getPageSize(), pageable.getPageSize())
                    .collectList()
                    .map(list -> new PageImpl<>(list, pageable, total)));
}

3. 使用Querydsl R2DBC扩展 #

如果项目复杂、动态查询场景多，可以引入Querydsl R2DBC依赖，它能通过类型安全的方式构建动态SQL，自动处理可选条件、排序和分页，避免手动拼接SQL的麻烦。

依赖配置（以Maven为例） #

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-r2dbc</artifactId>
    <version>5.0.0</version>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <version>5.0.0</version>
    <scope>provided</scope>
</dependency>

实现示例 #

先通过APT生成实体的Q类，然后在Repository中使用：

public interface YourQuerydslRepository extends ReactiveQuerydslPredicateExecutor<YourEntity> {
}

// Service层代码
public Flux<YourDto> queryWithQuerydsl(Optional<Long> userCountryId, Optional<Long> customerId, Pageable pageable) {
    QUser u = QUser.user;
    QCatalog c = QCatalog.catalog;
    QCatalogContract cc = QCatalogContract.catalogContract;

    // 构建动态Predicate
    BooleanBuilder predicate = new BooleanBuilder();
    userCountryId.ifPresent(id -> predicate.and(u.userCountryId.eq(id)));
    customerId.ifPresent(id -> predicate.and(cc.customerId.eq(id)));

    // 构建查询
    return querydslRepository.findAll(
            predicate,
            PageableExecutionUtils.getPageable(pageable)
        )
        .map(this::convertToDto);
}

Querydsl会自动处理JOIN、排序、分页逻辑，完全避免手动拼接SQL的问题，同时保证类型安全。

内容的提问来源于stack exchange，提问作者Etibar - a tea bar