Mybatis 多对多关系

本文主要通过学生选课的场景，进行Mybatis多对多的案例介绍。

开发环境

• idea
• mysql

工程代码下载地址 https://github.com/psp0001060/06_Mybatis_Many2Many.git

代码介绍

1.sql

CREATE TABLE `course` (
  `id` int(11) NOT NULL,
  `course_code` varchar(10) NOT NULL,
  `course_name` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Create Table

CREATE TABLE `student` (
  `id` int(11) NOT NULL auto_increment,
  `name` tinytext,
  `sex` tinytext,
  `age` int(4) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `student_course` (
  `id` decimal(3,0) NOT NULL,
  `student_id` int(11) default NULL,
  `course_id` int(3) default NULL,
  PRIMARY KEY  (`id`),
  KEY `stuFK` (`student_id`),
  KEY `courseFK` (`course_id`),
  CONSTRAINT `courseFK` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `stuFK` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.model

Course:

package test.model;

import java.util.List;

public class Course {
    private Integer id;

    private String courseCode;

    private String courseName;

    private List<Student> students;// 选课学生

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCourseCode() {
        return courseCode;
    }

    public void setCourseCode(String courseCode) {
        this.courseCode = courseCode == null ? null : courseCode.trim();
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName == null ? null : courseName.trim();
    }
}

Student:

package test.model;

import java.util.List;

public class Student {
    private Integer id;

    private String name;

    private String sex;

    private Integer age;

    private List<Course> courses;// 所选的课程

    public List<Course> getCourses() {
        return courses;
    }

    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex == null ? null : sex.trim();
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

3.Mapper

CourseMapper:

package test.dao;

import test.model.Course;

public interface CourseMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Course record);

    int insertSelective(Course record);

    Course selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Course record);

    int updateByPrimaryKey(Course record);

    //根据课程id，获取课程信息及所选学生信息
    Course selectCourseWithStudents(Integer id);
}

StudentMapper:

package test.dao;

import test.model.Course;
import test.model.Student;

public interface StudentMapper {
    int deleteByPrimaryKey(Integer id);

    int insertSelective(Student record);

    Student selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Student record);

    //学生x选课y(向student_course表插入记录)
    int studentChooseCourse(Integer id,Student student, Course course);

    //查询student级联查询出所选的course并且组装成完整的对象
    Student getStudentByIdWithCourses(Integer id);
}

4.Mapper.xml

CourseMapper:

<?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="test.dao.CourseMapper" >
  <resultMap id="courseResultMap" type="test.model.Course" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="course_code" property="courseCode" jdbcType="VARCHAR" />
    <result column="course_name" property="courseName" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, course_code, course_name
  </sql>
  <select id="selectByPrimaryKey" resultMap="courseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from course
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from course
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="test.model.Course" >
    insert into course (id, course_code, course_name
      )
    values (#{id,jdbcType=INTEGER}, #{courseCode,jdbcType=VARCHAR}, #{courseName,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="test.model.Course" >
    insert into course
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="courseCode != null" >
        course_code,
      </if>
      <if test="courseName != null" >
        course_name,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="courseCode != null" >
        #{courseCode,jdbcType=VARCHAR},
      </if>
      <if test="courseName != null" >
        #{courseName,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="test.model.Course" >
    update course
    <set >
      <if test="courseCode != null" >
        course_code = #{courseCode,jdbcType=VARCHAR},
      </if>
      <if test="courseName != null" >
        course_name = #{courseName,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="test.model.Course" >
    update course
    set course_code = #{courseCode,jdbcType=VARCHAR},
      course_name = #{courseName,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

StudentMapper.xml:

<?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="test.dao.StudentMapper" >
  <resultMap id="studentResultMap" type="test.model.Student" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="sex" property="sex" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, name, sex, age
  </sql>
  <select id="selectByPrimaryKey" resultMap="studentResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from student
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from student
    where id = #{id,jdbcType=INTEGER}
  </delete>

  <insert id="insertSelective" parameterType="test.model.Student" >
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="sex != null" >
        sex,
      </if>
      <if test="age != null" >
        age,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="sex != null" >
        #{sex,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="test.model.Student" >
    update student
    <set >
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="sex != null" >
        sex = #{sex,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>

  <insert id="studentChooseCourse">
    <!-- param1代表方法中第一个参数 以此类推 -->
    insert into
    student_course(id,student_id,course_id)
    values
    (#{param1},#{param2.id},#{param3.id})
  </insert>
  <!-- 继承上面那个基本的映射,再扩展出级联查询 -->
  <resultMap id="StudentResultWithCourses" type="Student" extends="studentResultMap">
    <collection property="courses" ofType="Course">
        <id property="id" column="cid"></id>
        <result property="courseCode" column="course_code"/>
        <result property="courseName" column="course_name"/>
    </collection>
  </resultMap>

    <select id="getStudentByIdWithCourses" parameterType="int" resultMap="StudentResultWithCourses">
        select s.id,s.name,c.id as cid,c.course_code,c.course_name,sc.id,sc.student_id,sc.course_id
        from student s,course c,student_course sc
        where
        s.id=#{id}
        and
        s.id=sc.student_id
        and
        sc.course_id=c.id
    </select>
</mapper>

5.Test

package mybatis_helloworld;

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 org.junit.Test;
import test.dao.StudentMapper;
import test.model.Course;
import test.model.Student;

import java.io.IOException;
import java.io.InputStream;

public class StudentTest {
    private String filename = "mybatis-config.xml";


    @Test
    public void testInsertStudent() {
        InputStream is;
        try {
            is = Resources.getResourceAsStream(filename);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder()
            .build(is);
            SqlSession session = factory.openSession();

            StudentMapper mapper = session.getMapper(StudentMapper.class);

            Student student = new Student();
            student.setName("zs");
            student.setAge(23);
            student.setSex("man");

            mapper.insertSelective(student);
            session.commit();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 学生选择课程
     */
    @Test
    public void testStudentChooseCourse(){
        InputStream is;
        try {
            is = Resources.getResourceAsStream(filename);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder()
                    .build(is);
            SqlSession session = factory.openSession();

            StudentMapper mapper = session.getMapper(StudentMapper.class);

            Student student = new Student();
            student.setId(1);

            Course course = new Course();
            course.setId(2);

            mapper.studentChooseCourse(1, student, course);
            session.commit();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询某个学生的个人信息及所选的课程信息
     */
    @Test
    public void testGetStudentByIdWithCourses(){
        InputStream is;
        try {
            is = Resources.getResourceAsStream(filename);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder()
                    .build(is);
            SqlSession session = factory.openSession();

            StudentMapper mapper = session.getMapper(StudentMapper.class);

            Student student =mapper.getStudentByIdWithCourses(1);

            System.out.println("姓名："+student.getName());
            for (Course course:
                 student.getCourses()) {
                System.out.println(course.getCourseCode()+"--"+course.getCourseName());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}