JPA/hibernate big collections

Question

In a scenario with two types of entities, Parent and Child:

Parent - @OneToMany Collection children;

The default is to have lazy loading on the collection of children. This model works great for small numbers of children, but if the number grows very large this seems unsustainable. So for occasions where I think the number of children will be very large I have used service methods with paging (like "getChildren(Parent parent, int offset, int count)") instead.

Question is: is this the best way to handle situations like this? Or have I missed something?

Thanks, Piotr

Answer 1

This model works great for small numbers of children, but if the number grows very large this seems unsustainable.

I'd say that it all depends on what you want to do with them but in most cases, this is true.

So for occasions where I think the number of children will be very large I have used service methods with paging (like "getChildren(Parent parent, int offset, int count)") instead.

Paging is a very natural approach if you need to display a (potentially very) large number of results for browsing. Humans most often don't want or need all records and they can't deal with huge numbers of results anyway. The case of applications having to deal with all results at once is of course different but JPA might simply not be appropriate for them.

Question is: is this the best way to handle situations like this? Or have I missed something?

IMO, it is definitely much better than feeding a result page with the the whole collection that you'd get by calling parent.getChildren(), and it will save some database, network, app server resources.

Another thing you might consider is restricting the max number of results when doing a search. Instead of paging 10⁶ results (who is going to browse that anyway?), it's common (at least to my experience) to ask the user to perform a more restrictive search i.e. to add search criteria until the number of results becomes human manageable. This is a bit different from your initial question though.