java - 如何将 from-subquery 转换为 Hibernate Criteria 语句

Question

型号

我有以下包含三个类的模型。 A 类包含 1 个 B，并且可以包含 0 个或多个 C。 B 类和 C 类都包含我想在 A 范围内求和的金额。

class TableA {
    @Id
    Id id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "table_b_id", nullable = false)
    TableB tableB;

    @OneToMany(mappedBy = "tableA", fetch = FetchType.LAZY)
    Set<TableC> tableCs;
}

class TableB {
    @Id
    Id id;

    @Column(name = "amount_b")
    Long amountB;
}

class TableC {
    @Id
    Id id;

    @Column(name = "amount_c", nullable = false)
    Long amountC;

    @JoinColumn(name = "table_a_id")
    TableA tableA;
}

问题

我的任务是计算两个值“sum(c.amount) + b.amount”的组成，并查看它们是否在定义的最小/最大间隔内，例如[0, 100]。结果是符合此条件的 A 列表。我被要求仅在 hibernate 标准中执行此操作。

为此，我构建了一个 SQL 语句来表达此要求:

select compositeSum from 
(SELECT sum(tableC.amountC) + tableB.amountB as 'compositeSum'
FROM tableA A
left join tableB B on A.b_id = B.id
left join tableC C on C.a_id = A.id) SUBQUERY
where compositeSum between 0 and 100;

尝试

由于有一个聚合函数“sum()”和一个算术运算“+”，我尝试使用子查询来解决这个问题。我得到的最接近的是以下解决方案:

Long min = 0l;
Long max = 100l;

DetachedCriteria subquery = DetachedCriteria.forClass(TableA.class, "inner")
                .createAlias("tableC", "tableC", JoinType.LEFT_OUTER_JOIN)
                .createAlias("tableB", "tableB")
                .setProjection(Projections.sqlProjection("coalesce(sum(amountC), 0) + amountB", new String[] {"compositeSum"}, new Type[] {StandardBasicTypes.LONG}));

Criteria criteria = session().createCriteria(TableA.class, "outer")
                .add(Subqueries.ge(max, subquery))
                .add(Subqueries.le(min, subquery));

问题

问题在于这会转换为 where 子句中使用的子查询。

select * from A this_ 
where ? >= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join table_B tableB2_ on tableA_.table_b_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id) 
and ? <= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join tableB tableB2_ on tableA_.table_B_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id)

我想构建一个可以馈送到主查询的 from 子句的子查询。相反，我得到了一个输入到 where 子句的子查询。这不是我想要的，并且会导致奇怪的结果。

有谁知道是否可以将子查询提供给 Hibernate Criteria 中的 from 子句？非常感谢!

解决方案

(已更新)

显然，在指向外部查询的内部查询中添加一个额外的过滤器解决了这个问题。要从子查询引用父查询实体，请使用魔术关键字“this”。

Long min = 0l;
Long max = 100l;

DetachedCriteria subquery = DetachedCriteria.forClass(TableA.class, "inner")
                .createAlias("tableC", "tableC", JoinType.LEFT_OUTER_JOIN)
                .createAlias("tableB", "tableB")
                .setProjection(Projections.sqlProjection("coalesce(sum(amountC), 0) + amountB", new String[] {"compositeSum"}, new Type[] {StandardBasicTypes.LONG}))
                .add(Restrictions.eqProperty("id", "this.id"));

Criteria criteria = session().createCriteria(TableA.class, "outer")
                .setProjection(Projections.property("id"))
                .add(Subqueries.ge(max, subquery))
                .add(Subqueries.le(min, subquery));

添加附加过滤器背后的逻辑是最小/最大是标量，并且您希望子查询返回另一个标量而不是列表来进行比较，否则会导致不稳定的行为。

这将成为一个相关子查询:

select * from A this_ 
where ? >= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join table_B tableB2_ on tableA_.table_b_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id 
where tableA_.id = this_.id) 
and ? <= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join tableB tableB2_ on tableA_.table_B_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id
where tableA_.id = this_.id)

此解决方案是一种不同类型的子查询，并且可能会慢一些，因为您执行子查询两次，但它可以完成任务。

having 子句

请注意，SQL 解决方案也可以使用having-子句。下面，您可以找到上述内容到having-clause的转换。唯一的问题是 Hibernate Criteria 不支持having-clauses。

SELECT coalesce(sum(amountC),0) + amountB as 'compositeSum'
FROM table_A tableA
left join table_B tableB on tableA.table_b_id = tableB.id
left join table_C tableC on tableC.table_a_id = tableA.id

group by m.id
having compositeSum between 0 and 100

Answer 1

您不需要子查询。简化您的查询，如下所示:

SELECT sum(tableC.amountC) + tableB.amountB as 'compositeSum'
FROM tableA A
left join tableB B on A.b_id = B.id
left join tableC C on C.a_id = A.id
where (sum(tableC.amountC) + tableB.amountB) between 0 and 100;

现在相应地更改您的 hibernate 代码。