Spring Boot中使用Pageable对数据库存为String的整数排序

我使用PostgreSQL + Spring Boot 3.2.2，尝试让startTimestamp字段按数值从小到大排序，但因为该字段存储为String类型，默认排序和使用Sort.by("startTimeStamp").ascending()都不符合预期——比如值为"938"的条目会排在"620"前面，是按字符串字典序而非数值顺序排序的。

相关代码 #

实体类

public class YoutubeVideo {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID Id;
    private String videoId;
    private String startTimeStamp;
    private String fullYouTubeUrl;

    // getter、setter、构造方法等
}

服务层查询方法

public Page<YoutubeVideoDto> findAllByYtVideoId(Integer pageNum, String videoId) {
        Page<YoutubeVideo> dbResponse = youtubeVideoRepository
                        .findAllByYtVideoId(PageRequest.of(pageNum, NUMBER_OF_ITEMS_PER_PAGE, Sort.by("startTimeStamp").ascending()), videoId);
                                                
        return dbResponse.map(this::mapYoutubeVideoDto);
};

JPA Repository

public interface YoutubeVideoRepository extends JpaRepository<YoutubeVideo, UUID> {
    Page<YoutubeVideo> findAllByYtVideoId(Pageable pageable, String videoId);
}

解决方案 #

方案1：修改字段类型（最优解） #

直接将数据库和实体类中的startTimestamp改为Integer类型，从根源避免类型转换问题。

1. 修改实体类：

public class YoutubeVideo {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String videoId;
    private Integer startTimeStamp; // 替换为Integer类型
    private String fullYouTubeUrl;

    // getter、setter、构造方法等
}

2. 执行PostgreSQL字段类型修改脚本：

ALTER TABLE youtube_video ALTER COLUMN start_time_stamp TYPE integer USING start_time_stamp::integer;

3. 重启服务后，原有排序代码即可按数值正常排序。

方案2：自定义JPQL查询，数据库层面转换类型排序 #

如果无法修改字段类型，可在查询时将String转为Integer再排序：

修改Repository

public interface YoutubeVideoRepository extends JpaRepository<YoutubeVideo, UUID> {
    @Query("SELECT y FROM YoutubeVideo y WHERE y.videoId = :videoId ORDER BY CAST(y.startTimeStamp AS integer) ASC")
    Page<YoutubeVideo> findAllByYtVideoIdOrderByStartTimeStampAsInteger(Pageable pageable, @Param("videoId") String videoId);
}

修改服务层调用

public Page<YoutubeVideoDto> findAllByYtVideoId(Integer pageNum, String videoId) {
    // JPQL已指定排序，无需在PageRequest中添加Sort
    Page<YoutubeVideo> dbResponse = youtubeVideoRepository
            .findAllByYtVideoIdOrderByStartTimeStampAsInteger(PageRequest.of(pageNum, NUMBER_OF_ITEMS_PER_PAGE), videoId);
    return dbResponse.map(this::mapYoutubeVideoDto);
};

方案3：自定义Sort对象实现动态转换排序 #

如果需要动态控制排序方向，可自定义Sort表达式实现类型转换：

public Page<YoutubeVideoDto> findAllByYtVideoId(Integer pageNum, String videoId) {
    // 自定义排序规则，将startTimeStamp转为Integer后排序
    Sort sort = Sort.by(new Order(Sort.Direction.ASC, "startTimeStamp") {
        @Override
        public String getProperty() {
            return "CAST(startTimeStamp AS integer)";
        }
    });
    Page<YoutubeVideo> dbResponse = youtubeVideoRepository
            .findAllByYtVideoId(PageRequest.of(pageNum, NUMBER_OF_ITEMS_PER_PAGE, sort), videoId);
    return dbResponse.map(this::mapYoutubeVideoDto);
};

注：该方式依赖Hibernate等JPA提供者支持自定义表达式，PostgreSQL环境下可正常使用。

方案4：查询后内存排序（仅适合小数据量） #

若以上方案均无法实现，可先查询数据再在内存中排序，但会加载全量数据到内存，大数据量下性能极差：

public Page<YoutubeVideoDto> findAllByYtVideoId(Integer pageNum, String videoId) {
    // 先查询符合条件的所有数据，不指定排序
    Page<YoutubeVideo> dbResponse = youtubeVideoRepository
            .findAllByYtVideoId(PageRequest.of(pageNum, NUMBER_OF_ITEMS_PER_PAGE), videoId);
    // 内存中按数值排序
    List<YoutubeVideo> sortedList = dbResponse.getContent().stream()
            .sorted(Comparator.comparingInt(v -> Integer.parseInt(v.getStartTimeStamp())))
            .collect(Collectors.toList());
    // 重新构建Page对象
    Page<YoutubeVideo> sortedPage = new PageImpl<>(sortedList, dbResponse.getPageable(), dbResponse.getTotalElements());
    return sortedPage.map(this::mapYoutubeVideoDto);
};

内容的提问来源于stack exchange，提问作者Access _ Max