MyBatis简易教程（05）：mybatis关联映射（多表查询多对多）

MyBatis简易教程汇总，详见：https://www.cnblogs.com/uncleyong/p/17984096

准备测试数据

教师表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王老师');
INSERT INTO `teacher` VALUES ('2', '李老师');
INSERT INTO `teacher` VALUES ('3', '张老师');

班级表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for classs
-- ----------------------------
DROP TABLE IF EXISTS `classs`;
CREATE TABLE `classs` (
  `c_id` int(11) NOT NULL AUTO_INCREMENT,
  `c_caption` varchar(255) NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of classs
-- ----------------------------
INSERT INTO `classs` VALUES ('1', '一年级一班');
INSERT INTO `classs` VALUES ('2', '一年级二班');
INSERT INTO `classs` VALUES ('3', '一年级三班');

教师和班级关联表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for teacher2cls
-- ----------------------------
DROP TABLE IF EXISTS `teacher2cls`;
CREATE TABLE `teacher2cls` (
  `tc_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`tc_id`),
  KEY `fk_teacher_id` (`teacher_id`),
  KEY `fk_class_id` (`class_id`),
  CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `classs` (`c_id`),
  CONSTRAINT `fk_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher2cls
-- ----------------------------
INSERT INTO `teacher2cls` VALUES ('1', '1', '1');
INSERT INTO `teacher2cls` VALUES ('2', '1', '2');
INSERT INTO `teacher2cls` VALUES ('3', '2', '1');
INSERT INTO `teacher2cls` VALUES ('4', '2', '2');
INSERT INTO `teacher2cls` VALUES ('5', '1', '3');
INSERT INTO `teacher2cls` VALUES ('6', '3', '3');

一个教师给多个班级授课

一个班级有多个教师授课

模型

示例

根据教师编号查询教师信息，及其对应的授课班级

教师实体类：加班级集合， private List<Classs> classses;

package com.qzcsbj.bean;


import java.util.List;

public class Teacher {

  private long tId;
  private String tName;
  // 班级集合，一个老师教多个班级的课程
  private List<Classs> classses;


  public long getTId() {
    return tId;
  }

  public void setTId(long tId) {
    this.tId = tId;
  }


  public String getTName() {
    return tName;
  }

  public void setTName(String tName) {
    this.tName = tName;
  }

  public List<Classs> getClassses() {
    return classses;
  }

  public void setClassses(List<Classs> classses) {
    this.classses = classses;
  }

  @Override
  public String toString() {
    return "Teacher{" +
            "tId=" + tId +
            ", tName='" + tName + '\'' +
            '}';
  }
}

班级实体类

package com.qzcsbj.bean;


public class Classs {

  private long cId;
  private String cCaption;


  public long getCId() {
    return cId;
  }

  public void setCId(long cId) {
    this.cId = cId;
  }


  public String getCCaption() {
    return cCaption;
  }

  public void setCCaption(String cCaption) {
    this.cCaption = cCaption;
  }

  @Override
  public String toString() {
    return "Classs{" +
            "cId=" + cId +
            ", cCaption='" + cCaption + '\'' +
            '}';
  }
}

mapper接口

package com.qzcsbj.mapper;

import com.qzcsbj.bean.Teacher;

/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public interface TeacherMapper {
    // 根据教师id查询教师信息，包含其授课的班级
    public Teacher getTeacherById(int tId);
}

映射文件：TeacherMapper.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="com.qzcsbj.mapper.TeacherMapper">
    <select id="getTeacherById" resultMap="TeacherMap">
        select * from gift.teacher t
        join gift.teacher2cls tc on tc.teacher_id=t.t_id
        join classs c on c.c_id=tc.class_id
        where t.t_id=#{tId}
    </select>

    <resultMap id="TeacherMap" type="Teacher">
        <id column="t_id" property="tId"/>
        <result column="t_name" property="tName"/>
        <!--多个班级-->
        <collection property="classses" ofType="Classs">
            <id column="c_id" property="cId"/>
            <result column="c_caption" property="cCaption"/>
        </collection>
    </resultMap>
</mapper>

测试类

package com.qzcsbj;

import com.qzcsbj.bean.*;
import com.qzcsbj.mapper.*;
import com.qzcsbj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;


/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class testMybatis2 {

    SqlSession session = null;
    TeacherMapper teacherMapper = null;

    @Before
    public void init(){
        System.out.println("初始化。。。");
        session = MyBatisUtils.getSession();
        teacherMapper = session.getMapper(TeacherMapper.class);
    }

    @After
    public void destory(){
        System.out.println("关闭session");
        MyBatisUtils.closeSession(session);
    }

   @Test
    public void testGetTeacherById(){
       Teacher teacher = teacherMapper.getTeacherById(1);
       System.out.println("教师信息是：" + teacher);
       System.out.println("该教师教授的的班级有：");
       List<Classs> classses = teacher.getClassses();
       for (Classs classs : classses) {
           System.out.println(classs);
       }
   }
}

结果

根据班级编号查询班级信息，及其对应的教师信息

班级实体类：加教师集合，private List<Teacher> teachers;

package com.qzcsbj.bean;


import java.util.List;

public class Classs {

  private long cId;
  private String cCaption;
  // 教师集合，一个班级有多个教师授课
  private List<Teacher> teachers;


  public long getCId() {
    return cId;
  }

  public void setCId(long cId) {
    this.cId = cId;
  }


  public String getCCaption() {
    return cCaption;
  }

  public void setCCaption(String cCaption) {
    this.cCaption = cCaption;
  }

  public List<Teacher> getTeachers() {
    return teachers;
  }

  public void setTeachers(List<Teacher> teachers) {
    this.teachers = teachers;
  }

  @Override
  public String toString() {
    return "Classs{" +
            "cId=" + cId +
            ", cCaption='" + cCaption + '\'' +
            '}';
  }
}

教师实体类

package com.qzcsbj.bean;


import java.util.List;

public class Teacher {

  private long tId;
  private String tName;
  // 班级集合，一个老师教多个班级的课程
  private List<Classs> classses;


  public long getTId() {
    return tId;
  }

  public void setTId(long tId) {
    this.tId = tId;
  }


  public String getTName() {
    return tName;
  }

  public void setTName(String tName) {
    this.tName = tName;
  }

  public List<Classs> getClassses() {
    return classses;
  }

  public void setClassses(List<Classs> classses) {
    this.classses = classses;
  }

  @Override
  public String toString() {
    return "Teacher{" +
            "tId=" + tId +
            ", tName='" + tName + '\'' +
            '}';
  }
}

mapper接口

package com.qzcsbj.mapper;

import com.qzcsbj.bean.Classs;

/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public interface ClasssMapper {
    // 根据班级id查询班级信息，并查询出其对应的教师
    public Classs getClasssById(int cId);
}

映射文件：ClasssMapper.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="com.qzcsbj.mapper.ClasssMapper">

    <select id="getClasssById" resultMap="ClasssMap">
        select * from gift.teacher t
        join gift.teacher2cls tc on tc.teacher_id=t.t_id
        join classs c on c.c_id=tc.class_id
        where c.c_id=#{cId}
    </select>

    <resultMap id="ClasssMap" type="Classs">
        <id column="c_id" property="cId"/>
        <result column="c_caption" property="cCaption"/>
        <!--有多个教师-->
        <collection property="teachers" ofType="Teacher">
            <id column="t_id" property="tId"/>
            <result column="t_name" property="tName"/>
        </collection>
    </resultMap>
</mapper>

测试类

package com.qzcsbj;

import com.qzcsbj.bean.Classs;
import com.qzcsbj.bean.Teacher;
import com.qzcsbj.mapper.ClasssMapper;
import com.qzcsbj.mapper.TeacherMapper;
import com.qzcsbj.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;


/**
 * @公众号 : 全栈测试笔记
 * @博客 : www.cnblogs.com/uncleyong
 * @微信 : ren168632201
 * @描述 : <>
 */
public class testMybatis3 {

    SqlSession session = null;
    ClasssMapper classsMapper = null;

    @Before
    public void init(){
        System.out.println("初始化。。。");
        session = MyBatisUtils.getSession();
        classsMapper = session.getMapper(ClasssMapper.class);
    }

    @After
    public void destory(){
        System.out.println("关闭session");
        MyBatisUtils.closeSession(session);
    }

   @Test
    public void testGetClasssById(){
       Classs classs = classsMapper.getClasssById(1);
       System.out.println("班级信息是：" +classs);
       System.out.println("班级的授课教师有：");
       List<Teacher> teachers = classs.getTeachers();
       for (Teacher teacher : teachers) {
           System.out.println(teacher);
       }
   }
}

结果