Fetch Query not working in Spring Data JPA with Pageable

Published on March 25, 2015 by

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.

Featured

Learn Spring Framework today!

Take an online course and become a Spring Framework champion!

Here is what is covered:

  • The Spring Framework basics, including Spring MVC
  • How to work with JDBC and MySQL (in the context of Spring)
  • Build real applications step by step
  • A detailed walkthrough of Spring's core concepts
  • ... and much more!
Spring Framework logo
Author avatar
Bo Andersen

About the Author

I am a back-end web developer with a passion for open source technologies. I have been a PHP developer for many years, and also have experience with Java and Spring Framework. I currently work full time as a lead developer. Apart from that, I also spend time on making online courses, so be sure to check those out!

18 comments on »Fetch Query not working in Spring Data JPA with Pageable«

  1. Dmytro

    Thanks you for pretty approach.

  2. Alex

    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

    • 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!

      • Alex

        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

        • 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.

          • Alex

            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

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

  3. Jose Antonio

    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!

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

  4. quangnm92

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

  5. unlex

    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…

    • Preetam

      Hi unlex,

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

  6. RUCHI WADHWA

    Hello Anderson,

    Great article. Helped me resolve my problem.

  7. Alexey Zalivko

    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

    • Hello Alexey,

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

  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")
    • I am happy that it helped you, and thank you for sharing your solution! :-)

  9. Arya Wiratama

    Thank you for the explanation. it’s very helpful

Leave a Reply

Your e-mail address will not be published.