MyBatis如何合并LEFT-JOIN

首先举一个例子，假如学生有很多课程，在Mybatis中最常见的写法应该是这样的

<select>
    select 
    	stu.id as id,
    	stu.name as name
    	les.id as les_id,
    	les.name as les_name
    from
    	student stu 
    left join lesson les stu.id = les.stu_id
</select>

这种场景就是一对多的查询，我们一般用LEFT JOIN来查。假如LEFT JOIN 查询了如下的结果

id	name	les_id	les_name
1	tony	11	php
1	tony	12	cpp
1	tony	13	java

你肯定不希望有三个重复ID的Student

var arr = [
    {id: '1', name: 'tony',les_id: '11', les_name: "php"},
    {id: '1', name: 'tony',les_id: '12', les_name: "cpp"},
    {id: '1', name: 'tony',les_id: '13', les_name: "java"}
]

而是希望返回的是一个如下的对象

{
    id: '1',
    name: 'tony',
    lessons: [
        {id:11,name:'php'},
        {id:12,name:'cpp'},
        {id:13,name:'java'},
    ]
}

这时就要靠MyBatis神奇的ResultMap框架了，其实这个就是函数式编程中常见的GroupBy操作

分析

在Mybatis的多级映射中，使用Map作为GroupBy的依据，断点如下

org.apache.ibatis.executor.resultset.DefaultResultSetHandler#applyNestedResultMappings

其中重点在如下位置加上断点并对比combinedKey的变化情况

final CacheKey rowKey = createRowKey(nestedResultMap, rsw, columnPrefix);
final CacheKey combinedKey = combineKeys(rowKey, parentRowKey);
Object rowValue = nestedResultObjects.get(combinedKey);

更多层嵌套如何写？

假如有如下Student对象，LIST对象中嵌入了课程detail对象，那么这个时候如何写SQL的自动映射呢？

{
 	id: 1,
    name: 'yoki',
    lessons: [{
        id: '11',
        name: 'math',
        detail:{
            time: 40,
            hard: 'middile'
        }
    }]
}

假如我们使用columnPrefix，那么对应的映射为

<resultMap type="xxxx" id="vstudentMap" extend="studentMap">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <collection property="lessons" resultMap="lessonMap"columnPrefix="les_" />
</resultMap>
<resultMap type="xxxx" id="lessonMap">
    <id property="time" column="time" />
    <result property="hard" column="hard" />
    <association property="detail" columnPrefix="ldt_" resultMap="resultMap"/>
</resultMap>

此刻关键就在于加入正确的前缀

select 
	stu.id,
	stu.name,
	les.id as les_id,
	les.name as les_name,
	-- 此次前缀要加两次，否则prefix将无法生效
	ldt.time as les_ldt_time,
	ldt.hard as les_ldt_hard
from
	student stu
left join lesson les on stu.lesson_id = les.id
left join lesson_dtl ldt on les.detail_id = ldt.id

上面的两次前缀如果没有加的话，那么将返回null，这个在MyBatis的官网上与StackOverflow上都没有的例子，这种方法能够完整复与继承用各种resultMap，对代码维护性是最佳的

（很多人不会这样写，就只能拷代码了，引入重复性）

如何返回完整的分页

从上面可以发现，假如当前业务有分页需求，你想查10条数据

select * from (
	select temp.*, rownum as row_id from(
    	select 
            stu.id,
            stu.name,
            les.id as les_id,
            les.name as les_name,
            ldt.time as les_ldt_time,
            ldt.hard as les_ldt_hard
        from
            student stu
        left join lesson les on stu.lesson_id = les.id
        left join lesson_dtl ldt on les.detail_id = ldt.id
    ) where rownum <= 20
) where row_id > 10;

上面的SQL，看起来是没有问题的，网上大部分也是这种场景，但是这种可以说与鸡肋差不多

单表的分页一般通过Mybatis各种开源插件即可实现
而多表分页，一般涉及到JOIN查询，就会有一对多，由于rownum被MyBatis软分组合并影响，导致最终结果会变少，导致在查看数据时前台每次分页的size会不断变化，用户体验较差

你可能需要用临时表这样写

with stu_view as (
	select * from (
        select temp.*, rownum as row_id from(
            select 
                DISTINCT stu.id
            from
                student stu
            left join lesson les on stu.lesson_id = les.id
            left join lesson_dtl ldt on les.detail_id = ldt.id
            order by stu.id
        ) where rownum <= 20
    ) where row_id > 10;
)
select 
    stu.id,
    stu.name,
    les.id as les_id,
    les.name as les_name,
    ldt.time as les_ldt_time,
    ldt.hard as les_ldt_hard
from
    student stu
left join lesson les on stu.lesson_id = les.id
left join lesson_dtl ldt on les.detail_id = ldt.id
where stu.id in (select stu_view.id from stu_view)
order by stu.id

这样写性能明显变差了，但是也是没有办法的办法，因为你查出的并不是每次10条，所以统计去重后的count就要代入条件计算，后面还要再left join一次。虽然慢了，但是普通10行或者30行的分页应该是够了

distinct: 对重复字段进行过滤，如果你在这里的DISTINCT后面引入了LEFT JOIN的表字段，那么在一对多的情况下，DISTINCT将不会生效
order by id: 保证id是按照顺序提取出符合条件的前N条
where stu.id in: 由于这里的id是存储在B-Tree中的连续字段，因此实际用in并不会慢，相反如果这里使用了INNER JOIN的HASH INDEX，速度反而变得更慢

如何写动态SQL

如下是我在进行复杂查询时，涉及到分页与报表导出的SQL例子

<select id="selectByCondition">
    <if test="!param.export">
    with stu_view as (
        select * from (
            select temp.*, rownum as row_id from(
                select 
                    DISTINCT stu.id
                from
                    student stu
                left join lesson les on stu.lesson_id = les.id
                left join lesson_dtl ldt on les.detail_id = ldt.id
        		<include refid="whereclause">
                order by stu.id
            ) where rownum ;lt= 20
        ) where row_id ;gt 10;
    )
    </if>
    select 
        stu.id,
        stu.name,
        les.id as les_id,
        les.name as les_name,
        ldt.time as les_ldt_time,
        ldt.hard as les_ldt_hard
    from
        student stu
    left join lesson les on stu.lesson_id = les.id
    left join lesson_dtl ldt on les.detail_id = ldt.id
    <if test="!param.export">where stu.id in (select stu_view.id from stu_view)</if>
    <if test="param.export"><include refid="whereclause"></if>
    order by stu.id
</select>

充分使用if标签，避免维护多个sql，这样基本上就玩到MyBatis的较高水平了

更加推荐的方案是，使用metabase、redash等BI工具进行查询，配合jinja模版引擎实现