How to apply restriction on both joined entities in hibernate

Question

class Category{

    @Id
    @Column(name="ID")
    private Integer id;
    private String name;

    @OneToMany
    @JoinColumn(name="CATEGORY_ID",referencedColumnName="ID")
    private Set<Item> items;

}

class Item{

    private Integer id;

    @Column(name="CATEGORY_ID")
    private Integer categoryId;

    private String name;

    private String color;
}

Here I have two entity(Category & Item) that has one to many relationship.

I tried to get items that are under category id =5 and item color =red. I would like the result to be something like

{ id:5, name:'fruits' items:[{id:3,name:"tomato",color:red},{id:55,name:"apple", color:"red"}] }

Criteria c=session.createCriteria("Category.class","cat");
c.createAlias("cat.items","it");
c.add(Restrictions.eq("cat.id,5));
c.add(Restrictions.eq("it.color","red"));

List<Category> cateList=c.list();

However I'm getting all the items in the category id =5; Wasted a lot of time.Need Help.Thanks

Category            
ID  NAME        
1   Flower      
5   Fruit       


Item            
ID  CATEGORY_ID NAME    COLOR
3   5           tomato  red
55  5           apple   red
4   5           banana  yellow
6   5           orange  orange
7   5           grape   green
1   1           rose    red
2   1           rose    yellow

Answer 1

I would take a look at Hibernate @Filter and @FilterDef annotations.

What you basically want to do is to define a filter that applies the color predicate to the entries in the collection when they're fetched so that only those applicable are in the collection.

@Entity
public class Category {
  @OneToMany
  @Filter(name = "itemsByColor")
  private Set<Item> items;
}

@Entity
@FilterDef(name = "itemsByColor",
  defaultCondition = "color = :color",
  parameters = { @ParamDef(name = "color", type = String.class ) })
public class Item {
  private String color;
}

What you need to do is enable that filter before executing your query:

session.enableFilter( "itemsByColor" )
       .setParameter( "color", "red" );

Now execute your query:

Criteria c=session.createCriteria("Category.class","cat");
c.createAlias("cat.items","it");
c.add(Restrictions.eq("cat.id,5));
c.add(Restrictions.eq("it.color","red"));
List<Category> cateList=c.list();

Your Category instances returned should now only contain Items which have a color of red.

Be aware that when a filter is enabled, it remains in effect for the duration of the session.

It's sometimes useful to enable the filter specifically for one query like this and immediately disable it after the results have been retrieved to avoid incorrect results of other queries against Category.