数据库查询一对多和多对一 myBatis

多对一

例子：多个学生对应一个老师，查询学生信息+老师信息

1.创建两个实体类 student and teacher

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

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

2.分别创建StudentMapper接口和xml

public interface StudentMapper {
    List<Student> getListStudent();
    List<Student> getListStudent2();
}


<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.chen.dao.StudentMapper">
    第一种查询方式
    <!--一对多学习-->
    <!--按照结果嵌套处理-->
    <select id="getListStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s, teacher t
        where s.tid = t.id;
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>
    <!--================================================================-->
    第两种查询方式
    <!--思路
        1.查询所有的学生信息
        2、根据查询的学生id 查找对应的老师信息
        子查询的方式-->
    <select id="getListStudent" resultMap="studentTeacher">
        select * from student
    </select>
    <resultMap id="studentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性 需要单独处理
        对象使用：association
        集合使用：collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
    
</mapper>

一对多

1.创建两个实体类 student and teacher

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

}

2.分别创建一个TeacherMapper接口和xml

public interface TeacherMapper {

    /**获取指定老师的所有学生*/
    Teacher getTeacher(@Param("id") int id);

    Teacher getTeacherid(@Param("tid")int id);

}


<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.chen.dao.TeacherMapper">
//    第一种查询方式
    <!--按照结果嵌套查询-->
    <!--javaType 用来指定实体类中的属性类型
        oftype   用来指定映射到list 或者集合中pojo类型 泛型中的约束和类型 -->
        
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from student s, teacher t
        where s.tid = t.id
        and t.id = #{id}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

//   第二种查询方式
    <!--子查询-->
    <select id="getTeacherid" resultMap="TeacherStudent2">
        select * from teacher where id = #{tid}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" column="id" javaType="ArrayList" ofType="student" select="getStudentByTeacher"/>
    </resultMap>
    <select id="getStudentByTeacher" resultType="student">
        select * from student where tid = #{tid}
    </select>
</mapper>

@Data 注解需要用到以下架包

 <dependencies>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.10</version>
    </dependency>        
</dependencies>