Search code examples
hibernatejpamultiple-columnsone-to-many

JPA Join table with multiple columns for different collections

I have the following db structure (security_margin is One to Many to security_margin_service_model):

enter image description here

I Have the following code in a jpa Entity called SecurityMargin where i try to model a join table for the three entities (security_margin, model and service)

@Column
@OneToMany
@JoinTable(name = "security_margin_service_model",
        joinColumns = {@JoinColumn(name = "margin_id")},
        inverseJoinColumns = {@JoinColumn(name = "service_id")})
List<Service> services = new ArrayList<>();

@Column
@OneToMany
@JoinTable(name = "security_margin_service_model",
        joinColumns = {@JoinColumn(name = "margin_id")},
        inverseJoinColumns = {@JoinColumn(name = "model_id")})
List<Model> models = new ArrayList<>();

When i persist the entity SecurityMargin for I would expect to see something like this:

margin_id service_id model_id
e7f90b99-674c-4975-b5c0-4525d2587e12 67cabe71-2a77-4bab-b6d4-35980e626123 48

and instead I get them stored in two different rows.

margin_id service_id model_id
e7f90b99-674c-4975-b5c0-4525d2587e12 48
e7f90b99-674c-4975-b5c0-4525d2587e12 67cabe71-2a77-4bab-b6d4-35980e626123

Not sure how to tell hibernate not to call the insert twice in this case...I'm lost!

Any help really appreciated. Thanks!

Solution

  • The result you are getting is expected since you have two independent collections which just happen to use the same table for their mapping. Let's assume you have more than one service and one model then that can lead to a lot of combinations: ServiceMargin (sm_id1): service: [ 1, 2 ] model: [ 1 ]

    should it result in

    [ sm_id1, 1, 1 ]
[ sm_id1, 2, null ]

    or

    [ sm_id1, 1, null ]
[ sm_id1, 2, 1 ]

    or

    [ sm_id1, 1, null ]
[ sm_id1, 2, null ]
[ sm_id1, null, 1 ]

    The simplest way to achieve what you are asking for is to create one more entity that will be mapped to the security_margin_service_model table and have a single pair of Service/Model in it. Then instead of having two collections in SecurityMargin you'd have a single collection of this new entity.

    But that only makes sense if there's some sort of relation between the service and model pair saved within the same SecurityMargin. Otherwise, consider having separate tables security_margin_service and security_margin_model.