How to use orderby on ID of mapping/third table in @ManyToMany relation in hibernate

Question

I have two main tables and one mapping table for many to many relationship. I want to apply @OrderBy("ID ASC") on PACKAGE_ACTION_MAPPING table Id column. But when I use @OrderBy("ID ASC"), sorting is applied on Int_Action Id column instead of PACKAGE_ACTION_MAPPING Id column.

CREATE TABLE INT_PACKAGE (
  ID BIGINT PRIMARY KEY AUTOINCREMENT,
  NAME VARCHAR (32) NOT NULL
);

CREATE TABLE INT_ACTION (
  ID BIGINT PRIMARY KEY AUTOINCREMENT,
  NAME VARCHAR (32) NOT NULL
);

CREATE TABLE PACKAGE_ACTION_MAPPING (
  ID BIGINT PRIMARY KEY AUTOINCREMENT,
  PACKAGE_ID BIGINT REFERENCES INT_PACKAGE(ID),
  ACTION_ID BIGINT REFERENCES INT_ACTION(ID)
);

Entity class in Java:

@Entity
@Table(name = "INT_PACKAGE")
public class IntPackage {
    @Id
    private Long id;
    private String name;

    @ManyToMany(cascade = { CascadeType.ALL })
    @JoinTable(name = "PACKAGE_ACTION_MAPPING", joinColumns = {
            @JoinColumn(name = "PACKAGE_ID") }, inverseJoinColumns = { @JoinColumn(name = "ACTION_ID") })
    @OrderBy("ID ASC")
    private List<IntAction> actions;

    //getters setters
}


@Entity
@Table(name = "INT_ACTION")
public class IntAction {
    @Id
    private Long id;
    private String name;

    @JsonIgnore
    @ManyToMany(mappedBy = "actions")
    private List<IntPackage> intPackage;

    //getter setters
}

Please suggest some way to achieve it.

score 1 · Accepted Answer · answered Oct 19 '21 at 12:52

1

Instead of sorting on the database I would recommend you to use a SortedSet instead and make the class comparable.

@Entity
@Table(name = "INT_PACKAGE")
public class IntPackage {
    @Id
    private Long id;
    private String name;

    @ManyToMany(cascade = { CascadeType.ALL })
    @JoinTable(name = "PACKAGE_ACTION_MAPPING", joinColumns = {
            @JoinColumn(name = "PACKAGE_ID") }, inverseJoinColumns = { @JoinColumn(name = "ACTION_ID") })
    private SortedSet<IntAction> actions;

    //getters setters
}


@Entity
@Table(name = "INT_ACTION")
public class IntAction implements Comparable<IntAction> {
    @Id
    private Long id;
    private String name;

    @JsonIgnore
    @ManyToMany(mappedBy = "actions")
    private List<IntPackage> intPackage;

    //getter setters

    public int compareTo(IntAction other) {
        return Integer.compare(id, other.id);
    }
}

answered Oct 19 '21 at 12:52

Christian Beikov

15,141
2
32
58

This solution only works because the property `id` is present on the entity itself. But it doesn't work if you have a dedicated order property on the join table – Blockost Apr 12 '22 at 10:14
1

If you have an order property on the join table, you will use a `List` with `@OrderColumn` which is ordered automatically by Hibernate/JPA, so no need for `@OrderBy` – Christian Beikov Apr 12 '22 at 10:25
You're right, this would work, I tried it. But I found that using `@OrderColumn` with a `List` introduces a lots of issues (e.g can't have `null` order value even though the column is nullable, duplicate order values are ignored, [Hibernate - @OrderColumn - removing item causes exception](https://stackoverflow.com/questions/41578970/hibernate-ordercolumn-removing-item-causes-exception)) so I try to stay away from it. I'm using `Hibernate 5.6.4` and all these issues (some are quite old) are still present... – Blockost Apr 12 '22 at 11:13
1

If you have multiple rows with he same order or null, how would you like Hibernate to behave? Some say this should be an error, others say it should do X or Y. Hibernate chose the sensible behavior, which is to expect the same kind of data it writes where duplicate or null order values are impossible. Not though, that the order can be temporarily null if you are using `@OneToMany(mappedBy = "...")`, but doesn't make sense in a join table. – Christian Beikov Apr 12 '22 at 12:44
I would like it to behave the same way as if I was doing the `order by` in SQL (which handles duplicate and null values). But you're right, it's matter of opinion and Hibernate devs choose one direction and that's probably fine for some people – Blockost Apr 12 '22 at 13:48
I don't know if this is just some UI logic that you are trying to encode or if there is an actual business logic connected to the ordering, but if you want to do this just for 1-2 UI screens, you might be better off using a DTO model where you can be in control of the query that is used. Blaze-Persistence Entity-Views can help you a lot here: https://github.com/Blazebit/blaze-persistence#entity-view-usage – Christian Beikov Apr 14 '22 at 06:20
It's an actual business need where I want my many-to-many relationships to be persisted and retrieved in the same order as they were selected (in a multiple ` – Blockost Apr 14 '22 at 09:42
If the order is important to you, then you should be using the `@OrderColumn` annotation. – Christian Beikov Apr 19 '22 at 09:03

How to use orderby on ID of mapping/third table in @ManyToMany relation in hibernate

1 Answers1