Spring data JpaRepository pagination and group by


Ignoring why I need to do a Group By without an aggregate, I found my solution:

@Query("SELECT pr FROM PermissionRule pr WHERE pr.organizationId = ?1 AND pr.type = ?2 GROUP BY objectReference")
Page<PermissionRule> getFunds(Long organizationId, String type, Pageable pageable);

Seems more straight forward than the native_query approach.

Useful link

Author by


Updated on December 10, 2022


  • Fofole
    Fofole 11 months

    In my JpaRepository interface I got the following method that works fine:

    Page<PermissionRule> findByOrganizationIdAndTypeStringAndObjectReferenceIgnoreCaseContaining(
            Long organizationId, String typeId, String searchTerm, Pageable pageable);

    What I need is the same query with a group by object_reference.

    This is what I tried doing : EDIT

    @Query(value = "SELECT object_reference, dst_type, other FROM permission_rule pr WHERE pr.organization_id = %?1% AND pr.dst_type_id = %?2% AND pr.object_reference LIKE %?3% GROUP BY object_reference", nativeQuery = true)
    Page<PermissionRule> getFunds(Long organizationId, String dstTypeId, String searchTerm, Pageable pageable);

    but I get the following error

    InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method

    Also if it helps my entity column that I want to use in GROUP BY is

    @Column(name = "object_reference", nullable = false)
    private String objectReference;

    My question is whether there is an easier way to do it or I still need a custom query (do I need to move the custom query in the Entity maybe or use named query instead?) ?

    • hamed
      hamed over 4 years
      I think this link can help you.
  • Deepak Khillare
    Deepak Khillare over 4 years
    You probably need to use countQuery for pagination to work with native query.