JPA中使用别名实现Union查询的问题求助

为什么ORM会自动添加COUNT语句？ #

这通常是Spring Data JPA（或其底层ORM如Hibernate）的分页机制导致的：当你的查询方法返回Page<Human>或者参数里包含Pageable时，框架会自动先执行一条COUNT查询来获取总记录数，以此计算分页的总页数。它会尝试把你的原生Union查询包装成SELECT COUNT(p) FROM (你的Union SQL) p的格式，但Union查询的结构、别名或者大小写问题会导致这个自动生成的COUNT SQL语法错误，最终抛出InvalidDataAccessResourceUsageException。

解决方法 #

针对这个问题，你可以从以下几个方向入手解决：

1. 禁用自动COUNT查询（不需要分页时）

如果你的业务不需要分页，直接把返回类型改成List<Human>，去掉Pageable参数。这样ORM就不会触发COUNT查询，直接执行你的原生Union语句：

@Query(value = "SELECT fn AS firstname, ln AS lastname FROM person UNION SELECT first AS firstname, last AS lastname FROM people",
       nativeQuery = true)
List<Human> findAllHumans();

2. 手动指定COUNT查询（需要分页时）

如果必须用分页，在@Query注解里通过countQuery属性提供正确的COUNT语句，避免框架自动生成错误的格式：

@Query(value = "SELECT fn AS firstname, ln AS lastname FROM person UNION SELECT first AS firstname, last AS lastname FROM people",
       countQuery = "SELECT COUNT(*) FROM (SELECT fn AS firstname, ln AS lastname FROM person UNION SELECT first AS firstname, last AS lastname FROM people) AS temp",
       nativeQuery = true)
Page<Human> findAllHumans(Pageable pageable);

注意这里要把Union查询包裹成子查询，并用别名（比如temp），避免数据库语法报错。

3. 确保DTO与查询结果的映射正确

你的HumanDTO的字段是区分大小写的（FIRSTNAME、LASTNAME），要保证查询的别名和DTO字段匹配，或者通过映射注解明确对应关系：

• 方法一：用构造函数映射（确保构造函数参数顺序和查询列顺序一致）

public class Human {
    private String FIRSTNAME;
    private String LASTNAME;

    // 构造函数参数顺序要和SQL查询的列顺序完全一致
    public Human(String FIRSTNAME, String LASTNAME) {
        this.FIRSTNAME = FIRSTNAME;
        this.LASTNAME = LASTNAME;
    }

    // Getter和Setter
}

• 方法二：用@SqlResultSetMapping明确映射关系（更灵活，不受顺序影响）

@SqlResultSetMapping(
    name = "HumanResultSetMapping",
    classes = @ConstructorResult(
        targetClass = Human.class,
        columns = {
            @ColumnResult(name = "firstname", property = "FIRSTNAME"),
            @ColumnResult(name = "lastname", property = "LASTNAME")
        }
    )
)

然后在@Query里指定这个映射：

@Query(value = "SELECT fn AS firstname, ln AS lastname FROM person UNION SELECT first AS firstname, last AS lastname FROM people",
       nativeQuery = true,
       resultSetMapping = "HumanResultSetMapping")
List<Human> findAllHumans();

总结 #

核心问题是框架的自动分页COUNT逻辑与原生Union查询不兼容，要么通过返回List禁用分页，要么手动提供正确的COUNT查询，同时确保DTO和查询结果的映射没有大小写或顺序问题，就能解决这个异常。

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