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

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.

CodePudding user response：

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);
    }
}