MyBatis中的collection两种使用方法
表关系: 问题表
1==>n 问题选项表
,
需求: 查询问题
时候,联查出来问题选项
主要解决:代码复用性高, 主表分页查询正确,只需要执行一次sql查询, 主表分页查询不正确
//问题 实体类
public class AnotherGoodsInfo {
private Integer id;
private String goods_name;
private Integer goods_type;
private String brand_name;
private String category_name;
private Double special_price;
private String city_name;
private String short_name;
private Integer city_id;
private List<GoodsImage> imageList; // 问题选项 *** 问题表里不需要有这个属性对应的字段
private String image;
}
//问题选项 实体类
public class GoodsImage {
private Integer id;
private String image_url;
private String small_image_url;
}
方式一:
QuestionMapper.xml
<mapper namespace="com.xxx.modules.xxx.mapper.QuestionMapper">
<resultMap id="goodsInfoMapper" type="com.huamo.thirdparty.entity.AnotherGoodsInfo">
<result property="id" column="id"/>
<result property="goods_name" column="goods_name"/>
<result property="goods_type" column="goods_type"/>
<result property="brand_name" column="brand_name"/>
<result property="category_name" column="category_name"/>
<result property="special_price" column="special_price"/>
<result property="city_name" column="city_name"/>
<result property="city_id" column="city_id"/>
<result property="short_name" column="short_name"/>
<result property="image" column="image"/>
<collection property="imageList" select="getGoodsImages" ofType="com.huamo.thirdparty.entity.GoodsImage" javaType="ArrayList"
column="{series_id=id,goods_type=goods_type}">
</collection>
<!-- qid/sort是定义的变量名, id/sort是主表的字段id/sort,
先查出主表的结果, 然后主表记录数是几 就执行几次 collection 的select,
javaType和ofType 写不写都行,
select的值: 对应xml的namespace + 对应xml中的代码片段的id,
column作为select语句的参数传入,如果只传一个参数id可以简写: column="id" -->
</resultMap>
<!-- 查询列表主查询(先查出主表的结果, 然后主表记录数是几 就执行几次 collection 的select,其实就相当于在java代码中写个循环判断在查询,只不过这里交给sql执行不用java代码执行 其实是一样得) -->
<select id="getAllGoodsInfo" resultMap="goodsInfoMapper">
select t1.id,substring(concat(t1.goods_name,ifnull(t6.goods_title,"") ),1,100) as goods_name,t1.goods_type,t4.name as brand_name,t5.category_name,t6.special_price, t7.city_name,t1.venue_cityid as city_id,t7.short_name
from (
select id,supplier_id,venue_cityid,goods_name,goods_type
from hxjb_goods_series
where
(goods_type = 11 OR (goods_type in (9,12) and audit_status = 2))
and line_flag = 1 AND del_flag = 1
) t1
LEFT join `hxjb_supplier_brand` t3 on t1.supplier_id = t3.supplier_id
LEFT join hxjb_brand t4 on t3.brand_id = t4.id
LEFT join hxjb_supplier t2 on t1.supplier_id = t2.id
LEFT join `hxjb_supplier_category` t5 on t2.supplier_category_id = t5.id
LEFT join hxjb_goods t6 on t1.id = t6.`goods_series_id`
left join hxjb_venue_city t7 on t1.venue_cityid = t7.id
ORDER BY t1.id DESC
</select>
<!--子查询-->
<select id="getGoodsImages" parameterType="java.util.Map" resultType="com.huamo.thirdparty.entity.GoodsImage">
select image_url from hxjb_goods_image
where goods_series_id = #{series_id} and del_flag = 1
<choose>
<when test="goods_type == 12">
and image_type_id = 4
</when>
<otherwise> and image_type_id = 2
<!-- pqo.qid = #{qid} <!-- 变量名 qid 对应上文的 qid -->
<!-- 如果上文中 collection只传一个参数column="id",只要类型匹配,在这里随便写个变量名就可以取到值 #{xyz} --> -->
</otherwise>
</choose>
</select>
----------------------------------------------------------------------------------------------------------------
方式二:
/问题 实体类
public class Question {
private String id; //ID
private String content; //问题
private String type; //问题类型 1:单选,2:多选,3:问答
private Integer sort; //排序
private List<QuestionOption> options; //问题选项 *** 问题表里不需要有这个属性对应的字段
//...
}
//问题选项 实体类
public class QuestionOption{
private String id; //ID
private String qid; //问题ID *** 问题选项表里需要有这个属性对应的字段
private String content; //选项
private Integer sort; //排序
//...
}
方式一:
QuestionMapper.xml
<mapper namespace="com.xxx.modules.xxx.mapper.QuestionMapper">
只需要执行一次sql查询, 主表分页查询不正确
<resultMap id="BaseResultMap" type="com.xxx.modules.xxx.entity.Question" >
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="content" property="content" jdbcType="VARCHAR" />
<result column="type" property="type" jdbcType="VARCHAR" />
<result column="sort" property="sort" jdbcType="INTEGER" />
<collection property="options" javaType="java.util.ArrayList" ofType="com.xxx.modules.data.entity.QuestionOption">
<id column="o_id" property="id" jdbcType="VARCHAR" />
<result column="o_content" property="content" jdbcType="VARCHAR" />
<result column="o_sort" property="sort" jdbcType="INTEGER" />
</collection>
<!-- 列的别名 o_id,o_content,o_sort , 起别名是因为主子表都有这几个字段
这里要写 ofType, javaType还是可以不写 -->
</resultMap>
<!-- 查询列表 -->
<select id="selectList" resultMap="BaseResultMap">
SELECT
pq.id, pq.content, pq.type, pq.sort
,pqo.id AS oid ,pqo.content AS ocontent ,pqo.sort AS osort <!-- 联查子表字段,起别名 -->
FROM
question AS pq
LEFT JOIN question_option pqo ON pq.id = pqo.qid <!-- 联查子表 -->
<where>
</where>
</select>
注意: 主子表要查询出来的字段名重复,要起别名
方式二:
<mapper namespace="com.xxx.modules.xxx.mapper.QuestionMapper">
<resultMap id="BaseResultMap" type="com.xxx.modules.xxx.entity.Question" >
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="content" property="content" jdbcType="VARCHAR" />
<result column="type" property="type" jdbcType="VARCHAR" />
<result column="sort" property="sort" jdbcType="INTEGER" />
<collection property="options" javaType="java.util.ArrayList" ofType="com.xxx.modules.xxx.entity.QuestionOption"
select="com.xxx.modules.xxx.mapper.QuestionOptionMapper.selectList" column="{qid=id,sort=sort}" />
<!-- qid/sort是定义的变量名, id/sort是主表的字段id/sort,
先查出主表的结果, 然后主表记录数是几 就执行几次 collection 的select,
javaType和ofType 写不写都行,
select的值: 对应xml的namespace + 对应xml中的代码片段的id,
column作为select语句的参数传入,如果只传一个参数id可以简写: column="id" -->
</resultMap>
<!-- 查询列表 注意: 主子表要查询出来的字段名重复,要起别名 -->
<select id="selectList" resultMap="BaseResultMap">
SELECT
pq.id, pq.content, pq.type, pq.sort
FROM
question AS pq
<where>
</where>
</select>
<mapper namespace="com.xxx.modules.xxx.mapper.QuestionOptionMapper">
<!-- 查询列表 -->
<select id="selectList" resultType="QuestionOption">
SELECT
pqo.id, pqo.content, pqo.sort
FROM
question_option AS pqo
<where>
pqo.qid = #{qid} <!-- 变量名 qid 对应上文的 qid -->
<!-- 如果上文中 collection只传一个参数column="id",只要类型匹配,在这里随便写个变量名就可以取到值 #{xyz} -->
</where>
</select>
————————————————————————————————————————————————
详细请看:https://blog.csdn.net/lzxomg/article/details/89739651
posted on 2021-06-04 14:38 UnmatchedSelf 阅读(2072) 评论(0) 编辑 收藏 举报