如何将FROM-子查询转换为Hibernate准则语句

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;
}

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;

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));

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)

有人知道在Hibernate条件中是否可以向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)

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代码。