0

I have 3 sql tables:

Account (ID (BIGINT),...)
Card (ID (BIGINT),...)
RelationAccountCard (ACCOUNT (BIGINT), CARD(BIGINT), QUANTITY(int))

One account can have multiple cards, one card can have multiple account

@Data
@Entity
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    @ManyToMany
    @JoinTable(
        name = "RELATION_ACCOUNT_CARD",
        joinColumns = @JoinColumn(name = "ACCOUNT"),
        inverseJoinColumns = @JoinColumn(name = "CARD"))
    private Set<Card> cardsOwned = new HashSet<>();
}


@Data
@Entity
public class Card {

    @Id
    private long id;

    @JsonIgnore
    @ManyToMany(mappedBy = "cardsOwned")
    private java.util.Set<Account> accounts;
}

This code above is working if the field "QUANTITY" in relationAccountCard didn't exist. I would like to make the code works knowing that I added the "QUANTITY" field. I tried for hours yesterday without success, do you have an idea of how to do this?

Basically I need to replace private Set<Card> cardsOwned; by private Set<RelationAccountCard> relationAccountCards; but I don't know how to

Christophe b
  • 14
  • 2
  • it is possible that the solution is here: https://stackoverflow.com/questions/5127129/mapping-many-to-many-association-table-with-extra-columns I'll try this – Christophe b Jan 11 '22 at 18:07
  • Your model doesn't show where the quantity value is coming from (or should come from). All you have is a list. would a map be a better representation of the relationship, with the entity being the key and quantity the value? This can be done with ElementCollection and @MapKeyJoinColumn for the join column def. There is no way to make it bidirectional though without making the relation table an entity. – Chris Jan 11 '22 at 19:31
  • I fixed my problem thanks to the stack overflow link above. thanks for your answer Chris, you are right, I had to make an entity RelationAccountCard. The quantity need to come from the RelationAccountCard entity. Basically, I added an ID in RelationAccountCard, and I removed the composite primary key, I added an unique constraint for (card, account) instead. Then after doing that, I just followed the answer on the link I provided above – Christophe b Jan 11 '22 at 20:19
  • Does this answer your question? [Mapping many-to-many association table with extra column(s)](https://stackoverflow.com/questions/5127129/mapping-many-to-many-association-table-with-extra-columns) – Alexey Veleshko Jan 19 '22 at 15:35
  • Alexey I tried this link and I didn't managed to make it work so I had to change my database as I explaied above – Christophe b Jan 30 '22 at 19:06

1 Answers1

-1

The solution was to delete the composite primary key. To make (card,account) unique, and to add an ID.

Once it was done, I simply followed the answer of this stackoverflow post:

Mapping many-to-many association table with extra column(s)

Christophe b
  • 14
  • 2