JPA：我如何在实体的Set字段中排序？ [英] JPA: How do I sort on a Set field in my entity?
问题描述
我使用Spring 3.2.11.RELEASE，Hibernate 4.3.6.Final和JPA 2.1。我有以下实体：
@Entity
@Table（name =user）
public class User实现Serializable，Comparable< User>
{
...
@ManyToMany
@JoinTable（name =user_organization，joinColumns = {@JoinColumn（name =USER_ID）}，inverseJoinColumns = {@JoinColumn（name =ORGANIZATION_ID）}）
@LazyCollection（LazyCollectionOption.FALSE）
@SortNatural
Private SortedSet< Organization>组织;
上面这些排序组织是由组织的名称字段完成的。当我运行JPA查询来检索用户对象时，我想根据与其关联的组织的有序列表进行排序。我试过这个...
$ b $ pre $ final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder（）;
final CriteriaQuery< User> criteria = builder.createQuery（User.class）;
...
最终SetJoin< User，Organization> orgsJoin = orderByRoot.join（User_.organizations，JoinType.LEFT）;
orderByExpr = orgsJoin.get（Organization_.name）;
criteria.orderBy（builder.asc（orderByExpr）;
但它不起作用对于用户与多个组织关联的情况，有时候用户的列表中的字母组织较低的用户会在正确的用户之前返回。没有编写大量原生SQL，我如何使用JPA / CriteriaBuilder解决这个问题？
编辑：这是我想要的一个例子，我正在寻找不同的用户。 b
- 用户A拥有AAA建筑和ZZZ建筑组织
- 用户B仅拥有MMM建筑用户C有组织CCC大厦和VVV Buidling用户D有组织AAA大厦和MMM Buidling
我想要用户D，用户A，用户C和用户B，因为用户A的组织拼接按字母顺序排列比用户C的组织结合ns，它依次按字母顺序排列，低于用户B的组织。
你不能为实体编写这样的查询。如果您使用的是原生SQL，您可以通过user.id获取GROUP BY组织名称，但这不是您想要的。
您可以简单地获取加入用户和组织：
final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder（）;
final CriteriaQuery< User> criteria = builder.createQuery（User.class）;
Root< User> root = criteria.from（User.class）;
获取<用户，组织>组织= root.fetch（组织）;
criteria.select（c）;
TypedQuery< User> query = em.createQuery（criteria）;
列表<用户> users = query.getResultList（）;
并将它们排序在内存中：
<$ （用户，新比较器<用户>（）{
@Override
public int compare（User user1，User user2）{
SortedSet< ; Organization> organizations1 = user1.getOrganizations（）;
SortedSet< Organization> organizations2 = user2.getOrganizations（）;
if（organizations1.isEmpty（））{
if（如果（organizations2.isEmpty（））{
return 0;
} else {
return -1;
}
} else {
} （））{
return 1;
} else {
Organization o1 = organizations1.first（）;
Organization o2 = organizations2.first（）;
return o1 .compareTo（o2）;
}
}
}
}）;
更新
如果您不想要获取内存中的所有内容进行排序，那么我认为只有JPQL或Criteria API才有可能。假设组织名称是VARCHAR（50），我们需要先使用正确的填充，然后我们需要运行 group_concat code>本地查询优先：
PostgrSql
SELECT o.user_id，
string_agg（RPAD（o.name，50）ORDER BY o.name）as ordr
FROM Organization o
GROUP BY o.user_id
order by ordr
MySQL
SELECT o.user_id，
group_concat（RPAD（o.name，50）ORDER BY o.name）as ordr
FROM Organization o
GROUP BY o.user_id
order by ordr
通过对user_id进行排序，您可以简单地将user_ids提取为 List< Long> 然后运行第二个JPQL查询来获取用户：
List< Long> ; userIds = ...;
TypedQuery< User> q = em.createQuery（
select u+
from User u+
where u.id in：userIds
，User.class）;
q.setParameter（userIds，userIds）;
列表<用户> users = q.getResultList（）;
I’m using Spring 3.2.11.RELEASE, Hibernate 4.3.6.Final, and JPA 2.1. I have the following entity with the following field …
@Entity
@Table(name = "user")
public class User implements Serializable, Comparable<User>
{
…
@ManyToMany
@JoinTable(name = "user_organization", joinColumns = { @JoinColumn(name = "USER_ID") }, inverseJoinColumns = { @JoinColumn(name = "ORGANIZATION_ID") })
@LazyCollection(LazyCollectionOption.FALSE)
@SortNatural
private SortedSet<Organization> organizations;
Above, the sort on organizations is done by the organization’s name field. When I run a JPA query to retrieve User objects, I would like to sort based on an ordered list of the organizations with which they are associated. I have tried this …
final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
final CriteriaQuery<User> criteria = builder.createQuery(User.class);
…
final SetJoin<User, Organization> orgsJoin = orderByRoot.join(User_.organizations, JoinType.LEFT);
orderByExpr = orgsJoin.get(Organization_.name);
criteria.orderBy(builder.asc(orderByExpr);
But it doesn’t work for cases where a user is associated with multiple organizations — sometimes a user with an alphabetically lower organization in his list is returned ahead of the correct users. Without writing a whole bunch of native SQL, how could I solve this problem with JPA/CriteriaBuilder?
Edit: Here's an example of what I want. I am looking for distinct users.
- User A has organizations, "AAA Building" and "ZZZ Building"
- User B has organizations of only "MMM Building"
- User C has organizations "CCC Building" and "VVV Buidling"
- User D has organizations "AAA Building" and "MMM Buidling"
I would want User D, User A, User C, and User B because the concatenation of user A's organizations are alphabetically lower than the concatenation of User C's organizations, which are in turn alphabetically lower than User B's organizations.
You cannot write such query for entities. If you were using native SQL you could GROUP BY organization names by the user.id, but that's not what you want.
You can simply fetch join Users and Organizations:
final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
final CriteriaQuery<User> criteria = builder.createQuery(User.class);
Root<User> root = criteria.from(User.class);
Fetch<User, Organization> organizations = root.fetch("organizations");
criteria.select(c);
TypedQuery<User> query = em.createQuery(criteria);
List<User> users = query.getResultList();
and sort them them in memory:
Collections.sort(users, new Comparator<User>() {
@Override
public int compare(User user1, User user2) {
SortedSet<Organization> organizations1 = user1.getOrganizations();
SortedSet<Organization> organizations2 = user2.getOrganizations();
if(organizations1.isEmpty()) {
if(organizations2.isEmpty()) {
return 0;
} else {
return -1;
}
} else {
if(organizations2.isEmpty()) {
return 1;
} else {
Organization o1 = organizations1.first();
Organization o2 = organizations2.first();
return o1.compareTo(o2);
}
}
}
});
Update
If you don't want to fetch everything in memory for sorting, then I don't think it's possible with JPQL or Criteria API only.
Assuming the organization name is a VARCHAR(50), we need to use a right padding first, then we need to run a group_concat native query first:
PostgrSql
SELECT o.user_id,
string_agg(RPAD(o.name, 50) ORDER BY o.name) as ordr
FROM Organization o
GROUP BY o.user_id
order by ordr
MySQL
SELECT o.user_id,
group_concat(RPAD(o.name, 50) ORDER BY o.name) as ordr
FROM Organization o
GROUP BY o.user_id
order by ordr
Having the user_id sorted, you can simply extract the user_ids to a List<Long> and then run a second JPQL query to fetch Users:
List<Long> userIds = ...;
TypedQuery<User> q = em.createQuery(
"select u " +
"from User u " +
"where u.id in :userIds "
, User.class);
q.setParameter("userIds", userIds);
List<User> users = q.getResultList();
这篇关于JPA：我如何在实体的Set字段中排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
