Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Sorting of hierarchical entities in EclipseLink(using @Order By or JPQL doesn't seem to work )
Sorting of hierarchical entities in EclipseLink [message #1034762] Fri, 05 April 2013 21:36 Go to next message
Michael Burger is currently offline Michael BurgerFriend
Messages: 2
Registered: April 2013
Junior Member
Hello,
I have a simple hierarchical schema that looks like this:

CATEGORY 1:many PROPERTY 1:many PROPERTY_VALUE

I am successfully retrieving the entire tree of values from the root CATEGORY entity and would like to retrieve each subordinate PROPERTY_VALUE list in sorted order. A column in the database called ORDER_NBR has been created in order to provide a sort field.

I have tried both the @ORDER BY annotation on the PROPERTY entity as well as adding an ORDER BY clause to each named query that fetches a collection of PROPERTY_VALUEs. Neither of these seem to get the collection in any consistent order, let alone the expected ascending order.

Here is an excerpt from the log:

[EL Info]: 2013-04-05 15:42:17.036--ServerSession(621683982)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.3.2.v20111125-r10461
[EL Fine]: 2013-04-05 15:42:20.192--Thread(Thread[main,5,main])--Detected database platform: org.eclipse.persistence.platform.database.oracle.Oracle11Platform
[EL Config]: 2013-04-05 15:42:20.223--ServerSession(621683982)--Connection(2101902664)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(...
...
Driver: Oracle JDBC driver Version: 11.2.0.1.0
[EL Info]: 2013-04-05 15:42:25.77--ServerSession(621683982)--Thread(Thread[main,5,main])--file:/D:/Workspaces/AIC_Java_Framework_3x_POC/AIC JAVA FW Test/classes/_ConfigSourceDB_SE login successful
[EL Fine]: 2013-04-05 15:42:25.832--ServerSession(621683982)--Connection(117311230)--Thread(Thread[main,5,main])--SELECT NAMESPACE_NM, CATEGORY_NM FROM ZYUSRJFWDV01.FT_CATEGORY WHERE (NAMESPACE_NM = ?)
bind => [com.config.test.propertylist.sort]
...
[EL Fine]: 2013-04-05 15:42:26.082--ServerSession(621683982)--Connection(117311230)--Thread(Thread[main,5,main])--SELECT PROPERTY_NM, CATEGORY_NM, NAMESPACE_NM FROM ZYUSRJFWDV01.FT_PROPERTY WHERE ((NAMESPACE_NM = ?) AND (CATEGORY_NM = ?))
bind => [com.config.test.propertylist.sort, categoryWithPropertyList]
...
[EL Fine]: 2013-04-05 15:42:26.176--ServerSession(621683982)--Connection(117311230)--Thread(Thread[main,5,main])--SELECT ORDER_NBR, PROPERTY_VALUE_NM, CATEGORY_NM, NAMESPACE_NM, PROPERTY_NM FROM ZYUSRJFWDV01.FT_PROPERTY_VALUE WHERE ((NAMESPACE_NM = ?) AND ((PROPERTY_NM = ?) AND (CATEGORY_NM = ?))) ORDER BY ORDER_NBR ASC
bind => [com.config.test.propertylist.sort, propertyList, categoryWithPropertyList]

Here you can see each successive SELECT as it works it way down the hierarchy with the final SELECT including the ORDER BY clause on the PROPERTY_VALUE table fetch. Yet when the final result is inspected, the PROPERTY_VALUE list is still out of order. I am attaching my entity classes as well as the persistence.xml.

Any thoughts would be appreciated.
Thanks


Re: Sorting of hierarchical entities in EclipseLink [message #1037389 is a reply to message #1034762] Tue, 09 April 2013 13:47 Go to previous message
Michael Burger is currently offline Michael BurgerFriend
Messages: 2
Registered: April 2013
Junior Member
I have since found the cause of this issue. It stems from the JPA entity classes that are generated by Rational Application Developer(Eclipse IDE). When the entities were created as part of the JPA wizard based on the DB schema, the one-to-many [collection]relationship was stored as a java Set type which does NOT support sorting and is more concerned with uniqueness. Once I changed the collection type to List from Set in the entity classes, the ORDER BY worked.
Previous Topic:[Closed] EclipseLink.Examples.student.web JPARSApplication cannot be instantiated
Next Topic:em.merge() creates 2 insert statements
Goto Forum:
  


Current Time: Mon Dec 22 05:52:13 GMT 2014

Powered by FUDForum. Page generated in 0.01582 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software