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 ?

Community · Accepted Answer · 2017-05-23 10:27:20Z

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, 2017 at 10:27

CommunityBot

11 silver badge

answered Dec 17, 2015 at 11:51

Slava Vedenin

60.4k13 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manu Over a year ago

I actually forgot to use Subqueries but when I run under a single subquery, I do not have the error. Can you say why?

Slava Vedenin Over a year ago

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.

Madhusudana Reddy Sunnapu · Accepted Answer · 2015-12-17 12:49:16Z

1

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.

answered Dec 17, 2015 at 12:49

Madhusudana Reddy Sunnapu

8,2772 gold badges21 silver badges29 bronze badges

1 Comment

Manu Over a year ago

The "id" thing in t*.id.field* is due to reverse engineering... so don't care about this "id".

Collectives™ on Stack Overflow

Hibernate multiple subqueries

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related