当我们需要查询的数据分布在多个表中时,我们可以采用联表,但有时候联表语句比较复杂,或者联表后的数据集太大,不但性能上可能会有问题,维护起来也很麻烦。
Mybatis提供了分步查询的功能。分步查询可以将复杂的联表查询分解成多个单表(或多个简单的联表)查询,维护起来更容易,还可以实现延迟加载,节省资源。
Mybatis的分步查询的分步执行流程是:执行上层的查询语句,如果有n条数据数据,m个分步查询,则分n次依次执行这m个分步查询语句,也就是总共执行n × m
个分步查询语句;如果某个分步查询语句也有自身的分步查询,则先执行完自身的分步查询再执行下一个分步查询,也就是采用深度递归的方式执行。
代码:
- 分步查询典型用法:
最上层的查询:
<select id="listClassInfos" resultMap="listClassInfosMap">
SELECT `id`, `name` FROM `class_info` WHERE NAME LIKE CONCAT('%', #{name}, '%') LIMIT #{pageStart}, #{pageSize}
</select>
<resultMap id="listClassInfosMap" type="com.example.study.vo.ClassInfoVo">
<id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
<result column="name" property="name" jdbcType="VARCHAR" javaType="java.lang.String"/>
<!-- 分步查询1:select可以引用其它Mapper已有的查询语句,需要写全路径 -->
<association property="master" column="{classId = id}"
select="com.example.study.mapper.ClassMasterInfoMapper.getMasterByClassId"/>
<!-- 分步查询2:select也可以引用本Mapper的查询语句,不需要写全路径 -->
<collection property="students" column="{classId = id}" select="listStudentsByClassId"/>
<!-- 以上两个分步查询都可以添加fetchType="lazy"配置,在该查询中开启延迟加载,否则为全局默认 -->
</resultMap>
<select id="listStudentsByClassId" resultType="com.example.study.entity.StudentInfoEntity">
SELECT
`id` as `id`,
`name` as `name`,
`sex` as `sex`,
`class_id` as `classId`
FROM `student_info`
WHERE `class_id` = #{classId}
</select>
ClassMasterInfoMapper.xml中的查询:
<select id="getMasterByClassId" resultMap="getMasterByClassIdMap">
SELECT
`id` as `id`,
`class_id` as `classId`,
`name` as `name`,
`sex` as `sex`
FROM `class_master_info`
WHERE `class_id` = #{classId}
</select>
<resultMap id="getMasterByClassIdMap" type="com.example.study.entity.ClassMasterInfoEntity">
<id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
<result column="name" property="name" jdbcType="VARCHAR" javaType="java.lang.String"/>
<result column="sex" property="sex" jdbcType="VARCHAR" javaType="java.lang.String"/>
<!-- 分步查询1:select可以引用其它Mapper已有的查询语句,需要写全路径 -->
<association property="self" column="{selfId = id}" select="self"/>
</resultMap>
<select id="self" resultType="com.example.study.entity.ClassMasterInfoEntity">
SELECT
`id` as `id`,
`class_id` as `classId`,
`name` as `name`,
`sex` as `sex`
FROM `class_master_info`
WHERE `id` = #{selfId}
</select>
- 当最上层查询到2条数据时,sql执行顺序和内容如下(从
org.apache.ibatis.mapping.MappedStatement.getBoundSql(Object parameterObject
方法打断点,执行boundSql.getSql()
获得执行的sql内容):
# 第1个sql:查询最上层的数据,查到2条数据
SELECT `id`, `name` FROM `class_info` WHERE NAME LIKE CONCAT('%', ?, '%') LIMIT ?, ?
# 第2个sql:第1条数据,第1个子查询
SELECT
`id` as `id`,
`class_id` as `classId`,
`name` as `name`,
`sex` as `sex`
FROM `class_master_info`
WHERE `class_id` = ?
# 第3个sql:第1条数据,第1个子查询的第1个子查询(只有一个)
SELECT
`id` as `id`,
`class_id` as `classId`,
`name` as `name`,
`sex` as `sex`
FROM `class_master_info`
WHERE `id` = ?
# 第4个sql:第1条数据,第2个子查询
SELECT
`id` as `id`,
`name` as `name`,
`sex` as `sex`,
`class_id` as `classId`
FROM `student_info`
WHERE `class_id` = ?
# 第5个sql:第2条数据,第1个子查询
SELECT
`id` as `id`,
`class_id` as `classId`,
`name` as `name`,
`sex` as `sex`
FROM `class_master_info`
WHERE `class_id` = ?
# 第6个sql:第2条数据,第1个子查询的第1个子查询(只有一个)
SELECT
`id` as `id`,
`class_id` as `classId`,
`name` as `name`,
`sex` as `sex`
FROM `class_master_info`
WHERE `id` = ?
# 第7个sql:第2条数据,第2个子查询
SELECT
`id` as `id`,
`name` as `name`,
`sex` as `sex`,
`class_id` as `classId`
FROM `student_info`
WHERE `class_id` = ?