Блог

Я использую NamedNativeQueries с SqlResultSetMappings в приложении Spring Data (JPA Hibernate MySQL), и я добился успеха с разбиением на страницы, но не с сортировкой.

Я пробовал две формы запросов:

 @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"),
  

и второе — это просто использование обозначения count во втором запросе вместо использования обозначения #page.

 @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 #{#страница} не будет компилироваться.

Я столкнулся с той же проблемой, когда мне пришлось динамически фильтровать / сортировать с помощью 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 я использую 2 избыточных условия для :sortColumn и :sortDirection , потому что после указания as @Param в функции repository вам нужно использовать их в реальном запросе.

При вызове функции в моем сервисе у меня было логическое значение, которое определяет направление, и строка, которая определяет столбец сортировки следующим образом:

 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)
}
  

Две вещи, которые мне в этом не нравятся, — это избыточное использование параметров sortColumn и sortDirection в запросе count и то, как я написал инструкцию order by . Причиной наличия отдельных CASE инструкций является то, что у меня были разные типы данных для столбцов, по которым я сортировал, и если они несовместимы (например, nvarchar и date ), запрос завершится ошибкой:

Conversion failed when converting date and/or time from character string

Я мог бы также, вероятно, вложить условные обозначения, то есть сначала указать направление, а затем создать внутренний регистр для столбцов, но мои навыки SQL зашли только так далеко.

Надеюсь, это поможет! Любые отзывы или улучшения очень приветствуются.