[FIXED] Could not extract result set after using page(pagable) springboot

Issue

Repository

@Query(value="SELECT * FROM Invoiceupload.invoiceupload where email=:email", nativeQuery=true)

public Page getInvoiceDetailByEmail(String email,Pageable pageable);

Controller

// View all invoices by a vendor

    @GetMapping("/viewByEmail/{email}")
    public Page<Map<String,Object>> getVendorInvoices(@RequestHeader (value="Authorization") String token,@PathVariable("email") String email,Pageable pageable){

        if(request.checkVendorTokenValidity(token)!= null) {    

            return invoiceRepository.getInvoiceDetailByEmail(email, pageable);
        }
        else
            throw new Unauthorized(ErrorMessages.NOT_AUTHORISED);
    }

I am trying to fetch all invoices from db passing email,for now I have 19 invoice records in my db So when i try to run this endpoint from post man it works fine ,returns all 19 invoices BUT when I added another invoice (total=20) it failed to fetch

error:

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

When I tried replacing Page> with List> it worked fine, so i think there is a problem in page and pageable ,can some one please help

Solution

To enable pagination for native queries , you needs to declare an additional attribute in your native query. That is countQuery.

 @Query(value="SELECT * FROM Invoiceupload where email=:email", 
  countQuery = "SELECT count(*) FROM Invoiceupload", 
  nativeQuery = true)
Page<Invoiceupload> getInvoiceDetailByEmail(@Param("email")String email,Pageable pageable);

Answered By - DEBENDRA DHINDA
Answer Checked By - David Goodson (JavaFixing Volunteer)