How to get n:m tables?-CodePudding

I have a spring-boot application with a MySql database. I have a many-to-many relationship between a pizza table and a topping table so I've made an extra table where I store these relationships. The tables:

CREATE TABLE topping (
    id SERIAL PRIMARY KEY,
    topping_name VARCHAR(64) NOT NULL,
    price INT NOT NULL,
    spicy bool NOT NULL
);

CREATE TABLE pizza (
    id SERIAL PRIMARY KEY,
    pizza_name VARCHAR(64) NOT NULL
);

CREATE TABLE pizza_with_topping (
    pizza_id BIGINT UNSIGNED NOT NULL,
    topping_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT pizza_with_topping_ibfk_1
    FOREIGN KEY(pizza_id)
        REFERENCES pizza(id),
    CONSTRAINT pizza_with_topping_ibfk_2
    FOREIGN KEY(topping_id)
        REFERENCES topping(id)
);

In spring-boot I found that I have to extend the CrudRepository interface and I can call the findAll() method from this to get the contents of a table. At the momment I get the contents of all 3 tables like so:

Iterable<Pizza> pizzasInDb = pizzaRepository.findAll();
Iterable<Topping> toppingsInDb = toppingRepository.findAll();
Iterable<PizzaWithTopping> pizzaToppingConnectionTable = pizzaWithToppingRepository.findAll();

After this based on these 3 tables I manually create objects that contain both the pizza's name and it's toppings. Since I have set foreign keys in pizza_with_topping table I was wondering if there is a better way to get this model? Maybe with the call of built-in functions that automatically makes this model object for me based on the foreign keys.

CodePudding user response：

Yes, there is. You can model your domain with many-to-many relationships using JPA. If you are using annotation this cam be achieve using @ManyToMany There is an example here