many-to-many associations and criteria queries

akz · **Joined:** Fri Mar 16, 2007 6:04 am **Posts:** 14

Hello,

I have a Member and a Keyword pojo.
Things are like this:

Code:

class Keyword  {
long id;
String title;
}

and

Code:

class Member {
long id;
String name;
Set<Keyword> keywords; 
}

The association is many-to-many, so I end up with three tables in the database. I want to build a query that will get me a list of members that have a given keyword. Here is my code:

Code:

    public List getAllByKeyword(Keyword key) {
        Criteria criteria = getSession().createCriteria(Member.class).
                createCriteria("keywords", CriteriaSpecification.INNER_JOIN ).
                add( Restrictions.eq("id", new Long(key.getId()) ) ) .
                setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );
        return criteria.list();
}

The result is always an empty list. The thing is that when I copy-paste the query produced by hibernate directly to my database client and execute it, I get the desired results.
What am I doing wrong here??

Thanks,
akz

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You've put the result transformer on the wrong criteria. The easiest way to fix it is to change the nested createCriteria to createAlias.

However, if you've got a many-to-many, why don't you use key.getMembers()? It's no harder to implement a bidirectional many-to-many. That would save you this sort of hassle.