Mybatis复杂查询环境搭建以及一对多实现

第一步：环境搭建

1. 创建一对多的数据库关系表：
   学生与教师（这里一个学生对应一个教师）

//教师表
CREATE TABLE `teacher` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

//学生表
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `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 AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

2. 导入Lombok,根据表新建实体类（Teacher，Student）

//学生类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

//教师类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

3. 建立Mapper接口及对应的Mapper.xml文件

public interface StudentMapper {
    //查询所有学生以及对应的老师
    List<Student> getStudents();
    List<Student> getStudents2();
}
public interface TeacherMapper {
    @Select("select *from `teacher` where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

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.chen.dao.StudentMapper">


   <!--方式一：按照查询嵌套处理（子查询）-->
    <select id="getStudents" resultMap="StudentTeacher">
        select *from student
    </select>

    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"></association>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select *from teacher where id = #{id}
    </select>


    <!--方式二：按照结果嵌套处理-->
    <select id="getStudents2" resultMap="StudentTeacher2">
       select s.id sid,s.name sname,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>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname" ></result>
        </association>
    </resultMap>
</mapper>

4. 在mybatis核心配置文件中绑定注册StudentMapper.xml

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

5. 测试查询是否能够成功

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