Sub-Select In hibernate criteria

Question

I have a sql table A with column names

name, id1, id2, val1

and a table B with column names

id1, id2, key1, key2

and this is my sql query

SELECT
  v1.id1,
  v1.id2
FROM (
       SELECT
         A.id1,
         A.id2,
         min(val1) AS x
       FROM A
         JOIN B ON A.id1 = B.id1 AND A.id2 = B.id2
       GROUP BY A.id1, A.id2
     ) AS v1
WHERE v1.x > 10

using the DetachedCriteria i was able to form the sub-query

DetachedCriteria subCriteria = DetachedCriteria.forClass(A_model.class);
subCriteria.createAlias("b", "b_model");
subCriteria.setProjection(Projections.projectionList()
                            .add(Projections.groupProperty("id1"))
.add(Projections.groupProperty("id2"))
.add(Projections.min("val1"),"x");

but i am facing a hard time in creating the outer query.

any suggestion how can i create the criteria for the above SQL?

Thanks in anticipation.

Answer 1

Sub-selects in the from clause are not supported by Hibernate for the time being. However, your query can be rewritten in a simpler and more efficient form by utilizing the HAVING clause:

SELECT A.id1, A.id2,
FROM A JOIN B ON A.id1 = B.id1 AND A.id2 = B.id2
GROUP BY A.id1, A.id2
HAVING min(val1) > 10

The above query can be easily ported to HQL or Criteria.

Answer 2

Consider creating a view for the data you need:

create view A_B_BY_ID1_AND_ID2 as
select A.id1,
       A.id2,
       min( val1 ) as x
from A
join B on A.id1 = B.id1 and A.id2 = B.id2
group by A.id1,
         A.id2

Then create a DTO to represent this data:

@Entity(table="A_B_BY_ID1_AND_ID2")
@Data //are you on board with lombok?
public class ABById1AndId2 {
    @Column
    private int x;
    @Column
    private int id1;
    @Column
    private int id2;
}

then access it like anything else:

session.createCriteria(ABById1AndId2.class).add(Restrictions.gt("x", 10)).list();