OneToMany: get size of collection

Question

I'm working with Spring Data JPA(Hibernate inside). I have two entities Quiz and Question. OneToMany relation between them. Quiz has list of Question. How can I get information about Quiz and size of list of Question? If I write like that:

Quiz quiz = quizRepository.findOne(1);
int questionCount = quiz.getQuestions().size();

Hibernate generates two queries:

1. Select information about Quiz
2. Select all Questions

But I need only Quiz information and size of Questions.

How can I do it without second select?

Answer 1

If you are open to using Hibernate-specific annotations, you could do the following:

class Quiz {
  @LazyCollection(LazyCollectionOption.EXTRA)
  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "quiz", orphanRemoval = true)
  private Set<Question> questions;
}

Note the use of @LazyCollection(LazyCollectionOption.EXTRA).

Now, if you do quizRepsitory.findOne(...).getQuestions().size(), Hibernate will fire the query SELECT COUNT(...) FROM question WHERE quiz_id=?. However, if you do for(Question question : quizRepsitory.findOne(...).getQuestions()) { ... }, Hibernate will fire a different query: SELECT * FROM question WHERE quiz_id=?. Also, if quiz.questions has been loaded already, the COUNT query is not fired again. Saves you having to map Quiz to Question twice, once for the actual collection and again just for the count.

Answer 2

It is not clear from your question whether you want to merely avoid the second SQL query or whether you want to completely avoid loading the questions into memory.

As noted elsewhere you can deal with the first scenario by specifying the fetch mode either on the relationship itself or via a criteria/JPQLquery which loads the quiz. This will load everything in one SQL query but you will still have the overhead of loading the questions: loading the questions may not be an issue in your case but for large datasets the overhead may be considerable if you only need a count.

For the second scenario you have various options. Hibernate specific non-portable, solutions would be to make use of Hibernate's @Formula annotation.

How to map calculated properties with JPA and Hibernate

class Quiz{
    @Formula("select count(*) from question where quiz_id = id")
    int numberOfQuestions;
}

or to use the Hibernate @LazyCollection(LazyCollectionOption.EXTRA) property which allows you to call size() without loading all the records.

Both of which which will give you the number of questions without loading the entire collection to memory.

The second, non-Hibernate specific, portable, solution is to create a view say vw_quiz_summary_data which would have the same information. You can then map this as normal entity and link it to a Quiz as either a one-to-one relation or as a @SecondaryTable.