40

Is there a way I can count the size of an associated collection without initializing?

e.g.

Select count(p.children) from Parent p

(there is a good reason why I cant do this any other way as my where clause is more complicated and my from clause is a polymorphic query)

Thanks.

Péter Török
  • 109,525
  • 28
  • 259
  • 326
DD.
  • 19,793
  • 49
  • 140
  • 237
  • Beware that you seem to have little control over the key used when doing existence checking with contains on a LazyCollection. That's a bit of a gotcha because you cannot use natural keys to do the existence check. –  Jun 12 '12 at 05:47

3 Answers3

65

A possible solution other than queries might be mapping children with lazy="extra" (in XML notation). This way, you can fetch the Parent with whatever query you need, then call parent.getChildren().size() without loading the whole collection (only a SELECT COUNT type query is executed).

With annotations, it would be

@OneToMany
@org.hibernate.annotations.LazyCollection(
org.hibernate.annotations.LazyCollectionOption.EXTRA
)
private Set<Child> children = new HashSet<Child>();

Update: Quote from Java Persistence with Hibernate, ch. 13.1.3:

A proxy is initialized if you call any method that is not the identifier getter method, a collection is initialized if you start iterating through its elements or if you call any of the collection-management operations, such as size() and contains(). Hibernate provides an additional setting that is mostly useful for large collections; they can be mapped as extra lazy. [...]

[Mapped as above,] the collection is no longer initialized if you call size(), contains(), or isEmpty() — the database is queried to retrieve the necessary information. If it’s a Map or a List, the operations containsKey() and get() also query the database directly.

So with an entity mapped as above, you can then do

Parent p = // execute query to load desired parent
// due to lazy loading, at this point p.children is a proxy object
int count = p.getChildren().size(); // the collection is not loaded, only its size
Péter Török
  • 109,525
  • 28
  • 259
  • 326
  • can you elaborate a bit more on this. – Varun Mehta May 27 '10 at 19:33
  • Hibernate shouldn't run a `COUNT(*)` query if only `isEmpty()` is required. See also: https://blog.jooq.org/2016/09/14/avoid-using-count-in-sql-when-you-could-use-exists/ – Lukas Eder Sep 14 '16 at 15:42
  • The `COUNT` is executed because Hibernate caches the collection size so that both `collection.isEmpty()` and `collection.size()` use the `cachedSize` instead of always executing `COUNT(*)`. However, you're right about `collection.isEmpty()` which could use EXISTS instead. But then, `EXTRA_LAZY` is not really a performance optimization either (I see it more like a code smell) since if you have a very large collection, it's way better to avoid using a collection and just use a paginated query instead. – Vlad Mihalcea Sep 15 '16 at 09:45
  • @VladMihalcea: I'm not sure if I follow. This doesn't have anything to do with pagination, just with existence of child records. I think that's quite a common use-case, no? – Lukas Eder Sep 15 '16 at 13:20
  • The LazyCollectionOption.EXTRA was added so that you don;t have to load the collection entirely even when you navigate it. So, instead of loading it all, it just loads every element one after another, one by one, like a cursor. If you only need a few records, you'll not see any significant performance degradation. But if you load many items, the performance is going to be really bad. – Vlad Mihalcea Sep 15 '16 at 13:35
1

You can use Session#createFilter which is a form of HQL which explicitly operates on collections. For example, you mention Parent and Children so if you have a Person p the most basic form would be:

session.createFilter( p.getChildren(), "" ).list()

This simply returns you a list of the children. It is important to note that the returned collection is not "live", it is not in any way associated with p.

The interesting part comes from the second argument. This is an HQL fragment. Here for example, you might want:

session.createFilter( p.getChildren(), "select count(*)" ).uniqueResult();

You mentioned you have a where clause, so you also might want:

session.createFilter( p.getChildren(), "select count(*) where this.age > 18" ).uniqueResult();

Notice there is no from clause. That is to say that the from clause is implied from the association. The elements of the collection are given the alias 'this' so you can refer to it from other parts of the HQL fragment.

Steve Ebersole
  • 8,735
  • 2
  • 42
  • 44
-2

You can do the same like this:

@Override
public FaqQuestions getFaqQuestionById(Long questionId) {
    session = sessionFactory.openSession();
    tx = session.beginTransaction();
    FaqQuestions faqQuestions = null;
    try {
        faqQuestions = (FaqQuestions) session.get(FaqQuestions.class,
                questionId);
        Hibernate.initialize(faqQuestions.getFaqAnswers());

        tx.commit();
        faqQuestions.getFaqAnswers().size();
    } finally {
        session.close();
    }
    return faqQuestions;
}

Just use faqQuestions.getFaqAnswers().size()nin your controller and you will get the size if lazily intialised list, without fetching the list itself.

neel4soft
  • 417
  • 1
  • 4
  • 12