Mybatis 多表实现多对多查询、添加操作

缺乏、安全感 2023-01-22 14:48 26阅读 0赞

### Mybatis 多表实现多对多查询、添加操作 ###

*  学习内容：
 *   *  1. 多对多之添加操作
     *   *  1.1、需求
         *  1.2、数据库表(多对多数据库设计需要设计中间表)
         *  1.3、类设计
         *  1.4、Mapper 接口和 Mapper XML 文件编写
         *  1.5、编写单元测试类
     *  2. 单向多对多之额外 SQL 查询
     *   *  2.1、需求
         *  2.2、需要添加 Mapper 接口 和 Mapper XML
         *  2.3、单元测试方法
 *  总结：

# 学习内容： #

--------------------

## 1. 多对多之添加操作 ##

### 1.1、需求 ###

保存两个学生和两个老师，且这两个老师都教了这个两个学生。

### 1.2、数据库表(多对多数据库设计需要设计中间表) ###

*  表设计  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6dmFy_size_16_color_FFFFFF_t_70]

### 1.3、类设计 ###

*  Teacher 实体类

package com.yy.domain;
    @Setter 
    @Getter 
    @ToString 
    public class Teacher {  
    	private Long id; 
    	private String name; 
    }

*  Employee 实体类

package com.yy.domain;
    @Setter 
    @Getter 
    @ToString 
    public class Student {  
    private Long id; 
    private String name; 
    	// 关联属性 
    	private List<Teacher> teachers = new ArrayList<>(); 
    }

### 1.4、Mapper 接口和 Mapper XML 文件编写 ###

**注意**: Mapper XML 放置的位置是在 resources 目录下。

*  TeacherMapper 接口

package com.yy.mapper;
    public interface TeacherMapper {  
    	void save(Teacher teacher); 
    }

*  TeacherMapper.xml 文件

<insert id="save" useGeneratedKeys="true" keyProperty="id"> 
    	INSERT INTO teacher(name) VALUES(#{name}) 
    </insert>

*  StudentMapper 接口

package com.yy.mapper;
    public interface StudentMapper {  
    	void save(Student student); 
    	// 往中间表插入关系数据 
    	void insertRelation(@Param("teacherId")Long teacherId, @Param("studentId")Long studentId); 
    }

*  StudentMapper.xml 文件

<insert id="save" useGeneratedKeys="true" keyProperty="id"> 
    	INSERT INTO student(name) VALUES(#{name}) 
    </insert> 
    <insert id="insertRelation"> 
    	INSERT INTO teacher_student(teacher_id, student_id) VALUES (#{teacherId}, # {studentId}) 
    </insert>

### 1.5、编写单元测试类 ###

public class Many2manyTest { 
            @Test
            public void testSave() throws Exception { 
                Teacher teacher1 = new Teacher();
                teacher1.setName("波老师");
                Teacher teacher2 = new Teacher();
                teacher2.setName("罗老师");
                Student s1 = new Student();
                s1.setName("小强");
                Student s2 = new Student();
                s2.setName("小红");
                s1.getTeachers().add(teacher1);
                s1.getTeachers().add(teacher2);
                // s1 被两个老师交了 
                s2.getTeachers().add(teacher1);
                s2.getTeachers().add(teacher2);
                // s2 被两个老师交了 
                SqlSession session = MyBatisUtil.getSession();
                TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
                StudentMapper studentMapper = session.getMapper(StudentMapper.class);
                teacherMapper.save(teacher1);
                teacherMapper.save(teacher2);
                studentMapper.save(s1);
                studentMapper.save(s2);
                // 往中间表存入数据老师教学生的关系数据 
                for (Teacher t : s1.getTeachers()) { 
                    studentMapper.insertRelation(t.getId(), s1.getId());
                }
                for (Teacher t : s2.getTeachers()) { 
                    studentMapper.insertRelation(t.getId(), s2.getId());
                }
                session.commit();
                session.close();
            }
        }

## 2. 单向多对多之额外 SQL 查询 ##

### 2.1、需求 ###

根据 id 查询学生，并查询其老师。

### 2.2、需要添加 Mapper 接口 和 Mapper XML ###

*  StudentMapper 接口

package com.yy.mapper;
    public interface StudentMapper {  
    	Student get(Long id);
    }

*  StudentMapper.xml 文件

<resultMap type="Student" id="baseResultMap"> 
    	<id column="id" property="id"/> 
    	<result column="name" property="name"/> 
    	 
    	<collection select="cn.wolfcode.mapper.TeacherMapper.queryByStudentId" column="id" property="teachers"/>
    </resultMap> 
    <select id="get" resultMap="baseResultMap">
    	SELECT id, name FROM student WHERE id = #{id}
    </select>

*  TeacherMapper XML

package com.yy.mapper;
    public interface queryByStudentId {  
    	Teacher get(Long id); 
    }

*  TeacherMapper.xml 文件

<select id="queryByStudentId" resultType="Teacher"> 
    	SELECT t.id, t.name 
    	FROM teacher_student ts 
    	JOIN teacher t ON ts.teacher_id = t.id
    	WHERE ts.student_id = #{studentId} 
    </select>

### 2.3、单元测试方法 ###

public class Many2oneTest {  
    	@Test 
    	public void testGet() throws Exception {  
    		SqlSession session = MyBatisUtil.getSession(); 
    		StudentMapper studentMapper = session.getMapper(StudentMapper.class); 
    		Student student = studentMapper.get(2L); 
    		System.out.println(student); session.close(); 
    	}
    }

# 总结： #

以上就是使用 Mybatis 多表实现多对多查询、添加操作了，代码仅供参考，欢迎讨论交流。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p6dmFy_size_16_color_FFFFFF_t_70]: /images/20221020/62dc965b34d442b7b46eabdc20980855.png