解决Ibatis一对多映射n+1问题
背景: 一个存在一对多的逻辑关系,比如视频和视频标签(严格来讲是多对多的关系),目前想取出一些视频和该视频包含的标签,传统的做法往往会存在n+1问题,对性能有些影响
在IBatis中如何做?马上进入实战
配置文件如下:
<sqlMap namespace="search">
< resultMap id ="video-map" class ="xx.index.dataset.VideoVO" groupBy ="videoId" >
< result property ="videoId" column ="videoId" />
............................
< result property ="tagList" resultMap ="search.tag-map" />
</ resultMap >
< resultMap id ="tag-map" class ="java.util.HashMap" >
< result property ="tagName" column ="tagName" />
</ resultMap >
< statement id ="query" resultMap ="video-map" parameterClass ="xx.index.dataset.DataSetVO" >
select v.videoId as videoId,
.............
vtg.name as tagname,
............
from video v
inner join videotagrel vtgrel on v.videoId=vtgrel.videoId
inner join videotag vtg on vtgrel.tagId=vtg.tagId
...........
</statement>
........
</sqlMap>
< resultMap id ="video-map" class ="xx.index.dataset.VideoVO" groupBy ="videoId" >
< result property ="videoId" column ="videoId" />
............................
< result property ="tagList" resultMap ="search.tag-map" />
</ resultMap >
< resultMap id ="tag-map" class ="java.util.HashMap" >
< result property ="tagName" column ="tagName" />
</ resultMap >
< statement id ="query" resultMap ="video-map" parameterClass ="xx.index.dataset.DataSetVO" >
select v.videoId as videoId,
.............
vtg.name as tagname,
............
from video v
inner join videotagrel vtgrel on v.videoId=vtgrel.videoId
inner join videotag vtg on vtgrel.tagId=vtg.tagId
...........
</statement>
........
</sqlMap>
我们采用多表关联的方式用一个sql取出所有数据,然后定义我们映射规则(见上面的 resultMap段),从规则中可以看出,我们将查出来的结果根据videoId进行分组,然后相同videoId的标签会归入video的tagList,最后你会得到一个 video列表,其中每个video又包含一个相关的标签列表,整个过程一句sql搞定 避免了n+1问题