TOPLINK SORTING: How to

-- all code tested on Jdeveloper 10.1.3
One of the first queries I had when I started working with Toplink was how to get sorted results. Here are few techniques that may help newbies (especially the tip at the end regarding child collections sorting).

All example will be given using a two table schema:
CREATE TABLE ACCOUNTS (
LOGIN VARCHAR2(10) NOT NULL PRIMARY KEY,
PASSWORD VARCHAR2(20),
CREATED DATE);

CREATE TABLE LOGINS(
ACCOUNTS_LOGIN VARCHAR2(10) NOT NULL PRIMARY KEY, -- FK pointing to ACCOUNT->LOGIN
LOGIN_TIME DATE NOT NULL PRIMARY KEY,
LOGOUT_TIME DATE,
COMPUTER VARCHAR2(30));

ALTER TABLE LOGINS ADD CONSTRAINT
LOGINS_ACCOUNTS_FK FOREIGN KEY(ACCOUNTS_LOGIN)REFERENCES ACCOUNTS(LOGIN) ENABLE;

For rest of the article, I will assume you have generated pojo using toplink over above mentioned tables.

Sorting Toplink Named Queries
Named queries can be sorted using “AfterLoad” technique.

Let say you have a readAllquery findAllLoginsOf that takes a parameter login (String). A expression is defined as 1. accountsLogin EQUAL login. This query will return all logins by a particular user - without sorting. To add sorting do this:

1. Create static function (This can be any class e.g. MyAfterLoads)
public static void afterLoadLogins(ClassDescriptor descriptor){ // Again, name of method is immaterial.
ReadAllQuery raq=(ReadAllQuery)descriptor.getDescriptorQueryManager().getQuery("findAllLogins");// Default query generated using Toplink POJO creations
raq.addAscendingOrdering("loginTime");
raq=(ReadAllQuery)descriptor.getDescriptorQueryManager().getQuery("findAllLoginsOf");// Our query
raq.addAscendingOrdering("loginTime");
}

2. In Structure window right click on the Logins descriptor. (You will need to first select Toplink map file using the application navigator first).

3. Through the context menu choose Advanced Properties -> After Load

4. Point to the class MyAfterLoads and choose from the drop-down your method. Note: In earlier versions of jdev 10.1.3 there is a bug. So if your method des not appear in the list you may need to open up the class MyAfterLoads choose “Reformat”.

That is all. Note that you can have different sorting for different named queries.

Sorting Toplink Direct Queries
In case you writing Toplink queries directly in java then there is a way for sorting there as well. Taking the same example i.e. findAllLoginsOf, a common way to query it will be:

// LIST (A)
Session session=getSessionFactory().acquireSession();
ExpressionBuilder builder=new ExpressionBuilder();
Expression exp=builder.get("accountsLogin").equal(user);
List account=(List)session.readAllObjects(Logins.class,exp);

However this will return unsorted list. To sort you will need to use the following code. Notice that instead of readAllObject now I am using executeQuery. Bold is the different part in list A & list B.

// LIST (B)
Session session=getSessionFactory().acquireSession();
ExpressionBuilder builder=new ExpressionBuilder();
Expression exp=builder.get("accountsLogin").equal(user);
ReadAllQuery query=new ReadAllQuery(builder);
query.setReferenceClass(Logins.class);
query.addAscendingOrdering("loginTime");
query.setSelectionCriteria(exp);
List logins=(List)session.executeQuery(query);

Sorting Java Entities
You can always do sorting using java Collections i.e. implement Comparable in your POJO (In our case that would be class Logins). We will have something like this after implementing

public int compareTo(Object o){
Logins that = (Logins) o;
return this.getLoginTime().compareTo(that.getLoginTime());
}

Now when you have your collection then all you need to do is to pass it to Collections.sort(List). So list A can be modified as (modification in bold):

Session session=getSessionFactory().acquireSession();
ExpressionBuilder builder=new ExpressionBuilder();
Expression exp=builder.get("accountsLogin").equal(user);
List account=(List)session.readAllObjects(Logins.class,exp);
logins = (List)Collections.sort(account);

The main strength (or weakness) of this particular java technique is that you have only one sorting criteria per table. There are other java techniques as well that give you more options in this regard.

Sorting Child Collection
Use above mentioned technique (i.e. implementing Comparable) to sort child collections.
Accounts account;
//accounts initialization code
//.....
List logins = account.getLoginsCollection();
logins = (List)Collections.sort(account);