Mybatis-多表操作

• 目录

  多表模型分类(回顾)：

  一对一的数据准备

  一对一的功能实现

  一对多的数据准备

  一对多的功能实现

  多对多的数据准备

  多对多的功能实现

  ---

• 之前学习的都是基于单表操作的
• 而实际开发中，随着业务难度的加深，肯定需要多表操作的

• 多表模型分类(回顾)：

• 一对一：
• 在任意一方建立外键，关联对方的主键
• 一对多：
• 在多的一方建立外键，关联一的一方的主键
• 多对多：
• 借助中间表，中间表至少两个字段，分别关联两张表的主键

• 一对一的数据准备

• 一对一模型：人和身份证，一个人只有一个身份证
• 环境准备
• 

• CREATE TABLE person(
  	id INT PRIMARY KEY auto_increment,
  	name VARCHAR(30),
  	age INT
  );
  INSERT INTO person VALUES (NULL,'张三',23),(NULL,'李四',24),(NULL,'王五',25);
  
  CREATE TABLE card(
  	id INT PRIMARY KEY auto_increment,
  	number VARCHAR(30),
  	pid INT,
  	CONSTRAINT kk FOREIGN KEY (pid) REFERENCES person(id)
  );
  INSERT INTO card VALUES (NULL,'12345',1),(NULL,'23456',2),(NULL,'34567',3);

• 
• 

• package demo4.bean;
  
  public class Person {
      private Integer id;  //主键id
      private String name; //人的姓名
      private Integer age; //人的年龄
  
      public Person() {
      }
  
      public Person(Integer id, String name, Integer age) {
          this.id = id;
          this.name = name;
          this.age = age;
      }
  
      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;
      }
  
      public Integer getAge() {
          return age;
      }
  
      public void setAge(Integer age) {
          this.age = age;
      }
  @Override
      public String toString() {
          return "Person{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  ", age=" + age +
                  '}';
      }
  }

• 

• package demo4.bean;
  
  public class Card {
      private Integer id;  //主键id
      private String number;  //身份证号
      private Person p;  //所属人的对象
  
      public Card() {
      }
  
      public Card(Integer id, String number, Person p) {
          this.id = id;
          this.number = number;
          this.p = p;
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getNumber() {
          return number;
      }
  
      public void setNumber(String number) {
          this.number = number;
      }
  
      public Person getP() {
          return p;
      }
  
      public void setP(Person p) {
          this.p = p;
      }
  @Override
      public String toString() {
          return "Card{" +
                  "id=" + id +
                  ", number='" + number + '\'' +
                  ", p=" + p +
                  '}';
      }
  }

• 

• <!--起别名-->
      <typeAliases>
          <package name="demo4.bean"/>
      </typeAliases>

• 一对一的功能实现

• <resultMap>：
• 配置字段和对象属性的映射关系标签
• id属性：唯一标识
• type属性：实体对象类型
• <id>：
• 配置主键映射关系标签
• property属性：实体对象变量名称
• <result>：
• 配置非主键映射关系标签
• column属性：表中字段名称
• property属性：实体对象变量名称
• <association>：
• 配置被包含对象的映射关系标签
• property属性：被包含对象的变量名
• javaType属性：被包含对象的数据类型
• 创建一个包，用于存放对应映射配置文件
• 
• 

• <?xml version="1.0" encoding="UTF-8" ?>
  <!--MyBatis的DTD约束-->
  <!DOCTYPE mapper
          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="demo4.table01.OneToOneMapper">
      <!--配置字段和实体对象属性的映射关系-->
      <resultMap id="OneToOne" type="card">
          <!--id是给主键配置的-->
          <id column="cid" property="id"/>
          <result column="number" property="number"/>
          <!--
          association：配置被包含对象的映射关系
          property：被包含对象的变量名
          javaType：被包含对象的数据类型
          -->
          <association property="p" javaType="person">
              <id column="pid" property="id"/>
              <result column="name" property="name"/>
              <result column="age" property="age"/>
          </association>
      </resultMap>
      <select id="selectAll" resultMap="OneToOne">
          SELECT c.id cid,number,pid,name,age FROM card c,person p WHERE c.pid=p.id
      </select>
  </mapper>

• <mappers>
          <!--mapper引入指定的映射配置文件-->
          <mapper resource="demo4/one_to_one/OneToOneMapper.xml"/>
      </mappers>

• 再创建一个包用于存放对应的映射接口以及测试类并测试
• 
• 

• public interface OneToOneMapper {
      //查询全部
      public abstract List<Card> selectAll();
  }
  

• public class Test01 {
      @Test
      public void selectAll() throws Exception{
          //1.加载核心配置文件
          InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
          //2.获取SqlSession工厂对象
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
          //3.通过工厂对象获取SqlSession
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
          //4.获取StudentMapper接口的实现类对象
          OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
          //5.调用实现类的方法，接收结果
          List<Card> list = mapper.selectAll();
          //6.处理结果
          for(Card c : list){
              System.out.println(c);
          }
          //7.释放资源
          sqlSession.close();
          is.close();
      }
  }

• 

• 一对多的数据准备

• 一对多模型：班级和学生，一个班级可以有多个学生
• 环境准备
• 

• CREATE TABLE classes(
  	id INT PRIMARY KEY auto_increment,
  	name VARCHAR(30)
  );
  INSERT INTO classes VALUES (NULL,'一班'),(NULL,'二班');
  
  CREATE TABLE student(
  	id INT PRIMARY KEY auto_increment,
  	name VARCHAR(30),
  	age INT,
  	cid INT,
  	CONSTRAINT kkk FOREIGN KEY (cid) REFERENCES classes(id)
  );
  INSERT INTO student VALUES (NULL,'张三',23,1),(NULL,'李四',24,1),(NULL,'王五',25,2),(NULL,'赵六',26,2);

• 
• 

• public class Classes {
      private Integer id; //主键id
      private String name; //班级名称
      private List<Student> students;//班级中所有学生对象
  
      public Classes() {
      }
  
      public Classes(Integer id, String name, List<Student> students) {
          this.id = id;
          this.name = name;
          this.students = students;
      }
  
      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;
      }
  
      public List<Student> getStudents() {
          return students;
      }
  
      public void setStudents(List<Student> students) {
          this.students = students;
      }
  
      @Override
      public String toString() {
          return "Classes{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  ", students=" + students +
                  '}';
      }
  }

• 

• public class Student {
      private Integer id; //主键id
      private String name;//学生姓名
      private Integer age;//学生年龄
  
      public Student() {
      }
  
      public Student(Integer id, String name, Integer age) {
          this.id = id;
          this.name = name;
          this.age = age;
      }
  
      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;
      }
  
      public Integer getAge() {
          return age;
      }
  
      public void setAge(Integer age) {
          this.age = age;
      }
  
      @Override
      public String toString() {
          return "Student{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  ", age=" + age +
                  '}';
      }
  }

• 一对多的功能实现

• <resultMap>：
• 配置字段和对象属性的映射关系标签
• id属性：唯一标识
• type属性：实体对象类型
• <id>：
• 配置主键映射关系标签
• property属性：实体对象变量名称
• <result>：
• 配置非主键映射关系标签
• column属性：表中字段名称
• property属性：实体对象变量名称
• <collection>：
• 配置被包含集合对象的映射关系标签
• property属性：被包含集合对象的变量名
• ofType属性：集合中保存的对象数据类型
• 创建一个包，用于存放对应映射配置文件
• 
• 

• <?xml version="1.0" encoding="UTF-8" ?>
  <!--MyBatis的DTD约束-->
  <!DOCTYPE mapper
          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="demo4.table02.OneToManyMapper">
      <resultMap id="OneToMany" type="classes">
          <id column="cid" property="id"/>
          <result column="cname" property="name"/>
          <!--
              collection：配置被包含的集合对象映射关系
              property：被包含对象的变量名
              ofType：被包含对象的实际数据类型
          -->
          <collection property="students" ofType="student">
              <id column="sid" property="id"/>
              <result column="sname" property="name"/>
              <result column="sage" property="age"/>
          </collection>
      </resultMap>
      <select id="selectAll" resultMap="OneToMany">
          SELECT c.id cid,c.name cname,s.id sid,s.name sname,s.age sage FROM classes c,student s WHERE c.id=s.cid
      </select>
  </mapper>

• <mappers>
          <!--mapper引入指定的映射配置文件-->
          <mapper resource="demo4/one_to_one/OneToOneMapper.xml"/>
          <mapper resource="demo4/one_to_many/OneToManyMapper.xml"/>
      </mappers>

• 再创建一个包用于存放对应的映射接口以及测试类
• 
• 
• 

• public interface OneToManyMapper {
      //查询全部
      public abstract List<Classes> selectAll();
  }

• @Test
      public void selectAll() throws Exception {
          //1.加载核心配置文件
          InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
          //2.获取SqlSession工厂对象
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
          //3.通过工厂对象获取SqlSession
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
          //4.获取OneToManyMapper接口的实现类对象
          OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
          //5.调用实现类的方法，接收结果
          List<Classes> classes = mapper.selectAll();
          //6.处理结果
          for(Classes cls : classes){
              System.out.println(cls.getId()+","+cls.getName());
              List<Student> students = cls.getStudents();
              for(Student student : students){
                  System.out.println("\t"+student);
              }
          }
          //7.释放资源
          sqlSession.close();
          is.close();
      }

• 多对多的数据准备

• 多对多模型：学生和课程，一个学生可以选择多门课程，一个课程也可以被多个学生所选择
• 环境准备(沿用上次的student表略微改动)
• 
• 

• CREATE TABLE course(
  	id INT PRIMARY KEY auto_increment,
  	name VARCHAR(30)
  );
  INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
  
  CREATE TABLE stu_cr(
   id INT PRIMARY KEY auto_increment,
   sid INT,
   cid INT,
   CONSTRAINT kk_1 FOREIGN KEY (sid) REFERENCES student(id),
   CONSTRAINT kk_2 FOREIGN KEY (cid) REFERENCES course(id)
  );
  INSERT INTO stu_cr VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

• 

• public class Course {
      private Integer id;  //主键id
      private String name; //课程名称
  
      public Course() {
      }
  
      public Course(Integer id, String name) {
          this.id = id;
          this.name = name;
      }
  
      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;
      }
  
      @Override
      public String toString() {
          return "Course{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  '}';
      }
  }

• 

• public class Student {
      private Integer id; //主键id
      private String name;//学生姓名
      private Integer age;//学生年龄
      private List<Course> courses;//学生所选择的课程集合
  
      public Student() {
      }
  
      public Student(Integer id, String name, Integer age, List<Course> courses) {
          this.id = id;
          this.name = name;
          this.age = age;
          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;
      }
  
      public Integer getAge() {
          return age;
      }
  
      public void setAge(Integer age) {
          this.age = age;
      }
  
      public List<Course> getCourses() {
          return courses;
      }
  
      public void setCourses(List<Course> courses) {
          this.courses = courses;
      }
  
      @Override
      public String toString() {
          return "Student{" +
                  "id=" + id +
                  ", name='" + name + '\'' +
                  ", age=" + age +
                  ", courses=" + courses +
                  '}';
      }
  }

• 多对多的功能实现

• <resultMap>：
• 配置字段和对象属性的映射关系标签
• id属性：唯一标识
• type属性：实体对象类型
• <id>：
• 配置主键映射关系标签
• property属性：实体对象变量名称
• <result>：
• 配置非主键映射关系标签
• column属性：表中字段名称
• property属性：实体对象变量名称
• <collection>：
• 配置被包含集合对象的映射关系标签
• property属性：被包含集合对象的变量名
• ofType属性：集合中保存的对象数据类型
• 创建一个包，用于存放对应映射配置文件
• 

• <?xml version="1.0" encoding="UTF-8" ?>
  <!--MyBatis的DTD约束-->
  <!DOCTYPE mapper
          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="demo4.table03.ManyToManyMapper">
      <resultMap id="ManyToMany" type="student">
          <id column="sid" property="id"/>
          <result column="sname" property="name"/>
          <result column="sage" property="age"/>
          <collection property="courses" ofType="course">
              <id column="cid" property="id"/>
              <result column="cname" property="name"/>
          </collection>
      </resultMap>
      <select id="selectAll" resultMap="ManyToMany">
          SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHERE sc.sid=s.id AND sc.cid=c.id
      </select>
  </mapper>

• 

• <mappers>
          <!--mapper引入指定的映射配置文件-->
          <mapper resource="demo4/one_to_one/OneToOneMapper.xml"/>
          <mapper resource="demo4/one_to_many/OneToManyMapper.xml"/>
          <mapper resource="demo4/many_to_many/ManyToManyMapper.xml"/>
      </mappers>

• 再创建一个包用于存放对应的映射接口以及测试类
• 

• public interface ManyToManyMapper {
      //查询全部
      public abstract List<Student> selectAll();
  }

• 

• @Test
      public void selectAll() throws Exception {
          //1.加载核心配置文件
          InputStream is = Resources.getResourceAsStream("MybatisConfig.xml");
          //2.获取SqlSession工厂对象
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
          //3.通过工厂对象获取SqlSession
          SqlSession sqlSession = sqlSessionFactory.openSession(true);
          //4.获取ManyToManyMapper接口的实现类对象
          ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);
          //5.调用实现类的方法，接收结果
          List<Student> students = mapper.selectAll();
          //6.处理结果
          for (Student student : students) {
              System.out.println(student.getId()+","+student.getName()+","+student.getAge());
              List<Course> courses = student.getCourses();
              for(Course course : courses){
                  System.out.println("\t"+course);
              }
          }
          //7.释放资源
          sqlSession.close();
          is.close();
      }

•