Ordering By Mapping Table Value in Hibernate

Question

I have a @ManyToMany mapping where the table self-references through a mapping table, and we want to order on an order id in the actual mapping table, but are finding it difficult to configure this.

We could perform it in hibernate xml, so it is natural to assume the support is there in JPA annotations. Does anybody know how we can order on a value in the mapping table?

The table is:

wap_site_components

intid
strname
intcomponentdef
dtmcreated      
intcustomer 

and the mapping table that self-references is:

wap_site_component_relations

intid     
intparent (references intid in wap_site_components)
intchild  (references intid in wap_site_components)
intorder (this is the value we want to order the collection on)

In Hibernate Annotations we have:

@ManyToMany (fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable (name = "wap_site_component_relations", 
    joinColumns = {@JoinColumn (name = "intparent", referencedColumnName = "id") }, 
    inverseJoinColumns = {@JoinColumn (name = "intchild", referencedColumnName = "id") })
public Set<WapComponent> getChildren() {
    return children;
}

This is how we performed it in hibernate xml:

        <set
          name="children"
          table="wap_site_component_relations"
          lazy="true"
          cascade="none"
          sort="unsorted"
          order-by="intorder"
          mutable="false"
        >
          <cache 
            usage="read-only" 
          />

          <key
            column="intparent"
          >
          </key>

          <many-to-many
            class="se.plusfoursix.im46.data.wap.WapComponent"
            column="intchild"
            outer-join="auto"
           />

       </set>

So we want to use the @OrderBy tag but cannot reference the intorder value in the mapping table. Any ideas? Thanks.

(We have tried an @OrderBy(intorder) over the children collection in code but that hasn't worked out for us)

Answer 1

You could create an annotated object to represent the link table, then use @oneToMany to map from the parent to the childlink and then to the child

@Entity
public class Parent {
   @id
   Long id;

   @OneToMany(targetEntity = ChildLink.class) 
   Set<ChildLink> childLinks;
}

public class ChildLink {

    @Id
    @OrderBy
    Long orderBy;

    @ManyToOne
    Set<Parent> parents;

    @ManyToOne
    Set<Child> children;
}

Just a rough example. You can then programatically produce the set children from the set of childLinks, perhaps in the getChildren method of the parent class.