相关文章
MyBatis系列汇总:MyBatis系列
前言
-
先看官网关于结果集的说明
-
我们一般使用最多的就是result,但在实际情况中,可能会遇到复杂类型的关联!这个时候就需要用到
association
和collection
-
下面是前置条件
-
student表
-
CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) 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
-
-
teacher表
-
CREATE TABLE `teacher` ( `id` int(10) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
-
加点测试数据
-
实体类
-
@Data public class Student { private Integer id; private String name; //需要关联一个老师类 private Teacher teacher; }
-
@Data public class Teacher { private int id; private String name; }
-
-
其他的跟前面文章保持一致即可!多种方式任君选择~
一、多对一
-
首先我们正常查询一下学生试试看结果:
-
public interface StudentMapper { List<Student> getStudent(); }
-
<select id="getStudent" resultType="Student"> select * from student </select>
-
@Test public void getStudent(){ SqlSession session = MybatisUtils.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); List<Student> students = mapper.getStudent(); students.forEach(student -> { System.out.println(student); }); session.close(); }
-
-
执行结果:
-
结果显而易见,在查询中,如果有这种特殊的嵌套类,正常返还是null。下面我们就要使用嵌套映射来解决解决这个问题。
①、查询嵌套(子查询)
-
注意点:resultMap和resultType只能有一个。不能同时存在。
-
修改mapper.xml
-
<select id="getStudent" resultMap="Student"> select * from student </select> <resultMap id="Student" type="com.dy.pojo.Student"> <result property="id" column="id"></result> <result property="name" column="name"></result> <!--对象使用assiociation--> <!--集合用collection--> <association property="teacher" column="tid" javaType="com.dy.pojo.Teacher" select="getTeacher"></association> </resultMap> <select id="getTeacher" resultType="com.dy.pojo.Teacher"> select * from teacher where id = #{tid}; </select>
-
-
执行结果:
-
Nice!完美解决问题!
②、结果嵌套(联表查询)
-
mapper.xml:
-
<select id="getStudent2" resultMap="Student"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id; </select> <resultMap id="Student" type="com.dy.pojo.Student"> <result property="id" column="sid"></result> <result property="name" column="sname"></result> <association property="teacher" javaType="com.dy.pojo.Teacher"> <result property="name" column="tname"></result> </association> </resultMap>
-
-
执行结果:
二、一对多
-
基本前提:
-
@Data@Alias("Teacher2")public class Teacher2 { private int id; private String name; private List<Student1> studentList;}
-
@Datapublic class Student1 { private Integer id; private String name; private int tid;}
①、结果嵌套(联表查询)
-
mapper.xml
-
<select id="getTeacher" resultMap="Teacher2"> 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="Teacher2" type="com.dy.pojo2.Teacher2"> <result property="id" column="tid"></result> <result property="name" column="tname"></result> <!--集合中的泛型信息,我们用oftype获取--> <collection property="studentList" ofType="com.dy.pojo2.Student1"> <result property="id" column="sid"></result> <result property="name" column="sname"></result> </collection> </resultMap>
-
-
执行结果:
-
②、查询嵌套(子查询)
-
mapper.xml
-
<select id="getTeacher2" resultMap="Teacher2"> select * from teacher where id = #{id} </select> <resultMap id="Teacher2" type="com.dy.pojo2.Teacher2"> <collection property="studentList" column="id" javaType="ArrayList" ofType="com.dy.pojo2.Student1" select="getStudentByTeacherId"></collection> </resultMap> <select id="getStudentByTeacherId" resultType="com.dy.pojo2.Student1"> select * from student where tid = #{id} </select>
-
-
执行结果:
-
完美!
三、总结
-
关联 - association 多对一
-
集合 - collection 一对多
-
javaType & ofType
- JavaType用来指定实体中属性类型
- ofType映射到list中的类型,泛型中的约束类型
-
注意点:
- 保证sql可读性,尽量保证通俗易懂
- 如果问题不好排查错误,使用日志
- resultMap 和 resultType 要区分清楚!
路漫漫其修远兮,吾必将上下求索~
如果你认为i博主写的不错!写作不易,请点赞、关注、评论给博主一个鼓励吧~hahah