如何在R2dbcRepository中实现带排序与分页的子查询？原生查询报错解决方案求助

我刚好之前碰到过类似的R2DBC原生查询场景，你的问题主要是把R2DBC Repository的@Query用法和JPA搞混了，加上R2DBC本身对结果映射、参数绑定的规则不一样，咱们一步步来解决：

先理清你之前踩的坑 #

1. R2DBC的@Query没有nativeQuery=true属性：这是JPA的专属配置，R2DBC的@Query默认就是执行原生SQL，加这个属性反而会报错。
2. 参数绑定规则不同：R2DBC用:$paramName（或者位置绑定$1），而不是JPA的:paramName；另外排序字段、方向这类SQL标识符不能用参数绑定，因为数据库会把它们当成字符串值，导致语法错误。
3. 结果映射问题：直接返回Object[]或者用JPA的select new DTO()语法都不行，R2DBC不支持HQL风格的DTO构造，需要手动映射结果。

解决方案1：修正R2dbcRepository的@Query写法 #

我们可以用SpEL表达式来安全注入排序标识符，同时手动处理分页偏移量，再用Tuple接收结果后映射到DTO：

1.1 Repository层代码 #

import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import reactor.core.publisher.Flux;
import org.springframework.data.r2dbc.repository.query.Tuple;

public interface RegistrationRepository extends ReactiveCrudRepository<Registration, Long> {

    @Query("""
            SELECT R.ID, R.REGISTRATION_DATE, R.NAME, U.FULL_NAME, R.STATUS, R.LAST_MODIFIED_DATE,
                   (SELECT ASSESSOR.FULL_NAME FROM JHI_USER ASSESSOR WHERE ASSESSOR.USERNAME = R.LAST_MODIFIED_BY) AS LAST_MODIFIED_BY
            FROM REGISTRATION R
            INNER JOIN JHI_USER U ON R.ID = U.REGISTRATION_ID
            WHERE U.SUBMITTED_REGISTRATION = TRUE
              AND LOWER(U.FULL_NAME) LIKE CONCAT('%', LOWER(:$fullName), '%')
            ORDER BY #{#sortColumn} #{#sortDirection}
            LIMIT :$pageSize OFFSET :#{#pageNumber * #pageSize}
            """)
    Flux<Tuple> findAllByAppUsers(String fullName, String sortColumn, String sortDirection, int pageSize, int pageNumber);
}

1.2 Service层映射到DTO #

在Service里要先验证排序参数（防止SQL注入），再把Tuple转成你的RegistrationDTO：

@Service
public class RegistrationService {

    private final RegistrationRepository registrationRepository;

    public RegistrationService(RegistrationRepository registrationRepository) {
        this.registrationRepository = registrationRepository;
    }

    public Flux<RegistrationDTO> getFilteredRegistrations(String fullName, String sortColumn, String sortDirection, int pageSize, int pageNumber) {
        // 防护SQL注入：限制排序字段只能是允许的列
        List<String> allowedColumns = List.of("ID", "REGISTRATION_DATE", "NAME", "FULL_NAME", "STATUS", "LAST_MODIFIED_DATE", "LAST_MODIFIED_BY");
        String safeSortColumn = allowedColumns.contains(sortColumn.toUpperCase()) ? sortColumn.toUpperCase() : "REGISTRATION_DATE";
        
        // 限制排序方向只能是ASC/DESC
        String safeSortDirection = List.of("ASC", "DESC").contains(sortDirection.toUpperCase()) ? sortDirection.toUpperCase() : "ASC";

        return registrationRepository.findAllByAppUsers(fullName, safeSortColumn, safeSortDirection, pageSize, pageNumber)
                .map(tuple -> RegistrationDTO.builder()
                        .id(tuple.get("ID", Long.class))
                        .registrationDate(tuple.get("REGISTRATION_DATE", LocalDateTime.class))
                        .name(tuple.get("NAME", String.class))
                        .fullName(tuple.get("FULL_NAME", String.class))
                        .status(tuple.get("STATUS", String.class))
                        .lastModifiedDate(tuple.get("LAST_MODIFIED_DATE", LocalDateTime.class))
                        .lastModifiedBy(tuple.get("LAST_MODIFIED_BY", String.class))
                        .build());
    }
}

解决方案2：用R2dbcEntityTemplate更灵活处理复杂查询 #

如果你的查询逻辑还要更复杂，直接用R2dbcEntityTemplate或者DatabaseClient会更可控，完全自定义SQL执行和结果映射：

import org.springframework.data.r2dbc.core.R2dbcEntityTemplate;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Flux;

@Service
public class RegistrationService {

    private final R2dbcEntityTemplate template;

    public RegistrationService(R2dbcEntityTemplate template) {
        this.template = template;
    }

    public Flux<RegistrationDTO> getFilteredRegistrations(String fullName, String sortColumn, String sortDirection, int pageSize, int pageNumber) {
        // 先做参数安全校验
        List<String> allowedColumns = List.of("ID", "REGISTRATION_DATE", "NAME", "FULL_NAME", "STATUS", "LAST_MODIFIED_DATE", "LAST_MODIFIED_BY");
        String safeSortColumn = allowedColumns.contains(sortColumn.toUpperCase()) ? sortColumn.toUpperCase() : "REGISTRATION_DATE";
        String safeSortDirection = List.of("ASC", "DESC").contains(sortDirection.toUpperCase()) ? sortDirection.toUpperCase() : "ASC";

        int offset = pageNumber * pageSize;
        // 拼接安全的SQL
        String sql = """
                SELECT R.ID, R.REGISTRATION_DATE, R.NAME, U.FULL_NAME, R.STATUS, R.LAST_MODIFIED_DATE,
                       (SELECT ASSESSOR.FULL_NAME FROM JHI_USER ASSESSOR WHERE ASSESSOR.USERNAME = R.LAST_MODIFIED_BY) AS LAST_MODIFIED_BY
                FROM REGISTRATION R
                INNER JOIN JHI_USER U ON R.ID = U.REGISTRATION_ID
                WHERE U.SUBMITTED_REGISTRATION = TRUE
                  AND LOWER(U.FULL_NAME) LIKE CONCAT('%', LOWER($1), '%')
                ORDER BY %s %s
                LIMIT $2 OFFSET $3
                """.formatted(safeSortColumn, safeSortDirection);

        return template.getDatabaseClient()
                .sql(sql)
                .bind(0, fullName)
                .bind(1, pageSize)
                .bind(2, offset)
                .map((row, metadata) -> RegistrationDTO.builder()
                        .id(row.get("ID", Long.class))
                        .registrationDate(row.get("REGISTRATION_DATE", LocalDateTime.class))
                        .name(row.get("NAME", String.class))
                        .fullName(row.get("FULL_NAME", String.class))
                        .status(row.get("STATUS", String.class))
                        .lastModifiedDate(row.get("LAST_MODIFIED_DATE", LocalDateTime.class))
                        .lastModifiedBy(row.get("LAST_MODIFIED_BY", String.class))
                        .build())
                .all();
    }
}

关键注意点 #

• SQL注入防护：排序字段和方向不能直接接收用户输入就拼SQL，一定要做白名单校验，防止恶意注入。
• 分页偏移量：R2DBC不会自动处理Pageable的偏移量，需要手动计算offset = pageNumber * pageSize。
• 结果映射：R2DBC不支持JPA的select new DTO()语法，必须通过Tuple、RowMapper或者手动映射Row对象来转换为DTO。

内容的提问来源于stack exchange，提问作者sophie