当前位置：首页 > news >正文

技术沙龙：Mybatis中resultMap使用案例分享

news 来源：原创 2025/5/21 22:59:53

一、联表查询和单表查询优劣分析

1.1 联表查询的优缺点

优点：

缺点：

1.2 单表查询的优缺点

优点：

缺点：

1.3 适用场景建议

联表查询更适合：

单表查询更适合：

1.4 优化建议

1.5 总结

二、Mybatis中resultType与resultMap之间的关系和使用场景

2.1 reslutType

2.2 reslutMap

三、案例演示

3.1 vo

3.2 controller

3.3 service

3.4 mapper

3.5 结果演示

一、联表查询和单表查询优劣分析

选择哪种方式取决于具体的业务场景、数据量、性能需求等因素。以下是综合分析：

1.1 联表查询的优缺点

优点：

减少网络传输和数据库连接次数：通过一次查询获取多个表的数据，减少IO开销。
简化业务逻辑：数据库层面完成数据关联，减少代码复杂度。
适合数据量小、关联简单的场景：如果表之间有良好的索引，联表查询效率较高。

缺点：

性能问题：数据量大时，联表查询可能导致笛卡尔积或全表扫描，性能下降。
维护困难：复杂SQL难以调试和扩展，分库分表时迁移成本高。
数据库压力大：多表关联可能增加CPU和内存消耗。

1.2 单表查询的优缺点

优点：

性能稳定：单表查询通常更快，尤其适合高频、小数据量的场景。
易于缓存和扩展：结果集更易缓存，分库分表时影响较小。
业务逻辑灵活：通过代码拼接数据，便于需求变更和调试。

缺点：

多次查询：需要多次数据库连接，可能增加IO开销。
代码复杂度高：需手动处理数据关联，代码量增加。

1.3 适用场景建议

联表查询更适合：

数据量较小且关联简单（如主从表关系）。
需要一次性获取完整关联数据的场景（如报表生成）。

单表查询更适合：

数据量大或关联复杂（如多对多关系）。
高并发场景，需减少数据库压力。
需要分库分表或分布式架构。

1.4 优化建议

联表优化：确保关联字段有索引，避免全表扫描。
单表优化：使用`IN`或批量查询减少IO次数。
冗余设计：适当冗余字段以减少关联查询。

1.5 总结

没有绝对的好坏，需根据实际需求权衡。联表查询适合简单关联和小数据量，单表查询适合复杂业务和大数据场景。建议通过性能测试选择最优方案。

二、Mybatis中resultType与resultMap之间的关系和使用场景

2.1 reslutType

使用方式：指定结果集的类型，MyBatis 会自动将查询结果映射到指定类型的对象中，要求查询结果的列名与对象的属性名一致。

优点：使用简单便捷，代码量少。当查询结果与实体类属性名完全匹配时，能快速完成映射。

缺点：灵活性较差，对于复杂的映射关系，如列名与属性名不一致、映射结果对象中还包含对象，像一对多，多对一这种，无法很好地处理。

2.2 reslutMap

使用方式：需要手动定义映射关系，在 reslutMap 元素中明确指定数据库列和实体类属性之间的对应关系。

优点：具有高度的灵活性，可以处理各种复杂的映射需求，包括列名与属性名不一致、嵌套查询、多表关联查询等。当然resultMap还可以像java类一样继承。总之，只要你想，这玩意儿你可以玩出花来。但是注意性能问题，尽量不要过多的嵌套。尽量配置延迟加载lazyLoadingEnabled以达到按需加载。

缺点：配置相对复杂，需要编写额外的 XML 配置，增加了代码量和维护成本。

三、案例演示

案例说明

我要分页查询学生详细信息，这些学生信息分布在多张表中：学生基本信息表、学生老师关系表、学生课时表、课程表。那么每个学生会关联不同的表，做分页查询时，分页统计的应该是主表的记录数，而不是关联后的总记录数。

分析

所以，如果使用resultType可能要先查出来主表的记录数，然后根据主表的记录分别查询各自的相关信息。这种方式显示是更多的通过代码的逻辑去实现。

但是今天，我不想用这种方式，我选择使用reslutMap进行配置，减少代码的逻辑控制。

演示

3.1 vo

/*** @author hssy*/
@ApiModel("学生详情Vo")
@Data
@EqualsAndHashCode(callSuper = true)
public class StudentInfoVo extends StudentInfo {@JsonIgnoreprivate String sysOrgCode;@JsonIgnoreprivate Date createTime;@JsonIgnoreprivate Date updateTime;@JsonIgnoreprivate Integer deleteFlag;@ApiModelProperty("助教ID")private Integer assistantId;@ApiModelProperty("助教姓名")private String assistantName;@ApiModelProperty("课程详情列表")private List<CourseVo> courseVoList;// 创建CourseVo内部类@Datapublic static class CourseVo {private Integer id;private String courseName;private Integer courseRemainHour;}
}

3.2 controller

/*** @author hssy*/
@Api(tags = "学生管理")
@RestController
@RequestMapping("/student")
public class StudentController {@Resourceprivate StudentInfoService studentInfoService;@ApiOperation(value = "查询学生信息(分页)")@PostMapping("/queryStudentPage")public Result<Page<StudentInfoVo>> queryStudentPage(@RequestBody StudentQueryForm queryForm, PageForm pageForm) {Page<StudentInfoVo> page = studentInfoService.queryStudentPage(queryForm, pageForm);return Result.success(page);}
}

3.3 service

/*** <p>* 学员基本信息表 服务实现类* </p>** @author Mr.Hongtao* @since 2025-04-29*/
@Service
public class StudentInfoServiceImpl extends ServiceImpl<StudentInfoMapper, StudentInfo> implements StudentInfoService {@Overridepublic Page<StudentInfoVo> queryStudentPage(StudentQueryForm queryForm, PageForm pageForm) {// 1. 构建分页对象Page<StudentInfo> page = new Page<>(pageForm.getPageNum(), pageForm.getPageSize());// 2. 执行自定义分页查询Page<StudentInfoVo> resultPage = baseMapper.selectStudentDetailPage(page, queryForm);// 3. 转换vo分页对象return resultPage;}
}

3.4 mapper

Page<StudentInfoVo> selectStudentDetailPage(Page<StudentInfo> page,@Param("query") StudentQueryForm queryForm);

<select id="selectStudentDetailPage" resultMap="studentInfoVoResultMap">SELECTsi.*,sa.assistant_id,e.name as assistant_name,sch.course_id,c.course_name,sch.remaining_hour as course_remain_hourFROM student_info siLEFT JOIN student_assistant sa ON si.id = sa.student_idLEFT JOIN employee e ON sa.assistant_id = e.idLEFT JOIN student_course_hour sch ON si.id = sch.student_idLEFT JOIN course c ON sch.course_id = c.id<if test="query.studentName != null and query.studentName != ''">AND si.student_name LIKE CONCAT('%', #{query}, '%')</if><!-- 其他查询条件 --></select><resultMap id="studentInfoVoResultMap" type="com.hssy.edu.vo.StudentInfoVo"><!-- 基础字段映射 --><id column="id" property="id"/><result column="student_name" property="studentName"/><!-- 其他student_info字段映射... --><result column="student_gender" property="studentGender"/><result column="birthday" property="birthday"/><result column="student_phone" property="studentPhone"/><result column="school" property="school"/><result column="grade" property="grade"/><result column="parent_name" property="parentName"/><result column="kinship" property="kinship"/><result column="parent_phone" property="parentPhone"/><result column="parent_job" property="parentJob"/><result column="family_address" property="familyAddress"/><result column="note" property="note"/><result column="create_time" property="createTime"/><result column="update_time" property="updateTime"/><result column="delete_flag" property="deleteFlag"/><!-- 助教信息 --><result column="assistant_id" property="assistantId"/><result column="assistant_name" property="assistantName"/><!-- 课程列表(一对多) --><collection property="courseVoList" ofType="com.hssy.edu.vo.StudentInfoVo$CourseVo"><result column="course_id" property="id"/><result column="course_name" property="courseName"/><result column="course_remain_hour" property="courseRemainHour"/></collection></resultMap>

3.5 结果演示

{"code": 1,"msg": "请求成功！","data": {"records": [{"id": 1,"studentName": "王浩辰","studentGender": "男","birthday": "2010-05-15","studentPhone": "13800138001","school": "第一实验小学","grade": "五年级","parentName": "王强","kinship": "父亲","parentPhone": "13900139001","parentJob": "工程师","familyAddress": "北京市海淀区中关村大街1号","note": "数学成绩优秀","assistantId": 4,"assistantName": "赵六","courseVoList": [{"id": 8,"courseName": "化学实验班","courseRemainHour": 7},{"id": 5,"courseName": "高中数学冲刺班","courseRemainHour": 18},{"id": 3,"courseName": "物理竞赛小班","courseRemainHour": 15}]},{"id": 2,"studentName": "李雨萱","studentGender": "女","birthday": "2011-03-22","studentPhone": "13800138002","school": "第二实验小学","grade": "四年级","parentName": "张芳","kinship": "母亲","parentPhone": "13900139002","parentJob": "教师","familyAddress": "北京市朝阳区建国路88号","note": "擅长舞蹈","assistantId": 4,"assistantName": "赵六","courseVoList": [{"id": 7,"courseName": "雅思写作强化","courseRemainHour": 22},{"id": 2,"courseName": "英语口语1对1","courseRemainHour": 0}]},{"id": 3,"studentName": "张子轩","studentGender": "男","birthday": "2010-11-08","studentPhone": "13800138003","school": "阳光小学","grade": "五年级","parentName": "张建国","kinship": "父亲","parentPhone": "13900139003","parentJob": "医生","familyAddress": "上海市浦东新区陆家嘴环路100号","note": "体育特长生","assistantId": 4,"assistantName": "赵六","courseVoList": [{"id": 1,"courseName": "初中数学强化班","courseRemainHour": 6}]},{"id": 4,"studentName": "刘思琪","studentGender": "女","birthday": "2011-07-19","studentPhone": "13800138004","school": "明珠小学","grade": "四年级","parentName": "王美玲","kinship": "母亲","parentPhone": "13900139004","parentJob": "律师","familyAddress": "广州市天河区体育西路189号","note": "英语口语突出","assistantId": 4,"assistantName": "赵六","courseVoList": [{"id": 4,"courseName": "小学作文基础班","courseRemainHour": 16}]},{"id": 5,"studentName": "陈宇航","studentGender": "男","birthday": "2010-09-30","studentPhone": "13800138005","school": "实验小学","grade": "五年级","parentName": "陈志强","kinship": "父亲","parentPhone": "13900139005","parentJob": "公务员","familyAddress": "深圳市福田区深南大道2008号","note": "编程兴趣浓厚","assistantId": 4,"assistantName": "赵六","courseVoList": [{"id": 6,"courseName": "少儿编程启蒙","courseRemainHour": 27}]},{"id": 6,"studentName": "杨可欣","studentGender": "女","birthday": "2011-02-14","studentPhone": "13800138006","school": "育才小学","grade": "四年级","parentName": "李秀兰","kinship": "母亲","parentPhone": "13900139006","parentJob": "会计师","familyAddress": "杭州市西湖区文三路369号","note": "美术特长生","assistantId": 4,"assistantName": "赵六","courseVoList": []},{"id": 7,"studentName": "赵天佑","studentGender": "男","birthday": "2010-06-25","studentPhone": "13800138007","school": "光明小学","grade": "五年级","parentName": "赵德才","kinship": "爷爷","parentPhone": "13900139007","parentJob": "退休教师","familyAddress": "成都市武侯区人民南路四段1号","note": "围棋三段","assistantId": 6,"assistantName": "孙八","courseVoList": []},{"id": 8,"studentName": "周雯","studentGender": "女","birthday": "2011-04-17","studentPhone": "13800138008","school": "希望小学","grade": "四年级","parentName": "孙建军","kinship": "舅舅","parentPhone": "13900139008","parentJob": "设计师","familyAddress": "重庆市渝中区解放碑1号","note": "钢琴五级","assistantId": 6,"assistantName": "孙八","courseVoList": []},{"id": 9,"studentName": "吴俊杰","studentGender": "男","birthday": "2010-12-03","studentPhone": "13800138009","school": "实验小学","grade": "五年级","parentName": "吴刚","kinship": "父亲","parentPhone": "13900139009","parentJob": "工程师","familyAddress": "武汉市武昌区中南路1号","note": "机器人竞赛获奖","assistantId": 6,"assistantName": "孙八","courseVoList": []},{"id": 10,"studentName": "郑怡","studentGender": "女","birthday": "2011-01-28","studentPhone": "13800138010","school": "阳光小学","grade": "四年级","parentName": "刘秀兰","kinship": "奶奶","parentPhone": "13900139010","parentJob": "退休医生","familyAddress": "西安市雁塔区小寨西路1号","note": "作文比赛一等奖","assistantId": 6,"assistantName": "孙八","courseVoList": []},{"id": 11,"studentName": "王子涵","studentGender": "男","birthday": "2010-08-11","studentPhone": "13800138011","school": "第一实验小学","grade": "五年级","parentName": "王勇","kinship": "父亲","parentPhone": "13900139011","parentJob": "医生","familyAddress": "南京市玄武区中山路1号","note": "科学小发明","assistantId": 6,"assistantName": "孙八","courseVoList": []},{"id": 12,"studentName": "李若曦","studentGender": "女","birthday": "2011-05-09","studentPhone": "13800138012","school": "第二实验小学","grade": "四年级","parentName": "张静","kinship": "母亲","parentPhone": "13900139012","parentJob": "律师","familyAddress": "天津市和平区南京路1号","note": "舞蹈比赛金奖","assistantId": 6,"assistantName": "孙八","courseVoList": []},{"id": 13,"studentName": "张豪","studentGender": "男","birthday": "2010-10-22","studentPhone": "13800138013","school": "明珠小学","grade": "五年级","parentName": "张爱国","kinship": "父亲","parentPhone": "13900139013","parentJob": "公务员","familyAddress": "青岛市市南区香港中路1号","note": "游泳健将","assistantId": 9,"assistantName": "郑十一","courseVoList": []},{"id": 14,"studentName": "刘雨桐","studentGender": "女","birthday": "2011-06-18","studentPhone": "13800138014","school": "育才小学","grade": "四年级","parentName": "王志明","kinship": "叔叔","parentPhone": "13900139014","parentJob": "企业经理","familyAddress": "大连市中山区人民路1号","note": "英语演讲比赛冠军","assistantId": 9,"assistantName": "郑十一","courseVoList": []},{"id": 15,"studentName": "陈昊然","studentGender": "男","birthday": "2010-04-07","studentPhone": "13800138015","school": "希望小学","grade": "五年级","parentName": "陈建国","kinship": "父亲","parentPhone": "13900139015","parentJob": "建筑师","familyAddress": "厦门市思明区湖滨南路1号","note": "数学奥赛获奖","assistantId": 9,"assistantName": "郑十一","courseVoList": []},{"id": 16,"studentName": "杨诗涵","studentGender": "女","birthday": "2011-09-12","studentPhone": "13800138016","school": "光明小学","grade": "四年级","parentName": "李美丽","kinship": "母亲","parentPhone": "13900139016","parentJob": "设计师","familyAddress": "长沙市芙蓉区五一大道1号","note": "绘画特长","assistantId": 9,"assistantName": "郑十一","courseVoList": []},{"id": 17,"studentName": "赵泽宇","studentGender": "男","birthday": "2010-07-05","studentPhone": "13800138017","school": "实验小学","grade": "五年级","parentName": "赵国强","kinship": "父亲","parentPhone": "13900139017","parentJob": "工程师","familyAddress": "郑州市金水区花园路1号","note": "编程比赛获奖","assistantId": 9,"assistantName": "郑十一","courseVoList": []}],"total": 34,"size": 20,"current": 1,"orders": [],"optimizeCountSql": true,"hitCount": false,"countId": null,"maxLimit": null,"searchCount": true,"pages": 2}
}

检查了一遍，分页信息也正确，数据完全正确。这种方式我认为优点是大大降低了业务实现类的复杂程度。代码看着更清爽简洁。

欢迎各位加入讨论，发表观点看法，我们一起学习进步！

一、联表查询和单表查询优劣分析

1.1 联表查询的优缺点

优点：

缺点：

1.2 单表查询的优缺点

优点：

缺点：

1.3 适用场景建议

联表查询更适合：

单表查询更适合：

1.4 优化建议

1.5 总结

二、Mybatis中resultType与resultMap之间的关系和使用场景

2.1 reslutType

2.2 reslutMap

三、案例演示

3.1 vo

3.2 controller

3.3 service

3.4 mapper

3.5 结果演示

相关文章：