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

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

我当前的解决方案是在本机查询中运行，但我想以一种基于对象的方式进行查询。
我尝试通过使用特定查询来按条件进行操作，只是选择所有状态，然后手动进行计数。但是使用这种方法，我要调用两个查询：一个在我的主表中获取详细信息，另一个在ID与主表相同的情况下选择所有状态。
有没有更有效的方法可以做到这一点？

这是我的本机查询（简体）：

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
}

使用本机查询方法，我可以在Cm实体中添加临时字段：

@Transient
private Long countPending;

@Transient
private Long countFailed;

@Transient
private Long countProcessed;

如何使用条件api进行操作，如果可能的话，只需进行一次交易即可。

预期的输出将是这样的：

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

最佳答案

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

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不支持的）

然后条件查询变为：

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[]。如果要对瞬态字段使用当前方法，最简单的方法是将适当的构造函数添加到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中列出所有属性

关于java - 使用JPA标准选择另一个表中每种状态的出现次数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55322569/