Search code examples
hibernatehql

how to combine columns from multiple subqueries in hibernate

I want to write the following query in HQL:

select v1.maxx, v2.maxx from
  (select max(id) maxx from mytable where my_column is not null ) v1,
  (select max(id) maxx from mytable where my_column is null) v2;

I've got the following so far:

  DetachedCriteria d1=DetachedCriteria.forClass(MyTable.class, "d1")
      .setProjection(Projections.max("id"))
      .add(Property.forName("myColumn").isNull();

  DetachedCriteria d2=DetachedCriteria.forClass(MyTable.class, "d2")
      .setProjection(Projections.max("id"))
      .add(Property.forName("myColumn").isNotNull();

But I'm having a hard time combining them into one criteria object.

Solution

  • Could't find how to include multiple subqueries in SELECT. So I rewrote the query as follows and it works with HQL:

    select max(k.id), max(l.id) from myTable k, myTable l
  where k.myColumn is not null and l.myColumn is null;