Spring数据JpaRepository的分页和分组依据 [英] Spring data JpaRepository pagination and group by

问题描述

在我的JpaRepository界面中，我得到了以下效果很好的方法:

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

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

我需要的是使用object_reference进行分组的同一查询.

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

但出现以下错误

InvalidJpaQueryMethodException:无法将本机查询与方法中的动态排序和/或分页一起使用

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

如果它对我要在GROUP BY中使用的实体列有所帮助，则是

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;

我的问题是是否有更简便的方法还是我仍然需要自定义查询(我是否需要在Entity中移动自定义查询或使用命名查询?)?

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

推荐答案

忽略了为什么我需要不进行汇总的分组依据，我找到了解决方案:

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

似乎比native_query方法更直接.

Seems more straight forward than the native_query approach.

有用的 查看全文