Sorting Rows in Memory
Question
I would like to sort rows of a ViewObject in memory and present it as another ViewObject. Is there a way to do that?
Answer
Generate custom ViewObject class and edit it like the following:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
package testp.kava.VO4.si51mt;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import oracle.jbo.Row;
import oracle.jbo.ViewObject;
import oracle.jbo.server.QueryCollection;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
/*
* This is an example ViewObject class that sorts data in memory.
*
* It uses another VO (of the same VO class) to fetch data in
* unsorted way. This VO is referred to as the unsorted base VO.
* Reference to this VO is held in mUnsortedBaseVO.
*
* Rows are drawn from mUnsortedBaseVO and sorted based on the
* values of the attribute specified by mNameOfAttrToSort.
*/
public class si51mtEmpViewImpl extends ViewObjectImpl
implements testp.kava.VO4.si51mt.common.si51mtEmpView
{
/*
* mSortData is flag indicating whether this VO instance
* will sort data or not. When this VO instance is the
* unsorted base VO, this flag will be false.
*
* Otherwise it is true. Note that if this VO is
* an unsorted base VO, data is retrieved from database
* just like a normal VO.
*/
private boolean mSortData = true;
/*
* Reference to the unsorted base VO.
*/
private ViewObject mUnsortedBaseVO = null;
/*
* Specifies the name of the attribute to sort by.
*/
private String mNameOfAttrToSort = "EmpNum";
public si51mtEmpViewImpl()
{
}
public void setNameOfAttrToSort(String attrName)
{
mNameOfAttrToSort = attrName;
}
void setSortData(boolean b)
{
mSortData = b;
}
/*
* This routine sorts data.
*/
private void sortData(Object qc)
{
/* Retrieve unsorted rows from the unsorted base VO */
Row[] rows = mUnsortedBaseVO.getAllRowsInRange();
ArrayList rowIndicesArr = new ArrayList(rows.length + 1);
for (int j = 0; j < rows.length; j++)
{
Row row = rows[j];
/*
* For each row, get the sort attr value.
* We do linear (slow) sort. You can replace this block
* with fancier sort algorithm.
*/
Object attrVal = row.getAttribute(mNameOfAttrToSort);
int k = 0;
for ( ; k < rowIndicesArr.size(); k++)
{
int indx = ((Integer) rowIndicesArr.get(k)).intValue();
Row rowToComp = rows[indx];
Object attrValToCompare = rowToComp.getAttribute(mNameOfAttrToSort);
/* Handling of nulls. Note that nulls will show up first */
if (attrVal == null)
{
if (attrValToCompare != null)
{
break;
}
}
else /* if (attrVal != null) */
{
if (attrValToCompare != null)
{
/* Both attrVal and attrValToCompare are not null. Do comparison */
if (attrVal instanceof String)
{
if (((String) attrVal).compareTo((String) attrValToCompare) < 0)
{
break;
}
}
else if (attrVal instanceof Integer)
{
if (((Integer) attrVal).intValue() < ((Integer) attrValToCompare).intValue())
{
break;
}
}
else if (attrVal instanceof BigDecimal)
{
if (((BigDecimal) attrVal).compareTo((BigDecimal) attrValToCompare) < 0)
{
break;
}
}
}
}
}
/* Then, store index to the row in sorted order */
rowIndicesArr.add(k, new Integer(j));
}
/* Put sorted index iterator as the user-data for this QC */
setUserDataForCollection(qc, rowIndicesArr.iterator());
}
public void remove()
{
/* Remove the unsorted base VO if we have one */
if (mUnsortedBaseVO != null)
{
mUnsortedBaseVO.remove();
}
}
protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams)
{
/*
* Check to see if executeQueryForCollection() is being called
* for the unsorted base VO of not. If so, we simply go to super
* to execute and retrieve data from database in the usual way (no sort).
*/
if (mSortData)
{
if (mUnsortedBaseVO == null)
{
/*
* Create the unsorted base VO. Use a unique name.
*/
mUnsortedBaseVO = getApplicationModule().createViewObject(getName() + "__UnsortedBaseVO", getDefFullName());
/* Set the SortData flag to false for the unsorted base VO */
((si51mtEmpViewImpl) mUnsortedBaseVO).setSortData(false);
/* Set the range size to -1 to retrieve all rows */
mUnsortedBaseVO.setRangeSize(-1);
}
sortData(qc);
}
super.executeQueryForCollection(qc, params, noUserParams);
}
protected boolean hasNextForCollection(Object qc)
{
/* For the unsorted base VO, use base impl behavior */
if (mSortData == false)
{
return super.hasNextForCollection(qc);
}
else
{
/* For sorted VO, get the next row index from the index array */
Iterator iter = (Iterator) getUserDataForCollection(qc);
boolean hasMore = iter.hasNext();
if (hasMore == false)
{
setFetchCompleteForCollection(qc, true);
}
return hasMore;
}
}
protected void releaseUserDataForCollection(Object qc, Object data)
{
super.releaseUserDataForCollection(qc, data);
}
protected ViewRowImpl createRowFromResultSet(Object qc,
ResultSet resultSet)
{
if (mSortData == false)
{
return super.createRowFromResultSet(qc, resultSet);
}
else
{
/* For sorted VO, create a new row and then copy the entity array */
Iterator iter = (Iterator) getUserDataForCollection(qc);
if (iter.hasNext())
{
int indx = ((Integer) iter.next()).intValue();
ViewRowImpl rowBase = (ViewRowImpl) mUnsortedBaseVO.getRowAtRangeIndex(indx);
ViewRowImpl newRow = createNewRowForCollection(qc);
/*
* Note that we have to define a public method initializeEntities() and
* in the row subclass. See the discussion on si51mtEmpViewRowImpl
* below for details (initializeEntities() calls setEntities()).
*/
((si51mtEmpViewRowImpl) newRow).initializeEntities(rowBase.getEntities());
return newRow;
}
else
{
setFetchCompleteForCollection(qc, true);
return null;
}
}
}
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Generate custom ViewRow class, say si51mtEmpViewRowImpl, and add the following method:
public void initializeEntities(oracle.jbo.server.EntityImpl[] entities)
{
setEntities(entities);
}
We need this method because setEntities() is protected and not directly accessible from si51mtEmpViewImpl.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From the client I have test code like the following:
public void doProcess() throws Exception
{
/* Find ViewObject, an instance of si51mtEmpView */
ViewObject voEmp = myAM.findViewObject("Emp");
Row row;
/* Order by EmpJob */
((testp.kava.VO4.si51mt.common.si51mtEmpView) voEmp).setNameOfAttrToSort("EmpJob");
logln("Emps sorted by EmpJob");
int j = 0;
while ((row = voEmp.next()) != null)
{
logln("Emp " + j + ": " + row.getAttribute("EmpNum") + "/" + row.getAttribute("EmpName") + "/" +
row.getAttribute("EmpJob") + "/" + row.getAttribute("EmpSal") + "/" +
row.getAttribute("EmpMgr") + "/" + row.getAttribute("EmpComm"));
j++;
}
logSep(); /*--------------------------------------------------*/
/* Now sort by EmpName */
((testp.kava.VO4.si51mt.common.si51mtEmpView) voEmp).setNameOfAttrToSort("EmpName");
voEmp.executeQuery();
j = 0;
while ((row = voEmp.next()) != null)
{
logln("Emp " + j + ": " + row.getAttribute("EmpNum") + "/" + row.getAttribute("EmpName") + "/" +
row.getAttribute("EmpJob") + "/" + row.getAttribute("EmpSal") + "/" +
row.getAttribute("EmpMgr") + "/" + row.getAttribute("EmpComm"));
j++;
}
logSep(); /*--------------------------------------------------*/
/* By Salary */
((testp.kava.VO4.si51mt.common.si51mtEmpView) voEmp).setNameOfAttrToSort("EmpSal");
voEmp.executeQuery();
j = 0;
while ((row = voEmp.next()) != null)
{
logln("Emp " + j + ": " + row.getAttribute("EmpNum") + "/" + row.getAttribute("EmpName") + "/" +
row.getAttribute("EmpJob") + "/" + row.getAttribute("EmpSal") + "/" +
row.getAttribute("EmpMgr") + "/" + row.getAttribute("EmpComm"));
j++;
}
logSep(); /*--------------------------------------------------*/
/* By Commission */
((testp.kava.VO4.si51mt.common.si51mtEmpView) voEmp).setNameOfAttrToSort("EmpComm");
voEmp.executeQuery();
j = 0;
while ((row = voEmp.next()) != null)
{
logln("Emp " + j + ": " + row.getAttribute("EmpNum") + "/" + row.getAttribute("EmpName") + "/" +
row.getAttribute("EmpJob") + "/" + row.getAttribute("EmpSal") + "/" +
row.getAttribute("EmpMgr") + "/" + row.getAttribute("EmpComm"));
j++;
}
logSep(); /*--------------------------------------------------*/
}
Using Sorted VO for Detail in Master-Detail
If the sorted VO is to play the role of the detail VO in a master-detail relationship (specifically data-model master-detail), then you need to make the following modifications to the VO's executeQueryForCollection(). The code blocks surrounded by "MASTER-DETAIL BEGIN" and "MASTER-DETAIL END" have been added:
protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams)
{
/*
* Check to see if executeQueryForCollection() is being called
* for the unsorted base VO of not. If so, we simply go to super
* to execute and retrieve data from database in the usual way (no sort).
*/
if (mSortData)
{
if (mUnsortedBaseVO == null)
{
/*
* Create the unsorted base VO. Use a unique name.
*/
mUnsortedBaseVO = getApplicationModule().createViewObject(getName() + "__UnsortedBaseVO", getDefFullName());
// MASTER-DETAIL BEGIN
mUnsortedBaseVO.setWhereClause("DEPTNO = :1");
// MASTER-DETAIL END
/* Set the SortData flag to false for the unsorted base VO */
((si51mtEmpViewImpl) mUnsortedBaseVO).setSortData(false);
/* Set the range size to -1 to retrieve all rows */
mUnsortedBaseVO.setRangeSize(-1);
}
// MASTER-DETAIL BEGIN
mUnsortedBaseVO.setWhereClauseParam(0, params[0]);
mUnsortedBaseVO.executeQuery();
// MASTER-DETAIL END
sortData(qc);
}
super.executeQueryForCollection(qc, params, noUserParams);
}
By adding where-clause and setting where-clause param for the unsorted base VO, the unsorted data is now filtered by the where-clause appropriate for the ViewLink (this example assumes that the ViewLink links DEPTNO of Dept and Emp).
When master (Dept) navigates, the framework will trigger a call to executeQueryForCollection. This in turn will set the appropriate where-clause parameter for the unsorted base. Then, sortData() sorts the filtered rows.
If your ViewLink involves more than one attribute, you need to set where-clause that has more (as many as the number of attrs involved in the ViewLink) bind variables. Also, setWhereClause() needs to be called for each of these bind vars.
For example, if the ViewLink links
Dept's DEPTNO to Emp's DEPTNO and
Dept's LOC to Emp's EMPLOC
the appropriate where-clause would be
mUnsortedBaseVO.setWhereClause("DEPTNO = :1 AND EMPLOC = :2");
and the appropriate setWhereClauseParam calls would be
mUnsortedBaseVO.setWhereClauseParam(0, params[0]);
mUnsortedBaseVO.setWhereClauseParam(1, params[1]);
2:41:22 PM 
|
