Mybatis多对一处理（以老师学生为例）

1、基本环境搭建

• 多个学生，对应一个老师

• 对于学生而言，多个学生，关联一个老师【多对一】（关联）

• 对于老师而言，一个老师，有很多学生【一对多】（集合）

• 数据库sql

  CREATE TABLE `teacher` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  
  INSERT INTO teacher(`id`, `name`) VALUES (1, '王老师');
  
  CREATE TABLE `student` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fktid` (`tid`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  
  INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, '小明', '1'); 
  INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, '小红', '1'); 
  INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, '小张', '1'); 
  INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, '小李', '1'); 
  INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, '小王', '1');
  

• 测试环境搭建流程

  • 导入lombok

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
    </dependency>
    

  • 新建实体类Teacher，Student

    @Data
    
    public class Teacher {
        private int id;
        private String name;
    }
    

    @Data
    public class Student {
        private int id;
        private String name;
    
        //学生需要关联一个对象老师
        private Teacher teacher;
    
    }
    

  • 建立Mapper接口

    public interface TeacherMapper {
    
    }
    

    public interface StudentMapper {
    
    }
    

  • 建立Mapper.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.ping.dao.TeacherMapper">
        
    </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.ping.dao.StudentMapper">
        
    </mapper>
    

  • 在核心配置文件中绑定注册Mapper接口或者文件

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    
    <!--configuration核心配置文件-->
    <configuration>
    
        <!--引入外部配置文件-->
        <properties resource="db.properties" />
    
        <settings>
            <!--标准日志工厂实现-->
            <setting name="logImpl" value="STDOUT_LOGGING"/>
    
        </settings>
    
        <!--可以给实体类起别名-->
        <typeAliases>
            <package name="com.ping.pojo"/>
        </typeAliases>
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${driver}"/>
                    <property name="url" value="${url}"/>
                    <property name="username" value="${username}"/>
                    <property name="password" value="${password}"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper class="com.ping.dao.TeacherMapper"/>
            <mapper class="com.ping.dao.StudentMapper"/>
        </mappers>
    
    </configuration>
    

  • 测试查询是否能够成功

2、方式一：按照查询嵌套处理

• 接口StudentMapper

  public interface StudentMapper {
  
      //查询所有的学生信息，以及对应的老师的信息
      public List<Student> getStudent();
  }
  

• 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="com.ping.dao.StudentMapper">
  
      <!--
      思路：子查询
          1.查询所有的学生信息
          2.根据查询出来的学生的tid，寻找对应的老师
      -->
      
      <select id="getStudent" resultMap="StudentTeacher">
          select * from student;
      </select>
  
      <resultMap id="StudentTeacher" type="Student">
          <result property="id" column="id"/>
          <result property="name" column="name"/>
          <!--复杂的属性，需要单独处理，  对象：association，  集合：collection-->
          <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
      </resultMap>
  
      <select id="getTeacher" resultType="Teacher">
          select * from teacher where id = #{id};
      </select>
  
  </mapper>
  

• 测试类

  public class MyTest {
  
      @Test
      public void testStudent(){
          SqlSession sqlSession = MybatisUtils.getSqlSession();
          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
          List<Student> student = mapper.getStudent();
          for (Student student1 : student) {
              System.out.println(student1);
          }
  
          sqlSession.close();
      }
  }
  

• 测试结果

  

3、方式二：按照结果嵌套处理（推荐）

• 接口StudentMapper

  public interface StudentMapper {
  
      public List<Student> getStudent2();
  }
  

• 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="com.ping.dao.StudentMapper">
  
      <!--按照结果嵌套处理-->
      <select id="getStudent2" resultMap="StudentTeacher2">
          select s.id sid,s.name sname,t.id ti,t.name tname
          from student s,teacher t
          where  s.tid = t.id;
      </select>
  
      <resultMap id="StudentTeacher2" type="Student">
          <result property="id" column="sid"/>
          <result property="name" column="sname"/>
          <association property="teacher" javaType="Teacher">
              <result property="name" column="tname"/>
              <result property="id" column="ti"/>
          </association>
      </resultMap>
  
  </mapper>
  
  

• 测试类

  public class MyTest {
  
      @Test
      public void testStudent2(){
          SqlSession sqlSession = MybatisUtils.getSqlSession();
          StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
          List<Student> student = mapper.getStudent2();
          for (Student student1 : student) {
              System.out.println(student1);
          }
  
          sqlSession.close();
      }
  }
  

• 测试结果