MyBatis中复杂的结果映射

    科技2022-07-20  99

    1.多对一的处理 多对一的理解:

    多个学生对应一个老师如果对于学生这边,就是一个多对一的现象,即从学生这边关联一个老师!

    实体类的编写

    @Data public class Teacher { private int id; private String name; } @Data public class Student { private int id; private String name; //多个学生可以是同一个老师,即多对一 private Teacher teacher; }

    实体类对应的Mapper接口

    package com.liu.mapper; import com.liu.pojo.Student; import java.util.List; /** * @author liucong * @date 2020/10/4 - 9:29 */ public interface StudentMapper { //查询所有的学生信息,以及对应的老师的信息! List<Student> getStudent(); List<Student> getStudent2(); } package com.liu.mapper; import com.liu.pojo.Teacher; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; /** * @author liucong * @date 2020/10/4 - 9:29 */ public interface TeacherMapper { @Select("select * from teacher where id=#{id}") Teacher getTeacher(@Param("id") int id); }

    Mapper接口对应的 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.liu.mapper.StudentMapper"> <!--子查询 思路: 1.查询所有的学生信息 2.根据查询出来的学生的tid,寻找对应的老师! --> <select id="getStudent" resultMap="studentTeacher"> select * from mybatis.student; </select> <resultMap id="studentTeacher" type="student"> <id 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=#{tid} </select> </mapper>

    测试

    public class MyTest { @Test public void test02(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> students = mapper.getStudent(); for (Student student : students) { System.out.println(student); } sqlSession.close(); } }

    除了上面这种方式,还有其他思路吗? 我们还可以按照结果进行嵌套处理;

    Mapper接口对应的 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.liu.mapper.StudentMapper"> <!--按照结果嵌套处理--> <select id="getStudent2" resultMap="studentTeacher2"> select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t where s.tid=t.id; </select> <resultMap id="studentTeacher2" type="student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="teacher"> <id property="id" column="tid"/> <result property="name" column="tname"/> </association> </resultMap> </mapper>

    测试

    @Test public void test03(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> students = mapper.getStudent2(); for (Student student : students) { System.out.println(student); } sqlSession.close(); }

    小结:

    按照查询进行嵌套处理就像SQL中的子查询按照结果进行嵌套处理就像SQL中的联表查询

    2.一对多的处理

    一对多的理解:

    一个老师拥有多个学生如果对于老师这边,就是一个一对多的现象,即从一个老师下面拥有一群学生(集合)!

    实体类编写

    package com.liu.pojo; import lombok.Data; /** * @author liucong * @date 2020/10/4 - 9:25 */ @Data public class Student { private int id; private String name; private int tid; } package com.liu.pojo; import lombok.Data; import java.util.List; /** * @author liucong * @date 2020/10/4 - 9:27 */ @Data public class Teacher { private int id; private String name; //一个老师拥有多个学生 private List<Student> students; }

    TeacherMapper接口编写

    package com.liu.mapper; import com.liu.pojo.Teacher; import org.apache.ibatis.annotations.Param; import java.util.List; /** * @author liucong * @date 2020/10/4 - 9:29 */ public interface TeacherMapper { //获取指定老师的所有学生及老师信息 Teacher getTeacher2(@Param("id") int id); //获取指定老师的所有学生及老师信息 Teacher getTeacher3(@Param("id") int id); }

    编写接口对应的Mapper配置文件

    <?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.liu.mapper.TeacherMapper"> <select id="getTeacher2" resultMap="teacherStudent"> select t.id tid,t.name tname,s.id sid,s.name sname from teacher t,student s where t.id=s.tid and t.id=#{id} </select> <resultMap id="teacherStudent" type="teacher"> <id property="id" column="tid"/> <result property="name" column="tname"/> <!--javaType=""指定属性的类型--> <!--集合中的泛型信息,我们使用ofType--> <collection property="students" ofType="student"> <id property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap> </mapper>

    测试

    @Test public void test02(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher2(1); System.out.println(teacher); sqlSession.close(); }

    使用子查询

    接口对应的Mapper配置文件

    <select id="getTeacher3" resultMap="teacherStudent2"> select * from mybatis.teacher where id=#{id} </select> <resultMap id="teacherStudent2" type="teacher"> <id property="id" column="id"/> <result property="name" column="name"/> <collection property="students" javaType="ArrayList" ofType="student" select="getStudentByTeacherId" column="id"/> </resultMap> <select id="getStudentByTeacherId" resultType="student"> select * from mybatis.student where tid=#{tid} </select>

    测试

    @Test public void test03(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher3(1); System.out.println(teacher); sqlSession.close(); }

    小结

    关联-association集合-collection所以association是用于一对一和多对一,而collection是用于一对多的关系JavaType和ofType都是用来指定对象类型的 JavaType是用来指定pojo中属性的类型ofType指定的是映射到list集合属性中pojo的类型。
    Processed: 0.013, SQL: 8