Fetch Query not working in Spring Data JPA with Pageable

I recently had a problem with one of my Spring Data JPA queries; for some reason, I was getting the following exception.

query specified join fetching, but the owner of the fetched association was not present in the select list

This seemed strange, because I had written a standard JPQL/HQL query that I knew worked. My Spring Data JPA repository looked something like this:


@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
	@Query("SELECT c FROM Company c INNER JOIN FETCH c.employees WHERE c.id = :companyId")
	Page<Company> findById(@Param("companyId") int companyId, Pageable pageable);
}

Exactly the same thing happened when I was using JPA 2.1’s new feature, entity graphs.


@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
	@EntityGraph(value = "some.entity.graph", type = EntityGraph.EntityGraphType.FETCH)
	@Query("SELECT c FROM Company c INNER JOIN FETCH c.employees WHERE c.id = :companyId")
	Page<Company> findById(@Param("companyId") int companyId, Pageable pageable);
}

The problem turned out to be caused by the pagination functionality provided by Spring Data JPA by extending theĀ PagingAndSortingRepository interface (extended by theĀ JpaRepository interface). Whenever one uses this pagination functionality, Spring Data JPA executes two queries; one to get the number of rows matching the query, and one that fetches the data for a given page. The problem has to do with how Spring Data JPA generates the count query.

Now, I have not looked into the details of this problem to verify my theory, but the following is what I believe to be the case. Spring Data JPA takes the query provided by the @Query annotation and attempts to construct a count query based on it. As a result, it includes the FETCH joins, but the JPA provider (Hibernate in my case) throws an exception because using the FETCH keyword does not make sense for a count query. Fetching data is pointless if you are only returning the number of matched rows, and Hibernate does not know how to map the result data to objects.

Luckily, Spring Data JPA provides a way to override the auto generated count query, by using the countQuery attribute within the @Query annotation. In this query, we will leave out the FETCH joins.


@Repository
public interface CompanyRepository extends JpaRepository<Company, Integer> {
	@EntityGraph(value = "some.entity.graph", type = EntityGraph.EntityGraphType.FETCH)
	@Query(
		value = "SELECT c FROM Company c INNER JOIN FETCH c.employees WHERE c.id = :companyId",
		countQuery = "SELECT COUNT(c) FROM Company c INNER JOIN c.employees WHERE c.id = :companyId"
	)
	Page<Company> findById(@Param("companyId") int companyId, Pageable pageable);
}

As you can see, the two queries are pretty similar, with the exception of the FETCH keywords being removed. This causes the JPA provider to execute a correct count query, and thus our repository method now works!

Note that in the above example, the @EntityGraph is entirely optional. One could just as well define FETCH joins within the query itself, and then remove these for the count query. Please also note that the exception mentioned in the beginning of this post can occur for a number of reasons; I merely wanted to share my findings in my particular case because it was less obvious than in many other cases.

I hope that this helps you find the cause of your problem, because it was a bit tricky to find. Thank you for reading.

17 Comments

  1. Dmytro said:

    Thanks you for pretty approach.

    June 16, 2015
    Reply
  2. Alex said:

    Hi,

    I found your article helpful to understand why I had this count query out of the hat, and I have a similar issue, altough it’s not a failure nor an error.

    The automatically generated count query from Pageable is returning 1 or more rows if I execute it to my Database Query CLI (the exact same as hibernate log prompt me) but the following queries are not called, so I always got a void array, even if my query is valid. I mean, if it were returning 0 row, I could have understood why the following queries are not called but …

    Any idea ? thx a lot

    December 9, 2015
    Reply
    • Bo Andersen said:

      Hello Alex,

      Thank you for your comment. Just to clarify, is it correct that the count query is executed and that you can see it in the Hibernate log, but it returns 0, causing the returned array to be empty? But if you execute the same query manually, it returns a number greater than 0?

      Thanks!

      December 9, 2015
      Reply
      • Alex said:

        Thanks for the fast reply Bo,
        I don’t see the result of the query in Hibernate logs, I only know that if I manually execute the exact same query, I get the right row number of the concerned item.
        But this is the last query executed, after this I go out my method and there is not other queries, so I guess when the query is performed in my application, it should return 0, hence my elements not retrieved. I’ll try to redifine the count query or remove it

        December 9, 2015
        Reply
        • Bo Andersen said:

          That’s sounds pretty strange, because if you see the query in the Hibernate logs and that returns more than 0 when you execute it manually, then I don’t see any reason why Hibernate would receive a different response. Perhaps you could post your queries here, and I can take a look at it and see if I can spot a mistake.

          December 9, 2015
          Reply
          • Alex said:

            thx Bo, you can delete my posts if you want, as I found the solution, and the issue is not related to Java, but to my Javascript client, which was sending a bad param to my Pageable, hence this behaviour. thanks for help anyway

            December 10, 2015
          • Bo Andersen said:

            No problem Alex, glad you found a solution! :-)

            December 10, 2015
  3. Jose Antonio said:

    Thanks a lot. It works fine!
    We’ve been fighting with this problem and almost give up.
    Now I’m the coolest guy in my team X-D

    Great work!

    March 22, 2016
    Reply
    • Bo Andersen said:

      Awesome, Jose! Happy to hear that it helped you guys! :-)

      March 23, 2016
      Reply
  4. quangnm92 said:

    Thank a lot, i was banging my head to the wall before finding your solution, nice catch.

    May 18, 2016
    Reply
  5. unlex said:

    For some reason, the generated SQL query by Hibernate (which I see in the logs) does not contain LIMIT statement. I use almost exactly the same example…

    July 13, 2016
    Reply
    • Preetam said:

      Hi unlex,

      Did you find a fix to this ? I am facing similar issue since last 3-4 weeks.

      November 15, 2016
      Reply
  6. RUCHI WADHWA said:

    Hello Anderson,

    Great article. Helped me resolve my problem.

    July 19, 2016
    Reply
  7. Alexey Zalivko said:

    Hello Bo Andersen,

    That was exactly the key article helped me to deal with the issue
    “query specified join fetching, but the owner of the fetched association was not present in the select list”

    Good notice.

    Thanks,
    Alexey

    November 28, 2016
    Reply
    • Bo Andersen said:

      Hello Alexey,

      I am happy that this helped you solve the issue. Happy coding! :-)

      December 3, 2016
      Reply
  8. Nice solution … CountQuery had me a bit perplexed there to work with an inner join … After modifications I’m good … Thanks man…

    @Query(value = "SELECT i FROM Invoice i inner join fetch i.customer c where i.invoiceFromDate >= :fromDate AND i.invoiceToDate = :fromDate AND i.invoiceToDate <= :toDate")
    
    December 2, 2016
    Reply
    • Bo Andersen said:

      I am happy that it helped you, and thank you for sharing your solution! :-)

      December 3, 2016
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *