CODEWITHSUNDEEP
javahibernatemany-to-manyone-to-many
chillworld
chillworld

Reputation: 4277

hibernate ManyToMany with an order in jointable

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 multiple ParticipantMovements with the order of the move inside them.

The legs come from a Transport what will do different legs, ex a plane who flies from Brussel to Paris(leg 1) then Paris to New York (leg 2) and From New York back to Brussels (leg3).

We can have Participants step in in each startpoint and step out each end point.
Example : participant 1 flies leg 1(order 1) and leg 2(order 2).
Another will fly leg 2(order 1) and leg 3(order 2).
In addition to have the order correct for each participant the most logic is to put the order in the joinTable.

I do not have to see the order in Java but I need to persist it correct and also retrieve the List in correct order.

Is this the only solution or can I have a list in correct order also(and how do I persist it correctly)? :

ParticipantMove : 

@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()

Upvotes: 3

Views: 2131

Answers (1)

Petar Butkovic
Petar Butkovic

Reputation: 1900

You can use @OrderColumn index. It will persist in your join table, same index as it's in ArrayList or any other list. It will keep order form 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()

Last one will append order by clause to your sql query.

Hope it helps.

Upvotes: 4

Related Questions