Spring Data paginate and sort a NamedNativeQuery (JPA-Hibernate-MySql)

Question

I'm using NamedNativeQueries with SqlResultSetMappings in a Spring Data (JPA Hibernate MySQL) application, and I've been successful with the Pagination, but not with the sorting.

I've tried two forms of queries:

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

and the second is simply using the count notation on a second query instead of using the #page notation.

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

Both methods work for pagination, but the sorting is ignored.

Here is the repository:

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

And the pageable gets assembled like this:

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

No errors are thrown, but the sorting simply doesn't get done and no ORDER BY is generated.

Explicitly adding something like ORDER BY #{#page} won't compile.

how did you managed to get pagination on NamedNativeQuery? did you use page object in the query or did you pass it in any way in NamedNativeQuery? I notice that even though you are passing in the pageable parameter but the return type is List instead of Page. — yousafsajjad
– yousafsajjad, Commented Jun 23, 2020 at 14:57

RobC · Accepted Answer · 2019-08-14 13:20:57Z

I encountered the same problem, where I had to dynamically filter/sort using a NamedNativeQuery by different columns and directions; apparently the Sorting was ignored. I found this workaround, which is not necessarily nice but it does the job:

For the repository:

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

The native queries look like this:

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

Notice in the count query I use the 2 redundant conditions for :sortColumn and :sortDirection, because once specified as @Param in the repository function, you need to use them in the actual query.

When calling the function, in my service I had a boolean which dictates the direction and a string that dictates the sorting column like this:

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

The 2 things that I don't like about this are the redundant usage of the sortColumn and sortDirection params in the count query, and the way I wrote the order by statement. The reasoning for having separate CASE statements is because I had different data types for the columns that I sorted by, and if they are incompatible (e.g. nvarchar and date), the query will fail with the error:

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

I could also probably nest the conditionals, i.e. first making a case for the direction, the making an inner case for the columns, but my SQL skills only went this far.

Hope this helps! Any feedback or improvements are very welcomed.

Collectives™ on Stack Overflow

Spring Data paginate and sort a NamedNativeQuery (JPA-Hibernate-MySql)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related