Spring Data JPA结合PostgreSQL使用原生查询搭配Sort.by时生成错误SQL的问题

看起来你遇到了Spring Data JPA原生查询分页排序的典型坑，我来帮你分析和解决这个问题：

问题根源分析 #

1. 原生查询的Sort字段匹配问题：
   当使用原生SQL时，Spring Data JPA不会帮你做实体类驼峰属性到数据库下划线列名的自动转换——你在Sort.by里指定的字段必须是数据库表的实际列名，而不是实体类的属性名。比如如果你的parent表中结束日期列是end_date，但你写了endDate，数据库肯定找不到这个字段，直接报错。

2. 自动生成的count查询不准确：
   对于带JOIN、子查询的复杂原生查询，Spring Data JPA自动生成的count查询经常会出错——要么错误地包含了排序逻辑，要么因为JOIN导致重复计数，最终触发SQL语法或逻辑错误。

解决方案 #

方案1：修正Sort中的字段名（必须做）

先确认你parent表中对应的日期列实际名称，如果是下划线命名的end_date，修改PageRequest的Sort部分：

PageRequest pageRequest = PageRequest.of(1, 10, Sort.by(Sort.Direction.DESC, "end_date"));

如果列名确实是endDate，为了避免歧义（比如子查询中万一有同名字段），可以加上表别名明确归属：

PageRequest pageRequest = PageRequest.of(1, 10, Sort.by(Sort.Direction.DESC, "p.endDate"));

方案2：手动指定count查询（强烈推荐）

复杂原生查询的自动count查询几乎都会出问题，直接在@Query注解里自定义countQuery，确保计数逻辑准确：

@Query(value = """
        SELECT p.*
        FROM parent p
                 JOIN (SELECT parent_id, sum(amount) AS total_amount
                       FROM child
                       GROUP BY parent_id) c ON p.id = c.parent_id
        WHERE p.number = :number
          AND p.diff != 0
          AND p.diff <> total_amount;
        """, 
        countQuery = """
        SELECT COUNT(DISTINCT p.id)
        FROM parent p
                 JOIN (SELECT parent_id, sum(amount) AS total_amount
                       FROM child
                       GROUP BY parent_id) c ON p.id = c.parent_id
        WHERE p.number = :number
          AND p.diff != 0
          AND p.diff <> total_amount;
        """,
        nativeQuery = true)
Page<ParentEntity> findAllWithDiffByNumber(@Param("number") String number, Pageable paging);

这里用COUNT(DISTINCT p.id)是为了避免JOIN带来的重复计数问题，保证分页的总页数准确。

方案3：应急方案——手动在SQL中加排序（不推荐）

如果暂时不想调整Sort逻辑，也可以直接把排序语句写进原生SQL里，同时保留Pageable用于分页：

@Query(value = """
        SELECT p.*
        FROM parent p
                 JOIN (SELECT parent_id, sum(amount) AS total_amount
                       FROM child
                       GROUP BY parent_id) c ON p.id = c.parent_id
        WHERE p.number = :number
          AND p.diff != 0
          AND p.diff <> total_amount
        ORDER BY p.end_date DESC;
        """, 
        countQuery = """
        SELECT COUNT(DISTINCT p.id)
        FROM parent p
                 JOIN (SELECT parent_id, sum(amount) AS total_amount
                       FROM child
                       GROUP BY parent_id) c ON p.id = c.parent_id
        WHERE p.number = :number
          AND p.diff != 0
          AND p.diff <> total_amount;
        """,
        nativeQuery = true)
Page<ParentEntity> findAllWithDiffByNumber(@Param("number") String number, Pageable paging);

这种方式的缺点是排序逻辑硬编码，失去了Pageable灵活传参的优势，只适合临时应急。

总结 #

最稳妥的解决方式是方案1+方案2结合：先修正Sort的字段名匹配数据库实际列，再自定义准确的count查询，这样就能完美解决原生查询分页排序的问题了。

备注：内容来源于stack exchange，提问作者JiKra