Complex hibernate query using criteria API

Question

I am trying to write a complex query using criteria API. I could use some help with this.

I have 3 classes, Assets, AssetComponent and Tasks. The Task class has a 1 to M relationship with AssetComponent and AssetComponent has 1 to M relationship with Asset. I need to find all Assets (it should be unique list) that have some tasks associated with them (the task has a component and the component knows a assets it is connected).

This is what I have so far

public List<Asset> retrieveTask(Project project, boolean assigned) {

  DetachedCriteria subquery = DetachedCriteria.forClass(Task.class);

  subquery.add(Restrictions.eq("project", project));
  if (assigned) {
     subquery.add(Restrictions.isNotNull("assignedTo"));
  } else {
     subquery.add(Restrictions.isNull("assignedTo"));
  }

  subquery = subquery.createCriteria("component");
  subquery.add(Restrictions.isNotNull("asset"));

  Criteria criteria = super.createCriteria();
  criteria.add(Subqueries.in("id", subquery));

  return criteria.list();
}

The deataced query should return all of the tasks that have asset assigned to them and are a part of the give project. Now I need to see what are the unique assets for all of those tasks.

Thanks for helping me with this.

Cheers

are you required use a subquery with the super.createCritera(), are you responsible for the code behind super.createCriteria(), and can you show us the code behind that call? — John Ericksen
– John Ericksen, Commented May 24, 2012 at 17:33

John Ericksen · Accepted Answer · 2012-05-24 17:46:26Z

1

A bit of a shot in the dark, but how does this work for you:

public List<Asset> retrieveTask(Project project, boolean assigned) {

    DetachedCriteria subquery = DetachedCriteria.forClass(Task.class);

    subquery.add(Restrictions.eq("project", project));
    DetachedCriteria assetCriteria = subquery
        .createCriteria("component")
        .createCriteria("asset").setProjection(Projections.groupProperty("id"));

    Criteria criteria = super.createCriteria();
    if(assigned) {
        criteria.add(Subqueries.propertyIn("id", subquery));
    }
    else {
        criteria.add(Subqueries.propertyNotIn("id", subquery));
    }
    return criteria.list();
}

answered May 24, 2012 at 17:46

John Ericksen

11.2k6 gold badges48 silver badges75 bronze badges

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

4 Comments

Boris Horvat Over a year ago

This works perfectly. The thing that confused me was that DetachedCriteria assetCriteria that you used is never used in the code, but its subquery is. Thanks for help and fast reply. One more thing if how do I modify this code if I just want to return the number that represents the size of the list (using count or something like that)

Boris Horvat Over a year ago

I have changed the last part of the return into return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult(); is this ok?

John Ericksen Over a year ago

If you only want a single result back. I believe Hibernate will throw an exception if you have <> 1 result. If you are looking for unique assets you can also use a Unique Results Tranformer: criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Boris Horvat Over a year ago

yea I just want to get the single number back from the query and that is the size of the entities that match my query

Collectives™ on Stack Overflow

Complex hibernate query using criteria API

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related