Multiple results when using eager fetch in Criteria query

castortroy · **Joined:** Tue Jan 31, 2006 2:30 am **Posts:** 12

Hibernate version:
Hibernate 3.1.1
Hibernate Annotations 3.1 beta8

I have defined 2 classes using annotations as follows. Note that the contactNumbers member is eager fetched.

Code:

@Entity
public class ContactNumber
{
    @NotNull
    String number;

    String description;
}


@Entity
public class Person
{
...

   @OneToMany(fetch = FetchType.EAGER)
   @Cascade( { CascadeType.ALL, CascadeType.DELETE_ORPHAN })
   @JoinTable(name = "person_contactnumber", joinColumns = @JoinColumn(name = "person_id"), inverseJoinColumns = @JoinColumn(name = "contactNumber_id"))
   private List<ContactNumber>   contactNumbers   = new ArrayList<ContactNumber>();

...
}

I input the following test data into the database. Note that i enter 2 contactNumbers for the Person object created.

Code:

Person person = new Person();
List<ContactNumber> list= new ArrayList<ContactNumber>(2);

ContactNumber number = new ContactNumber();
number.setNumber("9111111");
number.setDescription("Work");
list.add(number);

number= new ContactNumber();
number.setNumber("1234567");
number.setDescription("Test");
list.add(number);

person.setContactNumbers(list);

session.save(person);

So the database now has 1 Person entry and 2 ContactNumbers for that Person.

And then when I use Criteria to query all the Persons in the database

Code:

session.createCriteria(Person.class).list();

the list contains 2 items referring to the same Person object I created (expected is that there would just be 1 Person in the list). I don't understand why the list would contain 2 items when the database only has 1 Person stored in it.

During my investigations, I found the following scenarios.
1. When the contactNumbers member of the Person class is NOT eager fetched, the list returns the correct number of Person instances. (As expected)

2. When the contactNumbers member of the Person class is eager fetched, the list returns multiple number of Persons based on the number of ContactNumbers associated to it. This probably explains why I get 2 Person items in the list since there are 2 contactNumbers created for that Person.

I don't understand why scenario 2 behaves this way. The number of Persons in the list generated by Criteria depends on the number of the eager fetched ContactNumbers. Can someone explain this behavior? Or is this a bug?

Thanks!

jbchurn · **Posted:** Tue Jun 13, 2006 3:16 am

Hi,

It's not a bug, I was having this issue too previously, but now I understand already.

When you DON'T USE eager fetch, then when you select from Person, only the person records will be retrieved. So if you have 1 record for person, then only 1 will be retrieved.

However, if you USE eager fetch, hibernate will generate sql that join the two tables together, eventhough you just want to get Person object only. So since you have 2 records in child table, normally if you select directly from database, you also get 2 records with the Person attribute repeating right? Samething happen to list() where you will get the 2 records as well.

If you want to distinct it, you can do something like below.

Code:

        Criteria c = _session.createCriteria(Person.class)
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .list();

Also, try to see the difference of the sql when you use and not use EAGER FETCH.

Hope it helps.

Don't forget to rate if you find this helpful. :)

castortroy · **Joined:** Tue Jan 31, 2006 2:30 am **Posts:** 12

Hi,

Thanks, that solution worked for the problem i described. But i found another problem where the collections of the root entity get multiplied by the count of another collection. I'll post this in another thread as it is a different but related problem.
http://forum.hibernate.org/viewtopic.php?t=961099