Spring Boot分页查询page=0时出现Hibernate HQL表未映射错误求助

我基于Spring Boot开发了一个分页查询表数据的API，当page参数为1时接口可正常返回数据，但当page为0时，触发Hibernate HQL错误，提示“table is not mapped”。相关代码如下：

控制器Get方法 #

@GetMapping("")
public Iterable<PositionDto> getPosition(@RequestParam(defaultValue = "0") Integer page,
                                        @RequestParam(defaultValue = "10") Integer size){
    Pageable pageable = PageRequest.of(page, size);
    return repository.findAllMapping(pageable);
}

PositionDto.java #

@Data
public class PositionDto implements Serializable{

    private static final long serialVersionUID = -2895197668336416172L;
    private Long positionId;
    private String title;
    private String level;
    private String parentPositionTitle;
    
    public PositionDto(Long positionId, String title, String level, String parentPositionTitle) {
        this.positionId = positionId;
        this.title = title;
        this.level = level;
        this.parentPositionTitle = parentPositionTitle;
    }
}

Position.java #

@Entity
@Data
@NamedNativeQuery(
        name = "find_all_position",
        query = "SELECT "
                    + "child.position_id as positionId, "
                    + "child.title as title, "
                    + "child.level as level, "
                    + "parent.title as parentPositionTitle "
                    + "FROM position child "
                    + "JOIN position parent "
                    + "ON child.parent_position_id = parent.position_id",
        resultSetMapping = "position_dto"
    )
@SqlResultSetMapping(
        name = "position_dto",
        classes = @ConstructorResult(
            targetClass = PositionDto.class,
            columns = {
                @ColumnResult(name = "positionId", type = Long.class),
                @ColumnResult(name = "title", type = String.class),
                @ColumnResult(name = "level", type = String.class),
                @ColumnResult(name = "parentPositionTitle", type = String.class),
            }
        )
    )
public class Position extends BaseAudit{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long positionId;
    private String title;
    private String level;
    private Long parentPositionId;
    
}

PositionRepository #

public interface PositionRepository extends PagingAndSortingRepository<Position, Long> {

    @Query(name = "find_all_position", nativeQuery = true)
    Page<PositionDto> findAllMapping(Pageable pageable);
    
    Optional<Position> findById(Long positionId);
    
    List<Position> findByLevel(String level);

    Position save(Position position);
    
    void deleteById(Long positionId);   
}

核心原因 #

当page=0时，Spring Data JPA处理原生SQL分页时，会自动生成count查询，但错误地使用HQL语法（而非原生SQL）构建统计语句。HQL要求使用实体类名，而你的原生SQL用的是数据库表名，因此触发“table is not mapped”错误；page=1时未触发该问题，是因为框架的分页逻辑在特定条件下跳过了错误的count查询生成。

解决方法 #

方式一：手动指定原生count查询（推荐）

通过给@Query指定对应的count查询名称，让框架使用正确的原生SQL统计总数：

1. 在Position实体类中新增count用的@NamedNativeQuery：

@Entity
@Data
// 保留原有查询
@NamedNativeQuery(
        name = "find_all_position",
        query = "SELECT "
                    + "child.position_id as positionId, "
                    + "child.title as title, "
                    + "child.level as level, "
                    + "parent.title as parentPositionTitle "
                    + "FROM position child "
                    + "JOIN position parent "
                    + "ON child.parent_position_id = parent.position_id",
        resultSetMapping = "position_dto"
    )
// 添加count查询
@NamedNativeQuery(
        name = "find_all_position_count",
        query = "SELECT COUNT(*) FROM position child JOIN position parent ON child.parent_position_id = parent.position_id"
    )
@SqlResultSetMapping(
        name = "position_dto",
        classes = @ConstructorResult(
            targetClass = PositionDto.class,
            columns = {
                @ColumnResult(name = "positionId", type = Long.class),
                @ColumnResult(name = "title", type = String.class),
                @ColumnResult(name = "level", type = String.class),
                @ColumnResult(name = "parentPositionTitle", type = String.class),
            }
        )
    )
public class Position extends BaseAudit{
    // 原有实体代码不变
}

2. 修改PositionRepository中的方法，指定countName：

public interface PositionRepository extends PagingAndSortingRepository<Position, Long> {

    @Query(name = "find_all_position", nativeQuery = true, countName = "find_all_position_count")
    Page<PositionDto> findAllMapping(Pageable pageable);
    
    // 其他方法不变
}

方式二：禁用自动count查询（仅特殊场景使用）

如果不需要分页总数统计，可以设置countQuery为空，但会导致返回的Page对象中totalElements和totalPages失效：

@Query(name = "find_all_position", nativeQuery = true, countQuery = "")
Page<PositionDto> findAllMapping(Pageable pageable);

额外优化：统一分页参数起始页

Spring Data JPA默认分页从0开始，但前端通常习惯从1开始。可以在控制器层做转换，避免用户传入0引发问题：

@GetMapping("")
public Iterable<PositionDto> getPosition(@RequestParam(defaultValue = "1") Integer page,
                                        @RequestParam(defaultValue = "10") Integer size){
    // 转换为框架需要的0起始页
    Pageable pageable = PageRequest.of(page - 1, size);
    return repository.findAllMapping(pageable);
}

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