java - JPA2 - 如何在 OpenJPA 中的 Join 中按嵌入复合主键中的字段进行排序

Question

我正在尝试使用 Criteria Query API + RAD/Dali 自动生成的 OpenJPA 2.1.2-SNAPSHOT 在 WebSphere v8.0.0.5 上的静态规范元模型来复制以下工作查询:

`SELECT *
FROM CENTER c
    INNER JOIN STATE s ON s.ID = c.STATE_ID
    INNER JOIN HOURS_OF_OPERATION h ON h.CENTER_ID = c.ID
WHERE c.CITY = '<city_name_here>'
ORDER BY h.WEEKDAY_NUMBER;`

我有四个实体构成此查询的核心:

• 中心.java
• 状态.java
• HoursOfOperation.java
• HoursOfOperationPK.java

每个州有许多中心。每个中心有很多营业时间。我需要返回一个结果集，其中包含中心信息、州缩写和中心营业时间(按工作日数字 1-7(代表星期一 - 星期日)ASC 排序)。

这是我的方法:

public Center getCenterInfo(String centerCityName) {
    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Center> cq = cb.createQuery(Center.class);
    Metamodel m = em.getMetamodel();
    EntityType<Center> _center = m.entity(Center.class);
    EntityType<State> _state = m.entity(State.class);

    Root<Center> center = cq.from( _center );
    Join<Center, State> state = center.join( Center_.state );
    Join<Center, HoursOfOperation> hop = center.join( Center_.hoursOfOperations );
    cq.select(center).distinct(true);
    Predicate predicate = cb.equal(center.get(Center_.city), centerCityName);
    cq.where(predicate);
    //cq.orderBy(cb.asc(hop.get(HoursOfOperation_.id)));<---Can't access PK field here
    center.fetch( Center_.state );
    center.fetch( Center_.hoursOfOperations );

    TypedQuery<Center> query = em.createQuery( cq );
    Center centerInfo = query.getSingleResult();

    return centerInfo;
}

我已经注释掉了我所坚持的那一行。我认为我必须调用某种方法来实例化某种 HoursOfOperationPK 实例，就像我使用 Join<Center, HoursOfOperation> hop 的方式一样。我认为这样做可以让我使用类似 cq.orderBy(cb.asc(hopPk.get(HoursOfOperationPK_.weekdayNumber))); 的东西我怎样才能实现这种排序？

其次，如果我不使用cq.select(center).distinct(true); ，我返回 49 条记录而不是 7 条记录。这是为什么？将记录计数减少到 7 的唯一方法是附加到选择的不同方法。我了解 DISTINCT 在 SQL 中的作用，但我的 ANSI 风格 SQL 语法顶部仅返回 7 条记录。

OpenJPA 日志输出表明 OrderBy 已应用于 HoursOfOperation.centerId。

以下是 HoursOfOperation 和 HoursOfOperationPK 实体的相关部分:

@Entity
@Table(name="HOURS_OF_OPERATION")
public class HoursOfOperation implements Serializable {
    private static final long serialVersionUID = 1L;

    @EmbeddedId
    private HoursOfOperationPK id;

    public HoursOfOperationPK getId() {
        return this.id;
    }

    public void setId(HoursOfOperationPK id) {
        this.id = id;
    }
}

@Embeddable
public class HoursOfOperationPK implements Serializable {
    //default serial version id, required for serializable classes.
    private static final long serialVersionUID = 1L;

    @Column(name="CENTER_ID", unique=true, nullable=false)
    private long centerId;

    @Column(name="WEEKDAY_NUMBER", unique=true, nullable=false)
    private long weekdayNumber;

    public HoursOfOperationPK() {
    }
    public long getCenterId() {
        return this.centerId;
    }
    public void setCenterId(long centerId) {
        this.centerId = centerId;
    }
    public long getWeekdayNumber() {
        return this.weekdayNumber;
    }
    public void setWeekdayNumber(long weekdayNumber) {
        this.weekdayNumber = weekdayNumber;
    }
}

编辑@perissf 我能够通过 ASC 使用显式顺序生成所需的结果(由于 weekdayNumber 是操作时间表的复合主键的一部分，似乎隐式发生了排序。不过，我宁愿进行显式排序，因为它可以帮助我处理其他查询，而我可能不太幸运):

    CriteriaBuilder cb = em.getCriteriaBuilder();

    CriteriaQuery<Center> cq = cb.createQuery(Center.class);
    Metamodel m = em.getMetamodel();
    EntityType<Center> _center = m.entity(Center.class);

    Root<Center> center = cq.from( _center );
    Expression<List<HoursOfOperation>> hop = center.get( Center_.hoursOfOperations );
    cq.select(center);
    Predicate predicate = cb.equal(center.get(Center_.city), centerCityName);
    cq.where(predicate);

    center.fetch( Center_.state );
    center.fetch( Center_.hoursOfOperations );

    TypedQuery<Center> query = em.createQuery( cq );
    Center centerInfo = query.getSingleResult();

但是，我也可以使用以下命令生成所需的 SQL，但唯一的问题是未设置中心的 hoursOfOperation(由于延迟加载。A center.Fetch(Center_.hoursOfOperation) 创建了重复记录。有人有解决方案吗？):

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Center> cq = cb.createQuery(Center.class);
    Metamodel m = em.getMetamodel();
    EntityType<Center> _center = m.entity(Center.class);
    Root<Center> center = cq.from( _center );
    EntityType<HoursOfOperation> _hoo = m.entity(HoursOfOperation.class);
    Root<HoursOfOperation> hoo = cq.from( _hoo );
    cq.select(center).distinct(true);
    Predicate predicate = cb.and(cb.equal(center.get(Center_.city), centerCityName), 
            cb.equal(center, hoo.get(HoursOfOperation_.center)));
    cq.where(predicate);
    cq.orderBy(cb.asc(hoo.get( HoursOfOperation_.id ).get(HoursOfOperationPK_.weekdayNumber)));
    center.fetch( Center_.state );

    TypedQuery<Center> query = em.createQuery( cq );
    Center centerInfo = query.getSingleResult();

Answer 1

我已经使用 EclipseLink + MySql 进行了测试，并且可以确认以下代码生成您正在查找的查询:

Root<Center> center = cq.from(Center.class);
Join<Center, HoursOfOperation> hoursOfOperation = center.join(Center_.hoursOfOperations);
Join<Center, State> state = center.join(Center_.stateId);
Path<HoursOfOperationPK> hoursOfOperationPK = hoursOfOperation.get(HoursOfOperation_.hoursOfOperationPK);
cq.where(cb.equal(center.get(Center_.city), "cityName"));
cq.orderBy(cb.asc(hoursOfOperationPK.get(HoursOfOperationPK_.weekdayNumber)));
TypedQuery<Center> query = em.createQuery(cq);

查询结果:

SELECT t1.id, t1.city, t1.state_id 
FROM state t0, hours_of_operation t2, center t1 
WHERE ((t1.city = ?) AND ((t0.id = t1.state_id) AND (t2.center_id = t1.id))) 
ORDER BY t2.weekday_number ASC

如果结果行与您预期的相比过多，这是由于与 State 实体的联接导致的，该实体没有被任何 where 谓词 过滤，因此结果行是两个表的笛卡尔积。