Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

Store query JCR Sql2 in a list or an array

Avatar

Level 2

hello I have this query and I would like to be able to store this query in a list or an array to carry out some processes with them here is the one that I have

resourceResolver = resourceResolverFactory.getAdministrativeResourceResolver(null);
Session session = resourceResolver.adaptTo(Session.class);
Workspace workspace = session.getWorkspace();
QueryManager queryManager = workspace.getQueryManager();
Query query = queryManager.createQuery("SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/projectGames/games])",Query.JCR_SQL2);
QueryResult result = query.execute();

NodeIterator nodeIterator = result.getNodes();


JSONArray arrayResult = new JSONArray();
JSONArray arrayGame = new JSONArray();
arrayResult.put(nodeIterator);
JSONObject game = new JSONObject();
for (int i = 0 ; i < arrayResult.length() ; i++)

{

    trying to store the data in json array
}

or if you could give me an example of a better way to do it, I hope you have made me understand and I appreciate all the help because I am a little new in the sling issues

1 Accepted Solution

Avatar

Correct answer by
Level 10

That looks correct - you need to iterate through the result set as you have done. A common pattern - ie:

//Setup the query to get all employee nodes

         sqlStatement = "SELECT * FROM [nt:unstructured] AS t WHERE ISDESCENDANTNODE('/content/employees')And contains (status, 'employee')";

                       

        javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");

           

        //Execute the query and get the results ...

        javax.jcr.query.QueryResult result = query.execute();

           

        //Iterate over the nodes in the results ...

        javax.jcr.NodeIterator nodeIter = result.getNodes();

           

        while ( nodeIter.hasNext() ) {

           

          //For each node-- create an Employee instance

            employee = new Employee();

                   

         javax.jcr.Node node = nodeIter.nextNode();

                     

         //Set all Employee object fields

         employee.setName(node.getProperty("name").getString());

         employee.setAddress(node.getProperty("address").getString());

         employee.setPosition(node.getProperty("job").getString());

         employee.setAge(node.getProperty("age").getString());

         employee.setDate(node.getProperty("start").getString());

         employee.setSalary(node.getProperty("salary").getString());

                     

          //Push the Employee Object to the list

         employList.add(employee);

          }

View solution in original post

3 Replies

Avatar

Correct answer by
Level 10

That looks correct - you need to iterate through the result set as you have done. A common pattern - ie:

//Setup the query to get all employee nodes

         sqlStatement = "SELECT * FROM [nt:unstructured] AS t WHERE ISDESCENDANTNODE('/content/employees')And contains (status, 'employee')";

                       

        javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");

           

        //Execute the query and get the results ...

        javax.jcr.query.QueryResult result = query.execute();

           

        //Iterate over the nodes in the results ...

        javax.jcr.NodeIterator nodeIter = result.getNodes();

           

        while ( nodeIter.hasNext() ) {

           

          //For each node-- create an Employee instance

            employee = new Employee();

                   

         javax.jcr.Node node = nodeIter.nextNode();

                     

         //Set all Employee object fields

         employee.setName(node.getProperty("name").getString());

         employee.setAddress(node.getProperty("address").getString());

         employee.setPosition(node.getProperty("job").getString());

         employee.setAge(node.getProperty("age").getString());

         employee.setDate(node.getProperty("start").getString());

         employee.setSalary(node.getProperty("salary").getString());

                     

          //Push the Employee Object to the list

         employList.add(employee);

          }

Avatar

Level 10

Even if you are using the AEM QUeryBuilder API - you still need to iterate through the result set:

// create query description as hash map (simplest way, same as form post)

                Map<String, String> map = new HashMap<String, String>();

            

             // create query description as hash map (simplest way, same as form post)

                          

                map.put("path", "/content/employees");

                map.put("type", "nt:unstructured");

                map.put("p.limit", "20"); // same as query.setHitsPerPage(20) below

                               

                Query query = builder.createQuery(PredicateGroup.create(map), session);

                                 

                query.setStart(0);

                query.setHitsPerPage(20);

                         

                SearchResult result = query.getResult();

                int hitsPerPage = result.getHits().size();

               

                // iterating over the results

                for (Hit hit : result.getHits()) {

                   

                    //For each node-- create an Employee instance

                    employee = new Employee();

                   

               

                    javax.jcr.Node node = hit.getNode();

                       

                     //Set all Employee object fields

                     employee.setName(node.getProperty("name").getString());

                     employee.setAddress(node.getProperty("address").getString());

                     employee.setPosition(node.getProperty("job").getString());

                     employee.setAge(node.getProperty("age").getString());

                     employee.setDate(node.getProperty("start").getString());

                     employee.setSalary(node.getProperty("salary").getString());

                                

                      //Push the Employee Object to the list

                     employList.add(employee);

                      }