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)