前言
项目源码位置：GitHub 传送门
在Mybatis中，可以使用resultMap（结果集映射）作为sql的返回类型
一般用来解决如下问题：
- 数据库表字段名和实体类属性名不一致的问题；
- 多对一问题:
- 例如：多个学生对应同一个老师，查询每个学生信息（包含老师对象属性）
- 一对多问题：
- 例如：一个老师教学多个学生，查询某个老师信息及其属下学生（包含学生列表）
1、字段名和属性名不一致问题
单纯的实体类字段和数据库字段不一致问题，较为简单。不在给出搭建环境的过程。
* 实体类字段 数据库字段
* id id
* goods_Name name
* goods_Amount amount
* goods_Price price
3、通过结果集映射
<select id="queryGoodsById" resultMap="GoodsMap">
select *
from goods
where id = #{id}
</select>
<!-- 结果集映射 -->
<resultMap id="GoodsMap" type="Goods">
<!--column数据库中的字段，property实体类中的属性-->
<result column="id" property="id" />
<result column="name" property="goods_Name" />
<result column="amount" property="goods_Amount" />
<result column="price" property="goods_Price" />
</resultMap>
测试单元
/**
* @description: 测试查询商品信息 数据库字段 和 实体类字段不一致问题
* @author: zhengyuzhu
* @date: 2023/11/22 9:19
**/
@Test
public void testDemo1(){
//第一步：获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//方式一：getMapper
GoodsMapper goodsMapper = sqlSession.getMapper(GoodsMapper.class);
Goods goods = goodsMapper.queryGoodsById(1001);
System.out.println(goods);
//关闭SqlSession
sqlSession.close();
/**
* 输出如下 :
* 实体类字段 数据库字段
* id id
* goods_Name name
* goods_Amount amount
* goods_Price price
*
* Goods{id=1001, goods_Name='null', goods_Amount=null, goods_Price=null}
*
*
*
* 第一种方式：字段起别名：
* select id,name as goods_Name,amount as goods_Amount,price as goods_Price
*
* Goods{id=1001, goods_Name='茶杯', goods_Amount=10, goods_Price=13.6}
*
*
* 第二种方式：结果集映射：
* 具体实现查看 GoodsMapper.xml 文件注释描述
*
* Goods{id=1001, goods_Name='茶杯', goods_Amount=10, goods_Price=13.6}
*
**/
}
2 、环境搭建
创建 学生表 和 教师表、插入数据
CREATE TABLE `tb_teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
INSERT INTO tb_teacher(`id`,`name`) VALUES (1,'玉小刚');
CREATE TABLE `tb_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 `tb_student`(`id`,`name`,`tid`) VALUES ('1','唐三','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('2','小舞','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('3','戴沐白','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('4','朱朱清','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('5','奥斯卡','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('6','宁荣荣','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('7','马红俊','1');
INSERT INTO `tb_student`(`id`,`name`,`tid`) VALUES ('8','白尘香','1');
3、多对一处理
获得所有学生及其对应老师（多个学生对应一个老师）
学生类
@Data //get，set
@NoArgsConstructor //无参构造
@AllArgsConstructor //有参构造
public class Student {
private Integer id;
private String name;
private Teacher teacher;
}
教师类
@Data //get，set
@NoArgsConstructor //无参构造
@AllArgsConstructor //有参构造
public class Teacher {
private Integer id;
private String name;
}
mybatis 核心配置文件
这个根据自己设置加载
别名
<typeAliases>
<package name="com.zyz.mybatis.entity" />
</typeAliases>
映射器
<mappers>
<mapper resource="com/zyz/mybatis/mapper/StudentMapper.xml"/>
</mappers>
有两种方式处理多对一关系
- 1、按照结果集嵌套处理
- 2、按照查询嵌套处理
1、按照结果集嵌套处理
<!--
1、按照结果集嵌套处理
这里重点说一下 这个结果集映射。如果对查询出来的数据字段 起了 别名。则映射的时候 要用这个别名。
-->
<resultMap id="StudentMap" type="Student">
<!--这里的column是与查询结果的字段名对应，字段重命名了则对应命名后的字段-->
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<!--复杂的属性需要单独处理 对象：association 集合：collection-->
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
<result property="id" column="tid"/>
</association>
</resultMap>
<!--查询学生-->
<select id="getStudent" resultMap="StudentMap">
select s.id sid,s.name sname,t.name tname,s.tid tid
from tb_student s
join tb_teacher t
on s.tid = t.id
</select>
测试单元
/**
* @description: 1、按照结果集嵌套处理 多对一
* @author: zhengyuzhu
* @date: 2023/11/22 23:07
**/
@Test
public void testDemo1(){
//第一步：获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudent();
for(Student student : studentList){
System.out.println(student);
}
sqlSession.close();
/**
* 输出如下：
*
* [com.zyz.mybatis.mapper.StudentMapper.getStudent]-==> Preparing: select s.id sid,s.name sname,t.name tname,s.tid tid from tb_student s join tb_teacher t on s.tid = t.id
* [com.zyz.mybatis.mapper.StudentMapper.getStudent]-==> Parameters:
* [com.zyz.mybatis.mapper.StudentMapper.getStudent]-<== Total: 8
* Student{id=1, name='唐三', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=2, name='小舞', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=3, name='戴沐白', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=4, name='朱朱清', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=5, name='奥斯卡', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=6, name='宁荣荣', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=7, name='马红俊', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=8, name='白尘香', teacher=Teacher{id=1, name='玉小刚'}}
*
**/
}
2、按照查询嵌套处理
<!--
2、 按照查询嵌套处理
-->
<select id="getStudent2" resultMap="StudentMap2">
select * from tb_student;
</select>
<select id="getTeacher" resultType="Teacher">
select * from tb_teacher where id = #{tid};
</select>
<!--这里将学生和对应老师分开查询，将查询结果组合-->
<resultMap id="StudentMap2" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
测试单元
/**
* @description: 2、 按照查询嵌套处理 多对一
* @author: zhengyuzhu
* @date: 2023/11/22 23:07
**/
@Test
public void testDemo2(){
//第一步：获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = studentMapper.getStudent2();
for(Student student : studentList){
System.out.println(student);
}
sqlSession.close();
/**
* 输出如下：
*
* [com.zyz.mybatis.mapper.StudentMapper.getStudent2]-==> Preparing: select * from tb_student;
* [com.zyz.mybatis.mapper.StudentMapper.getStudent2]-==> Parameters:
* [com.zyz.mybatis.mapper.StudentMapper.getTeacher]-====> Preparing: select * from tb_teacher where id = ?;
* [com.zyz.mybatis.mapper.StudentMapper.getTeacher]-====> Parameters: 1(Integer)
* [com.zyz.mybatis.mapper.StudentMapper.getTeacher]-<==== Total: 1
* [com.zyz.mybatis.mapper.StudentMapper.getStudent2]-<== Total: 8
* Student{id=1, name='唐三', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=2, name='小舞', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=3, name='戴沐白', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=4, name='朱朱清', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=5, name='奥斯卡', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=6, name='宁荣荣', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=7, name='马红俊', teacher=Teacher{id=1, name='玉小刚'}}
* Student{id=8, name='白尘香', teacher=Teacher{id=1, name='玉小刚'}}
*
**/
}
4、一对多处理
按指定ID查询老师及其所管理的学生（一个老师对应多个学生）
StudentT类
@Data //get，set
@NoArgsConstructor //无参构造
@AllArgsConstructor //有参构造
public class StudentT {
private Integer id;
private String name;
private Integer tid;
}
Teacher类
@Data //get，set
@NoArgsConstructor //无参构造
@AllArgsConstructor //有参构造
public class TeacherT {
private Integer id;
private String name;
private List<StudentT> studentTs;
}
mybatis 核心配置文件
这个根据自己设置加载
别名
<typeAliases>
<package name="com.zyz.mybatis.vo" />
</typeAliases>
映射器
<mappers>
<mapper resource="com/zyz/mybatis/mapper/TeacherMapper.xml"/>
</mappers>
有两种方式处理一对多关系
- 1、按照结果集嵌套处理
- 2、按照查询嵌套处理
1、按照结果集嵌套处理
<!--
1、 按结果嵌套查询
-->
<select id="getTeacherById" resultMap="TeacherById">
select t.id id, t.name tname, s.id sid,s.name sname,s.tid tid
from tb_teacher t
join tb_student s
on t.id = s.tid;
</select>
<resultMap id="TeacherById" type="TeacherT">
<result property="id" column="id"/>
<result property="name" column="tname"/>
<!--获取List<Student>中的泛型使用 ofType-->
<collection property="studentTs" ofType="StudentT" javaType="java.util.List">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
测试单元
/**
* @description: 1、 按结果嵌套查询 一对多
* @author: zhengyuzhu
* @date: 2023/11/22 23:07
**/
@Test
public void testDemo3(){
//第一步：获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
TeacherT teacher = teacherMapper.getTeacherById(1);
System.out.println(teacher);
sqlSession.close();
/**
* 输出如下：
*
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 1486566962.
[com.zyz.mybatis.mapper.TeacherMapper.getTeacherById]-==> Preparing: select t.id id, t.name tname, s.id sid,s.name sname,s.tid tid from tb_teacher t join tb_student s on t.id = s.tid;
[com.zyz.mybatis.mapper.TeacherMapper.getTeacherById]-==> Parameters:
[com.zyz.mybatis.mapper.TeacherMapper.getTeacherById]-<== Total: 8
TeacherT(id=1, name=玉小刚, studentTs=[
StudentT(id=1, name=唐三, tid=1),
StudentT(id=2, name=小舞, tid=1),
StudentT(id=3, name=戴沐白, tid=1),
StudentT(id=4, name=朱朱清, tid=1),
StudentT(id=5, name=奥斯卡, tid=1),
StudentT(id=6, name=宁荣荣, tid=1),
StudentT(id=7, name=马红俊, tid=1),
StudentT(id=8, name=白尘香, tid=1)
])
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@589b3632]
*
**/
}
2、按照查询嵌套处理
<!--
2、按照查询嵌套处理
-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from tb_teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="TeacherT">
<result property="id" column="id"/>
<collection property="studentTs" javaType="java.util.List" ofType="StudentT" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="StudentT">
select * from tb_student where tid = #{tid}
</select>
测试单元
/**
* @description: 2、 按照查询嵌套处理 一对多
* @author: zhengyuzhu
* @date: 2023/11/22 23:07
**/
@Test
public void testDemo4(){
//第一步：获得SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
TeacherT teacher = teacherMapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
/**
* 输出如下：
*
[org.apache.ibatis.datasource.pooled.PooledDataSource]-Created connection 1486566962.
[com.zyz.mybatis.mapper.TeacherMapper.getTeacher2]-==> Preparing: select * from tb_teacher where id = ?
[com.zyz.mybatis.mapper.TeacherMapper.getTeacher2]-==> Parameters: 1(Integer)
[com.zyz.mybatis.mapper.TeacherMapper.getStudentByTeacherId]-====> Preparing: select * from tb_student where tid = ?
[com.zyz.mybatis.mapper.TeacherMapper.getStudentByTeacherId]-====> Parameters: 1(Integer)
[com.zyz.mybatis.mapper.TeacherMapper.getStudentByTeacherId]-<==== Total: 8
[com.zyz.mybatis.mapper.TeacherMapper.getTeacher2]-<== Total: 1
TeacherT(id=1, name=玉小刚, studentTs=[
StudentT(id=1, name=唐三, tid=1),
StudentT(id=2, name=小舞, tid=1),
StudentT(id=3, name=戴沐白, tid=1),
StudentT(id=4, name=朱朱清, tid=1),
StudentT(id=5, name=奥斯卡, tid=1),
StudentT(id=6, name=宁荣荣, tid=1),
StudentT(id=7, name=马红俊, tid=1),
StudentT(id=8, name=白尘香, tid=1)
])
[org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@589b3632]
*
**/
}
5、后语
项目源码位置：GitHub 传送门
项目结构如下：
