Hibernate ManyToMany with order in conjunction
I have the following DB setup:
T_PARTICIPANT_MOVEMENT
ParticipantMove_SID BigInt PK
Participant_SID BigInt FK
MoveType_SID BigInt FK
MoveReason Varchar(255)
is_Ops Bit
Order Integer
T_LEG
Leg_SID BigInt PK
StartRegion_SID BigInt FK
EndRegion_SID BigInt FK
PlannedStartDate Datetime
PlannedEndDate Datetime
TJ_PMOV_LEG
Leg_PMov_SID BigInt PK
Leg_SID BigInt FK
ParticipantMove_SID BigInt FK
Order Integer
Small explication: a participant has several participants-participants with the order of movement within them.
The legs come from transport that different legs will do, for example a plane that flies from Brussels to Paris (leg 1), then Paris to New York (leg 2) and from New York back to Brussels (leg3).
We can invite Members to enter each starting point and exit each end point.
Example: participant 1 flies leg 1 (order 1) and leg 2 (order 2).
The other will fly with leg 2 (order 1) and leg 3 (order 2).
Also, in order for the order to be correct for each participant, it is most logical to place the order in the joinTable.
I don't need to see the order in Java, but I need to store it correctly and also get the list in the correct order.
Is this the only solution or can I also have the list in the correct order (and how to store it)?
Participant:
@OneToMany @JoinTable(name="TJ_PMOV_LEG")
@MapKeyColumn(name"order")
private Map<Integer,Leg> getLegs()
Leg:
@OneToMany @JoinTable(name="TJ_PMOV_LEG")
@MapKeyColumn(name"order")
private Map<Integer,ParticipantMove> getMoves()
source to share
You can use an index @OrderColumn
. It will be stored in your junction table, the same index as the ArrayList or any other list. It will keep the order in the list.
@OneToMany
@JoinTable(name="TJ_PMOV_LEG")
@OrderColumn(name="order_idx")
private List<Leg> getLegs()
Also you can use @OrderBy annotation to order your collection over some column. i.e.
@OneToMany
@JoinTable(name="TJ_PMOV_LEG")
@OrderBy(value="lower(Order) asc") //"Order" is column name form T_PARTICIPANT_MOVEMENT table
private List<ParticipantMove> getMoves()
The latter will add an order by clause to your sql query.
Hope it helps.
source to share