I have a very simple case of two entities: UserIdentity and Student. Each UserIdentity related to exactly one Student. I have created a OneToOne association between UserIdentity and Student.

When I load the UserIdentity table, I want Hibernate to eagerly load all the corresponding Student as well. This is working fine - however, I see in the log that it is generating n+1 queries (1 query for getting all the UserIdentity, and then n queries for getting the Student of each UserIdentity)

My goal is to have all the Student retrieved in ONE query.

I have tried using FetchType.JOIN - but it doesn't help.

Here is my mapping - can someone please tell me whats wrong?
(I have looked through the FAQs, docs and HibernateInAction book - but can't nail this down)

@Entity
@Table(name = "userIdentity", catalog = "ubc")
public class UserIdentity {
@Id
private String username;
private String password;
private String permissions;

@OneToOne
@JoinColumn(name = "STUDENT_ID")
@Fetch(FetchMode.JOIN)
private Student student;

......
.....
}

@Entity
@Table(name = "students", catalog = "ubc")
public class Student {
@Id
private String id;
private String firstName;
private String lastName;
private String middleInitial;

...
...
}


FYI - I use EntityQuery to get the UserIdentity table:
List<UserIdentity> userIdentityList = userIdentityQuery.getResultList();


@Name("userIdentityQuery")
public class UserIdentityQuery extends EntityQuery<UserIdentity> {

@Override
public String getEjbql() {
return "select userIdentity from UserIdentity userIdentity";
}

}

Use this query:



The FetchMode setting is not used when you are loading things with HQL.

Thanks for your reply - it worked. FYI - I had to slightly change your suggested query to "join fetch" instead of "fetch join".

Ah... of course. Sorry for that.

May I ask a quick follow up question?
Its regarding:
"The FetchMode setting is not used when you are loading things with HQL."

In practice, won't tables always be loaded in response to a HQL query? Can you please help me visualize a case when a table is loaded automatically?

Thanks in Advance!

The following doesn't use HQL:

* Session.get(),
* Criteria queries
* Navigating to an associated item or collection

See also: http://www.hibernate.org/hib_docs/v3/re ... ing-custom

OK, got it... thanks again :)

Is it possible to have a "cascading fetch"?

For instance, in the example above of UserIdentity and Student - a Student has an association with another entity called "Purchase"

@Entity
public class Purchase {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long purchaseId;
private Date purchaseDate;
private String status;

@ManyToOne
@JoinColumn(name="STUDENT_ID")
private Student student;
...
...

}

@Entity
@Table(name = "students", catalog = "ubc")
public class Student {
@Id
private String id;
private String firstName;
private String lastName;
private String middleInitial;

@OneToMany (mappedBy = "student")
private Set<Purchase> purchases = new HashSet<Purchase>();
...
...
}


Is it possible to extend the query:
"select userIdentity from UserIdentity userIdentity join fetch userIdentity.student"

to make it also "fetch" the "purchases" for each student - in a single SQL Select query??!

Once more - thanks very much in advance!

Yes it is. There are several examples in the Hibernate documentation. http://www.hibernate.org/hib_docs/v3/re ... joins.html

That worked!

Here is my query:
"select distinct userIdentity from UserIdentity userIdentity join fetch userIdentity.student s join fetch s.purchases"

Thanks a ton for all your help!! I really appreciate it :)

This cleanly resolves the n+1 query issue....very cool!