Hibernate query for multiple items in a collection

Question

I have a data model that looks something like this:

public class Item {
    private List<ItemAttribute> attributes;
    // other stuff
}

public class ItemAttribute {
    private String name;
    private String value;
}

(this obviously simplifies away a lot of the extraneous stuff)

What I want to do is create a query to ask for all Items with one OR MORE particular attributes, ideally joined with arbitrary ANDs and ORs. Right now I'm keeping it simple and just trying to implement the AND case. In pseudo-SQL (or pseudo-HQL if you would), it would be something like:

select all items
where attributes contains(ItemAttribute(name="foo1", value="bar1"))
AND attributes contains(ItemAttribute(name="foo2", value="bar2"))

The examples in the Hibernate docs didn't seem to address this particular use case, but it seems like a fairly common one. The disjunction case would also be useful, especially so I could specify a list of possible values, i.e.

where attributes contains(ItemAttribute(name="foo", value="bar1"))
OR attributes contains(ItemAttribute(name="foo", value="bar2"))
-- etc.

Here's an example that works OK for a single attribute:

return getSession().createCriteria(Item.class)
        .createAlias("itemAttributes", "ia")
        .add(Restrictions.conjunction()
            .add(Restrictions.eq("ia.name", "foo"))
            .add(Restrictions.eq("ia.attributeValue", "bar")))
        .list();

Learning how to do this would go a long ways towards expanding my understanding of Hibernate's potential. :)

Answer 1

SELECT item FROM Item item JOIN item.attributes attr 
    WHERE attr IN (:attrList) GROUP BY item

and then in the Java code:

List<ItemAttribute> attrList = new ArrayList<ItemAttribute>();
attrList.add(..); // add as many attributes as needed
...// create a Query with the above string
query.setParameter("attrList", attrList);

Answer 2

Why wouldn't the following work?

return getSession().createCriteria(Item.class)
    .createAlias("itemAttributes", "ia")
    .add(Restrictions.or()
        .add(Restrictions.conjunction()
            .add(Restrictions.eq("ia.name", "foo1"))
            .add(Restrictions.eq("ia.attributeValue", "bar1")))
        .add(Restrictions.conjunction()
            .add(Restrictions.eq("ia.name", "foo2"))
            .add(Restrictions.eq("ia.attributeValue", "bar2"))))
    .list();

That would be (name=foo1 && attributeValue=bar1) OR (name=foo2 && attributeValue=bar2)

Answer 3

I didn't test it, but this is how I should try to solve your problem if I would have to:

Map<String,String> map1 = new TreeMap<String,String>();  
map1.put("ia.name","foo1");  
map1.put("ia.value","bar1");  
Map<String,String> map2 = new TreeMap<String,String>();  
map2.put("ia.name","foo2");  
map2.put("ia.value","bar2");  
return getSession().createCriteria(Item.class)
.createAlias("itemAttributes", "ia")
.add(Restrictions.and()
     .add(Restrictions.allEq(map1))
     .add(Restrictions.allEq(map2))
)
.list();

Please, let me know if it worked. I think the same should work with or()...

Answer 4

Could you use aliasing to do this?

Criteria itemCriteria = session.createCriteria(Item.class);
itemCriteria.createAlias("itemAttributes", "ia1")
            .createAlias("itemAttributes", "ia2")
            .add(Restrictions.eq("ia1.name", "foo1"))
            .add(Restrictions.eq("ia1.attributeValue", "bar1")))
            .add(Restrictions.eq("ia2.name", "foo2"))
            .add(Restrictions.eq("ia2.attributeValue", "bar2")))

Not sure how hibernate handles joining on the same property twice explicitly like that, maybe worth trying?