paging and sorting with Jpa returns only around 10% records from db

When I try to retrieve data from JpaRepository with Pageable request it only return 10% from all records. as example I had 68 records in MySQL database and pageble request only return first 10 records. when i try to get 2nd resultset it returns empty page content object.

but if I request 100 records in one request it return all 68 records.

I just try this with JpaRepository and PagingAndSortingRepository too. but its same result

@Component
@Repository
public interface GRNHeaderDetailsRepo extends PagingAndSortingRepository<GrnHeader, GrnHeaderPrimaryKey> {

    @Query("SELECT grn,ld FROM GrnHeader grn left join LocationDetails ld on grn.glocationcode = ld.locationcode  WHERE grn.gdate BETWEEN ?1 AND ?2 AND (grn.gstatus ='G' OR grn.gstatus ='PG') AND (grn.grnheaderId.gsuppliercode LIKE %?3% OR grn.grnheaderId.gpono LIKE %?3% OR grn.grnheaderId.ggrnno LIKE %?3% OR grn.gdate LIKE %?3% OR grn.gtotal LIKE %?3% OR grn.gtotlinediscount LIKE %?3% OR grn.gtransactiontax LIKE %?3% OR grn.glocationname LIKE %?3% OR grn.gstatus LIKE %?3%)")
        Page<Object[]> getGrnCountDetailsCompanyWithDate(Date Date1,Date date2, String search ,Pageable page);
}

if anyone can explain the reason I will really appreciate it. Thank You!

** Updated **

Page<Object[]> GrnHeader = grnHeaderPagingRepo.getGrnCountDetailsCompanyWithDate(new Date(),new Date(),request.getParameter("search[value]"), PageRequest.of(Integer.parseInt(request.getParameter("start")),Integer.parseInt(request.getParameter("length")), Sort.by(Sort.Direction.ASC, sorting)));

** request object **

draw: 1
columns[0][data]: code
columns[0][name]: 
columns[0][searchable]: true
columns[0][orderable]: true
columns[0][search][value]: 
columns[0][search][regex]: false
columns[1][data]: po_no
columns[1][name]: 
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]: 
columns[1][search][regex]: false
columns[2][data]: grn_no
columns[2][name]: 
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]: 
columns[2][search][regex]: false
columns[3][data]: grn_date
columns[3][name]: 
columns[3][searchable]: true
columns[3][orderable]: true
columns[3][search][value]: 
columns[3][search][regex]: false
columns[4][data]: grn_amount
columns[4][name]: 
columns[4][searchable]: true
columns[4][orderable]: true
columns[4][search][value]: 
columns[4][search][regex]: false
columns[5][data]: outlet_name
columns[5][name]: 
columns[5][searchable]: true
columns[5][orderable]: true
columns[5][search][value]: 
columns[5][search][regex]: false
columns[6][data]: status
columns[6][name]: 
columns[6][searchable]: true
columns[6][orderable]: false
columns[6][search][value]: 
columns[6][search][regex]: false
columns[7][data]: details
columns[7][name]: 
columns[7][searchable]: true
columns[7][orderable]: false
columns[7][search][value]: 
columns[7][search][regex]: false
order[0][column]: 0
order[0][dir]: asc
start: 0
length: 10
search[value]: 
search[regex]: false
_: 1562825012290

** 2nd Page Request **

draw: 2
columns[0][data]: code
columns[0][name]: 
columns[0][searchable]: true
columns[0][orderable]: true
columns[0][search][value]: 
columns[0][search][regex]: false
columns[1][data]: po_no
columns[1][name]: 
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]: 
columns[1][search][regex]: false
columns[2][data]: grn_no
columns[2][name]: 
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]: 
columns[2][search][regex]: false
columns[3][data]: grn_date
columns[3][name]: 
columns[3][searchable]: true
columns[3][orderable]: true
columns[3][search][value]: 
columns[3][search][regex]: false
columns[4][data]: grn_amount
columns[4][name]: 
columns[4][searchable]: true
columns[4][orderable]: true
columns[4][search][value]: 
columns[4][search][regex]: false
columns[5][data]: outlet_name
columns[5][name]: 
columns[5][searchable]: true
columns[5][orderable]: true
columns[5][search][value]: 
columns[5][search][regex]: false
columns[6][data]: status
columns[6][name]: 
columns[6][searchable]: true
columns[6][orderable]: false
columns[6][search][value]: 
columns[6][search][regex]: false
columns[7][data]: details
columns[7][name]: 
columns[7][searchable]: true
columns[7][orderable]: false
columns[7][search][value]: 
columns[7][search][regex]: false
order[0][column]: 0
order[0][dir]: asc
start: 10
length: 10
search[value]: 
search[regex]: false
_: 1562825012292

Answer

Javadoc for PageRequest.of states:

/**
 * Creates a new unsorted {@link PageRequest}.
 *
 * @param page zero-based page index.
 * @param size the size of the page to be returned.
 * @since 2.0
 */
public static PageRequest of(int page, int size) {
    return of(page, size, Sort.unsorted());
}

You need to pass page number instead of row number as the first parameter (start: 1 in second request). Otherwise, you’re taking 10-th page in 0-based indexing, so you should have more than 100 rows to get anything.

Advertisement

Answer