如何替换JPA等价物上的本机order by子句？

Question

我使用JPA2.0标准构建器。我需要从一个表中获取数据，然后从另一个表中按列排序。这些表有一个关系：

 class Club{
   @OneToMany(mappedBy = "club")
    private List<Address> addresses;
    ...
    }

class Address{
@JoinColumn(name = "club_id", referencedColumnName = "id")
@ManyToOne(fetch = FetchType.LAZY)
private Club club;

@Column(name = "type")
private Long type;

@Column(name = "full_address")
    private String full_address;
    ...
        }

select c.full_name from club c
  ORDER BY (select a.full_address from address a WHERE c.id= a.club_id and a.type=1 LIMIT 1)


select c.full_name, (select a.full_address from address a WHERE a.type=1 AND c.id=a.club_id LIMIT 1) as full_address FROM club c
ORDER BY fullAddress;

谢谢！

Answer 1

本机查询也解决了问题，可以用JPA查询代替

select c.full_name, min(a.full_address) FROM club c LEFT JOIN address a on c.id = a.club_id
where a.id is null or a.type=1 or not exists(SELECT  1 from address aSub WHERE aSub .club_id=c.id AND aSub.type=1)
GROUP BY  c.id, c.full_name ORDER BY min(a.full_address);

JPA当量

 CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<ClubItem> query = builder.createQuery(ClubItem.class);

    Root<Club> root = query.from(Club.class);
    Join<Club, Address> addressJoin = root.join(Club_.address, JoinType.LEFT);

query.select(builder.construct(ClubItem.class, root.get(Club_.id), root.get(Club_.fullName), builder.function("min", String.class, addressJoin.get(Address_.fullAddress))));

    Subquery<Address> subquery = query.subquery(Address.class);
    Root<Address> addressRoot = subquery.from(Address.class);

    subquery.select(addressRoot);
    subquery.where(
            builder.and(
                    builder.equal(addressRoot.get(Address_.type), 1),
                    builder.equal(addressRoot.get(Address_.clubId), root.get(Club_.id))));   

    query.where(builder.or(builder.isNull(addressJoin), builder.equal(addressJoin.get(Address_.type), builder.literal(new Long(1))),
            builder.not(builder.exists(subquery))));

    query.groupBy(root.get(Club_.id), root.get(Club_.fullName))

            Order order = builder.asc(builder.function("min", String.class, addressJoin.get(Address_.fullAddress)));

            query.orderBy(order);            

    TypedQuery<ClubItem> contentQuery = em.createQuery(query);