Custom Query in Spring JPA Repository with Pagination

Question

I have tried implementing JPA Repository with Spring Boot it works fine. Now if i try to implement custom query in interface which extends JpaRepository using @Query Annotation it works fine returns List of beans.(using NamedQuery). Now when i try to use pagination for custom method/query it doesn't work.

Code :

Controller :

@RequestMapping("/custompages/{pageNumber}")
public String getAllEmployeesUsingNamedQueryWithPaging(@PathVariable Integer pageNumber,Model model)
{
    Page<Employee> page = employeeService.getAllEmployeesUsingNamedQueryWithPaging(pageNumber);

    System.out.println("current page "+page);
    System.out.println("current page content"+page.getContent());

     int current = page.getNumber() + 1;
    int begin = Math.max(1, current - 5);
    int end = Math.min(begin + 10, page.getTotalPages());

    model.addAttribute("empList", page.getContent());
    model.addAttribute("empPages", page);
    model.addAttribute("beginIndex", begin);
    model.addAttribute("endIndex", end);
    model.addAttribute("currentIndex", current);

    return "employeeWorkbench";
}

Service

@Override
public Page<Employee> getAllEmployeesUsingNamedQueryWithPaging(Integer  
pageNumber) {

    PageRequest pageRequest =
            new PageRequest(pageNumber - 1, PAGE_SIZE, 
    Sort.Direction.ASC, "id");
    return   
employeeDao.getAllEmployeesUsingNamedQueryWithPaging(pageRequest);
}

Dao

@Transactional
public interface EmployeeDao  extends JpaRepository<Employee, Long>{

@Query(name="HQL_GET_ALL_EMPLOYEE_BY_ID")//Works Fine
public List<Employee> getEmpByIdUsingNamedQuery(@Param("empId") Long
empId);     

@Query(name="HQL_GET_ALL_EMPLOYEE") //throws exception
public Page<Employee> getAllEmployeesUsingNamedQueryWithPaging(Pageable     
pageable);  
}

NamedQuery

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 
3.0//EN"  
"http://hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<query name="HQL_GET_ALL_EMPLOYEE">from Employee</query>

<query name="HQL_GET_ALL_EMPLOYEE_BY_ID">from Employee where id = 
:empId</query>

</hibernate-mapping>

Exception : java.lang.IllegalArgumentException: Type specified for TypedQuery [java.lang.Long] is incompatible with query return type [class com.mobicule.SpringBootJPADemo.beans.Employee]

I just want to have pagination functionality provided by Spring JPA Repository for custom methods and query also. How can I achieve this?

if instead of using a named query in xml, you put the query directly in the @Query("from Employee") does it change the behavior? also the @Transactional on the repository is redundant. Also for queries this simple you'd be better off just using the findAll the repository already has. — xenoterracide
also the error is implying that for some reason (not obvious to me) it thinks that the query specified should return a Long and not Employees — xenoterracide
@xenoterracide : Thanks for response. I tried with simply @Query("from Employee") but this will return me List<Employee> and NOT Page<Employee>. My motive is to use JpaRepositories build in Paging Functionality For my custom queries also. And as you said i used findAll this works fine with no issues even works well with pagination also.but i want to have same functionality for my custom queries also. — ashish
I have searched for this query i got know that when JpaRepository executes its method findAll,findBy......etc with paging it runs 2 queries ---one for count and other for actual data. — ashish
yes that count is intentional... so it knows how many pages it has. If you have the return type specified as Page<Employee> you should not get a List, you should only get a List if you ask for a List or a sub interface of List (such as Iterable) — xenoterracide

xenoterracide xenoterracide · Accepted Answer · 2015-08-06T15:28:40

I'm not sure why, but for some reason simply doing from Entity causes the "id" to be returned, instead you need to provide the entity returned in the select, like select f from Foo f

public interface FooRepo extends PagingAndSortingRepository<Foo, Long> {

@Query( "select f from Foo f" )
Page<Foo> findAllCustom( Pageable pageable );

Page<Foo> findAllByBarBazContaining( String baz, Pageable pageable );
}

I received the same error, with just from Foo. I also believe you can reference these by name to the xml file as you were. here's my full code

further testing says that from Foo f also works, I do not know why the alias is required, perhaps it is part of the JPQL spec.

Here is a test showing how to do simple paging, sorting by one property and sorting by multiple properties

@Test
public void testFindAllCustom() throws Exception {
    Page<Foo> allCustom = fooRepo.findAllCustom( pageable );

    assertThat( allCustom.getSize(), is( 2 ) );

    Page<Foo> sortByBazAsc = fooRepo.findAllCustom( new PageRequest( 0, 2, Sort.Direction.ASC, "bar.baz" ) );

    assertThat( sortByBazAsc.iterator().next().getBar().getBaz(), is( "2baz2bfoo" ) );

    Page<Foo> complexSort = fooRepo.findAllCustom( new PageRequest( 0, 2, new Sort(
            new Sort.Order( Sort.Direction.DESC, "bar.baz" ),
            new Sort.Order( Sort.Direction.ASC, "id" )
    ) ) );

    assertThat( complexSort.iterator().next().getBar().getBaz(), is( "baz1" ) );
}

Custom Query in Spring JPA Repository with Pagination

1 Answers