Order by using intermediate table column in JPA

Question

I have Class Product (corresponding to PRODUCT table)

Class Product {
     @Id
     @Column(name = "PRODUCT_ID")
     private Long id;

     @Column(name = "NAME")
     private String name;

     //other attributes
}

I have another class Owner (corresponding to OWNER table)

Class Owner{
     @Id
     @Column(name = "OWNER_ID")
     private Long id;

     @Column(name = "NAME")
     private String name;
}

I have an intermediate table named PRODUCT_OWNER (a product can have multiple owners).

The PRODUCT_OWNER table is something like this.

 _______________________________________
| PRODUCT_ID | OWNER_ID | DISPLAY_ORDER |
|____________|__________|_______________|

Inside my Product class I have written a join statement to perform join using intermediate PRODUCT_OWNER table

@OneToMany(targetEntity = Owner.class, cascade = { CascadeType.ALL, CascadeType.MERGE }, fetch = FetchType.LAZY)
@JoinTable(name = "PRODUCT_OWNER", 
           joinColumns = { @JoinColumn(name = "PRODUCT_ID") }, 
           inverseJoinColumns = { @JoinColumn(name = "OWNER_ID") })
private Set<Owner> productOwners = new HashSet<Owner>(0);

I have a column in my intermediate table as DISPLAY_ORDER. I want to sort the final Set productOwners using DISPLAY_ORDER. Is this possible in above approach. Please advice.

Please let me know if anything is not clear here, instead of downvoting. — Vivek, Mar 29 '16 at 09:02
Hi, the problem is that is doing a bad order or some exception? Anyway i think if your intermediate table have some extra column, maybe you could consider create an entity for it — cralfaro, Mar 29 '16 at 09:40
So where is this "DISPLAY_ORDER" being populated from? because JPA is not managing it. And if JPA is not managing it then you aren't going to order by it — Neil Stockton, Mar 29 '16 at 09:46
Edited my question. @cralfaro I want the final set order as per the intermediate table column "DISPLAY_ORDER". — Vivek, Mar 29 '16 at 12:41
And my reply still stands. You CANNOT have some arbitrary extra column in a join table that JPA doesn't know about and isn't populating. — Neil Stockton, Mar 29 '16 at 12:43
@NeilStockton well. JPA doesnt know about the PRODUCT_OWNER table either. Still we are able to use it to join 2 tables. FYI. I am new to JPA, still in learning phase. Please correct me if I am getting something wrong here. — Vivek, Mar 29 '16 at 12:47
Of course it _knows_ about the PRODUCT_OWNER table ... it's in your JPA metadata! I __have corrected you__ since you are getting something wrong here. You can't have arbitrary extra columns that WILL NOT BE POPULATED. If you want that column then you introduce a new Entity to represent the data in that table — Neil Stockton, Mar 29 '16 at 12:48
@Vivek maybe you could have a look on this link http://stackoverflow.com/questions/26051764/hibernate-manytomany-jointable-orderby-using-join-tables-field — cralfaro, Mar 29 '16 at 13:04
OK. Understood. Thanks for the help. I'll create an entity for intermediate table. — Vivek, Mar 29 '16 at 13:06

score 1 · Accepted Answer · answered Mar 29 '16 at 13:11

Hi you can try to add this to your JoinTable

@OneToMany(targetEntity = Owner.class, cascade = { CascadeType.ALL, CascadeType.MERGE }, fetch = FetchType.LAZY)
@JoinTable(name = "PRODUCT_OWNER", 
       joinColumns = { @JoinColumn(name = "PRODUCT_ID") }, 
       inverseJoinColumns = { @JoinColumn(name = "OWNER_ID") })
@OrderColumn(name="DISPLAY_ORDER")
private List<Owner> productOwners = new HashSet<Owner>(0);

Order by using intermediate table column in JPA

1 Answers1