Pagination Spring boot JPA Exception:Method has to have one of the following return types

Question

I am trying to display selected records per page using pagination using JPQL. My Main repo interface extends PagingAndSorting repository interface. Below are my classes

StudentRepositoryPaging.java

package com.hibernate.JPQLandNativeSql.repos;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.hibernate.JPQLandNativeSql.entities.Student;
//To enable paging support, we just need to pass "Pageable" as a parameter to the methods as an arg
public interface StudentRepositoryPaging extends PagingAndSortingRepository<Student, Long> 
{
    @Query("from Student") //for executing JPQL, we write our queries inside @query annotation,this query is : "select * from Student" but we can also write it as "from Student"
    Page<Student> findAllStudents(Pageable page); //this will return a list. 

    @Query("select st.fname , st.lname from Student st")
    Page<Object[]> findAllStudentsPartial(Pageable page);// this will return an object array containing the returned columns

    @Query("from Student where fname=:fname")
    List<Student> findAllStudentsByFirstName(@Param("fname")String fname,Pageable page); //named queries using which you can pass arguments and those arguments can be used in queries

    @Query("select lname,score from Student where fname=:fname")
    List<Object[]> findAllStudentsByFirstNamePartial(@Param("fname")String fname,Pageable page); //named queries using which you can pass arguments and those arguments can be used in queries

    @Query("from Student where score>:min and score<:max")
    List<Student> findAllStudentsByScore(@Param("min")int min,@Param("max")int max,Pageable page);

    @Modifying //if we are using statements such as insert , update , delete, we need to use@Modifying, because Hibernate assumes that we are using select statements
    @Query("delete from Student where id=:id")
    void deleteFromTable(Long id,Pageable page);

}

Test method : testFindAllPaging.java

@Test
    public void testFindAllPaging() {
        int pageNumber=0;
        int pageSize = 2;//number of database records to be displayed per page
        Pageable page = PageRequest.of(pageNumber, pageSize,Sort.by(Direction.DESC,"fname"));
        //Page<Student> findAllStudents = reposPaging.findAllStudents(page);
        Page<Object[]> studentsPartial = reposPaging.findAllStudentsPartial(page);
        for(Object[] s : studentsPartial ) {
            System.out.println(s[0]+"-----"+s[1]);
        }
    }

Below are the results in my table

When I am running my test method, I am getting below error:

Caused by: java.lang.IllegalStateException: Method has to have one of the following return types! [interface org.springframework.data.domain.Page, interface org.springframework.data.domain.Slice, interface java.util.List]
    at org.springframework.data.repository.query.QueryMethod.assertReturnTypeAssignable(QueryMethod.java:307) ~[spring-data-commons-2.2.1.RELEASE.jar:2.2.1.RELEASE]
    at org.springframework.data.repository.query.QueryMethod.<init>(QueryMethod.java:87) ~[spring-data-commons-2.2.1.RELEASE.jar:2.2.1.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryMethod.<init>(JpaQueryMethod.java:103) ~[spring-data-jpa-2.2.1.RELEASE.jar:2.2.1.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79) ~[spring-data-jpa-2.2.1.RELEASE.jar:2.2.1.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:574) ~[spring-data-commons-2.2.1.RELEASE.jar:2.2.1.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:567) ~[spring-data-commons-2.2.1.RELEASE.jar:2.2.1.RELEASE]
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[na:1.8.0_231]
    at java.util.Iterator.forEachRemaining(Iterator.java:116) ~[na:1.8.0_231]

Where am I going wrong ?

Answer 1

Return type Page<Object[]> will work only with native queries. So the method should look like

@Query(value = "select st.fname , st.lname from Student st", nativeQuery = true)
Page<Object[]> findAllStudentsPartial(Pageable page);