java - 使用 jpa 标准选择另一个表中每个状态的出现次数

Question

我需要在一个查询中使用条件 api 选择我的主表以及另一个表中每种状态的出现次数。

我当前的解决方案是使用 native 查询，这是可行的，但我想以更基于对象的方式来完成它。我尝试通过使用特定查询来按条件执行此操作，只是为了选择所有状态，然后手动对其进行计数。但通过这种方法，我调用两个查询:一个用于获取主表中的详细信息，另一个用于选择 id 与主表相同的所有状态。有没有更有效的方法来做到这一点？

这是我的 native 查询(简化):

SELECT * FROM(
SELECT a.id, a.type, b.count_pending, b.count_failed, b.count_processed
FROM CM AS a
LEFT JOIN ( SELECT 
 COUNT( CASE WHEN status = 'PENDING' THEN 1 ELSE NULL END ) count_pending,
 COUNT( CASE WHEN status = 'FAILED' THEN 1 ELSE NULL END ) count_failed,
 COUNT( CASE WHEN status = 'PROCESSED' THEN 1 ELSE NULL END ) count_processed
 FROM CM_PARAM WHERE id_cm = :cmId
 GROUP BY id_cm    
) AS b ON a.id_cm = b.id_cm
WHERE a.id_cm = :cmId) AS a

这是我的 CM 实体(简化):

@Entity
public class Cm {

  @Id
  private Long idCm;

  private String type;

  // other fields
  // setters and getters

}

这是我的 CM_PARAM 实体(简化):

@Entity
public class CmParam {

  @Id
  private Long idCmp;

  @ManyToOne
  @JoinColumn(name = "id_cm")
  private Cm cm;

  private String status;

  // other fields
  // setters and getters
}

使用 native 查询方法，我可以在我的 Cm 实体中添加 transient 字段:

@Transient
private Long countPending;

@Transient
private Long countFailed;

@Transient
private Long countProcessed;

如何使用标准 api 来完成此操作，如果可能的话，只需一次事务即可。

预期的输出会是这样的:

{
  "idCm": 1,
  "type": "sms",
  "countPending": 5,
  "countFailed": 3,
  "countProcessed": 9
}

Answer 1

您的查询可以在没有子查询连接的情况下重写:

SELECT
    a.id_cm,
    a.type
    COUNT(CASE WHEN b.status = 'PENDING' THEN 1 ELSE NULL END) countPending,
    COUNT(CASE WHEN b.status = 'FAILED' THEN 1 ELSE NULL END) countFailed,
    COUNT( CASE WHEN b.status = 'PROCESSED' THEN 1 ELSE NULL END ) countProcessed
FROM CM AS a
LEFT JOIN CM_PARAM AS b ON a.id_cm = b.id_cm
WHERE a.id_cm = ?1
GROUP BY a.id_cm, a.type

您必须将关联的反面添加到 Cm :

@OneToMany(mappedBy = "cm")
private Set<CmParam> params;

(否则，您将需要一个从 RIGHT JOIN 到 CmParam 的 Cm ，这是 Hibernate 不支持的)

Criteria 查询将变为:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<? extends Object[]> cq = cb.createQuery(new Object[0].getClass());

Root<Cm> a = cq.from(Cm.class);
Join<Cm, CmParam> b = a.join("params", JoinType.LEFT);
cq.where(cb.equal(a.get("idCm"), cb.parameter(Long.class, "idCm")));
cq.groupBy(a.get("idCm"), a.get("type"));
cq.multiselect(
        a.get("idCm"),
        a.get("type"),
        cb.count(cb.selectCase()
                .when(cb.equal(b.get("status"), "PENDING"), 1L)
                .otherwise(cb.nullLiteral(Long.class))),
        cb.count(cb.selectCase()
                .when(cb.equal(b.get("status"), "FAILED"), 1L)
                .otherwise(cb.nullLiteral(Long.class))),
        cb.count(cb.selectCase()
                .when(cb.equal(b.get("status"), "PROCESSED"), 1L)
                .otherwise(cb.nullLiteral(Long.class))));

请注意，结果的类型为 Object[] 。如果您想对 transient 字段使用当前方法，最简单的方法是将适当的构造函数添加到 Cm并使用 cb.construct()方法:

cq.select(cb.construct(Cm.class, a.get("idCm"), a.get("type"), ...))

请注意:

• 如果您不想添加 params字段至 Cm ，但你可以使用 INNER JOIN ，你可以使用Root<CmParam> b = cq.from(CmParam.class)和Join<CmParam, Cm> a = b.join("cm")反而。
• 如果在实际查询中您从Cm中选择更多属性不仅仅是cmId和status ，您可能需要将它们全部列出在 groupBy 中还有