AEM SQL 2 Query Java

mjb54261515 28-04-2019

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.

Accepted Solutions (1)

Accepted Solutions (1)

antoniom5495929 29-04-2019

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

Answers (4)

Answers (4)

Arun_Patidar
MVP
30-04-2019

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

mjb54261515 30-04-2019

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

mjb54261515 29-04-2019

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)