MyBatis Deep Dive - Many-to-Many Model with Collection Tags

Many-to-Many

In database design, a “many-to-many” relationship means records in two tables can be associated with each other, with multiple records associated with multiple records. For example, the relationship between a “student” table and a “course” table may be many-to-many, because one student can enroll in multiple courses, and one course can be taken by multiple students.

To implement many-to-many relationships, an intermediate table (association table) is usually used. This intermediate table acts as a bridge, connecting records from both tables through their primary keys.

Characteristics of Many-to-Many Relationships

• Bidirectionality: A can be associated with multiple B, and B can also be associated with multiple A
• Requires Intermediate Table: To represent this relationship, an intermediate table is usually used to maintain the association
• High Flexibility: Many-to-many relationships are very suitable for expressing complex business logic

Query Model

The relationship between user table and role table: one user has multiple roles, one role is used by multiple users. The many-to-many query requirement is to query a user and simultaneously query all roles that user has.

Create Tables

wzk_user_role

CREATE TABLE `wzk_user_role` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

wzk_role

CREATE TABLE `wzk_role` (
  `id` int(11) NOT NULL,
  `rolename` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Insert Data

wzk_user_role

INSERT INTO wzk_user_role VALUES(1, 1, 1);
INSERT INTO wzk_user_role VALUES(1, 2, 2);

wzk_role

INSERT INTO wzk_role VALUES(1, "ADMIN");
INSERT INTO wzk_role VALUES(2, "USER");

Query Statement

select u.*,r.*,r.id rid from wzk_user u
left join wzk_user_role ur on u.id=ur.user_id
inner join wzk_role r on ur.role_id=r.id;

Create Classes

WzkUser

package icu.wzk.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class WzkUser {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    private List<WzkOrder> orderList;
    private List<WzkRole> roleList;
}

WzkRole

package icu.wzk.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class WzkRole {
    private Integer id;
    private String rolename;
}

UserMapper

package icu.wzk.mapper;

import icu.wzk.model.WzkUser;
import java.util.List;

public interface UserMapper {
    List<WzkUser> findAll();
    List<WzkUser> findAllUserAndRole();
}

UserMapper.xml

<resultMap id="userRoleMap" type="icu.wzk.model.WzkUser">
    <result column="id" property="id"></result>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <result column="birthday" property="birthday"></result>
    <collection property="roleList" ofType="icu.wzk.model.WzkRole">
        <result column="rid" property="id"></result>
        <result column="rolename" property="rolename"></result>
    </collection>
</resultMap>

<select id="findAllUserAndRole" resultMap="userRoleMap">
    SELECT u.*, r.*, r.id rid
    FROM wzk_user u
    LEFT JOIN wzk_user_role ur on u.id=ur.user_id
    INNER JOIN wzk_role r on ur.role_id=r.id;
</select>

Write Code

package icu.wzk;

import icu.wzk.mapper.UserMapper;
import icu.wzk.model.WzkUser;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class WzkIcu10 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<WzkUser> dataList = userMapper.findAllUserAndRole();
        dataList.forEach(System.out::println);
        sqlSession.close();
    }
}

Running Results

WzkUser(id=1, username=wzk, password=icu, birthday=Mon Nov 11 00:00:00 CST 2024, orderList=null, roleList=[WzkRole(id=1, rolename=ADMIN)])
WzkUser(id=2, username=wzk2, password=icu2, birthday=Mon Nov 11 00:00:00 CST 2024, orderList=null, roleList=[WzkRole(id=2, rolename=USER)])

Notes

• Index Optimization: Create indexes on foreign key fields of the intermediate table to improve query performance
• Relationship Constraints: Use foreign key constraints to maintain data consistency and avoid orphaned data
• Batch Operations: When performing large-scale inserts and updates, try to use batch operations to improve efficiency
• Soft Delete: The intermediate table can be designed with a “soft delete” flag to preserve historical records

Optimizing Many-to-Many Relationship Model

Optimizing many-to-many relationship models requires attention to multiple aspects:

• Improve Performance: Optimize queries and updates through indexing, partitioning, and batch operations
• Ensure Data Consistency: Avoid data anomalies through foreign key constraints and transaction processing
• Increase Flexibility: Preserve historical data through soft deletes and logging, supporting recovery and auditing
• Handle Large Data Scenarios: Ensure system scalability through partitioning, sharding, or database splitting