0

Here is the scenario. I had a page with a list of records shown all (without pagination).

Now at the backend side, the records are retrieved with a combination of 2 separate named queries for each part (one each for actual records/count of part1/part2);

@NamedQuery(name = "CustomDOTO.findPart1", query = "SELECT part1";
@NamedQuery(name = "CustomDOTO.findPart1Count", query = "SELECT count(part1)";
@NamedQuery(name = "CustomDOTO.findPart2", query = "SELECT part2";
@NamedQuery(name = "CustomDOTO.findPart2Count", query = "SELECT count(part2)";

Now I need to implement pagination for the same. So I use a custom class PagedResponse for the same which actually just consists 2 things;

private Long totalRecords;
private List<T> records;

Also I use a custom method runPaginatedQuery to return paginated response;

protected <T> PagedResponse<T> runPaginatedQuery(Query query, Query cntQuery, int startingResult, int maxResults) {

}

My question is since I have 2 separate named queries, if I call the runPaginatedQuery method 2 times for each of the named queries, it does not help me, since the max results would increase on each call. How do I handle the same on the Java side? I already have the UI ready to handle.

halfer
  • 19,824
  • 17
  • 99
  • 186
copenndthagen
  • 49,230
  • 102
  • 290
  • 442

1 Answers1

0

I once had a similar issue. Don't use two queries, use one and parameterize it so it use LIMIT, and use for example one variable indicating the number of the current page, and some constant defining how many results will you get on one page.

Then you'll have something like

 LIMIT :offset, :resultsLimit

and you're setting offset to something like (page - 1) * DEFAULT_LIMIT_PER_PAGE, and resultsLimit to DEFAULT_LIMIT_PER_PAGE

Hope that helps!

Eel Lee
  • 3,513
  • 2
  • 31
  • 49
  • Thx a lot...So is this modification in the Named Query itself...OR any update to executePaginatedQuery method as well ? – copenndthagen Nov 15 '13 at 09:06
  • For some reasons I now realize that I cannot merge the 2 queries since they are called conditionally...so in some cases, it might be just 1, in other cases, both the queries can also get called... – copenndthagen Nov 15 '13 at 11:33
  • So I'll need to look at something which can be handled in the pagination java code – copenndthagen Nov 15 '13 at 11:34
  • Can you post all the relevant code, especially with the queries? – Eel Lee Nov 16 '13 at 20:58