Mybatis关联查询之一对多和多对一XML配置详解

https://blog.csdn.net/qq_33561055/article/details/78861131

*********************************************************

平时在开发过程中dao、bean和XML文件都是自动生成的，很少写XML的配置关系，今天记录一下mybatis的关联查询中的多对一和一对多的情况。

• 首先是有两张表(学生表Student和老师Teacher表)，为了更易懂，这里只设置了最简单的几个必要字段。表结构如下图

Student表：

Teacher表：

• 创建实体bean

Teacher.java:

    import java.util.List;  
      
    /** 
     * TODO 
     * @version 创建时间：2017年12月21日 上午9:02:45 
     */  
    public class Teacher {  
      
        private Integer id;  
        private String name;  
        private String className;  
        private List<Student> students;  
      
        public List<Student> getStudents() {  
            return students;  
        }  
      
        public void setStudents(List<Student> students) {  
            this.students = students;  
        }  
      
        public Integer getId() {  
            return id;  
        }  
      
        public void setId(Integer id) {  
            this.id = id;  
        }  
      
        public String getName() {  
            return name;  
        }  
      
        public void setName(String name) {  
            this.name = name;  
        }  
      
        public String getClassName() {  
            return className;  
        }  
      
        public void setClassName(String className) {  
            this.className = className;  
        }  
      
    }  

Sfudent.java

    /** 
     * TODO 
     *  
     * @author 作者 E-mail:2332999366@qq.com 
     * @version 创建时间：2017年12月21日 上午9:01:17 
     */  
    public class Student {  
      
        private Integer id;  
        private String name;  
        private Integer teacherId;  
        private String className;  
        private Teacher teacher;  
          
      
        public Teacher getTeacher() {  
            return teacher;  
        }  
      
        public void setTeacher(Teacher teacher) {  
            this.teacher = teacher;  
        }  
      
        public Integer getId() {  
            return id;  
        }  
      
        public void setId(Integer id) {  
            this.id = id;  
        }  
      
        public String getName() {  
            return name;  
        }  
      
        public void setName(String name) {  
            this.name = name;  
        }  
      
        public Integer getTeacherId() {  
            return teacherId;  
        }  
      
        public void setTeacherId(Integer teacherId) {  
            this.teacherId = teacherId;  
        }  
      
        public String getClassName() {  
            return className;  
        }  
      
        public void setClassName(String className) {  
            this.className = className;  
        }  
      
        @Override  
        public String toString() {  
            return "{id:"+this.id+",name:"+this.name+",className:"+this.className+",teacherId:"+this.teacherId+"}";  
        }  
    }   

下面重点来了：配置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.tz.mybatis.dao.studentDao">    
          
        <!-- /////////////////////////////////一对多的第一种写法，一般考虑到性能问题，不会这么实现//////////////////////// -->  
        <resultMap type="Teacher" id="teacherMap">  
            <id column="id" property="id"/>  
            <result column="name" property="name"/>  
            <collection property="students" ofType="Student" column="id">  
                <id column="sid" property="id"/><!-- 这里的column对应的是下面查询的别名，而不是表字段名 -->  
                <result column="sname" property="name"/><!-- property对应JavaBean中的属性名 -->  
                <result column="className" property="className"/>  
            </collection>  
        </resultMap>  
          
          
        <!-- 查询所有的老师级各自的所有学生 -->  
        <select id="getTeachers" parameterType="Teacher" resultMap="teacherMap">  
            SELECT  
                t.id,  
                t.NAME,  
                t.class_Name,  
                s.id AS sid,  
                s. NAME AS sname,  
                s.class_name as className  
            FROM  
                teacher t  
            LEFT JOIN student s ON t.id = s.teacher_id  
        </select>  
    </mapper>  

测试类：

    package com.tz.test;  
      
    import java.io.IOException;  
    import java.io.InputStream;  
    import java.util.List;  
    import org.apache.ibatis.io.Resources;  
    import org.apache.ibatis.session.SqlSession;  
    import org.apache.ibatis.session.SqlSessionFactory;  
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;  
    import org.junit.Before;  
    import org.junit.Test;  
    import com.tz.mybatis.bean.Student;  
    import com.tz.mybatis.bean.Teacher;  
      
    public class TeacherTest {  
      
        private SqlSessionFactory sqlSessionFactory;  
          
        @Before  
        public void init() throws IOException {  
            String resource = "mybatis-config.xml";  
            InputStream inputStream = Resources.getResourceAsStream(resource);  
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);  
        }  
          
        @Test  
        public void getTeachers() {  
            SqlSession session = sqlSessionFactory.openSession();  
            List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getTeachers");  
            System.out.println(list);  
        }  
          
    }  

下面给出第二种写法：

    <!-- //////////////////////////////////////////////一对多的第二种写法///////////////////////////////////////////////////// -->  
        <resultMap type="Teacher" id="teacherMaps">  
            <id column="id" property="id"/>  
            <result column="name" property="name"/>  
            <result column="class_name" property="className"/>  
            <collection property="students" ofType="Student" select="getStudents" column="id">  
            </collection>  
        </resultMap>  
          
          
        <!-- 查询所有的老师级各自的所有学生 -->  
        <select id="getAllTeacher" parameterType="Teacher" resultMap="teacherMaps">  
            SELECT  
                t.id,  
                t.NAME,  
                t.class_name  
            FROM  
                teacher t  
        </select>  
          
        <select id="getStudents" parameterType="int" resultType="Student">  
            select   
                s.id,  
                s. NAME,  
                s.class_name as className  
            from student s  
            where teacher_id = #{id}  
        </select>  

测试类：

    @Test  
        public void getTeachers2() {  
            SqlSession session = sqlSessionFactory.openSession();  
            List<Teacher> list = session.selectList("com.tz.mybatis.dao.studentDao.getAllTeacher");  
            System.out.println(list);  
        }  

查询学生信息(多对一)：

首先还是配置文件：

    <resultMap type="Student" id="studentMap">  
            <id column="id" property="id"/>  
            <result column="name" property="name"/>  
            <result column="class_name" property="className"/>  
            <result column="teacher_id" property="teacherId"/>  
            <association property="teacher" select="getTeacher" column="teacher_id" javaType="Teacher">  
            <!-- 这里要注意的是column对应的是student中的外键，而且需是表字段名 -->  
            </association>  
        </resultMap>  
          
          
        <select id="getStudent" resultMap="studentMap">  
            SELECT  
                s.id,  
                s.name,  
                s.class_name,  
                s.teacher_id  
            FROM  
                student s  
        </select>  
          
        <select id="getTeacher" resultType="Teacher" parameterType="int">  
            SELECT  
                t.id,  
                t.name,  
                t.class_name as className   
            FROM teacher t   
            where id = #{teacher_id}  
        </select>  

测试类：

    @Test  
        public void getStudents() {  
            SqlSession session = sqlSessionFactory.openSession();  
            List<Student> list = session.selectList("com.tz.mybatis.dao.studentDao.getStudent");  
            System.out.println(list);  
        }  

最后：当然如果不想配置这么麻烦的信息，可以直接写一个关联查询的SQL语句，返回结果直接由Map接受即可。不过这样就不太符合面向对象的理念了。