Querying collections to match a given set of values

yohan · **Joined:** Fri Jan 25, 2008 12:44 pm **Posts:** 7

Hibernate version: 3.2.5

Hi, I am new to Hibernate and I'm in confusion about how to write a HQL for a situation as follows.

I have a class called Owner, which has a collection of Strings as a java.util.Set, exposed as a property called tags.

The following HQL Query works when I need to find the Owener objects which has a given tag in their tags collection :

from Owner o where ? in elements(o.tags)

But, now I need to match the tags collection against a set of values (like an array of Strings), rather than just a single value as before.

I would like to know how to write a HQL query for this kind of situation.

Can I use Criteria API for this? If so, how?

Thanks for the help !

farzad · **Posted:** Fri Jan 25, 2008 1:12 pm

Can this possibly answer your question?

http://forum.hibernate.org/viewtopic.php?t=982248&highlight=

Farzad-

yohan · **Joined:** Fri Jan 25, 2008 12:44 pm **Posts:** 7

Thanks for the reply. I tried that but still, it doesn't solve my issue. In my issue, I want to select the Owner object which has maching tags (String) for all of the Strings which I specify as parameters.

For example, If I specify A,B,C as parameters, it should give me the set of Owner objects which has A, B and C in the tags collection (all of them).

Quote:

select distinct o from Owner o inner join o.tags t where t in ('A', 'B', C)

By modifying your query, I get the owner objects which has either A, B or C, where as I expect owner objects with all A, B and C in tags.

Can you suggest me a change in the query which you have given which will allow me to get it done?

Thanks.

P.S : Also, the number of parameters to match will change in my problem. For example, it could be 2 one time and it could be 3 next time. So I think Criteria API would suite better, but I have no idea how to use it. Is there anyway I could use it here?

yohan · **Joined:** Fri Jan 25, 2008 12:44 pm **Posts:** 7

Ok, I figured out a HQL for my problem, with the help of farzad's query.

Code:

select distinct o from Owner o 
where 
   o.id in (select o.id from Owner o inner join o.tags t where t = 'A') 
and 
   o.id in (select o.id from Owner o inner join o.tags t where t = 'B')

Since the number of criteria changes, I have to dynamically create the query at runtime (ex, to match against A, B and C). What I currently do is using StringBuilder to build the HQL by concatenating strings. I know that this is not the optimal solution.

I think Criteria API should be able to help. Any ideas?

farzad · **Posted:** Sun Jan 27, 2008 9:50 pm

I think I have a better solution for you.

Code:

        em.getTransaction().begin();

        Query q = em.createQuery("select p from Person p inner join p.areas area with area.name in (:area_codes) where size(p.areas) = :area_size group by p.name, p.id having count(area) = :area_size");
        List<String> area_codes = new ArrayList<String>(4);

        area_codes.add("A");
        area_codes.add("B");
        area_codes.add("C");

        q.setParameter("area_codes", area_codes);
        q.setParameter("area_size", area_codes.size());

        List<Person> people = q.getResultList();

        for (Person p : people)
            System.out.println(">>> name = " + p.getName());

        em.getTransaction().commit();

What this query does is that it makes sure a person has all the area codes in area_codes and nothing else. If you are interested in persons that have all the area_codes specified in area_codes and may be some more area codes then you will have to remove the size(p.areas) part:

Code:

select p from Person p inner join p.areas area with area.name in (:area_codes) group by p.name, p.id having count(area) = :area_size

One last tip, my person object has only id and name attributes and that's what I have to put in the group by part. However, your object might have a long list of attributes and it will be a pain to put all of them in the group by clause. In addition, someone might go and add more attributes in the class and forget to update the query accordingly. In this case, I suggest you only return id of your objects and have hibernate retrieve them one by one. This might have an impact on your performance but you could get around this kind of performance issues by using a second level cache and of course using it properly.

Farzad-