AEM SQL 2 Query Java | Community
Skip to main content
manoj_devapath
manoj_devapath
Level 5
April 29, 2019
Solved

AEM SQL 2 Query Java

  • April 29, 2019
  • 5 replies
  • 12867 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by antoniom5495929

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

5 replies

antoniom5495929
antoniom5495929Accepted solution
Level 7
April 29, 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

manoj_devapath
manoj_devapathAuthor
Level 5
April 30, 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)

antoniom5495929
antoniom5495929
Level 7
April 30, 2019

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
    manoj_devapathAuthor
    Level 5
    April 30, 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

    arunpatidar
    Community Advisor
    arunpatidarCommunity Advisor
    Community Advisor
    April 30, 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

    Arun Patidar
      Terms & ConditionsAccessibility statement

      Loading footer...