Sorting results when using TopLink ObjectLevelReadQuery conformresultsinunitofwork()

This article explains odd TopLink behaviors when  sorting query results that includes both in memory as well as persistent data from database.

As you know, conformresultsinunitofwork enables the query to merge results from database with those in-memory objects. For instance, the following data exist in EMPLOYEE table:
name                type
-----              ------
employee 1    contractor
employee 2    contractor

And in the session of the same transaction, there are newly created Employee objects "emplyee 3" and "emplyee 4", also contractors. If we want to find all the contractor employees including the ones in the memory, we should use conformresultsinunitofwork(), and the query result will be:
"employee 3"
"employee 2"
"employee 1"
"employee 4"

If the query result is sorted by name, like
ExpressionBuilder builder = new ExpressionBuilder();

You would expect that the result be:
"employee 1"
"employee 2"
"employee 3"
"employee 4"

You are wrong! The end result is not actually sorted!!! TopLink will actually just send the query to Oracle
server, which executes it and sorts the result by name, then TopLink will again add the in-memory objects on the top of collection returned from database:
"employee 3" (in-memory)
"employee 4" (in-memory)
"employee 1" (from DB)
"employee 2" (from DB)

Note that the in memory objects are not sorted at all, but the ones from DB are. The reason it behaves like this, is that Oracle server has no idea about the newly created objects. The solution is to just implement the query without sorting, and then sort results in memory using Java. Just remember that you don't sort it twice: once in Oracle server and once in your Java code.

No comments: