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.
36 comments on »Fetch Query not working in Spring Data JPA with Pageable«
Thanks you for pretty approach.
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!
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.
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! :-)
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! :-)
Thank a lot, i was banging my head to the wall before finding your solution, nice catch.
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…
Hi unlex,
Did you find a fix to this ? I am facing similar issue since last 3-4 weeks.
Hello Anderson,
Great article. Helped me resolve my problem.
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! :-)
Nice solution … CountQuery had me a bit perplexed there to work with an inner join … After modifications I’m good … Thanks man…
I am happy that it helped you, and thank you for sharing your solution! :-)
Thank you for the explanation. it’s very helpful
thanks, never thought to remove “fetch”completely from count query, very helpful!
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.
Thank you that saved my day!!
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?
Thanks a lot man!!!
Thanks! have similar issue and CountQuery worked for me.
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.
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.
Thank you so much!
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.
Thanks a lot for article, but this didn’t solve the N+1 issue after using the ‘INNER JOIN FETCH’
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”
SELECT distinct g FROM #{#entityName} g INNER JOIN g.gameVersions v where v.gameVersion.id = :id
it works correct i have problem with dublicates
Thank you Bo Andersen, you are a star, I was having this issue for two days, Your solution helped me a lot!
Thank you
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
@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.
Gino, you can look at this: https://stackoverflow.com/a/57290429/6123581