Hibernate multiple subqueries

Question

I want to use Criteria for this query :

SELECT * FROM table1 WHERE field1 in (
  SELECT field2 FROM table2 WHERE field3 in (
    SELECT field4 FROM table3 where field5 = %VAR%
  )
)

After reading Hibernate subquery detachedCriteria, I've tried this :

public List<Table1> findByParam(String param) {
  logger.info("finding all Table1 where param is '{}'", param);
  Session session = this.getSessionFactory().getCurrentSession();
  try {
    session.beginTransaction();
    DetachedCriteria field4List = DetachedCriteria.forClass(Table3.class, "t3").setProjection(Projections.distinct(Projections.property("t3.id.field4"))).add(Property.forName("t3.id.field5").eq(param));

    DetachedCriteria field2List = DetachedCriteria.forClass(Table2.class, "t2").setProjection(Projections.distinct(Projections.property("t2.id.field2"))).add(Property.forName("t2.id.field3").in(field4List));

    // NullpointerException on this line
    List<Table1> results = session.createCriteria(Table1.class, "t1").add(Property.forName("t1.id.field1").in(field2List)).list();

    logger.info("find all successful, result size: {}", results.size());
    session.getTransaction().commit();
    return results;
  } catch (RuntimeException re) {
    logger.error("find all failed", re);
    throw re;
  } finally {
    try {
      if (session.isOpen()) {
        session.close();
      }
    } catch (Exception e) {
      logger.warn("Something wrong with session", e);
    }
  }
}

But result is a NullPointerEXception on executing query.

public List<Table1> findByParam(String param) {
  logger.info("finding all Table1 where param is '{}'", param);
  Session session = this.getSessionFactory().getCurrentSession();
  try {
    session.beginTransaction();

    DetachedCriteria field2List = DetachedCriteria.forClass(Table2.class, "t2").setProjection(Projections.distinct(Projections.property("t2.id.field2")));

    // No Exception for the example. Don't know why ?
    List<Table1> results = session.createCriteria(Table1.class, "t1").add(Property.forName("t1.id.field1").in(field2List)).list();

    logger.info("find all successful, result size: {}", results.size());
    session.getTransaction().commit();
    return results;
  } catch (RuntimeException re) {
    logger.error("find all failed", re);
    throw re;
  } finally {
    try {
      if (session.isOpen()) {
        session.close();
      }
    } catch (Exception e) {
      logger.warn("Something wrong with session", e);
    }
  }
}

No NullPointerEXception on executing this above query.

Could someone help me fix the first ? And tell me why the second one does not run to an Exception ?

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

After reading Hibernate subquery detachedCriteria, I've tried this :

But in this example used Subqueries.propertyIn("myPersistedObjectId", exampleSubquery), however in your query Subqueries key isn't exist at all. Please check your example and using Subqueries.propertyIn(...) , your code should be something like this (I didn't check that code):

DetachedCriteria field2List = DetachedCriteria.forClass(Table2.class, "t2").
  setProjection(Projections.distinct(Projections.property("t2.id.field2"))).
  add(Subqueries.propertyIn("t2.id.field3",field4List));

List<Table1> results = session.createCriteria(Table1.class, "t1").
  add(Subqueries.propertyIn("t1.id.field1",field2List)).list();

edited May 23 '17 at 10:27

Community

1
1

answered Dec 17 '15 at 11:51

Slava Vedenin

58,326
13
40
59

I actually forgot to use `Subqueries` but when I run under a single subquery, I do not have the error. Can you say why? – Manu Dec 21 '15 at 09:37
1

It's look like as you previuos query work as SELECT * FROM table1, table2, table3 WHERE field1 = field3 and field5 = %VAR% or something similar. It isn't error, but you can't get such result as expected. – Slava Vedenin Dec 21 '15 at 09:43

score 1 · Answer 2 · answered Dec 17 '15 at 12:49

Not knowing which fields Table1, Table2 and Table3 are having, shouldn't the queries be looking like this:

DetachedCriteria field4List = DetachedCriteria.forClass(Table3.class, "t3").setProjection(Projections.distinct(Projections.property("t3.field4"))).add(Property.forName("t3.field5").eq(param));

DetachedCriteria field2List = DetachedCriteria.forClass(Table2.class, "t2").setProjection(Projections.distinct(Projections.property("t2.field2"))).add(Property.forName("t2.field3").in(field4List));

// NullpointerException on this line
List<Table1> results = session.createCriteria(Table1.class, "t1").add(Property.forName("t1.field1").in(field2List)).list();

I have replaced all t*.id.field* occurrences with t*.field*

If not, posting these 3 entities would help.

The "id" thing in t*.id.field* is due to reverse engineering... so don't care about this "id". — Manu, Dec 17 '15 at 13:57

Hibernate multiple subqueries

2 Answers2