Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes Object Relational Mapping and the fly likes OrderBy with ElementCollection doesn't work Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "OrderBy with ElementCollection doesn Watch "OrderBy with ElementCollection doesn New topic
Author

OrderBy with ElementCollection doesn't work

Max Bogdanov
Greenhorn

Joined: Mar 12, 2011
Posts: 11

Hello,

I try to use OrderBy with ElementCollection:

Entity class:


Main class

persistence.xml


Program's output:
[EL Info]: 2011-06-17 09:41:11.338--ServerSession(1068896286)--EclipseLink, version: Eclipse Persistence Services - 2.2.0.v20110202-r8913
[EL Info]: 2011-06-17 09:41:11.662--ServerSession(1068896286)--file:/C:/Users/max/workspace/netbeans/OCE-JPAD/TestOrderBy/src/_mySqlJPA login successful
[EL Fine]: 2011-06-17 09:41:11.696--ServerSession(1068896286)--Connection(649045642)--CREATE TABLE PERSON (ID INTEGER NOT NULL, name VARCHAR(255), PRIMARY KEY (ID))
[EL Fine]: 2011-06-17 09:41:11.758--ServerSession(1068896286)--Connection(649045642)--CREATE TABLE Person_PHONENUMBERS (Person_ID INTEGER, PHONENUMBERS VARCHAR(255))
[EL Fine]: 2011-06-17 09:41:11.765--ServerSession(1068896286)--Connection(649045642)--ALTER TABLE Person_PHONENUMBERS ADD CONSTRAINT FK_Person_PHONENUMBERS_Person_ID FOREIGN KEY (Person_ID) REFERENCES PERSON (ID)
[EL Fine]: 2011-06-17 09:41:11.856--ClientSession(1741587908)--Connection(649045642)--INSERT INTO PERSON (ID, name) VALUES (?, ?)
bind => [2 parameters bound]
[EL Fine]: 2011-06-17 09:41:11.88--ClientSession(1741587908)--Connection(649045642)--INSERT INTO Person_PHONENUMBERS (Person_ID, PHONENUMBERS) VALUES (?, ?)
bind => [2 parameters bound]
[EL Fine]: 2011-06-17 09:41:11.881--ClientSession(1741587908)--Connection(649045642)--INSERT INTO Person_PHONENUMBERS (Person_ID, PHONENUMBERS) VALUES (?, ?)
bind => [2 parameters bound]
[EL Fine]: 2011-06-17 09:41:11.882--ClientSession(1741587908)--Connection(649045642)--INSERT INTO Person_PHONENUMBERS (Person_ID, PHONENUMBERS) VALUES (?, ?)
bind => [2 parameters bound]
[EL Fine]: 2011-06-17 09:41:11.883--ClientSession(1741587908)--Connection(649045642)--INSERT INTO Person_PHONENUMBERS (Person_ID, PHONENUMBERS) VALUES (?, ?)
bind => [2 parameters bound]
[EL Fine]: 2011-06-17 09:41:11.893--ServerSession(1068896286)--Connection(649045642)--SELECT ID, name FROM PERSON WHERE (ID = ?)
bind => [1 parameter bound]
[EL Fine]: 2011-06-17 09:41:11.901--ServerSession(1068896286)--Connection(649045642)--SELECT t0.PHONENUMBERS FROM Person_PHONENUMBERS t0 WHERE (t0.Person_ID = ?)
bind => [1 parameter bound]
Person{id=1, personName=Max, phoneNumbers={[1111111, 3333333, 5555555, 2222222]}}
[EL Info]: 2011-06-17 09:41:11.905--ServerSession(1068896286)--file:/C:/Users/max/workspace/netbeans/OCE-JPAD/TestOrderBy/src/_mySqlJPA logout successful


Please, give me same advise why OrderBy does not work? I think the output should be like:
Person{id=1, personName=Max, phoneNumbers={[5555555, 3333333, 2222222, 1111111]}}
As I understand the problem in the query:
SELECT t0.PHONENUMBERS FROM Person_PHONENUMBERS t0 WHERE (t0.Person_ID = ?) EclipseLink doesn't use order by clause, but I don't understand why.

Thanks a lot


OC-JA,OCP-JP,OCP-JWCD,OCE-EJBD,OCE-JPAD
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Please log a bug on EclipseLink for this, it does seem to be ignored.

As a workaround you could use a DescriptorCustomizer and amend the DirectCollectionMapping's DirectReadQuery with the order by Expression.

You could also use an OrderColumn instead.


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Max Bogdanov
Greenhorn

Joined: Mar 12, 2011
Posts: 11

James Sutherland wrote:Please log a bug on EclipseLink for this, it does seem to be ignored.

As a workaround you could use a DescriptorCustomizer and amend the DirectCollectionMapping's DirectReadQuery with the order by Expression.

You could also use an OrderColumn instead.

Thank you James. Yes I think it's bug too, I will log it. I tried hibernate 3.6.5 fully JPA 2.0 compliant and it works perfect. I prepare my self for JPA 2.0 certification, so the workaround isn't suitable at this time(I made example with OrderBy), but many thanks.
 
Don't get me started about those stupid light bulbs.
 
subject: OrderBy with ElementCollection doesn't work