CODEWITHSUNDEEP
javajpacollectionssql-order-byrelational
Sérgio Andaluz Ramos
Sérgio Andaluz Ramos

Reputation: 113

JPA @OrderBy() Through Relational Table

I need some help with the JPA Framework. I've read some answers "kind of" about this topic but I couldn't reach any conclusion.

First heres an examplo of the design i'm wooking with.

@BusinessObject
public class ClassA {

    @Column(name = "ID", nullable = false)
    private Long id;

    @OneToMany(mappedBy = "classAAttr")
    private Collection<ClassAB> classABCollection;

    //STUFF AND OTHER COLUMNS.....
}

public class ClassAB {

    @Column(name = "ID", nullable = false)
    private Long id;

    @JoinColumn(name = "TABLE_A_ID", referencedColumnName = "ID")
    @ManyToOne
    private ClassA classAAttr;

    @JoinColumn(name = "TABLE_B_ID", referencedColumnName = "ID")
    @ManyToOne
    private ClassB classBAttr;

    //STUFF AND OTHER COLUMNS.....
}

@BusinessObject
public class ClassB {

    @Column(name = "ID", nullable = false)
    private Long id;

    @Column(name = "ORDERCLAUSE", nullable = false)
    private String orderClause;

    //STUFF AND OTHER COLUMNS.....
}

So I need to order the classABCollection in ClassA by the orderClause attribute in ClassB, but I can't find the right @OrderBy() clause AND/OR location for it.

I've read some things about the Comparator Interface but, unfortunately, due to business policy, I need to be sure that there is no other way...

How Should I Do It?

Thank you guys in advance.

Upvotes: 1

Views: 3756

Answers (2)

DevYumi
DevYumi

Reputation: 1

So - I found a roundabout. A query like one below wont work:

select * from A a  where xyz 
order by a.reference or a.reference.id

However, I found that by adding a function, we can make the query work: (Do note, the reference need not be null.. and use appropriate values.)

select * from A a  where xyz 
order by coalesce(a.reference , 0)

Upvotes: 0

Alan Hay
Alan Hay

Reputation: 23226

The API docs for @OrderBy note:

The property or field name must correspond to that of a persistent property or field of the associated class or embedded class within :

http://docs.oracle.com/javaee/6/api/javax/persistence/OrderBy.html

so sorting AB in A by a property of B is not possible.

The alternatives are to write a query or do an in memory sort by some means. Hibernate, for example, has an @Sort annotation which you can use to apply an in-memory sort on load, either by having the target Entity implement Comparable or by specifying a Comparator:

See section 2.4.6.1:

http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/

Upvotes: 2

Related Questions