AEM SQL 2 Query Java

[manoj_devapath]

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);

query.setLimit(limit);

query.setOffset(offset);

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.

[antoniom5495929 · Accepted solution]

Hi,

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 [0].

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 [0] which is very helpful.

Let me know if you need some other details.

[0] Query Builder API

Thanks,

Antonio

[manoj_devapath]

Hi antoniom54959291​

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

Limit may not be negative, is: -1

Exception:

java.lang.IllegalArgumentException: Limit may not be negative, is: -1 at org.apache.jackrabbit.oak.jcr.query.QueryImpl.setLimit(QueryImpl.java:139)

[antoniom5495929]

Hi,

I think that is better for you to use the AEM API [0]. By using AEM API you need to set the setHitsPerPage to 0.

[0] Query ("The Adobe AEM Quickstart and Web Application.")

Thanks,

Antonio

[manoj_devapath]

I tried with predicate query. i see results set working but I cannot make sort order work.

path=/content/apples/en&property=sling:resourceType&property.value=apples/components/page/article&orderby=@jcr:content/cq:lastModified&orderby.sort=desc

Please advise

[arunpatidar]

modify your query like below:

path=/content/apples/en

type=cq:PageContent

property=sling:resourceType

property.value=apples/components/page/ article

orderby=@cq:lastModified

orderby.sort=desc

p.limit=-1