How to use order by clause in query using java api?
Can anyone help on this please. Sample code snippet will be very helpful
Thanks in advance,
Nilesh Patil.
6 answers
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
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)
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,
IPredicate filter = ((BuildDefinitionQueryModel) buildDefinitionQueryModel).processArea()._in(itemHandleArgs);
but the actual data (processAreaHandles) is not used in the actual query
ClientFactory.getTeamBuildClient(repository), itemQuery, new Object[0]);
Comments
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.
thanks.. I thought I was crazy (or you guys had some really magic sauce!)
> but it doesn't look like you can query approvals only.
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()]);
Comments
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.
Actually, qpage.hasNext() will be false if the results all fit in one page. Try:
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>
repositoryitemservice doesn't have a fetchPartialItemsPermissionaware
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[])
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;
}
Comments
So you got it working?
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!...
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.