It's all about the answers!

Ask a question

How to use order by clause in query using java api?


Nilesh Patil (1733050) | asked Oct 24 '13, 1:29 a.m.
edited Oct 27 '13, 1:34 p.m. by Geoffrey Clemm (30.1k23035)
I have situation where I want query RTC database with order by(descending order) clause using java client api. I want to get only two records(to be very precise one record only) whose value is less than my condition(Integer number).

Can anyone help on this please. Sample code snippet will be very helpful

Thanks in advance,
Nilesh Patil.
 

6 answers



permanent link
Nick Edgar (6.5k711) | answered Oct 25 '13, 4:18 p.m.
JAZZ DEVELOPER
edited Oct 25 '13, 4:19 p.m.
Ralph's references show how to use the Work Items API, which is a higher level wrapper around the Repository layer query APIs.  What kind of items are you querying for?
Generally you'd use one of the orderByAsc/Dsc methods on com.ibm.team.repository.common.query.IQuery, passing the query model field to order by.

Here's an example from the RTC Eclipse UI code that queries for build definitions in the selected project/team areas.

        IItemQuery itemQuery = IItemQuery.FACTORY.newInstance(IBuildDefinitionQueryModel.ROOT);


        // get the team areas for the connected project areas in the repo and

        // use them to narrow the search

        IProcessAreaHandle[] processAreaHandles = getProcessAreaHandles(repository, monitor);


        IItemHandleInputArg[] itemHandleArgs = new IItemHandleInputArg[processAreaHandles.length];

        for (int i = 0; i < processAreaHandles.length; i++) {

            itemHandleArgs[i] = itemQuery.newItemHandleArg();

        }


        // create the query

        IBuildDefinitionQueryModel buildDefinitionQueryModel = IBuildDefinitionQueryModel.ROOT;

        final IItemQuery query = IItemQuery.FACTORY.newInstance(buildDefinitionQueryModel);


        IPredicate filter = ((BuildDefinitionQueryModel) buildDefinitionQueryModel).processArea()._in(itemHandleArgs);

        query.filter(filter);

        query.orderByDscUsingLocale(buildDefinitionQueryModel.id());


        // get the results

        IItemManager itemManager = repository.itemManager();

        ItemQueryIterator<IBuildDefinitionHandle> iter = new ItemQueryIterator<IBuildDefinitionHandle>(

                ClientFactory.getTeamBuildClient(repository), itemQuery, new Object[0]);

        LinkedList<IBuildDefinition> buildDefinitions = new LinkedList<IBuildDefinition>();

        while (iter.hasNext(monitor)) {

            List<IBuildDefinitionHandle> definitionHandles = iter.next(IQueryService.ITEM_QUERY_MAX_PAGE_SIZE, monitor);

            buildDefinitions.addAll(itemManager.fetchCompleteItems(definitionHandles, IItemManager.DEFAULT, monitor));

        }

        Iterator definitionsIter = buildDefinitions.iterator();

        while (definitionsIter.hasNext()) {

            Object next = definitionsIter.next();

            if (next == null) {

                definitionsIter.remove();

            }

        }

        return buildDefinitions.toArray(new IBuildDefinition[buildDefinitions.size()]);


Comments
Nick Edgar commented Oct 27 '13, 12:55 p.m. | edited Oct 27 '13, 12:55 p.m.
JAZZ DEVELOPER

It's important to use input args for the parts of the query that change, otherwise the changing values are embedded in the generated SQL statement, making it different each time, and thus defeating the DB's SQL statement cache.


ItemQueryIterator is an internal helper in the Build code.  It's a thin wrapper on:

com.ibm.team.build.client.ITeamBuildBaseClient.queryItems(IItemQuery, Object[], int, IProgressMonitor)

com.ibm.team.build.client.ITeamBuildBaseClient.fetchPage(UUID, int, int, IProgressMonitor)

where ITeamBuildBaseClient is the super-interface of ITeamBuildClient.
These add progress monitoring to the corresponding methods on com.ibm.team.repository.common.service.IQueryService


permanent link
Ralph Schoon (61.5k33643) | answered Oct 24 '13, 6:38 a.m.
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER
Please see my new addition in http://rsjazz.wordpress.com/2012/11/19/using-expressions-for-automation/ and also see
https://jazz.net/forum/questions/13870/querying-for-sorted-list-of-quotunresolvedquot-work-item

permanent link
sam detweiler (12.5k6189201) | answered Jan 28 '14, 11:44 p.m.
I know I am late to the party..

but I don't understand how this code can work.

      IProcessAreaHandle[] processAreaHandles = getProcessAreaHandles(repository, monitor);


        IItemHandleInputArg[] itemHandleArgs = new IItemHandleInputArg[processAreaHandles.length];

        for (int i = 0; i < processAreaHandles.length; i++) {

            itemHandleArgs[i] = itemQuery.newItemHandleArg();

        }


the actual process area handles to use for the filter are never supplied.

there is space (for size=...) allocated as part of the query definition,


the filter says 'in this list'
IPredicate filter = ((BuildDefinitionQueryModel) buildDefinitionQueryModel).processArea()._in(itemHandleArgs);

but the actual data (processAreaHandles) is not used in the actual query


  ItemQueryIterator<ibuilddefinitionhandle> iter = new ItemQueryIterator<ibuilddefinitionhandle>(

                ClientFactory.getTeamBuildClient(repository), itemQuery, new Object[0]);


Comments
Nick Edgar commented Jan 29 '14, 9:45 a.m. | edited Jan 29 '14, 9:48 a.m.
JAZZ DEVELOPER

You're absolutely right. The problem is that the code is buggy.  It has two queries: itemQuery and query. The latter has filters for process area, but it's the former that's actually run, so it selects all build definitions in the repo.


sam detweiler commented Jan 29 '14, 10:12 a.m. | edited Jan 29 '14, 10:28 a.m.

thanks.. I thought I was crazy (or you guys had some really magic sauce!)


I am doing this in a plugin.. find all workitems in this project area with approvals in state=x (then for users=Y)

I 'think' I have to GET the workitems with approvals in state=x, 
but the workitems might have OTHER approvals too.. so have to then loop thru the workitems and its approvals to get JUST the approvals in pending state,
THEN get their contributor entries.. AND compare to the list of contributors I care about.  
I am trying to write a balancing routine.. given many users in Role X, balance the approvals over them, rather than all to all, or all to one. 

but it doesn't look like you can query approvals only.
I really only want pending approvals for user set. (in this project area, which is why I cared about the project area above)


Nick Edgar commented Jan 29 '14, 2:20 p.m. | edited Jan 29 '14, 2:20 p.m.
JAZZ DEVELOPER

but it doesn't look like you can query approvals only.

That's correct. Approvals are 'helper' children on the main WorkItem item, and so are not queryable directly. I don't see any better way than what you describe.  The time to fetch the work items could be reduced significantly by doing partial fetches though, e.g. fetching just the id, state, resolution, and approvals (and maybe approval descriptors).  Avoid fetching description and comments. You might even avoid fetching summary until you've found your matches. IWorkItem has constants for these property names.

You can also batch fetches, which is recommended in general (max 2048 at a time, but query pages are normally 512 max so you're OK there).


permanent link
Nick Edgar (6.5k711) | answered Jan 29 '14, 9:46 a.m.
JAZZ DEVELOPER
The fix (plus improved progress monitoring), in com.ibm.team.build.internal.ui.dialogs.definitions.FetchBuildDefinitionsInConnectedProjectAreasJob.fetchBuildDefinitions(ITeamRepository, IProgressMonitor), looks like:

        SubMonitor subMonitor = SubMonitor.convert(monitor, 4);

        

        // get the team areas for the connected project areas in the repo and

        // use them to narrow the search

        IProcessAreaHandle[] processAreaHandles = getProcessAreaHandles(repository, subMonitor.newChild(1));


        // create the query

        IBuildDefinitionQueryModel buildDefinitionQueryModel = IBuildDefinitionQueryModel.ROOT;

        final IItemQuery query = IItemQuery.FACTORY.newInstance(buildDefinitionQueryModel);



        IItemHandleInputArg[] itemHandleArgs = new IItemHandleInputArg[processAreaHandles.length];

        for (int i = 0; i < processAreaHandles.length; i++) {

            itemHandleArgs[i] = query.newItemHandleArg();

        }


        IPredicate filter = ((BuildDefinitionQueryModel) buildDefinitionQueryModel).processArea()._in(itemHandleArgs);

        query.filter(filter);

        query.orderByDscUsingLocale(buildDefinitionQueryModel.id());


        // get the results

        IItemManager itemManager = repository.itemManager();

        ItemQueryIterator<IBuildDefinitionHandle> iter = new ItemQueryIterator<IBuildDefinitionHandle>(

                ClientFactory.getTeamBuildClient(repository), query, processAreaHandles);

        LinkedList<IBuildDefinition> buildDefinitions = new LinkedList<IBuildDefinition>();

        float size = iter.size(subMonitor.newChild(1));

        subMonitor.setWorkRemaining((int) (3 * Math.ceil(size / IQueryService.ITEM_QUERY_MAX_PAGE_SIZE)));

        while (iter.hasNext(subMonitor.newChild(1))) {

            List<IBuildDefinitionHandle> definitionHandles = iter.next(IQueryService.ITEM_QUERY_MAX_PAGE_SIZE, subMonitor.newChild(1));

            buildDefinitions.addAll(itemManager.fetchCompleteItemsPermissionAware(definitionHandles, IItemManager.DEFAULT, subMonitor.newChild(1)).getRetrievedItems());

        }

        return buildDefinitions.toArray(new IBuildDefinition[buildDefinitions.size()]);



permanent link
sam detweiler (12.5k6189201) | answered Jan 29 '14, 3:01 p.m.
edited Jan 29 '14, 3:07 p.m.
 thanks.. (hate this forum for limit on comment size)  I would only need approvals and their descriptors from a workitem, nothing else.. I am just trying to get a count of pending approvals for workitems in this project area.. expect ~100 maybe max. for the users returned in the role selected in this project area.. maybe 10 users..  I'm going to compare UUIDs so I don't have to load the contributor records either. 

I think this is what it looks like (not working, just written)

IQueryPage qpage =  fQueryService.queryItems(query, new IProjectArea[]{(IProjectArea) workItem.getProjectArea()}, IQueryService.ITEM_QUERY_MAX_PAGE_SIZE);
List<IWorkItemHandle> selectedWorkitemhandles = new ArrayList<IWorkItemHandle>();
while(qpage.hasNext())
{
selectedWorkitemhandles.addAll(((IItemQueryPage) qpage).getItemHandles());  
qpage =  fQueryService.fetchPage(qpage.getToken(), qpage.getNextStartPosition(), IQueryService.ITEM_QUERY_MAX_PAGE_SIZE);
}
List<IWorkItem> selectedWorkItems = (List<IWorkItem>) fRepositoryService.fetchItemsPermissionAware(selectedWorkitemhandles.toArray(new IWorkItemHandle[1]), new String[]{"test"}).getRetrievedItems();
for(IWorkItem wi:selectedWorkItems){
selectedApprovals.addAll(wi.getApprovals().getContents());
}

Comments
Nick Edgar commented Jan 29 '14, 4:42 p.m.
JAZZ DEVELOPER

Looks ok as a sketch. You'll want to use selectedWorkitemhandles.size() instead of 1, and give the actual property names.  It's fetchPartialItemsPermissionAware for partial fetch.  If you do the fetch inside the paging loop, you'll avoid the 2048 limit for max items fetched at once.



Nick Edgar commented Jan 29 '14, 4:44 p.m. | edited Jan 29 '14, 4:45 p.m.
JAZZ DEVELOPER

Actually, qpage.hasNext() will be false if the results all fit in one page. Try:

while (qpage != null) {
  …
  qpage = qpage.hasNext() ? <fetch next page> : null
}


Nick Edgar commented Jan 29 '14, 4:46 p.m.
JAZZ DEVELOPER

There are also some internal query iterators that help do the paging for you, e.g. com.ibm.team.build.internal.client.iterator.ItemQueryIterator<T extends IItemHandle>


Of course, I'd never recommend you use internals :).


sam detweiler commented Jan 29 '14, 5:00 p.m.

repositoryitemservice doesn't have a fetchPartialItemsPermissionaware 


Nick Edgar commented Jan 29 '14, 8:16 p.m.
JAZZ DEVELOPER

Sorry, I assumed you were on the client side, using IItemManager. On the server side, you can use com.ibm.team.repository.service.IRepositoryItemService.fetchItemsPermissionAware(IItemHandle[], String[]) 


permanent link
sam detweiler (12.5k6189201) | answered Jan 29 '14, 5:41 p.m.
edited Jan 29 '14, 8:57 p.m.
| edit: watch out for the code you copy !..

from https://jazz.net/library/article/1118

there are proper literal ids.. of course
private static final String PENDING = "com.ibm.team.workitem.approvalState.pending";
private static final String REJECTED = "com.ibm.team.workitem.approvalState.rejected";
private static final String APPROVED = "com.ibm.team.workitem.approvalState.approved";
		
	
working code
<code>
    private List<IApproval> querySpecificApprovalsinProjectArea( IProjectAreaHandle ProjectArea, String ApprovalState)
    {
        // save space for the list of approvals found
        List<IApproval> selectedApprovals = new ArrayList<IApproval>();
        // create the query model, aka workitems
        BaseWorkItemQueryModel workItems = BaseWorkItemQueryModel.WorkItemQueryModel.ROOT;
        // setup one condition of the query
        // approvals in pending state
        IStringField state = workItems.internalApprovalDescriptors().cumulativeStateIdentifier(); //.internalApprovals().stateIdentifier();

        IPredicate ApprovalStateFilter = state._eq(ApprovalState);

        IItemQuery query = IItemQuery.FACTORY.newInstance((IItemQueryModel) workItems);   
        // setup space for the project area args in the query
        IItemHandleInputArg[] itemHandleArgs = new IItemHandleInputArg[]{query.newItemHandleArg()};
       
        // the second condition for the query, in the specified project area
        IPredicate projectAreaFilter = workItems.projectArea()._in(itemHandleArgs);
        // increment the counter for this approver
        // combine the conditions both satisfied
        IPredicate CombinedFilter =ApprovalStateFilter._and(projectAreaFilter);

        // set the filter on the query   
        query.filter(CombinedFilter);
        try
        {
            // allocate space for the returned workitems
            List<IWorkItem> selectedWorkItems = new ArrayList<IWorkItem>();
           
            // execute the query, passing in the projectarea specific info 
            IQueryPage qpage =  fQueryService.queryItems(query, new IProjectAreaHandle[]{ProjectArea}, IQueryService.ITEM_QUERY_MAX_PAGE_SIZE);

            do
            {
                // if there are some results on this page
                if(qpage.getSize()>0)
                {
                    // loop thru all the returned workitems
                    for(IWorkItem wi: (List<IWorkItem>)fItemService.fetchItemsPermissionAware((IItemHandle[]) (((IItemQueryPage) qpage).getItemHandles()).toArray(new IItemHandle[1]), new String[]{IWorkItem.APPROVALS_PROPERTY,IWorkItem.APPROVAL_DESCRIPTORS_PROPERTY}).getRetrievedItems())
                    {
                        // get their approvals
                        for(IApproval ap: wi.getApprovals().getContents())
                        {
                            // only copy the pending  state approvals
                            // workitem might contain multiple
                            if(ap.getStateIdentifier().equalsIgnoreCase(ApprovalState))
                                selectedApprovals.add(ap);
                        }
                    }
                }           
                if(qpage.hasNext())
                    qpage =  fQueryService.fetchPage(qpage.getToken(), qpage.getNextStartPosition(), IQueryService.ITEM_QUERY_MAX_PAGE_SIZE);
                else
                    break;
            } while(true);
         }
        catch (TeamRepositoryException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return selectedApprovals;
    }
</code>


Comments
Nick Edgar commented Jan 29 '14, 8:19 p.m.
JAZZ DEVELOPER

So you got it working?


sam detweiler commented Jan 29 '14, 8:39 p.m. | edited Jan 29 '14, 8:46 p.m.

yes, thank you very much!..

corrected and working code above.

the balancing calculation code wasn't that hard either

now I should create an aspect editor to support managing the config data..

ouch, my head hurts!...


Nick Edgar commented Jan 30 '14, 8:39 a.m.
JAZZ DEVELOPER

My pleasure, good to hear.  FYI, this kind of majority vote type thing has been requested before. See the venerable Work item voting (22990) and its dups. My understanding is it's a pretty common thing in some change management methodologies, especially in the Systems space.

Your answer


Register or to post your answer.