Skip to content

Many-To-Many with Extra Columns

Estimated Lesson Duration: 90mins

Learning Objectives

  • Know how to create a model to handle a join table with extra columns
  • Understand how to break down a many-to-many relationship into two one-to-manys
  • Know what JPA annotations to use in this scenario

Introduction

Currently, we have a many-to-many relationship between Chocolate and Estate which is handled in the database via our JPA annotations and Hibernate.

We do not explicitly create a model ChocolateEstate to manage the relationship, it is done for us. If we run our server and open Postico we can see this for ourselves (i.e. that we have a table called chocolates_estates).

Our class diagram and ERD would look like this:

classDiagram
    Chocolate -- Estate_Chocolate
    Estate -- Estate_Chocolate
    class Chocolate{
        - id : Long
        - name : String
        - cocoaPercentage : int
        @ManytoMany
        - estates : List < Estate >
        + getName()
        + setName()  // ...etc
    }
    class Estate_Chocolate{
        - chocolate : Chocolate
        - estate : Estate
    }
    class Estate{
        - id: Long
        - name : String
        - country : String
        @ManyToMany
        - chocolates : List < Chocolate >
        + getName()
        + setName() // ...etc
    }
erDiagram
    chocolates ||--o{ estates_chocolates : ""
    estates ||--o{ estates_chocolates : ""
    chocolates{
        INT id PK
        VARCHAR name
        INT cocoa_percentage
    }
    estates_chocolates{
        INT chocolate_id FK
        INT estate_id FK
    }
    estates{
        INT id PK
        VARCHAR name
        VARCHAR country
    }

This is fine ... but what if we want our join table to include more than estate_id and chocolate_id? Perhaps there are other properties relevant to that relationship that we would like to keep track of.

Adding Another Model

If we wish to include extra columns in the join table, we need to make another model in our Spring application.

Let's suppose we would like a CocoaOrder class that: - continues to match a chocolate to an estate - also includes some extra information such as batchNumber and pricePaid.

Although this is still a many-to-many relationship, we should break this down into two one-to-manys.

Over the course of this lesson, we will modify our code to follow these digrams:

classDiagram
    Chocolate -- CocoaOrder
    Estate -- CocoaOrder
    class Chocolate {
        - id : Long
        - name : String
        - cocoaPercentage : int
        - cocoaOrders : List < CocoaOrder >   // @OneToMany
    }
    class CocoaOrder {
        - id : Long
        - chocolate : Chocolate // @ManyToOne
        - estate : Estate // @ManyToOne
        - batchNumber : String
        - pricePaid : double
    }
    class Estate {
        - id : Long
        - name : String
        - country : String
        - cocoaOrders : List < CocoaOrder > // @OneToMany
    }

This should result in a database this structure:

erDiagram
    chocolates ||--o{ cocoa_orders : ""
    estates ||--o{ cocoa_orders : ""
    chocolates{
        INT id PK
        VARCHAR name
        INT cocoa_percentage
    }
    cocoa_orders{
        INT id PK
        INT chocolate_id FK
        INT estate_id FK
        VARCHAR batch_number
        NUMERIC price_paid
    }
    estates{
        INT id PK
        VARCHAR name
        VARCHAR country
    }

CocoaOrder

open your start code and check your server runs (ChocolateApplication > main())

We can begin by creating a new POJO class in our models package. Right click on models and create a java class called CocoaOrder, with properties of id, chocolate, estate, batchNumber and pricePaid.

// models/CocoaOrder.java

public class CocoaOrder {

    private Long id;
    private Chocolate chocolate;
    private Estate estate;
    private String batchNumber;
    private double pricePaid;

    public CocoaOrder(Chocolate chocolate, Estate estate, String batchNumber, double pricePaid){
        this.chocolate = chocolate;
        this.estate = estate;
        this.batchNumber = batchNumber;
        this.pricePaid = pricePaid;
    }

    public CocoaOrder(){

    }

    // GETTERS & SETTERS
    //...
}

Annotate your class correctly. As we now know, CocoaOrder will need to be used to create a corresponding cocoa_orers table in the database. Note that CocoaOrder is the many-to-one side of the relationship.

Many CocoaOrders can have one chocolate. A Chocolate could appear on multiple orders. This makes sense as a single chocolate's cocoa could come from several different estates.

Many CocoaOrders can have one estate. An Estate could appear on multiple orders. This means that an Estate could contribute cocoa to many different chocolates.

// models/CocoaOrder.java

@Entity (name = "cocoa_orders") // ADDED
public class CocoaOrder {

    @Id  // ADDED
    @GeneratedValue(strategy = GenerationType.IDENTITY) // ADDED
    private Long id;

    @JsonIgnoreProperties({ "cocoaOrders"}) // ADDED
    @ManyToOne // Many orders can have one chocolate  // ADDED
    @JoinColumn(name = "chocolate_id") // ADDED
    private Chocolate chocolate;

    @JsonIgnoreProperties({ "cocoaOrders"}) // ADDED
    @ManyToOne // Many orders can have one estate // ADDED
    @JoinColumn(name = "estate_id") // ADDED
    private Estate estate;

    @Column(name = "batch_number" // ADDED
    private String batchNumber;

    @Column(name = "price_paid") // ADDED
    private double pricePaid;

// ...
}

Note the name parameter is added to conform to SQL conventions for naming columns.

As we will be persiting CocoaOrder objects to the database, we can also expect that the CocoaOrder class will require a repository, a service and a controller.

// repositories/CocoaOrderRepository

@Repository
public interface CocoaOrderRepository extends JpaRepository<CocoaOrder, Long> {
}

// services/CocoaOrderService

@Service
public class CocoaOrderService(){

    @Autowired
    CocoaOrderRepository cocoaOrderRepository;

}

// controllers/CocoaOrderController

@RestController
@RequestMapping("cocoa-orders")
public class CocoaOrderController {

    @Autowired
    CocoaOrderService cocoaOrderService;

}

Modifying the Chocolate class

We can now consider how to modify Chocolate so that it works with our new set up. Let's change our estates property to cocoaOrders, and replace the @ManyToMany annotation with a @OneToMany.

Modify your constructor and remove getters and setters for the estates property.

// models/Chocolate.java

@Entity (name = "chocolates")
public class Chocolate {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column
    private String name;

    @Column(name = "cocoa_percentage")
    private int cocoaPercentage;

    @OneToMany (mappedBy = "chocolate") // MODIFIED
    private List<CocoaOrder> cocoaOrders;  // MODIFIED

    public Chocolate (String name, int cocoaPercentage){
        this.name = name;
        this.cocoaPercentage = cocoaPercentage;
        this.cocoaOrders = new ArrayList<>(); // MODIFIED
    }

    // ...

    // REMOVED getters and setters for 'estates'

    public List<CocoaOrder> getCocoaOrders() { // ADDED
        return cocoaOrders;
    }

    public void setCocoaOrders(List<CocoaOrder> cocoaOrders) {  // ADDED
        this.cocoaOrders = cocoaOrders;
    }

WHile we're here we should also remove the addEstate() and removeEstate() methods from this class and replace them with methods to add and remove a cocoaOrder from a chocolate.

// models/Chocolate.java

// ...

// REMOVED addEstate() and removeEstate()

    public void addCocoaOrder(CocoaOrder cocoaOrder){ // ADDED
        this.cocoaOrders.add(cocoaOrder);
    }

    public void removeCocoaOrder(CocoaOrder cocoaOrder){ //ADDED
        this.cocoaOrders.remove(cocoaOrder);
    }

// ...

Modifying the Estate class

We can now do the same with the Estate class. Here we also need to replace chocolates with cocoaOrders, change the relationship to @OneToMany and update our constructor and methods.

// models/Estate.java

@Entity
@Table(name = "estates")
public class Estate {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Long id;

    @Column
    private String name;

    @Column
    private String country;

    // @JsonIgnoreProperties({"estate"})
    @OneToMany(mappedBy = "estate") // MODIFIED
    private List<CocoaOrder> cocoaOrders; // MODIFIED

    public Estate(String name, String country) {
        this.name = name;
        this.country = country;
        this.cocoaOrders = new ArrayList<CocoaOrder>(); // MODIFIED
    }

    public Estate() {
    }

    public void addChocolate(Chocolate chocolate){
        this.chocolates.add(chocolate);
    }

    // REMOVED addChocolate() 
    // REMOVED getters and setters for 'chocolates'


    public void addCocoaOrder(CocoaOrder cocoaOrder){ // ADDED
        this.cocoaOrders.add(cocoaOrder);
    }

    public void removeCocoaOrder(CocoaOrder cocoaOrder){
        this.cocoaOrders.remove(cocoaOrder);
    }

// ...

Dataloader

It's probably a good idea at this point to amend our dataloader and review our tables in Postico.

Our set up is now different. Instead of creating an Estate object, a Chocolate object and adding the estate to the chocolate, we now need three types of object.

We should now: - create an Estate object - create a Chocolate object - create a CocoaOrder object that takes in both

Remove the contents of the run() method in your dataloader and paste in the following:

// components/Dataloader.java

// ...

        // RABOT ESTATE
        Estate rabotEstate = new Estate("Rabot Estate", "St Lucia");
        estateRepository.save(rabotEstate);

        Chocolate saltedDark = new Chocolate("Salted Dark", 70);
        chocolateRepository.save(saltedDark);

        CocoaOrder cocoaOrder1 = new CocoaOrder(saltedDark, rabotEstate, "RE254", 47.50);
        cocoaOrderRepository.save(cocoaOrder1);

        Chocolate supermilkHazelnut = new Chocolate("Supermilk Hazelnut", 40);
        chocolateRepository.save(supermilkHazelnut);

        CocoaOrder cocoaOrder2 = new CocoaOrder(supermilkHazelnut, rabotEstate, "RE255", 50.50);
        cocoaOrderRepository.save(cocoaOrder2);


//      HACIENDA ZOLITA
        Estate haciendaZolita = new Estate("Hacienda Zolita", "Ecuador");
        estateRepository.save(haciendaZolita);

        Chocolate ecuadorDark = new Chocolate("Ecuador Dark", 72);
        chocolateRepository.save(ecuadorDark);

        CocoaOrder cocoaOrder3 = new CocoaOrder(ecuadorDark, haciendaZolita, "HZ641", 50.10);
        cocoaOrderRepository.save(cocoaOrder3);

        Chocolate mintyLove = new Chocolate("Minty Love", 72);
        chocolateRepository.save(mintyLove);

        CocoaOrder cocoaOrder4 = new CocoaOrder(mintyLove, haciendaZolita, "HZ954", 42.00);
        cocoaOrderRepository.save(cocoaOrder4);

//      KUAPA KOKOO
        Estate kuapaKokoo = new Estate("Kuapa Kokoo", "Ghana");
        estateRepository.save(kuapaKokoo);

        Chocolate pinkSalt = new Chocolate("Dark Pink Himalayan Salt", 60);
        chocolateRepository.save(pinkSalt);

        CocoaOrder cocoaOrder5 = new CocoaOrder(pinkSalt, kuapaKokoo, "KA274", 55.20);
        cocoaOrderRepository.save(cocoaOrder5);

//      MIXED ORIGIN CHOCOLATE
        Chocolate toffeeTruffle = new Chocolate("Toffee Truffle", 55);
        chocolateRepository.save(toffeeTruffle);

        CocoaOrder cocoaOrder6 = new CocoaOrder(toffeeTruffle, rabotEstate, "RE274", 47.50);
        cocoaOrderRepository.save(cocoaOrder6);

        CocoaOrder cocoaOrder7 = new CocoaOrder(toffeeTruffle, haciendaZolita, "HZ204", 39.70);
        cocoaOrderRepository.save(cocoaOrder7);

// ...

Run the code and use Postman to review your database tables. We will not yet be able to use all the functions we should, but you will be able to see our new set up in the database.

Data Transfer Objects

While we're here, we can think about whether we might need a DTO or two (or three). DTOs are especially useful if our object contains other objects. This is the case for all our models, so we will likely need a DTO for each of them.

We can start by modifying the DTO we already have, ChocolateDTO. We should change our ChocolateDTO to expect a list of CocoaOrder ids. We will need getters and setter for this new property.

// models/ChocolateDTO.java

public class ChocolateDTO {

    private String name;
    private int cocoaPercentage;
    private List<Long> cocoaOrderIds; // MODIFIED

    public ChocolateDTO(String name, int cocoaPercentage, List<Long> cocoaOrderIds){ // MODIFIED
        this.name = name;
        this.cocoaPercentage = cocoaPercentage;
        this.cocoaOrderIds = cocoaOrderIds; // MODIFIED
    }

// ...
// REMOVE Getter/Setter for estateIds

    public List<Long> getCocoaOrderIds() { // ADDED
        return cocoaOrderIds;
    }

    public void setCocoaOrderIds(List<Long> cocoaOrderIds) { // ADDED
        this.cocoaOrderIds = cocoaOrderIds;
    }

This means that when we POST or UPDATE a chocolate, we could expect the request body to look something like this:

// POST localhost:8080/chocolates/{id}
{
    "name" : "Twix",
    "cocoaPercentage" : 35,
}

// PUT localhost:8080/chocolates/{id}
{
    "name" : "Twix",
    "cocoaPercentage" : 35,
    "cocoaOrderIds" : [1, 3]
}

Creating a DTO simplifies matters for us. If we just used the Chocolate model to process the request, our request body could look like this:

{
    "name" : "Twix",
    "cocoaPercentage" : 35,
    "cocoaOrderIds" : [
        {
            "id" : 1,
            "chocolate" : {
                "id" : 5,
                "name" : "Twix",
                "cocoaPercentage" : 35,
            },
            "estate" : {
                "id" : 2,
                "name" : "Hacienda Zolita",
                "country" : "Ecuador"
            },
            "batchNumber" : "HZ344",
            "pricePaid" : 45.89
        },
        {
            "id" : 3,
            "chocolate" : {
                "id" : 5,
                "name" : "Twix",
                "cocoaPercentage" : 35,
            },
            "estate" : {
                "id" : 2,
                "name" : "Rabot Estate",
                "country" : "St Lucia"
            },
            "batchNumber" : "RC671",
            "pricePaid" : 45.10
        }
    ]
}

The ChocolateDTO allows us to simply supply the id(s) of relevant CocoaOrders. Working this way also helps prevent mistakes, especially when working with more complicated relationships.

EstateDTO, CocoaOrderDTO

In this spirit, we should create a DTO for Estate and CocoaOrder to allow us to post our objects more easily.

Create a new class in models called EstateDTO. This class should allow us to process a request body that looks like this:

{
    "name" : "Rabot Estate",
    "country" : "St Lucia",
    "cocoaOrderIds" : [2]
}

// models/EstateDTO

public class EstateDTO {

    private String name;
    private String country;
    private List<Long> cocoaOrderIds;

    public EstateDTO(String name, String country, List<Long> cocoaOrderIds) {
        this.name = name;
        this.country = country;
        this.cocoaOrderIds = cocoaOrderIds;
    }

// ...include default constructor, getters and setters

Create a new class in models called CocoaOrderDTO. This class should allow us to process a request body that looks like this:

{
    "chocolateId": 2,
    "estateId" : 1,
    "batchNumber" : "RC491",
    "pricePaid" : 55.00
}

// models/CocoaOrderDTO
public class CocoaOrderDTO {

    private Long chocolateId;
    private Long estateId;
    private String batchNumber;
    private double pricePaid;

    public CocoaOrderDTO(Long chocolateId, Long estateId, String batchNumber, double pricePaid) {
        this.chocolateId = chocolateId;
        this.estateId = estateId;
        this.batchNumber = batchNumber;
        this.pricePaid = pricePaid;
    }
// ...include default constructor, getters and setters

Note: DTOs classes do not require annotations as these objects are not persisted to the database.

Returning to CocoaOrderService

Now that we have a CocoaOrderDTO, we can return to CocoaOrderService to flesh out its functionality.

As CocoaOrder is a model, we will (eventually) need this service to perform all the necessary CRUD functionality. For the moment, saveCocoaOrder(), findCocoaOrder(), findAllCocoaOrders(), getCocoaOrder() and deleteCocoaOrder() should suffice. 

// services/CocoaOrderService

@Service
public class CocoaOrderService(){

    @Autowired
    CocoaOrderRepository cocoaOrderRepository;

    @Autowired
    ChocolateService chocolateService;

    @Autowired
    EstateService estateService;

    public List<CocoaOrder> saveCocoaOrder(CocoaOrderDTO cocoaOrderDTO){
//        find chocolate
        Chocolate chocolate = chocolateService.findChocolate(cocoaOrderDTO.getChocolateId());
//        find estate
        Estate estate = estateService.findEstate(cocoaOrderDTO.getEstateId());

        CocoaOrder cocoaOrder = new CocoaOrder(chocolate, estate, cocoaOrderDTO.getBatchNumber(), cocoaOrderDTO.getPricePaid());
        cocoaOrderRepository.save(cocoaOrder);
        return cocoaOrderRepository.findAll();
    }

    public CocoaOrder findCocoaOrder(Long id){
        return cocoaOrderRepository.findById(id).get();
    }

    public List<CocoaOrder> findAllCocoaOrders(){
        return cocoaOrderRepository.findAll();
    }

    public void deleteCocoaOrder(Long id){
        cocoaOrderRepository.deleteById(id);
    }

}

Knitting our application back together

ChocolateService

We can now amend our code to process our new structure. First on our TODO list is ChocolateService. In this class we should note:

  • Our ChocolateService no longer needs access to the estateRepository as the connection to an Estate is now handled by our new CocoaOrder class
  • To avoid a circular dependency issue, we will inject a CocoaOrderRepository object instead of the service
  • We can tweak our updateChocolate() method by replacing the for-loop that searches for the correct estates with one that looks for cocoaOrders

Note: our saveChocolate() method can remain the same as 'brand-new' chocolates will not have associated cocoaOrders.

Trainer may wish to inject CocoaOrderService in first instance to demonstrate circular dependency

updateChocolate()

// services/ChocolateService

@Service
public class ChocolateService{

    @Autowired
    ChocolateRepository chocolateRepository;

    @Autowired  // ADDED
    CocoaOrderRepository cocoaOrderRepository;    // ADDED

// ...
    public Chocolate updateChocolate(ChocolateDTO chocolateDTO, Long id){
        Chocolate chocolateToUpdate = chocolateRepository.findById(id).get();
        chocolateToUpdate.setName(chocolateDTO.getName());
        chocolateToUpdate.setCocoaPercentage(chocolateDTO.getCocoaPercentage());
        chocolateToUpdate.setEstates(new ArrayList<Estate>());

        for (Long cocoaOrderId : chocolateDTO.getCocoaOrderIds()){  // ADDED
            CocoaOrder cocoaOrder = cocoaOrderRepository.findById(cocoaOrderId); // ADDED
            chocolateToUpdate.addCocoaOrder(cocoaOrder);    // ADDED
        }

        chocolateRepository.save(chocolateToUpdate);
        return chocolateToUpdate;
    }

    public void saveChocolate(ChocolateDTO chocolateDTO) { // AS BEFORE
        Chocolate chocolate = new Chocolate(chocolateDTO.getName(), chocolateDTO.getCocoaPercentage());
        chocolateRepository.save(chocolate);
    }

// ...
}

deleteChocolate()

We should also consider our deleteChocolate() method. Now that a chocolate may have associated cocoaOrders, our delete method should take care to delete these orders first, before deleting the chocolate.

// services/chocolateService

@Service
public class ChocolateService{

// ...

public void deleteChocolate(Long chocolateId){
        //        find chocolate
        Chocolate chocolate = findChocolate(chocolateId);   // ADDED
    //        delete associated cocoaOrders
        for (CocoaOrder cocoaOrder : chocolate.getCocoaOrders()){   // ADDED
            cocoaOrderRepository.deleteById(cocoaOrder.getId());    // ADDED
        }
        chocolateRepository.deleteById(chocolateId);
    }

}

EstateService

We can do the same with the EstateService class.

We should: - inject a CocoaOrderRepository object - tweak our updateEstate() method by replacing the for-loop that searches for the correct chocolates with one that looks for cocoaOrders. This method should also be amended to take an EstateDTO instead of estate. - tweak our deleteEstate() method to take care of any cocoaOrders it may have

// services/EstateService

@Service
public class EstateService {

    @Autowired
    EstateRepository estateRepository;

    @Autowired
    CocoaOrderRepository cocoaOrderRepository;

    public List<Estate> findAllEstates(){
        return estateRepository.findAll();
    }

    public Estate findEstate(Long id){
        return estateRepository.findById(id).get();
    }

    public void saveEstate(EstateDTO estateDTO){
        Estate estate = new Estate(estateDTO.getName(), estateDTO.getCountry());
        estateRepository.save(estate);
    }

    public void deleteEstate(Long estateId){
//        find estate
        Estate estate = findEstate(estateId);   // ADDED
//        delete associated cocoaOrders
        for (CocoaOrder cocoaOrder : estate.getCocoaOrders()){  // ADDED
            cocoaOrderRepository.deleteById(cocoaOrder.getId()); // ADDED
        }
        estateRepository.deleteById(estateId);
    }

    public Estate updateEstate(EstateDTO estateDTO, Long id){   // MODIFIED
        Estate estateToUpdate = estateRepository.findById(id).get();
        estateToUpdate.setName(estateDTO.getName());
        estateToUpdate.setCountry(estateDTO.getCountry());

// REMOVE:         estateToUpdate.setChocolates(estate.getChocolates());

        estateToUpdate.setCocoaOrders(new ArrayList<CocoaOrder>()); // ADDED
        for (Long cocoaOrderId : estateDTO.getCocoaOrderIds()){ // ADDED
            CocoaOrder cocoaOrder = cocoaOrderRepository.findById(cocoaOrderId).get();  // ADDED
            estateToUpdate.addCocoaOrder(cocoaOrder);   // ADDED
        }

        estateRepository.save(estateToUpdate);
        return estateToUpdate;
    }

}

Controllers

update EstateController to use an EstateDTO in the CREATE and UPDATE routes

update ChocolateController to use ChocolateDTO in the CREATE and UPDATE routes

Test these routes

return to CocoaOrderController and begin to create the following routes: INDEX, SHOW, CREATE, DELETE (and if you have time, UPDATE)

// controllers/CocoaOrderController


@RestController
@RequestMapping("cocoa-orders")
public class CocoaOrderController {

    @Autowired
    CocoaOrderService cocoaOrderService;

//    INDEX
    @GetMapping
    public ResponseEntity<List<CocoaOrder>> getAllCocoaOrders(){
        return new ResponseEntity<>(cocoaOrderService.findAllCocoaOrders(), HttpStatus.OK);
    }

//    CREATE
    @PostMapping
    public ResponseEntity<List<CocoaOrder>> createCocoaOrder(@RequestBody CocoaOrderDTO cocoaOrderDTO){
        cocoaOrderService.saveCocoaOrder(cocoaOrderDTO);
        return new ResponseEntity<>(cocoaOrderService.findAllCocoaOrders(), HttpStatus.CREATED);
    }

//    DELETE
    @DeleteMapping(value = "/{id}")
    public ResponseEntity<Long> deleteCocoaOrder(@PathVariable Long id){
        cocoaOrderService.deleteCocoaOrder(id);
        return new ResponseEntity<>(id, HttpStatus.OK);
    }

//    SHOW
    @GetMapping(value = "/{id}")
    public ResponseEntity<CocoaOrder> getChocolate(@PathVariable Long id){
        CocoaOrder foundCocoaOrder = cocoaOrderService.findCocoaOrder(id);
        return new ResponseEntity<>(foundCocoaOrder, HttpStatus.FOUND);
    }

}