Too many hibernation collection filters

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 XML mapping:

  <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 connects the words-parents with the words-children.

I am trying to implement element filtering when data is retrieved from a database. The resulting set of objects should contain only elements of a certain type.

I tried different things:

• Using filtering in matching (sorry for the absence of parentheses)

 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 retrieves the data, I turned on the filter and set the parameter. According to the logs, hibernate seems to ignore this particular filter, as it does not have a condition as a result of the SQL query.

• Use of additional conditions in the 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 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 is another one that retrieves all the elements

  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

There are several points from your selected code snippet:

• For many-to-many relationships, you need 3 tables, two entity tables, and one join table. But since you have the entity of the same name, Word, I think that this table structure and comparisons seem wonderful.
• Try using HQL and specify "LEFT JOIN FETCH" to indicate which associations you need to get in the original sql SELECT.

See this link for many-to-many relationships, but they used the query criteria.

Request ManyToMany relationship with hibernation criteria