Spring Data PageRequest not returning specified count

Question

i am using spring data's Pageable to Limit the record fetch. i have more than 500 entries in database but i want to fetch only 5 but it not returning correctly .mostly it return 2,3 or 4 records (every time it return less than 5 record but i have 500 in database).so please help me .code for service class and repository given below

Service code :

    public List<Client> listAll() {
        Pageable pageable = new PageRequest(0,5);
        return (List<Client>) clientRepository.getAllClient(pageable);
       }

Repository code :

public interface ClientRepository extends CrudRepository<Client, Long> {

@Query("Select distinct p FROM Client p LEFT JOIN FETCH p.offices  LEFT JOIN FETCH p.users LEFT JOIN FETCH p.firmSeats")
public List<Client> getAllClient(Pageable page);
}

possible duplicate of [Spring-Data FETCH JOIN with Paging is not working](http://stackoverflow.com/questions/21549480/spring-data-fetch-join-with-paging-is-not-working) — Alan Hay, Apr 23 '15 at 10:51

score 1 · Answer 1 · answered Apr 23 '15 at 10:58

The issue here is as a result of the JOIN Fetch options you have specified for the the associations.

If you think about the result-set returned from the DB by this statement then it may contain multiple records per user e.g. all 5 records in the result set would be from Client x if he has, for example, 5 offices. Post-processing in the JPA tier then filters this to be 1 record so you would only see 1 record in your application.

Remove the JOIN fetch clauses to verify this is the case. If so you will need to look at specifying a custom countQuery for your Query (which would essentially mirror you main query but simply return the count):

http://codingexplained.com/coding/java/spring-framework/fetch-query-not-working-spring-data-jpa-pageable

Spring Data PageRequest not returning specified count

1 Answers1