5、SpringBoot+Mybatis整合------多对多

开发工具:STS

代码下载链接:https://github.com/theIndoorTrain/SpringBoot_Mybatis/tree/3baea10a3a1104bda815c206954b2b687511aa3d

前言:

之前我们探讨了一对一、一对多的映射关系,今天我们来讨论多对多的映射关系。

多对多,其实可以拆成多个一对多来理解。

比如:

学生-------课程----------学生选课的关系:

(1)查询某个学生所选的全部课程;

(2)查询选修某个课程的全部学生;

今天我们就来实现这个实例。

一、数据库建表:

1.student表:

图片描述

2.course表:

图片描述

3.student-course表:

图片描述

二、查询某个学生所选的全部课程代码实现:

1.代码实现:

(1)添加Course实体:

图片描述

图片描述

 package com.xm.pojo;
 /**
  * 课程实体
  * @author xm
  *
  */
 public class Course {
     private int id;
     private String name;
     public int getId() {
         return id;
     }
     public void setId(int id) {
         this.id = id;
     }
     public String getName() {
         return name;
     }
     public void setName(String name) {
         this.name = name;
     }
     
 
 }

Course.java

(2)添加StudntCourse实体:

图片描述

图片描述

 package com.xm.pojo;
 
 import java.util.List;
 /**
  * 学生选课实体
  * @author xm
  *
  */
 public class StudentCourse {
     
     private int sid;
     private int cid;
     private int sorce;
     
     private List<Student> students;
     private List<Course> courses;
     public int getSid() {
         return sid;
     }
     public void setSid(int sid) {
         this.sid = sid;
     }
     public int getCid() {
         return cid;
     }
     public void setCid(int cid) {
         this.cid = cid;
     }
     public int getSorce() {
         return sorce;
     }
     public void setSorce(int sorce) {
         this.sorce = sorce;
     }
     public List<Student> getStudents() {
         return students;
     }
     public void setStudents(List<Student> students) {
         this.students = students;
     }
     public List<Course> getCourses() {
         return courses;
     }
     public void setCourses(List<Course> courses) {
         this.courses = courses;
     }
     
     
 
 }

StudentCourse.java

(3)在Studnent实体中添加StudentCourse列表:

图片描述

图片描述

 package com.xm.pojo;
 
 import java.util.List;
 
 /**
  * name:学生实体
  * @author xxm
  *
  */
 public class Student {
     /**
      * content:主键id
      */
     private int id;
     /**
      * content:姓名
      */
     private String name;
     
     private List<Book> books;
     
     private List<StudentCourse> studentCourses;
     
     public Student() {
         // TODO Auto-generated constructor stub
     }
     
     
     public List<StudentCourse> getStudentCourses() {
         return studentCourses;
     }
 
 
     public void setStudentCourses(List<StudentCourse> studentCourses) {
         this.studentCourses = studentCourses;
     }
 
 
     public List<Book> getBooks() {
         return books;
     }
 
 
     public void setBooks(List<Book> books) {
         this.books = books;
     }
 
 
     public int getId() {
         return id;
     }
     public void setId(int id) {
         this.id = id;
     }
     public String getName() {
         return name;
     }
     public void setName(String name) {
         this.name = name;
     }
     
     
 
 }

Student.java

(4)在数据库操作接口中添加方法:

图片描述

图片描述

 package com.xm.mapper;
 
 import java.util.List;
 
 import com.xm.pojo.Student;
 
 public interface StudentMapper {
 
     /***********/
 
     /**
      * 根据学生id查询该学生选修的所有课程
      * @param id
      * @return
      */
     public Student selectCourseById(Integer id);
     
 }

StudentMapper.java

(5)完善mapper映射:

图片描述

图片描述

 <?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="com.xm.mapper.StudentMapper">
 
     
     
     <resultMap type="student" id="studentMap">
         <id property="id" column="id"/>
         <result property="name" column="name"/>
     </resultMap>
     
     
     
     <resultMap type="student" id="courseMap" extends="studentMap">
         <collection property="studentCourses" ofType="studentCourse">
             <result property="sorce" column="sorce"/>
             <collection property="courses" ofType="course">
                 <id property="id" column="cid"/>
                 <result property="name" column="cname"/>
             </collection>
         </collection>
     </resultMap>
 
         <!-- 根据学生id查询该学生选修的所有课程 -->
     <select id="selectCourseById" parameterType="int" resultMap="courseMap" >
         select a.*,b.sorce,c.id cid,c.name cname from student a,student_course b,course c where a.id=b.sid and b.cid=c.id and a.id=#{id}
     </select>
 </mapper>

StudentMapper.xml

(6)在controller中实现:

图片描述

图片描述

 package com.xm.controller;
 
 import java.util.List;
 
 import javax.websocket.server.PathParam;
 
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.web.bind.annotation.DeleteMapping;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.PathVariable;
 import org.springframework.web.bind.annotation.PostMapping;
 import org.springframework.web.bind.annotation.PutMapping;
 import org.springframework.web.bind.annotation.RestController;
 
 import com.xm.mapper.StudentMapper;
 import com.xm.pojo.Student;
 
 @RestController
 public class StudentController {
     @Autowired
     private StudentMapper studentMapper;
     
     /************/
     
     /**
      * 根据学生id查询该学生选修的所有课程
      * @param id
      * @return
      */
     @GetMapping("/student/course/{id}")
     public Student selectCourseById(@PathVariable("id") Integer id) {
         Student student = studentMapper.selectCourseById(id);
         return student;
     }
 
 }

StudentController.java

2.测试结果:

(1)数据库运行

图片描述

2.postman运行:

图片描述

三、查询选修某个课程的全部学生代码实现:

1.代码实现:

(1)Course实体中添加StudentCourse列表:

图片描述

图片描述

 package com.xm.pojo;
 
 import java.util.List;
 
 /**
  * 课程实体
  * @author xm
  *
  */
 public class Course {
     private int id;
     private String name;
     private List<StudentCourse> studentCourses;
     
     public List<StudentCourse> getStudentCourses() {
         return studentCourses;
     }
     public void setStudentCourses(List<StudentCourse> studentCourses) {
         this.studentCourses = studentCourses;
     }
     public int getId() {
         return id;
     }
     public void setId(int id) {
         this.id = id;
     }
     public String getName() {
         return name;
     }
     public void setName(String name) {
         this.name = name;
     }
     
 
 }

Course.java

(2)添加Course数据操作接口:

图片描述

图片描述

 package com.xm.mapper;
 
 import com.xm.pojo.Course;
 
 public interface CourseMapper {
     /**
      * 根据课程id查询选修此课程的全部学生
      * @param id
      * @return
      */
     public Course selectStudentById(Integer id);
 
 }

CourseMapper.java

(3)添加mapper映射:

图片描述

图片描述

 <?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="com.xm.mapper.CourseMapper">
     <resultMap type="course" id="courseMap">
         <id property="id" column="id"/>
         <result property="name" column="name"/>
     </resultMap>
     <resultMap type="course" id="studentMap" extends="courseMap">
         <collection property="studentCourses" ofType="studentCourse">
             <result property="sorce" column="sorce"/>
             <collection property="students" ofType="student">
                 <id property="id" column="sid"/>
                 <result property="name" column="sname"/>
             </collection>
         </collection>
     </resultMap>
     <!-- 根据课程id查询选修此课程的全部学生 -->
     <select id="selectStudentById" parameterType="int" resultMap="studentMap">
     select a.*,b.sorce,c.id sid,c.name sname from student c,student_course b,course a where a.id=b.cid and b.sid=c.id and a.id=#{id}
     </select>
 </mapper>

CourseMapper.xml

(4)添加controller:

图片描述

图片描述

 package com.xm.controller;
 
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.PathVariable;
 import org.springframework.web.bind.annotation.RestController;
 
 import com.xm.mapper.CourseMapper;
 import com.xm.pojo.Course;
 
 /**
  * 课程
  * @author xm
  *
  */
 @RestController
 public class CourseController {
     @Autowired
     private CourseMapper  courseMapper;
     
     /**
      * 根据课程id查询选修此课程的全部学生
      * @param id
      * @return
      */
     @GetMapping("/course/student/{id}")
     public Course selectStudentById(@PathVariable("id")Integer id) {
         
         Course course = courseMapper.selectStudentById(id);
         return course;
         
     }
 
 }

CourseController.java

2.测试结果:

(1)数据库运行

图片描述

(2)postman运行

图片描述

                                                                                                                   2018-06-22