Hibernate: separate sql query for every collection

Question

I have a Person class that has a collection of Contacts. Everything works ok, I get the list of persons with their contacts. However, in log I see that a separate query is made to read collection of every person. That is too bad. How to make hibernate make a join to read all the data in one query? I use JPA.

This is the person class:

@Entity
@Table(name = "tbl1")
public class PersonItem implements Serializable{
    @Id
    @Column(name="col1")
    private String guid;
    .....
    @ElementCollection(targetClass = ContactItem.class,fetch=FetchType.EAGER)
    @CollectionTable(name="tbl2",joinColumns=@JoinColumn(name="col2"))
    private List<ContactItem> contacts;
    ....
}

This is the contact class

@Embeddable
@Table(name = "tbl2")
public class ContactItem implements Serializable {

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

    @Column(name="col3")
    private String info;
}

This is the way I get the list of persons:

Query query = em.createQuery("Select p from PersonItem p WHERE p.guid IN (:guids)");
query.setParameter("guids", guids);
List<PersonItem> list=query.getResultList();

And this what I see in log (I have three persons in DB):

Hibernate: select personitem0_.col1 as col1_0_, personitem0_.col4 as col2_0_, personitem0_.col2 as col3_0_, personitem0_.col3 as col4_0_ from tbl1 personitem0_ where personitem0_.col1 in (? , ? , ?)
Hibernate: select contacts0_.col2 as col1_1_0_, contacts0_.col1 as col2_1_0_, contacts0_.col3 as col3_1_0_ from tbl2 contacts0_ where contacts0_.col2=?
Hibernate: select contacts0_.col2 as col1_1_0_, contacts0_.col1 as col2_1_0_, contacts0_.col3 as col3_1_0_ from tbl2 contacts0_ where contacts0_.col2=?
Hibernate: select contacts0_.col2 as col1_1_0_, contacts0_.col1 as col2_1_0_, contacts0_.col3 as col3_1_0_ from tbl2 contacts0_ where contacts0_.col2=?

Answer 1

Please, begin from a more simple mapping. Use plural names, and column prefixes.

@Entity
@Table(name = "persons")
public class Person {

    @Id
    @Column(name = "f_guid")
    private String guid;

    @OneToMany(mappedBy = "person", fetch = FetchType.EAGER)
    private List<Contact> contacts;

}

@Entity
@Table(name = "contacts")
public class Contact {

    @Id
    @Column(name = "f_guid")
    private String guid;

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

    @ManyToOne(fetch = FetchType.LAZY)    
    @JoinColumn(name = "fk_person")
    private Person person;

}

Person is associated to contacts by a foreign key fk_person in the contacts table.

Update

Looks like JPQL overrides a default fetching strategy. You need to specify a fetch explicitly

select p from PersonItem p left join fetch p.contacts WHERE p.guid IN (:guids)

If you have duplicates, cause of joins, you can use distinct

select distinct p from PersonItem p left join fetch p.contacts WHERE p.guid IN (:guids)