Hibernate Criteria joining unrelated children?

Question

I have three entities with one parents has two children, but I only listed two entities here.

@Entity
@Table(name = "person")
public class Person{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false) 
    private Long id;

    @Column(name = "name")
    private String name;

    @OneToMany(fetch=FetchType.EAGER)
    private Set<Phone> phones = new HashSet<Phone>();


    @OneToMany(fetch=FetchType.EAGER)
    @JoinColumn(name="person_id", referencedColumnName="id")        
    private List<Book> books = new ArrayList<Book>();

}


@Entity
@Table(name = "book")   
public class Book{
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false) 
    private Long id;

    @Column(name="person_id")
    private Long personId;

}

When I run following Hibernate criteria and noticed from SQL generated log, it is joining two children - phones and books together. I don't have any relationship between these two children. Why does SQL query generated from the Hibernate criteria try to join unrelated two children? I expected joining happening between parents and children, not between children.

        Criteria criteria = session.createCriteria(Person.class)
                .add(Restrictions.in("id", ids))
                .setFetchMode("phones", FetchMode.JOIN).setFetchMode("books", FetchMode.JOIN)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);               
        result = criteria.list();

user3487063 · Accepted Answer · 2014-09-06T14:18:07.983

1

If you want to use criteria then you need to issue two criteria for each relationship:

result = session.createCriteria(Person.class)
                .add(Restrictions.in("id", ids))
                .setFetchMode("phones", FetchMode.JOIN)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

   and

result =         session.createCriteria(Person.class)
                .add(Restrictions.in("id", ids))
                .setFetchMode("books", FetchMode.JOIN)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

else you can use hql instead of criteria and have:

 String hql=  "select new list(person.phones, person.books) from Person person";
session.createQuery(hql).list();

EDIT:

String hqlQuery = "select p from Person p left outer join p.phones as ph left outer join p.books as b";

edited Sep 06 '14 at 14:18

answered Sep 05 '14 at 19:17

user3487063

3,672
1
17
24

Thanks for your response. I was hoping to get a collection of Person object that include all it's attributes - id, name, phones and books. So, it can't be accomplished with one criteria? In my real model, I have large number of attributes in Person class. If I use hql, is there a way to get a collection of Person object that includes all it's attributes? – user3123690 Sep 05 '14 at 20:22
1

yes you can do that with simple left outer join in hql. see my edit. – user3487063 Sep 06 '14 at 14:05
left outer join works, but I have two issues. First, I need to remove duplicate based on Person Id. Second, I see that for each person id, it creates two queries - one for phones and one for books. Is there any way we can apply FetchMode.JOIN to make it one query? If I have 20,000 person records in the database, this would create 40,000 select statement. – user3123690 Sep 08 '14 at 15:49
Corrections to my previous comment. The first issue doesn't exist. I was wrong. As I mentioned previously if I have 20,000 person records, it create 40,000 queries when I run "select new list(person.phones, person.books) from Person person where p.id in (:ids)". I am seeing "116 Query select " statement twice for each person id. How does this affect performance? By the way, I am using your hqlQuery since the Criteria method didn't return what I want. Even the Criteria method worked, it will load entire large object twice into memory. – user3123690 Sep 08 '14 at 18:28
have you tried the last hqlQuery which uses left outer join , it fetches all person objects in a single query – user3487063 Sep 08 '14 at 18:31
Right. Could you take a look my previous comment about number of SQL queries it creates(About performance concerns that I have)? I know that you have answered to the question, but I would appreciate if you can give me more insight from performance prospect. – user3123690 Sep 08 '14 at 18:54

Hibernate Criteria joining unrelated children?

1 Answers1