Efficient Hibernate criteria for join returning many partial duplicates

Question

I'm fetching a long list of entities which refer to others which refer to... and, at the end, usually of all them refer to a single user as their owner. Not really surprising as what's queried are entities belonging to a single user. There are more parts duplicated in many rows; actually, just a small percentage are unique data. As the query seems to be slow, I though I could gain a bit by fetching things separately using

criteria.setFetchMode(path, FetchMode.SELECT);

This works in my above case, but when querying over many users (as admin), it gets terrible, as hibernate issues a separate query for every user, instead of something like

SELECT * FROM User WHERE id IN (?, ?, ..., ?)

or not fetching them at all (which can't get any worse than one query per entity). I wonder what am I missing?

So instead of fetching a lot of redundant data, I ran into the 1+N problem, where obviously 1+1 queries would do.

• Is there a way to instruct Hibernate to use the right query?
• Is there a way to prevent Hibernate from fetching the owners by specifying it in the criteria itself (rather than putting fetch=FetchType.LAZY on the field; the laziness should be query-specific)?

---

I don't think it matters, but my classes are like

class Child {
    @ManyToOne Father father;
    @ManyToOne Mother mother;
    ...
}
class Father {
    @ManyToOne User owner;
    ...
}
class Mother {
    @ManyToOne User owner;
    ...
}

and the query is like

createCriteria(Child.class)
.add(Restrictions.in("id", idList))
.add(Restrictions.eq("isDeleted", false))

.createAlias("Father", "f")
.add(Restrictions.eq("f.isDeleted", false))
.setFetchMode("f.owner", FetchMode.SELECT)

.createAlias("Mother", "m")
.add(Restrictions.eq("m.isDeleted", false))
.setFetchMode("m.owner", FetchMode.SELECT)

.list();

The important part is that owner does not get used and can be proxied. The javadoc for FetchMode.SELECT says

Fetch eagerly, using a separate select

so it basically promises 1+1 querying which I want rather than "using a separate select per entity".

Answer 1

unless the property is declared with @ManyToOne(fetch=FetchType.LAZY), you can't change anything

True, at least for the time being, until fetch profile capabilities are extended to provide the ability to change eager loading to lazy.

the default is FetchType.EAGER, which is stupid, as it can't be overridden

True, and I agree that it is bad, but in Hibernate native API everything is lazy by default; it is JPA that mandates to-one associations to be eager unless explicitly specified otherwise.

using criteria.setFetchMode(path, FetchMode.SELECT) is pointless as it's always a no-op (either it gets ignored because of the non-overridable eagerness of the property or the property is lazy already)!

With it you should be able to override other lazy fetch modes. See HHH-980 and this comment from one of the lead Hibernate contributors about the javadoc confusion.

fetching lazily leads by default to the 1+N problem

It has nothing to do with lazy loading specifically, it is the default for eager loading as well if you don't fetch the eagerly loaded association in the same query.

it can be controlled via a class-level @BatchSize annotation

You have to place it on class-level for it to take effect on to-one associations with that entity; this answer is helpful. For collection associations (to-many associations with that entity defined in other entities) you have the flexibility to define it separately for each association.