我不认为它与Order by count using Spring Data JpaRepository重复 .
让我解释一下我的问题: -
我有一个名为Account的域对象,它包含名称,状态,组对象列表,成员资格对象列表 .
例如:-
@Entity
public class Account {
private String name;
private String status;
@OneToMany( cascade = CascadeType.ALL, mappedBy = "account",orphanRemoval = true)
private List<Group> groups;
@OneToMany(mappedBy = "account", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Membership> memberships;
// getters and setters..
}
@Eintity
public class Group{
// fields and getters setters..
}
@Eintity
public class Membership{
// fields and getters setters..
}
我使用spring数据JPA存储库来查找所有帐户并传递pageable和querydsl谓词参数(例如 Page<Account> allAccountsPage = accountRepository.findAll(querydslPredicate, pageable);
) . 如果我只是想按名称和/或状态对帐户进行排序,那么使用pageable可以很好地进行排序,但如果我想按组大小和/或成员资格排序,则JPA存储库对我来说不起作用 .
我发现了一个可能的解决方案(即Order by count using Spring Data JpaRepository),但这对于单列排序很有用,但我需要实现多列排序,可能包括名称,状态,组(按大小),成员资格(按大小)以及任何顺序 .
Is there any possible and graceful way to do that?
EDIT:-
找到了一种使用hibernate的@Formula注释的方法 .
这是一种方式: -
@Entity
public class Account {
private String name;
private String status;
@OneToMany( cascade = CascadeType.ALL, mappedBy = "account",orphanRemoval = true)
private List<Group> groups;
@OneToMany(mappedBy = "account", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Membership> memberships;
@Formula("(select count(*) from groups where groups.account_id=id)")
private long groupSize;
@Formula("(select count(*) from membership where membership.account_id=id)")
private long membershipSize;
// getters and setters..
}
现在只需使用休眠条件查找所有帐户,而不是按组和/或成员身份排序,现在您可以按groupSize和membershipSize对其进行排序 .
但这并不是一个好方法,因为@formula会创建一个使查询变慢的子查询 . 此外,对于Account hibernate命中子查询的每个* select查询,获取groupSize和membershipSize,使每个查询都非常慢 .
所以上面的解决方案并不是解决我问题的优雅方式 . Can anybody suggest better solution than that?