Mybatis-10-多对一

多对一的理解：

• 多个学生对应一个老师

• 如果对于学生这边，就是一个多对一的现象，即从学生这边关联一个老师！

建库：

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');

9.1、测试环境搭建

步骤：

1. 新建实体类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;
   }

2. 建立Mapper接口

   public interface StudentMapper extends TeacherMapper {
   ​
       List<Student> getstudent1();
       List<Student> getstudent();
   }

3. 建立Mapper.xml文件

   【子查询】【多表查询】

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

   <mappers>
      <!--<mapper class="com.mosang.dao.StudentMapper"/>
       <mapper class="com.mosang.dao.TeacherMapper"/>-->
       <mapper resource="com/mosang/dao/TeacherMapper.xml"/>
       <mapper resource="com/mosang/dao/StudentMapper.xml"/>
   </mappers>

5. 测试

9.2、按照查询嵌套处理【子查询】

<!--
思路：
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属性名 javaType属性类型 column在多的一方的表中的列名
javaType=""指定属性的类型  ofType=""指定集合中的泛型信息
-->
    <association property="teacher" column="tid" javaType="Teacher" select="getteacher"/>
</resultMap>
    
<select id="getteacher" resultType="Teacher">
    select * from teacher where id=#{id}
</select>

测试：

public static void main(String[] args) {
    //方法一测试
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> list = mapper.getstudent();
    for (Student student : list) {
        System.out.println(student);
    }
    sqlSession.close();
}

 

9.3、按照结果嵌套处理【多表查询】

<select id="getstudent1" resultMap="StudentTeacher" >
    select t.id tid,t.name tname,s.id sid,s.name sname
    from teacher t join student s on t.id = s.tid
</select>
​
<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <!--关联对象property 关联对象在Student实体类中的属性-->
    <association property="teacher" column="tid" >
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

@Test
public void  tesst()
{
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentlist = mapper.getstudent1();
    for (Student student : studentlist) {
        System.out.println(student);
    }
​
    sqlSession.close();
}