Mybatis 一对多 和 多对一查询

一对多

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

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

方法一：嵌套查询

    <resultMap id="teacherStu" type="com.me.domain.Teacher" >
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" javaType="ArrayList" ofType="com.me.domain.Student" select="getStudentByTid" column="id"/>
    </resultMap>

    <select id="getTeacher" resultMap="teacherStu">
        select * from teacher where id = #{id}
    </select>
    
    <select id="getStudentByTid" resultType="com.me.domain.Student">
        select * from student where tid = #{id}
    </select>

方法二：联表查询

    <resultMap id="teacherStu" type="com.me.domain.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="com.me.domain.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
    <select id="getTeacher" resultMap="teacherStu">
        select s.id sid ,s.name sname ,t.id tid ,t.name tname
        from student s, teacher t
        where s.tid = t.id and t.id =#{id}
    </select>

多对一

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

@Data
public class Teacher {
    private int id;
    private String name;
}

方法一：嵌套查询

    <resultMap id="studentTea" type="com.me.domain.Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        
        <association property="teacher" column="tid" javaType="com.me.domain.Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getStudentList" resultMap="studentTea">
        select * from student
    </select>
    
    <select id="getTeacher" resultType="com.me.domain.Teacher">
        select * from teacher where id = #{tid}
    </select>

方法二：联表查询

    <resultMap id="studentTea" type="com.me.domain.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="com.me.domain.Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
    
    <select id="getStudentList" resultMap="studentTea2">
        select s.id sid ,s.name sname ,t.id tid ,t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>

**坑**

当你的项目设置了mybatis查询延迟加载为true时，使用嵌套查询将数据序列化返回给前端时会报错
com.fasterxml.jackson.databind.JsonMappingException: No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: java.util.ArrayList[0]->com.jv.dynamic.bean.Blog_$$_jvstd75_0["handler"])
所以在项目开发的时候，如果要使用第一种嵌套查询方式，那么对你的对象就需要认真考虑，如果不涉及到序列化，可以大胆使用它的延迟加载（fetchType=“lazy”），否则就一致性加载完成（fetchType=“eager”），即解决办法为在collection标签中加上fetchType=“eager”