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. :)

The answer here works: http://stackoverflow.com/questions/4834372/hibernate-criteria-on-collection-values — Taylor, Jan 14 '12 at 18:16

score 1 · Answer 1 · answered Jul 25 '10 at 20:10

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?

I get a org.hibernate.QueryException: duplicate association path: itemAttributes — Taylor, Jan 14 '12 at 17:32

score 0 · Answer 2 · answered Apr 15 '13 at 18:13

0

Use LEFT_OUTER_JOIN to prevent "WHERE x = 1 AND x = 2" kind of issue

CreateAlias("itemAttributes", "ia", JoinType.LEFT_OUTER_JOIN)

answered Apr 15 '13 at 18:13

yunpeng li

21
3

score 0 · Answer 3 · answered Jun 08 '10 at 20:51

0

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);

answered Jun 08 '10 at 20:51

Bozho

588,226
146
1,060
1,140

This looks like it address the OR case (i.e. the Item has at least one ItemAttribute that we specify in the attrList), but what about the AND case? Would it be something like WHERE attr1 in item.attributes AND attr2 in item.attributes etc. ? Does that even make sense to Hibernate? – aarestad Jun 08 '10 at 20:57
Also, I appear to be getting a stack trace when trying to do the above. The root error: Caused by: java.lang.IllegalArgumentException: Can not set java.lang.Integer field com.mycompany.domain.ImmutableDomainEntity.id to java.util.ArrayList – aarestad Jun 08 '10 at 21:17
You are getting an error because you're trying to alias a set (ItemAttributes) to a single entity (attr). You can only use the IN clause with a single entity. A better (working) way would be to invert the query: "SELECT a.item from ItemAttribute a WHERE a.name IN (:attrlist)" - no explicit JOIN is required if you have mapped the relationships properly. – Matt Brock Jun 16 '10 at 19:57
That would seem to get me all the Items with the listed attribute names, but now how do I specify the specific values for those attributes? – aarestad Jun 18 '10 at 16:26
1

@aarestad `AND attr.value IN (:attrValues)` – Bozho Jun 18 '10 at 16:41

score 0 · Answer 4 · answered Jun 11 '10 at 16:44

0

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)

answered Jun 11 '10 at 16:44

Andy

13,916
1
36
78

This might work for the disjunction, but the conjunction version where I would use Restrictions.and() didn't work. – aarestad Jun 18 '10 at 16:26
Yes, because that would be saying `WHERE x = 1 AND x = 2` essentially, which won't return any results. I can't think of a way to do the conjunction case in raw SQL. You may have to load the disjunction case and then refine that using application logic. – Andy Jun 18 '10 at 16:46

score 0 · Answer 5 · answered Jul 15 '10 at 15:48

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()...

Hibernate query for multiple items in a collection

5 Answers5

Linked