aspose file tools*
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
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: OrderBy with ElementCollection doesn't work