java - 如何将带有子查询的 SQL 查询转换为 JPA 条件生成器

Question

我正在尝试将此 SQL 查询转换为使用 JPA 条件生成器。答案必须是 double 或 float 。

SELECT CAST((COUNT(m.id) -
(SELECT COUNT(s.id) 
  FROM mobile_unit as s
  left JOIN incident as i ON s.incidentId=i.id
  JOIN organizational_unit as o ON s.organizationalUnitId=o.id
  WHERE (s.organizationalUnitId = 1 AND s.incidentId IS NULL))) AS float) 
/COUNT(m.id) 
FROM mobile_unit as m 
JOIN organizational_unit as o ON m.organizationalUnitId=o.id
WHERE m.organizationalUnitId = 1

Answer 1

在不对此进行测试的情况下，这是如何完成的粗略草图。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Float> cq = cb.createQuery(Float.class);

Root<MobileUnit> root = cq.from(MobileUnit.class);
// Join is actually unnecessary
root.join("organizationalUnit", JoinType.INNER);
cq.where(cb.equal(
    root.get("organizationalUnitId"),
    1
));

Subquery<Long> subquery = cq.subquery(Long.class);
Root<MobileUnit> subRoot = subquery.from(MobileUnit.class);
// Actually these joins are unnecessary, but you requested them..
subRoot.join("incident", JoinType.LEFT);
subRoot.join("organizationalUnit", JoinType.INNER);

subquery.select(cb.count(subRoot.get("id")));
subquery.where(cb.and(
    subRoot.get("organizationalUnitId").eq(cb.literal(1)),
    subRoot.get("incidentId").isNull()
));

cq.select(cb.quot(
    cb.diff(
        cb.count(root.get("id")),
        subquery
    ).as(Float.class),
    cb.count(root.get("id"))
));