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.

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!

36 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

  10. ismail

    thanks, never thought to remove “fetch”completely from count query, very helpful!

  11. Ethiraj

    java.lang.IllegalStateException: No aliases found in result tuple! Make sure your query defines aliases!

    Am getting this after hibernate the count query

    Note: count query formed properly.

  12. somerandomstranger

    Thank you that saved my day!!

  13. AdamYao

    How to automatically ignore the empty SQL parameter in the annotation @Query content? Is there a way to add conditions based on the judgment NULL?

  14. Raul Santos

    Thanks a lot man!!!

  15. Deepika

    Thanks! have similar issue and CountQuery worked for me.

  16. Amit

    What is wrong with below query:

    @Query(value = “select u.username as username, count(log.id) as count, log.downloaded_at as downloadeddate from file_download_log log join video v on log.video_id = v.id AND v.web_account_id = :webid join user u on u.id = log.user_id where log.downloaded_at >= :fromDate AND log.downloaded_at <= :toDate group by u.id \n– #pageable\n", countQuery = "select count(*) from (select count(*) as count from file_download_log log join video v on log.video_id = v.id AND v.web_account_id = :webid join user u on u.id = log.user_id group by u.id) c", nativeQuery = true)
    Page getUserWiseLogsWithDate(Pageable pageable, @Param(“webid”) Integer webid,
    @Param(“fromDate”) Date fromDate, @Param(“toDate”) Date toDate);

    Getting Error Like: Parameter with that name [fromDate] did not exist.

  17. PATRICK

    Oh man thanks for this solution. It was getting really late and I have a demo tomorrow morning and this query of mine works fine in SQL Developer but once I got it placed as @Query in my repository interface, I got this SQLException from the Count(ppp). It couldn’t figure out what ppp was and it’s my join table identifier for one of the tables. I was about to give up until I came across your page.

  18. Hugo

    Thank you so much!

  19. dixith

    In my case the hibernate generated queryCount query and the one that i mentioned explictly are same but 0 rows were returned .Any quick help is appreciated.

  20. kumar

    Thanks a lot for article, but this didn’t solve the N+1 issue after using the ‘INNER JOIN FETCH’

  21. Chadi Rahme

    In my case the Count query didnt effect the return results,but I must add any count query else it will thorw exception
    ex:if I put countQuery = “select 1”
    or countQuery = “select 0”

    I return the coorect results from my main query regardless the CountQuery!!

    @Query(
    value = “SELECT c FROM Company c INNER JOIN FETCH c.employees WHERE c.id = :companyId”,
    countQuery = “SELECT 1”)

    or

    @Query(
    value = “SELECT c FROM Company c INNER JOIN FETCH c.employees WHERE c.id = :companyId”,
    countQuery = “SELECT 0”

  22. rob

    SELECT distinct g FROM #{#entityName} g INNER JOIN g.gameVersions v where v.gameVersion.id = :id
    it works correct i have problem with dublicates

  23. Siyaphakama Sosibo

    Thank you Bo Andersen, you are a star, I was having this issue for two days, Your solution helped me a lot!

  24. Onur

    Thank you

  25. Gino Allison

    I don’t know if the author of this article is still here, but I have this problem when I use the Specification interface
    so I was wondering how to include this countQuery in a criteriaBuilder or a criteriaQuery, whatever

  26. ys peng

    @Gino Allison
    you can add
    if (query.getResultType() != Long.class) {}
    int to your Specification to check the query is countQuery or not.
    If it’s a countQuery, you can use Join instead of Fetch.

Leave a Reply

Your e-mail address will not be published.