Skip to main content

Many-to-Many Save

xuejmnet9/21/25About 4 min

Many-to-Many Save

A very special kind of save, because both self and target are aggregate roots and neither can affect the other's table. They are associated through a mapping table. Many-to-many has many modes:

  • Many-to-many with mapping table, and the mapping table has no business properties, such as user, user_role, role
  • Many-to-many with mapping table, and the mapping table has business properties. For example, company, company_user, user. Companies and users have an employment relationship. For example, outsourced personnel and a certain company. A company can employ multiple outsourced personnel, and multiple outsourced personnel can also take orders from multiple companies at the same time.
  • Many-to-many without mapping table. For example, this is a forced association. For example, a user has multiple bank cards, and a person also has multiple books. But bank cards and books can also be forcibly associated many-to-many through userId. This kind of association has no mapping table association. Of course, we have no way to save it.

Initialization

Mapping Table Auto-generated!!!

If and only if the navigation property is many-to-many and there is a mapping table, and the cascade delete is configured as DELETE (cascade = CascadeTypeEnum.DELETE), the mapping table will be automatically added and deleted. If the cascade table is configured as AUTO or SET_NULL, the framework will consider the mapping table to be involved in business logic and will not automatically handle the mapping table, only the values of the association relationship columns in the mapping table

Flow Diagram

Note that we didn't operate on any mapping table here. All are automatically generated by the framework (if many-to-many is not configured as cascade delete, relationships will not be automatically created and deleted). Many-to-many cascade does not cascade delete the role table, but cascade deletes the mapping table, which is user_role.

When Users Need to Modify Roles

Originally user u1 had two roles r1 and r2. Now we want r2 and r3. How should we handle this?

dto

@Data
public class UserRoleRequest {
    private String userId;
    private List<String> roleIds;
}

-- SQL statement 1
SELECT `id`, `company_id`, `name`, `age`, `create_time`
FROM `t_user`
WHERE `id` = 'u1'
-- SQL statement 2
SELECT `user_id`, `role_id`
FROM `t_user_role`
WHERE `user_id` IN ('u1')
-- SQL statement 3
SELECT t.`id`, t.`name`, t.`create_time`
FROM `t_role` t
WHERE t.`id` IN ('r1', 'r2')
-- SQL statement 4
SELECT `id`, `name`, `create_time`
FROM `t_role`
WHERE `id` IN ('r2', 'r3')
-- SQL statement 5
DELETE FROM `t_user_role`
WHERE `user_id` = 'u1'
	AND `role_id` = 'r1'
-- SQL statement 6
INSERT INTO `t_user_role` (`id`, `user_id`, `role_id`)
VALUES ('81ded72bc9554e428ea2ea5089b5961b', 'u1', 'r3')

The first three SQLs are used to query how many roles user u1 currently has. The fourth SQL is used to query whether the requested roleIds are in the system. The fifth and sixth SQLs delete the u1-r1 relationship and insert the u1-r3 relationship through differential changes.

Optimization

A simple role change but too many queries were executed. Next, we will talk about a convenient way to implement changes to the mapping table, thereby implementing changes to the relationship between user and role.

  1. Create a one-to-many relationship between user and user_role

Note that the entity relationship cascade here is delete.

  1. Add SaveKey, because we can't know the corresponding relationship during save, so we can implement business logic uniqueness through savekey
@Table("t_user_role")
@Data
@EntityProxy
@FieldNameConstants
@EasyAlias("user_role")
public class UserRole implements ProxyEntityAvailable<UserRole , UserRoleProxy> {
    @Column(primaryKey = true,primaryKeyGenerator = UUIDPrimaryKey.class)
    private String id;
    @SaveKey
    private String userId;
    @SaveKey
    private String roleId;
}

Write API


    @PostMapping("/update2")
    @EasyQueryTrack
    public Object update2(@RequestBody UserRoleRequest request) {
        SysUser sysUser = easyEntityQuery.queryable(SysUser.class)
                .include(user -> user.userRoleList())
                .whereById(request.getUserId()).singleNotNull();

        List<SysRole> list = easyEntityQuery.queryable(SysRole.class).whereByIds(request.getRoleIds())
                .toList();
        ArrayList<UserRole> userRoles = new ArrayList<>();
        for (SysRole sysRole : list) {
            UserRole userRole = new UserRole();
            userRole.setUserId(sysUser.getId());
            userRole.setRoleId(sysRole.getId());
            userRoles.add(userRole);
        }
        sysUser.setUserRoleList(userRoles);

        transactionTemplate.execute(status->{
            easyEntityQuery.savable(sysUser).executeCommand();
            return null;
        });

        return "ok";
    }

You can see the logic is still clear

{
    "userId":"u1",
    "roleIds":["r2","r3"]
}


-- SQL statement 1
SELECT `id`, `company_id`, `name`, `age`, `create_time`
FROM `t_user`
WHERE `id` = 'u1'
-- SQL statement 2
SELECT t.`id`, t.`user_id`, t.`role_id`
FROM `t_user_role` t
WHERE t.`user_id` IN ('u1')
-- SQL statement 3
SELECT `id`, `name`, `create_time`
FROM `t_role`
WHERE `id` IN ('r2', 'r3')
-- SQL statement 4
DELETE FROM `t_user_role`
WHERE `id` = '3c9def3553d643739fa5236b3b11186f'
-- SQL statement 5
INSERT INTO `t_user_role` (`id`, `user_id`, `role_id`)
VALUES ('b37b6e75f0854a809d8c8753296307b3', 'u1', 'r3')

Finally, by turning the intermediate variable into one-to-many, we can reduce one SQL this time. And these SQLs are indispensable. First, we must know whether the user exists and need to know how many user_role the user currently has. Second, we need to know whether the roleId requested by the user exists in the database, so SQL 3 is also essential. Then comes the differential diff SQL. So far, our many-to-many save is also all completed.

Contributors

xuejiaming芥子-李显亮
Default Footer
Copyright © 2025 xuejmnet