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:

Select information about Quiz
Select all Questions

But I need only Quiz information and size of Questions.

How can I do it without second select?

score 11 · Answer 1 · answered Jun 23 '15 at 12:42

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.

Alan Hay · Accepted Answer · 2019-09-19T07:33:13.313

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.

Thanks. I haven't know about @Formula before. But I've already found the solution that works great. @LazyCollection(LazyCollectionOption.EXTRA) private List questions; Hibernate doesn't load all related question, only size. — User 2016, Jun 23 '15 at 11:36
Yes, you are right. That is probably the best solution: forgot about that Hibernate option! — Alan Hay, Jun 23 '15 at 11:38

cнŝdk · Answer 3 · 2015-06-30T11:42:12.573

What you need is the JOIN value with the fetch property in your mapping, that will generate only one select query and allow you to get the size of your collection:

@OneToMany(fetch = FetchType.JOIN, mappedBy = "quiz")
public Set<Question> getQuestions() {
  ...
}

You can find more about it at Hibernate – fetching strategies examples, which explains all the fetch strategies, their generated queries and the differences between them, and you will see that:

With the fetch=”join” or @Fetch(FetchMode.JOIN) Hibernate generated only one select statement, it retrieves all its related collections when the Stock is initialized.

score -1 · Answer 4 · answered Jun 23 '15 at 06:43

It seems that you need to add FetchType=EAGER to Qustions collection in Quiz class.

In this case all questions(not only size, but all contents too) will be fetched in one query with Quiz.

class Quiz {
...
@OneToMany(fetch = FetchType.EAGER, mappedBy = "quiz")
    public Set<Question> getStockDailyRecords() {
        return this.stockDailyRecords;
    }
 ...
}

If you don't need eager fetch of questions in all cases - you can use fetch join in your query:

@Query(value = "SELECT q FROM Quiz q LEFT JOIN FETCH q.questions",

In this cases questions will be fetched eagerly only for Quiz from this query.

OneToMany: get size of collection

4 Answers4