Getting error with paging but working fine with List

Question

I am working on a project which has two entities: Student and teacher. It follows the @ManyToOne relationship. One Teacher can have multiple students. I am facing some difficulty while running the project.

Pageable page = PageRequest.of(pageNo, size)

@Query(value = "SELECT s FROM TeacherEntity s JOIN FETCH s.student d WHERE s.student IN (:students) and s.deleted = false" +
            " ORDER BY s.createdAt DESC")
Page<TeacherEntity> findByStudent(@Param("students") Collection<StudentEntity> students, Pageable pageable);

I am getting this error "FactoryBean threw exception on object creation; nested exception is java.lang.IllegalArgumentException: Count query validation failed for method public abstract org.springframework.data.domain.Page " when I use paging.

When I do it without using paging like this,

@Query(value = "SELECT s FROM TeacherEntity s JOIN FETCH s.student d WHERE s.student IN (:students) and s.deleted = false" +
            " ORDER BY s.createdAt DESC")
List<TeacherEntity> findByStudentIn(@Param("students") 
Collection<StudentEntity> student);

, it is working fine. Can someone please tell me what is the mistake I am doing?

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'teacherRepository': FactoryBean threw exception on object creation; nested exception is java.lang.IllegalArgumentException: Count query validation failed for method public abstract org.springframework.data.domain.Page com.pro.aks.repository.TeacherRepository.findByStudent(java.util.Collection,org.springframework.data.domain.Pageable)!
    at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:176) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:101) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1827) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getObjectForBeanInstance(AbstractAutowireCapableBeanFactory.java:1265) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:334) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeansOfType(DefaultListableBeanFactory.java:624) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeansOfType(DefaultListableBeanFactory.java:612) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.data.repository.config.DeferredRepositoryInitializationListener.onApplicationEvent(DeferredRepositoryInitializationListener.java:51) ~[spring-data-commons-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    at org.springframework.data.repository.config.DeferredRepositoryInitializationListener.onApplicationEvent(DeferredRepositoryInitializationListener.java:36) ~[spring-data-commons-2.3.2.RELEASE.jar:2.3.2.RELEASE]

@SKumar Can you please tell what do u mean by Hashcode here? — Abhishek, Sep 26 '20 at 18:28
so have you overriden equals method but not hashcode method ? Can you post your TeacherEntity to the question ? — SKumar, Sep 26 '20 at 18:32
I don't think there is an issue with it. Because code is working perfectly fine when I don't use pagination and use List — Abhishek, Sep 26 '20 at 18:44
If you have not overriden both the methods, then it's fine. Otherwise, that can be some issue if you haven't defined them correctly. Paging will need to find some x number of records and that rely on those methods. Are you getting correct number of records in that List scenario ? — SKumar, Sep 26 '20 at 18:53
@SKumar Yes sir, I am getting correct number of records in the List scenario — Abhishek, Sep 27 '20 at 04:23

stackbiz · Accepted Answer · 2020-09-27T18:09:07.127

-1

You need to print out the full stacktrace for checking the error: Count query validation failed

You need to define "countQuery" in your method "findByStudent". Check this link for the details: Spring Data and Native Query with pagination

Spring framework can't create the object because the method definition (annotation) is wrong.

Technology behind: Spring framework will use the "countQuery" annotation (HQL) to generate the "Raw SQL" (SQL), if this information is missing, then Spring framework don't know how to count the data.

edited Sep 27 '20 at 18:09

answered Sep 26 '20 at 18:20

stackbiz

1,136
1
5
22

added the stacktrace – Abhishek Sep 26 '20 at 18:23
Refer to this link, change the SQL may be help: https://stackoverflow.com/questions/44647630/validation-failed-for-query-for-method-jpql – stackbiz Sep 26 '20 at 18:31
Already checked, didn't help. The problem is why it is giving error while using paging and not when using List? – Abhishek Sep 26 '20 at 18:33
I have a suggestion, can you modify the SQL to the following simple SQL for testing? Let's test it together and find the root cause step by step: SELECT s FROM TeacherEntity s – stackbiz Sep 27 '20 at 03:47
Do you mean this simple query works but your original query does not? – stackbiz Sep 27 '20 at 05:28
I think this is happening because I am using Join fetch with pagination. – Abhishek Sep 27 '20 at 07:18
I have updated the detail in the answer, please check. – stackbiz Sep 27 '20 at 17:43

Getting error with paging but working fine with List

1 Answers1