在用mybatis查询时若遇到一对一或者一对多的关系,都会用到association或collection。在这要讲的是多层联表嵌套查询的时候怎么使用association和collection
这里有4个表 ,分别为视频,视频关联题目表,题目表,答案表。关联关系如上图所示
这里表的关系是:视频表----题目表(多对多),题目表----答案表(一对多) videopop是视频表和题目表关联的中间表
现在要做一个查询,给出一个视频的ID,然后得到和这个视频有关的题目还有相关联的答案
在这里可以知道 视频表中要关联查询题目表,并且在题目表中还要关联查询答案表,产生了多层联表查询。
1.给出3个表相应的映射类(视频表,题目表,答案表)不需要videopop这个中间表的实体类
Video.java (视频表)
public class Video {
private Long vId;
private String url;
private Integer category;
private Integer type;
private String title;
private Date createtime;
//视频表和题目表是多对多的关系,拆分开来是两个 一对多 ,所以在这里添加一个题目表实体类的集合的属性
private List<TikuList> tikuLists;
//get和set省略
}
TikuList.java(题库表)
public class TikuList {
private Integer id;
private Integer indexid;
private Integer typeid;
private String typetxt;
private String title;
private Integer orderid;
//这里题目表对应答案是一对多的关系,所以这里属性类型是 题库类的集合
private List<TikuAnswer> tikuAnswerList;
//get和set省略
}
TikuAnswer.java(答案表)
public class TikuAnswer {
private Integer id;
private Integer listid;
private String title;
private Integer isok;
private Integer orderid;
//get和set省略
}
2.在VideoMapper.xml中编写相应sql语句
如下拆分成两个resultmap 里面各自一个conllection 来嵌套查询
<!--这个map存放的是最后查询到的最后结果-->
<resultMap id="VideoMap" type="com.work.webstudy.Pojo.Video" >
<id column="v_id" property="vId" jdbcType="BIGINT" />
<result column="url" property="url" jdbcType="VARCHAR" />
<result column="category" property="category" jdbcType="INTEGER" />
<result column="type" property="type" jdbcType="INTEGER" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="createTime" property="createtime" jdbcType="TIMESTAMP" />
<!--这里是一个一对多的映射 即视频和题目的一对多-->
<collection property="tikuLists" ofType="com.work.webstudy.Pojo.TikuList" column="v_id" select="getlist"/>
</resultMap>
<!--这个Map存放的是通过视频ID 查询到的题目和答案-->
<resultMap id="ListMap" type="com.work.webstudy.Pojo.TikuList" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="indexid" property="indexid" jdbcType="INTEGER" />
<result column="typeid" property="typeid" jdbcType="INTEGER" />
<result column="typetxt" property="typetxt" jdbcType="VARCHAR" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="orderid" property="orderid" jdbcType="INTEGER" />
<collection property="tikuAnswerList" ofType="com.work.webstudy.Pojo.TikuAnswer">
<id column="rid" property="id" jdbcType="INTEGER" />
<result column="alistid" property="listid" jdbcType="INTEGER" />
<result column="atitle" property="title" jdbcType="VARCHAR" />
<result column="isok" property="isok" jdbcType="INTEGER" />
<result column="aorderid" property="orderid" jdbcType="INTEGER" />
</collection>
</resultMap>
<!--通过视频ID获得对应视频的题目和答案-->
<select id="getlist" resultMap="ListMap">
select tl.*,ta.id rid,ta.listid alistid,ta.title atitle,ta.orderid aorderid,ta.isok from videopop vp
left join tiku_list tl on vp.listid=tl.id
left join tiku_answer ta on tl.id=ta.listid
where vp.v_id=#{v_id}
</select>
<!--通过视频ID 获得视频的信息-->
<select id="selectsp" resultMap="VideoMap">
select * from video
where v_id=#{vid}
</select>
或者如下 拆成3个result 实现嵌套查询也可
<resultMap id="VideoMap" type="com.work.webstudy.Pojo.Video" >
<id column="v_id" property="vId" jdbcType="BIGINT" />
<result column="url" property="url" jdbcType="VARCHAR" />
<result column="category" property="category" jdbcType="INTEGER" />
<result column="type" property="type" jdbcType="INTEGER" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="createTime" property="createtime" jdbcType="TIMESTAMP" />
<collection property="tikuLists" ofType="com.work.webstudy.Pojo.TikuList" column="v_id" select="getlist"/>
</resultMap>
<resultMap id="ListMap" type="com.work.webstudy.Pojo.TikuList" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="indexid" property="indexid" jdbcType="INTEGER" />
<result column="typeid" property="typeid" jdbcType="INTEGER" />
<result column="typetxt" property="typetxt" jdbcType="VARCHAR" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="orderid" property="orderid" jdbcType="INTEGER" />
<collection property="tikuAnswerList" ofType="com.work.webstudy.Pojo.TikuAnswer" column="id" select="getanswer"/>
</resultMap>
<resultMap id="AnswerMap" type="com.work.webstudy.Pojo.TikuAnswer" >
<id column="rid" property="id" jdbcType="INTEGER" />
<result column="alistid" property="listid" jdbcType="INTEGER" />
<result column="atitle" property="title" jdbcType="VARCHAR" />
<result column="isok" property="isok" jdbcType="INTEGER" />
<result column="aorderid" property="orderid" jdbcType="INTEGER" />
</resultMap>
<!--获得题目信息-->
<select id="getlist" resultMap="ListMap">
select tl.* from videopop vp
left join tiku_list tl on vp.listid=tl.id
where vp.v_id=#{v_id}
</select>
<!--获得题目相应的答案信息-->
<select id="getanswer" resultMap="AnswerMap">
select ta.id rid,ta.listid alistid,ta.title atitle,ta.orderid aorderid,ta.isok
from tiku_answer ta
where listid=#{id}
</select>
<!--获得视频信息-->
<select id="selectsp" resultMap="VideoMap">
select * from video
where v_id=#{vid}
</select>
3.测试
videoMapper.java 接口里增加相应方法
Video selectsp(@Param("vid")int vid);
创建相应service或者直接调用测试
controller层代码:
@RequestMapping("/get")
public RetResult get(int vid){
Video video=testUserService.selectsp(vid);
return RetResponse.makeOKRsp(video);
}
结果: