one-to-many + Criteria-Api

gizeh · **Joined:** Wed Oct 17, 2007 10:31 am **Posts:** 3

Hi!
First I apologize for my bad English.
I'm new to hibernate and have a problem.

Hibernate version:3.2.0
DB: MySQL 5.0.41

I have two tables: Person, Memos
A Person can have zero, one or more memos.

<class name="myPackage.Person" table="Person">
<id name="pid" type="int">
<column name="pid" />
<generator class="assigned" />
</id>
...
<set name="memos" cascade="all" lazy="true">
<key column="mpid"/>
<one-to-many class="myPackage.Memos"/>
</set>
</class>

<class name="myPackage.Memos" table="Memos">
<property name="mpid" type="int">
<column name="mpid" />
</property>
<property name="memkz" type="string">
<column name="memkz" length="7">
</column>
</property>

mpid holds the person-id

Now I want to fetch all persons with pid<10 + all persons with memo.memkz="test"

I'm using the DetachedCriteria-Api:
peronCriteria.createAlias("memos","mem").add(Restrictions.disjunction().add(Restrictions.le("pid", 10)).add(Restrictions.eq("mem.memkz", "test")));

-> now i get all persons with memo.memkz="test" and all persons with pid<10, who have a memo. All persons with pid<10, who have no memo-entry, are missing in the result.

Can somebody help me, please!
(A HQL-query would also be useful.)

Thanks in advance,
gizeh

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

Set your mem stype to outer join, and make your memkz comparison be "or null". For the "or null" but, just use another disjunction. For the outer join, use peronCriteria.setFetchMode("memos", FetchMode.JOIN).

The HQL query can use a left join, so it is

Code:

select p from myPackage.Person p
left join p.memos m
where p.id < :maxid
and (m.memkz is null or m.memkz = :memkz)

Cameron McKenzie · **Posted:** Wed May 21, 2008 10:10 am

Satyadas is right, you need to free yourself from the chains of query languages, and start looking at your problems from an object oriented standpoint, and that's what the Criteria API will do.

Look at how this simple, object oriented Java code finds all the users who have not verified their status:

Code:

  User user = new User();
    user.setVerified(false);
    Example example = Example.create(user);
    Session session = HibernateUtil.beginTransaction();
    Criteria criteria = session.createCriteria(User.class);
    criteria.add(example);
  List results = criteria.list();
    HibernateUtil.commitTransaction();
    for (int i = 0; i<results.size(); i++) {
      System.out.println(results.get(i).toString());
    }

You could do something very, very similar with your Person class, and sidestep the whole need to figure out a query in the first place.

Here's a little tutorial on how to use the Criteria API from which that code snippet originates:

http://www.hiberbook.com/HiberBookWeb/learn.jsp?tutorial=09howtousethecriteriaapi

My signature links have some tutorials on creating DAOs that can help you create reusable components that can use the Criteria API effectively.

Good luck!