老师和班级的关系
一个老师可以教多个班级, 老师和班级可以看作时一对多的关系. 但是反过来, 班级和老师的关系是多对一吗? 很显然不是, 在把班级当作主体来看, 一个班级有多个老师来授课, 老师和班级又可以看成是多对一的关系. 像这种相互一对多, 多对一的关系, 我理解为多对多.
对于这种关系的数据库表格设计需要添加一个中间关联的表, 老师, 班级之间通过中间表相互关联起来
数据库表格部分 (三张表 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 如上图所示
来源: http://www.bubuko.com/infodetail-3654671.html