MyBatis 查询时属性中一对多的处理（一条数据对应多条数据）

- 日理万妓 2021-09-21 15:32 353阅读 0赞

### 目录 ###

*  前期准备
 *  按照查询嵌套处理
 *  按照结果嵌套处理
 *  小结

多对一处理：[https://blog.csdn.net/weixin\_44953227/article/details/112786245][https_blog.csdn.net_weixin_44953227_article_details_112786245]

官方文档：[https://mybatis.org/mybatis-3/zh/sqlmap-xml.html\#Result\_Maps][https_mybatis.org_mybatis-3_zh_sqlmap-xml.html_Result_Maps]

--------------------

# 前期准备 #

数据表

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

Student 实体类

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

Teacher 实体类

public class Teacher { 
        private int id;
        private String name;
    
        private List<Student> students;
    }

接口

public interface TeacherMapper { 
        // 查询嵌套处理 - 子查询
        Teacher getTeacherById(int id);
    
        // 结果嵌套处理
        Teacher getTeacherResultById(int id);
    }

# 按照查询嵌套处理 #

*  collection：处理集合
    
     *  property：实体类中属性字段
     *  column：查询结果中需要传递给子查询的字段
     *  javaType：指定属性的类型
     *  ofType：指定集合中泛型
     *  select：子查询SQL

<mapper namespace="com.pro.dao.TeacherMapper">
        
        <resultMap id="TeacherMap" type="com.pro.pojo.Teacher">
            <result property="id" column="id"/>
            <collection property="students" column="id" javaType="List" ofType="com.pro.pojo.Student" select="getStudentById"/>
        </resultMap>
    
        <select id="getTeacherById" resultMap="TeacherMap">
            select * from teacher where id = #{id}
        </select>
    
        
        <select id="getStudentById" resultType="com.pro.pojo.Student">
            select * from student where tid = #{id}
        </select>
    </mapper>

# 按照结果嵌套处理 #

*  collection：处理集合
    
     *  javaType：指定属性的类型
     *  ofType：指定集合中泛型

<mapper namespace="com.pro.dao.TeacherMapper">
        
        <resultMap id="TeacherResult" type="com.pro.pojo.Teacher">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            
            <collection property="students" javaType="List" ofType="com.pro.pojo.Student">
                <result property="id" column="sid"/>
                <result property="name" column="sname"/>
            </collection>
        </resultMap>
    
        <select id="getTeacherResultById" resultMap="TeacherResult">
            select t.*, s.id sid, s.name sname  from teacher t, student s where t.id = #{id} and t.id = s.tid
        </select>
    </mapper>

# 小结 #

1.  关联 - association 处理多对一
2.  集合 - collection 处理一对多
3.  javaType & ofType
    
    1.  javaType： 用来指定实体类中属性的类型
    2.  ofType： 指定集合中的类型，泛型中的约束类型

[https_blog.csdn.net_weixin_44953227_article_details_112786245]: https://blog.csdn.net/weixin_44953227/article/details/112786245
[https_mybatis.org_mybatis-3_zh_sqlmap-xml.html_Result_Maps]: https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps