Insert data into relationship table (many-to-many) with Spring boot

Question

I made a Spring Boot application which contains some users. These users can belong to 0, one or many groups (I omitted some lines of code for a better visualisation):

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @ManyToMany
    @JoinTable(
            name = "group_user",
            joinColumns = {@JoinColumn(name = "user_id")},
            inverseJoinColumns = {@JoinColumn(name = "group_id")}
    )
    private List<Group> groups = new ArrayList<>();

    public User(String name, List<Group> groups) {
        this.name = name;
        this.groups = groups;
    }
}

A group can contain 0, one or many users.

@Entity
public class Group {

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

    private String name;

    @ManyToMany(mappedBy = "groups")
    private List<User> users = new ArrayList<>();

    public Group(String name, List<User> users) {
        this.name = name;
        this.users = users;
    }
}

I'm using MySQL, and I have created 3 tables:

CREATE TABLE user (
    id integer NOT NULL AUTO_INCREMENT, 
    name varchar(255), 
    PRIMARY KEY (id)
);

CREATE TABLE group (
    id integer NOT NULL AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
);

CREATE TABLE group_user (
    user_id int NOT NULL,
    group_id int NOT NULL,
    PRIMARY KEY (user_id, group_id),
    KEY group_id (group_id),
    CONSTRAINT group_user_ibfk_1
    FOREIGN KEY (user_id) REFERENCES user (id),
    CONSTRAINT group_user_ibfk_2
    FOREIGN KEY (group_id) REFERENCES group (id)
);

I managed to link an user to a group by creating this new User, passing to its constructor a group, and calling my userDao.save() method:

userDao.save(new User(name, groups));

Now I want to edit my created user and make him belong to another group. How can I do this without creating a new user?

For example, I have this user, who is in no group:

INSERT INTO user VALUES(1, 'Jordan');

And these groups:

INSERT INTO group VALUES(1, 'Group 1');
INSERT INTO group VALUES(2, 'Group 2');

Now, how can I (in Java), link my user to Group 1 and Group 2?

score 4 · Accepted Answer · answered May 12 '18 at 13:19

You have to select a group entity from the database with the id of your choice. You either use the JPA interface for that entity with the method findById or create your own custom method in the interface.

When you have the entity for the group, you add it on to the User entity field groups. After that you save the user entity.

The same process goes to the other side group in a bi-directional relationship.

Recommended reading:

score 0 · Answer 2 · answered May 12 '18 at 12:55

Create table like as:

mysql> CREATE TABLE users (
    ->     id integer NOT NULL AUTO_INCREMENT, 
    ->     name varchar(255), 
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.24 sec)

mysql> CREATE TABLE groups (
    ->     id integer NOT NULL AUTO_INCREMENT,
    ->     name varchar(255),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TABLE group_users (
    ->     user_id int NOT NULL,
    ->     group_id int NOT NULL,
    ->     PRIMARY KEY (user_id, group_id),
    ->     CONSTRAINT group_user_ibfk_1
    ->     FOREIGN KEY (user_id) REFERENCES users(id),
    ->     CONSTRAINT group_user_ibfk_2
    ->     FOREIGN KEY (group_id) REFERENCES groups(id)
    -> );
Query OK, 0 rows affected (0.23 sec)

Inserting dummy data into it:

mysql> INSERT INTO user VALUES(1, 'Jordan');
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO users VALUES(1, 'Jordan');
Query OK, 1 row affected (0.08 sec)

mysql> 
mysql> INSERT INTO groups VALUES(1, 'Group 1');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO groups VALUES(2, 'Group 2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into group_users values(1,1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into group_users values(1,2);
Query OK, 1 row affected (0.08 sec)

Actual Data in tables look like:

mysql> select * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | Jordan |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from groups;
+----+---------+
| id | name    |
+----+---------+
|  1 | Group 1 |
|  2 | Group 2 |
+----+---------+
2 rows in set (0.00 sec)

mysql> select * from group_users;
+---------+----------+
| user_id | group_id |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
+---------+----------+
2 rows in set (0.00 sec)

Data after joined the all of the tables will look like.

mysql> select u.id user_id, u.name user_name,g.id group_id, g.name group_name from users u, groups g, group_users gu where u.id = gu.user_id and g.id = gu.group_id;
+---------+-----------+----------+------------+
| user_id | user_name | group_id | group_name |
+---------+-----------+----------+------------+
|       1 | Jordan    |        1 | Group 1    |
|       1 | Jordan    |        2 | Group 2    |
+---------+-----------+----------+------------+
2 rows in set (0.05 sec)

Hope, you cleared with the design, and how it will work in your case.

Insert data into relationship table (many-to-many) with Spring boot

2 Answers2

Create table like as:

Inserting dummy data into it:

Actual Data in tables look like:

Data after joined the all of the tables will look like.