mybatis----子查询和联表查询的对比（按照结果嵌套处理、按照查询嵌套处理）

92 阅读 0 评论 61 点赞

我是靠谱客的博主心灵美服饰，最近开发中收集的这篇文章主要介绍mybatis----子查询和联表查询的对比（按照结果嵌套处理、按照查询嵌套处理），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

需求：查询所有teacher信息以及其对应的student信息

一、需要用到的两张表

在这里插入图片描述

二、实体类

1、实体类Student

package com.roy.pojo;
public class Student {
    private int id;
    private String name;
    private int tid;
    //为了控制文章篇幅，此处省略无参构造，有参构造，get/set/tostring
}

2、实体类Teacher

package com.roy.pojo;
import java.util.List;
public class Teacher {
    private int id;
    private String name;
    private String password;
    private List<Student> student;
    //同上
}

三、dao层

1、查询student信息的接口

package com.roy.dao;
import com.roy.pojo.Student;
public interface StudentMapper {
    Student selectStudentByTid(int id);
}

2、查询teacher信息的接口

package com.roy.dao;
import com.roy.pojo.Teacher;
import java.util.List;
public interface TeacherMapper {
    List<Teacher> selectAllTeachers();
}

四、dao层对应的StudentMapper.xml和TeacherMapper.xml

自我感悟：*mapper.xml是对传统实现类的封装。我们只需在xml中修改就可以完成各种实现，而不需要像传统实现类那样在代码上直接修改，传统实现类修改起来太麻烦，牵一发而动全身。纯属新手感悟，理解不对的地方，请大佬指正。

1、通过子查询实现功能

### **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.roy.dao.StudentMapper">
    <select id="selectStudentByTid" resultType="Student">
    select * from student where id=#{id}
  </select>
</mapper>

### **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.roy.dao.TeacherMapper">
    <select id="selectAllTeachers" resultMap="Teacher_Student">
    select * from teacher
  </select>
    <resultMap id="Teacher_Student" type="Teacher">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="password" column="password"/>
        <collection property="student" javaType="List" ofType="Student" column="id" select="selectStudentByTid"/>
    </resultMap>
    <select id="selectStudentByTid" resultType="Student">
        select * from student where tid=#{id}
    </select>
</mapper>

2、通过联表查询实现功能

### **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.roy.dao.StudentMapper">
    <select id="selectStudentByTid" resultType="Student">
        select * from student where tid=#{id}
    </select>
</mapper>
### **TeacherMapper**
<?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.roy.dao.TeacherMapper">
    <select id="selectAllTeachers" resultMap="Teacher_Student">
        select t.id tid, t.name tname, t.password tpwd, s.id sid, s.name sname, s.tid stid from teacher t left join student s on t.id=s.tid
    </select>
    <resultMap id="Teacher_Student" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <result property="password" column="tpwd"/>
        <collection property="students" javaType="List" ofType="Student" column="tid">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="stid"/>
        </collection>
    </resultMap>
</mapper>