Mybatis：应用多表查询

多表查询

多对一

实体：学生、老师

关系：学生多对一老师

实体与接口

实体(需要导入lombok依赖)

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
    int id;
    String name;
    /**
     * 多个学生可以是同一个老师，即多对一
     * Teacher teacher可以通过teacher类获取id
     * 简单的来说teacher.id要作为一个外键对象
     */
    private Teacher teacher;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Teacher {
    int id;
    String name;
}

接口

/**
 * 实现增删改查的功能
 * @author DELL
 */
public interface StudentMapper {
    /**
     * 多表查询
     */
    public List<Student> getStudent();
}

/**
 * 老师表
 */
public interface TeacherMapper {

    /**
     * 简单的查询
     */
    public List<Teacher> getTeacher();
}

方法1（子查询）

思路

1、查询所有的学生信息
2、根据查询出来的学生的tid，寻找对应的老师

映射文件（重点关注）

<select id="getStudent" resultMap="StudentTeacher">
            select * from student
            </select>
        <!--       这里的student需要去核心配置文件中设置别名-->
    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>

        <!--    通过表象看本质，实质上就是解决数据库字段和属性名不一致的问题，用外键teacher作为一个对象，对象的类型javaType（主体）是teacher，由select=getTeacher查询得出的结果-->
        <!--        复杂的属性，需要单独处理 对象：association 集合：collection-->
        <!--        javaType 为 student类中的 Teacher teacher-->

        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"></association>
    </resultMap>

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

方法2（通过结果进行映射）

映射文件（重点关注）

<!--第二种方法-->
<select id="getStudent" resultMap="StudentTeacher">
    select student.id as sid,student.name as sname, teacher.name as tname
    from student,teacher
    where student.tid = teacher.id
</select>

    <resultMap id="StudentTeacher" type="student">
<!--        proper为属性名 column为字段-->
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
<!--        关联对象property 关联对象在Student实体类中的属性，把teacher类中name关联给tname-->
        <association property="teacher" javaType="teacher" >
            <result property="name" column="tname"/>
        </association>
    </resultMap>

通用测试方法

public class MyBatisUtils {
    private static String resource = "mybatis-config.xml";
    private static InputStream inputStream;
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            inputStream = Resources.getResourceAsStream(resource);
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession(){
        SqlSession session = sqlSessionFactory.openSession();
        return session;
    }
}

一对多

实体：学生、老师

关系：老师一对多学生

实体与接口

实体

public class Student {
    int id;
    String name;
    int tid;

    public Student(){
    }

    public Student(int id, String name, int tid){
        this.id = id;
        this.name=name;
        this.tid = tid;
    }
    。。get/set，toString方法
}

public class Teacher {
    int id;
    String name;
    /**
     * 一个老师拥有多个学生
     */
    List<Student> students;

    public Teacher() {
    }

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }
    ....set/get方法，toString方法
}

接口

public interface StudentMapper {
    /**
     * 多表查询
     */
    public List<Student> getStudent();
}

public interface TeacherMapper {
    /**
     * 简单的查询
     * 查询所有的老师
     */
    public List<Teacher> getTeacher();

    /**
     *简单的查询单条记录
     * 获取指定老师，及老师下的所有学生
     */
    public Teacher selectTeacherById(int n);
}

方法1

映射文件（需要重点关注）

<!--===================按结果查询-->
    <select id="selectTeacherById" resultMap="TeacherAndStudent">
        select teacher.id as tid, teacher.name as tname,student.id as sid, student.name as sname
        from teacher, student
        where teacher.id=student.tid and teacher.id= #{id}
    </select>

    <resultMap id="TeacherAndStudent" type="teacher">
        <result  property="name" column="tname"/>
<!--        取集合里面的每一个值-->
        <collection property="students" ofType="student">
            <result property="id" column="sid"></result>
            <result property="name" column="sname"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>

方法2（连表查询）

    <select id="selectTeacherById" resultMap="TeacherAndStudent">
        #         根据老师的id查询老师的记录
        select  * from teacher where id = #{id}
    </select>

    <resultMap id="TeacherAndStudent" type="Teacher">
        <!--    取一个集合,从集合里面取值-->
        <collection property="students" javaType="ArrayList" ofType="student" column="id" select="getStudentByTeacherId"></collection>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="student">
        #       通过老师的id查询这个老师下所有学生
        select *
        from student
        where tid = #{id}
    </select>

通用测试方法

public class MybatisTest {
    //获取日志
    Logger logger = Logger.getLogger(MybatisTest.class);
    @Test
    public void selectTeacherById02(){
        SqlSession session = MybatisUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.selectTeacherById(1);
        System.out.println(teacher.getName());
        System.out.println(teacher.getStudents());
    }


}

扩展：

避免慢索引

必定面试问题

• mysql引擎
• innoDB底层原理
• 索引
• 索引优化