I am running aem sql 2 query in java.
query : SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE([/content/apples/en]) AND s.[sling:resourceType] = 'apples/components/page/article' ORDER BY s.[published] DESC
I am using javax.jcr.query.Query query API to generate a query. I am also
Session session = resolver.adaptTo(Session.class);
QueryManager queryManager = session.getWorkspace().getQueryManager();
javax.jcr.query.Query query = queryManager.createQuery(sqlStatement, javax.jcr.query.Query.JCR_SQL2);
QueryResult result = query.execute();
In result i am able to getting 10 results with starting from offset. But if want actual full request set of the query Can you suggest me correct way to get it.
I am trying to implement pagination logic. in result set. I have to result back first 10 results and total count of the query.
Please suggest correct way to implement this using SQL2 query.
if you want to display all the result comes from the query you need to set the limit to -1 as described into the docs .
My suggestion is to avoid this usage because if you have a lot of element you can face some performance issue; the best way is to use a pagination in order to get all the elements by page.
Have a look to the docs  which is very helpful.
Let me know if you need some other details.
 Query Builder API
modify your query like below:
I think that is better for you to use the AEM API . By using AEM API you need to set the setHitsPerPage to 0.
 Query ("The Adobe AEM Quickstart and Web Application.")
I tried with predicate query. i see results set working but I cannot make sort order work.
I checked query but it does not say to set limit to -1.
Here is the API I am using.
Query (Content Repository for Java Technology API Version 2.0)
When I tried setting lmit to -1 Following error
java.lang.IllegalArgumentException: Limit may not be negative, is: -1 at org.apache.jackrabbit.oak.jcr.query.QueryImpl.setLimit(QueryImpl.java:139)