On-demand eager loading

Question

Hi guys,

I make a query:

String query = "SELECT DISTINCT a FROM A a FETCH ALL PROPERTIES " +
     "JOIN a.Bs AS b " +
     "JOIN b.Cs AS c WHERE c = :c";
Query q = DAO.getSession().createQuery(query);
q.setParameter("c", c);
return q.list();

Even though I've said FETCH ALL PROPERTIES on a, when I access all the collections that A has, they still need to be loaded, thus aren't eagerly loaded. They have been defined as lazy loading, and that is the default behaviour I want, but this is the exception: I would like them loaded right now. I've tried swapping JOIN for LEFT OUTER JOIN to provoke Hibernate into loading them, and I've tried setting q.setFetchMode("a", FetchMode.EAGER), but it doesn't exist for Query.

The list of As is quite long, and they have quite a few collections, so making this an n+1 query thing is very slow (about ten seconds, as opposed to doing it in a single query which would be sub-second speed). I'd far prefer one query and loading all that's necessary in that one go. Any suggestions on how I can do that?

PS, Little bonus question: If I replace the "JOIN b.Cs AS c WHERE c = :c"; line with "WHERE :c IN b.Cs";, I get an SQL exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1

The double paranthesis it's referring to is "and ('151000000-0000' in (.))" where 151000000-0000 is the primary key of c. Any idea why I get this error when I do it this way compared to not getting it when I do it the with joining b.Cs in?

UPDATE, as requested, here is the way I use for mapping. B and C are very similarly designed:

@Entity
@Table(name = "tblA")
public class A  {
  @Id
  String AId;

  @Column(name = "shortName", length = 12, nullable = false)
  String shortName;

  @OneToMany(fetch=FetchType.LAZY, mappedBy="theA")
  private Set<B> Bs;

  @OneToMany(fetch=FetchType.LAZY, mappedBy="theA")
  private Set<D> Ds;

  @OneToMany(fetch=FetchType.LAZY, mappedBy="theA")
  private Set<E> Es;

  @OneToMany(fetch=FetchType.LAZY, mappedBy="theA")
  private Set<F> Fs;
}

theA in B, D, E and F is defined like this:

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name = "AId", nullable = true)
@ForeignKey(name="FK_KategoriID")
private A theA;

Cheers

Nik

Answer 1

FETCH ALL PROPERTIES only works for layz properties (where a property is a String, Integer, ...) not one-to-may associations (i.e. collections)

see A Short Primer On Fetching Strategies

Answer 2

fetch all properties is not what you want; it's used for telling Hibernate that you want it to fetch single-valued lazy-loaded properties. Details are here.

You need to specify join fetch in your query instead:

SELECT DISTINCT a FROM A a
  LEFT JOIN FETCH a.Bs AS b
  LEFT JOIN FETCH b.Cs AS c
 WHERE c = :c

As far as bonus question goes, WHERE :c IN b.Cs is illegal syntax. Depending on how your C is mapped, you may want to look at elements() function instead.