Spring数据分页并排序NamedNativeRequesty（JPA Hibernate MySql）

Question

我在Spring数据（JPA Hibernate MySQL）应用程序中使用NamedNativeQueries和SqlResultSetMappings，我已经成功地完成了分页，但没有完成排序。

我尝试了两种形式的查询：

@NamedNativeQuery(
  name = "DatasetDetails.unallocatedDetailsInDataset",
  resultClass = DatasetDetails.class,
  resultSetMapping = "DatasetDetails.detailsForAllocation",
  query = "SELECT dd.id, fk_datasets_id, fk_domains_id, fk_sources_id, dom.name AS domain, " +
  "src.name AS source " +
  "FROM datasets AS d " +
  "JOIN datasets_details AS dd ON dd.fk_datasets_id = d.id " +
  "JOIN sources AS src ON src.id = dd.fk_sources_id " +
  "JOIN domains AS dom ON dom.id = dd.fk_domains_id " +
  "WHERE fk_datasets_id = :datasetId " +
  "AND dd.id NOT IN (" +
  "SELECT fk_datasets_details_id from allocations_datasets_details)  \n/* #page */\n"),

第二种方法是在第二个查询中简单地使用计数符号，而不是使用#页面符号。

@NamedNativeQuery(
  name = "DatasetDetails.unallocatedDetailsInDataset.count",
  resultClass = DatasetDetails.class,
  resultSetMapping = "DatasetDetails.detailsForAllocation",
  query = "SELECT count(*)
....

这两种方法都适用于分页，但忽略了排序。

这是存储库：

public interface DatasetDetailsRepository extends PagingAndSortingRepository<DatasetDetails, Long> {
    @Query(nativeQuery = true)
    List<DatasetDetails> unallocatedDetailsInDataset(@Param("datasetId") long datasetId,
                                                     @Param("page") Pageable page);
}

可分页页面的组装方式如下：

Sort sort = Sort.by(Sort.Order.asc(DatasetDetails.DOMAIN), Sort.Order.asc(DatasetDetails.SOURCE));
Pageable page = PageRequest.of(page, limit, sort);

没有抛出错误，但排序没有完成，也没有生成ORDER BY。

通过#{#page}显式添加类似ORDER的内容将无法编译。

Answer 1

我也遇到了同样的问题，我必须使用一个名为NamedNativeQuery的函数按不同的列和方向动态过滤/排序；显然，排序被忽略了。我发现这个解决方法不一定很好，但确实有效：

对于存储库：

List<MyEntity> findMyEntities(
    @Param("entityId") long entityId,
    @Param("sortColumn") String sortColumn,
    @Param("sortDirection") String sortDirection,
    Pageable page);

本机查询如下所示：

@NamedNativeQueries({
  @NamedNativeQuery(name = "MyEntity.findMyEntities",
      query = "select e.field1, e.field2, ..." +
              " from my_schema.my_entities e" +
              " where condition1 and condtition2 ..." +
              " order by " +
              "   CASE WHEN :sortColumn = 'name'      and :sortDirection = 'asc'  THEN e.name      END ASC," +
              "   CASE WHEN :sortColumn = 'birthdate' and :sortDirection = 'asc'  THEN e.birthdate END ASC," +
              "   CASE WHEN :sortColumn = 'name'      and :sortDirection = 'desc' THEN e.name      END DESC," +
              "   CASE WHEN :sortColumn = 'birthdate' and :sortDirection = 'desc' THEN e.birthdate END DESC" +
  ),
  @NamedNativeQuery(name = "MyEntity.findMyEntities.count",
          query = "select count(*) from my_schema.my_entities e" +
                  " where condition1 and condtition2 ..." +
                  "  and :sortColumn = :sortColumn and :sortDirection = :sortDirection"
  )
})

注意，在count查询中，我使用了两个冗余条件：sortColumn和sortDirection，因为一旦在存储库函数中指定为Param，就需要在实际查询中使用它们。

调用函数时，在我的服务中有一个布尔值指示方向，还有一个字符串指示排序列，如下所示：

public Page<MyEntity> serviceFindFunction(Long entityId, String sortColumn, Boolean sortDirection, Integer pageNumber, Integer pageSize) {
    String sortDir = (sortDirection) ? 'asc' : 'desc';
    Pageable pageable = new PageRequest(pageNumber, pageSize); // Spring Data 1.0 syntax

    // for Spring Data 2.0, as you were using, simply:
    // Pageable pageable = PageRequest.of(pageNumber, pageSize);

    return entityRepository.findMyEntities(entityId, sortColumn, sortDir, pageable)
}

关于这一点，我不喜欢的两件事是在count查询中重复使用sortColumn和sortDirection参数，以及我编写order by语句的方式。使用单独的CASE语句的原因是，我排序的列的数据类型不同，如果它们不兼容（例如，nvarchar和date），查询将失败，并出现错误：

从字符串转换日期和/或时间时转换失败

我也可能嵌套条件，即首先为方向做一个案例，为列做一个内部案例，但我的SQL技能只做了这么多。

希望这有帮助！欢迎任何反馈或改进。