mybatis多对多关系
老师和班级的关系
一个老师可以教多个班级，老师和班级可以看作时一对多的关系。但是反过来，班级和老师的关系是多对一吗？很显然不是，在把班级当作主体来看，一个班级有多个老师来授课，老师和班级又可以看成是多对一的关系。像这种相互一对多，多对一的关系，我理解为多对多。
对于这种关系的数据库表格设计需要添加一个中间关联的表，老师、班级之间通过中间表相互关联起来
数据库表格部分(三张表tb_class、tb_teacher、tb_class_teacher)
SQL脚本
Create Table CREATE TABLE `tb_class` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(18) DEFAULT NULL, `NAME` varchar(18) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 Create Table CREATE TABLE `tb_teacher` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(18) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 Create Table CREATE TABLE `tb_class_teacher` ( `id` int NOT NULL AUTO_INCREMENT, `cid` int DEFAULT NULL, `tid` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `cid` (`cid`), KEY `tid` (`tid`), CONSTRAINT `tb_class_teacher_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `tb_class` (`id`), CONSTRAINT `tb_class_teacher_ibfk_2` FOREIGN KEY (`tid`) REFERENCES `tb_teacher` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
查询所有班级以及班级对应的授课老师
Mapper部分和Dao部分的编写
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.liziy.dao.ClazzDao"> <!-- 查询所有班级信息以及班级所属的授课老师 --> <select id="selectAllClazzAndTeacher" resultMap="ClazzTeacherMap"> SELECT c.id cid, c.code, c.name cname, t.id, t.name FROM tb_class c LEFT JOIN tb_class_teacher ct ON c.id = ct.cid LEFT JOIN tb_teacher t ON t.`id` = ct.tid </select> <resultMap id="ClazzTeacherMap" type="cn.liziy.entity.Clazz"> <id property="id" column="cid"/> <id property="code" column="code"/> <id property="name" column="cname"/> <collection property="teachers" javaType="ArrayList" ofType="cn.liziy.entity.Teacher"> <id property="id" column="id"/> <id property="name" column="name"/> </collection> </resultMap> </mapper>
package cn.liziy.dao; import cn.liziy.entity.Clazz; import java.util.List; /** * @Author liziyang * @Date 22:23 2020/8/6 * @Description 班级数据访问接口 **/ public interface ClazzDao { /** * 查询所有班级信息以及班级所属授课老师信息 * @return */ List<Clazz> selectAllClazzAndTeacher(); }
service层
package cn.liziy.service; import cn.liziy.entity.Clazz; import java.util.List; public interface ClazzService { /** * 查询所有班级信息以及班级所属授课老师信息 * @return */ List<Clazz> selectAllClazzAndTeacher(); }
package cn.liziy.service.impl; import cn.liziy.dao.ClazzDao; import cn.liziy.entity.Clazz; import cn.liziy.service.ClazzService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; /** * @ClassName ClazzServiceImpl * @Author:Liziy * @Date 2020/8/6 22:40 * @Description: **/ @Service public class ClazzServiceImpl implements ClazzService { @Autowired ClazzDao clazzDao; @Override public List<Clazz> selectAllClazzAndTeacher() { return clazzDao.selectAllClazzAndTeacher(); } }
Controller层
package cn.liziy.controller; import cn.liziy.entity.Clazz; import cn.liziy.service.ClazzService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.ResponseBody; import java.util.List; /** * @ClassName ClazzController * @Author:Liziy * @Date 2020/8/6 22:37 * @Description: **/ @Controller public class ClazzController { @Autowired ClazzService clazzService; @GetMapping("/clazz_teacher/all") public @ResponseBody List<Clazz> Clazz_Teacher(){ return clazzService.selectAllClazzAndTeacher(); } }
控制台日志
json数据
上图json查询出的所有班级以及班级的所有授课老师
反之也可以通过查询所有的老师以及老师授课的班级
mapper和Dao的编写
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.liziy.dao.TeacherDao"> <!-- 根据所有老师信息以及老师对应的授课班级 --> <select id="selectAllTeacherAndClazz" resultMap="TeacherClazzMap"> SELECT t.id, t.name, c.id cid, c.code, c.name cname FROM tb_teacher t LEFT JOIN tb_class_teacher ct ON t.id = ct.tid LEFT JOIN tb_class c ON c.id = ct.cid </select> <resultMap id="TeacherClazzMap" type="cn.liziy.entity.Teacher"> <id property="id" column="id"/> <result property="name" column="name"/> <collection property="clazzes" javaType="ArrayList" ofType="cn.liziy.entity.Clazz"> <id property="id" column="cid"/> <result property="code" column="code"/> <result property="name" column="cname"/> </collection> </resultMap> </mapper>
package cn.liziy.dao; import cn.liziy.entity.Teacher; import java.util.List; /** * @ClassName TeacherDao * @Author:Liziy * @Date 2020/8/7 16:33 * @Description: **/ public interface TeacherDao { /** * 查询所有老师信息以及授课的班级 * @return */ List<Teacher> selectAllTeacherAndClazz(); }
Sercie层
package cn.liziy.service; import cn.liziy.entity.Teacher; import java.util.List; public interface TeacherService { /** * 查询所有老师信息以及授课的班级 * @return */ List<Teacher> selectAllTeacherAndClazz(); }
package cn.liziy.service.impl; import cn.liziy.dao.TeacherDao; import cn.liziy.entity.Teacher; import cn.liziy.service.TeacherService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; /** * @ClassName TeacherServiceImpl * @Author:Liziy * @Date 2020/8/7 16:39 * @Description: **/ @Service public class TeacherServiceImpl implements TeacherService { @Autowired TeacherDao teacherDao; @Override public List<Teacher> selectAllTeacherAndClazz() { return teacherDao.selectAllTeacherAndClazz(); } }
Controller层
package cn.liziy.controller; import cn.liziy.entity.Teacher; import cn.liziy.service.TeacherService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.ResponseBody; import java.util.List; /** * @ClassName TeacherController * @Author:Liziy * @Date 2020/8/7 16:40 * @Description: **/ @Controller public class TeacherController { @Autowired TeacherService teacherService; @GetMapping("teacher_clazz/all") public @ResponseBody List<Teacher> Tracher_Clazz(){ return teacherService.selectAllTeacherAndClazz(); } }
控制台日志
json数据
对于没有任何授课的旗木卡卡西 clazzes显示为空
对于有多项授课班级的日向日足 clazzes的jsons如上图所示
