Hibernate many-to-many collection filtering

Question

I have the following POJO with a Set inside:

class Word {
    private Long id;
    private String word;
    private int type = WordListFactory.TYPE_DEFAULT;

    private Set<Word> refs = new HashSet<Word>();
...
}

Here's the mapping XML:

  <class name="kw.word.Word" table="word">
        <id name="id" column="id"  unsaved-value="null">
            <generator class="native"/>
        </id>

        <property name="word"
                  unique="true"
                  not-null="true"/>
        <property name="type"/>

        <set name="refs"
             table="word_key"
             cascade="save-update">

            <key column="word_id"/>
            <many-to-many class="kw.word.Word" 
                   column="word_ref_id"
                   fetch="join">                         
            </many-to-many>                            
        </set>


    </class>

There are two tables: word and *word_key*. The latter links word-parents to word-children.

I'm trying to implement set items filtering when the data is fetched from DB. The resulting object set must contain only items with a specific type.

I tried various things:

• Using filtering in mapping like (sorry for lack of brackets)

    
      many-to-many class="kw.word.Word"
                  column="word_ref_id"
                  fetch="join">
         filter name="word_type" condition="type=:type"          
     many-to-many

In the code that fetches data I enabled the filter and set the parameter. According to logs hibernate seems to ignore this particular filter as it there's no condition in resulting SQL query.

• Using additional condition in Criteria

  Word result = null;

    session.beginTransaction();

    Criteria crit = session.createCriteria(Word.class);       

    crit.add(Restrictions.like("word", key))
         .createAlias("refs", "r")
         .add(Restrictions.eq("r.type", getType()));//added alias and restriction for type

     List list = crit.list();

     if(!list.isEmpty())
         result = list.get(0);   

     session.getTransaction().commit();

now the resulting SQL seems to be OK

   select
        this_.id as id0_1_,
        this_.word as word0_1_,
        this_.type as type0_1_,
        refs3_.word_id as word1_,
        r1_.id as word2_,
        r1_.id as id0_0_,
        r1_.word as word0_0_,
        r1_.type as type0_0_ 
    from
        word this_ 
    inner join
        word_key refs3_ 
            on this_.id=refs3_.word_id 
    inner join
        word r1_ 
            on refs3_.word_ref_id=r1_.id 
    where
        this_.word like ? 
        and r1_.type=?

but right after this query there's another one that fetches all the items

 select
        refs0_.word_id as word1_1_,
        refs0_.word_ref_id as word2_1_,
        word1_.id as id0_0_,
        word1_.word as word0_0_,
        word1_.type as type0_0_ 
    from
        word_key refs0_ 
    left outer join
        word word1_ 
            on refs0_.word_ref_id=word1_.id 
    where
        refs0_.word_id=?

Maybe I'm doing something wrong?

Answer 1

From your given code snippet few points:

• In case of many-to-many relationship you require 3 table , two entity tables and one join table. But as you are having same entity -Word , i think the given table structure and mappings seems fine.
• Try to use HQL and specify 'LEFT JOIN FETCH' to specify which associations you need to be retrieved in the initial sql SELECT.

See this link related to many-to-many relationship,but they used criteria query.

http://stackoverflow.com/questions/264339/querying-manytomany-relationship-with-hibernate-criteria