一、需求简述
通过后台专家为你挑功能页面,维护导购文章(文章-选购要点-选购要点子项),完成电商app页面的搜索运营
1、E-R关系
如上图所示,文章、选购要点、选购要点子项三者之间的关系如下:
【文章】:【选购要点】= 1:N
【选购要点】:【选购要点子项】 = 1:N
即是:一篇【文章】有多个【选购要点】,一个选购要点可以包含多个【选购要点子项】
2、期望
通过仅仅编写一个sql语句,将上述三者的关系封装到一个实体对象(嵌套对象)中。
二、表结构
上述需求表设计如图所示:
1、表结构(ddl)
CREATE TABLE `core_clothes_expert_tips_article` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`comparison_category_id` int(11) NOT NULL COMMENT '比价三级分类id',
`title` varchar(125) NOT NULL COMMENT '文章标题',
`summary` varchar(500) NOT NULL COMMENT '文章摘要',
`create_time` datetime NOT NULL COMMENT '创建时间',
`creator` char(20) NOT NULL COMMENT '创建人id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 COMMENT = '服装搜索-专家解答文章';
CREATE TABLE `core_clothes_expert_tips_article_item` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`article_id` int(11) NOT NULL COMMENT '专家解答文章id',
`name` varchar(125) NOT NULL COMMENT '名称',
`star` int(2) NOT NULL COMMENT '重要程度(1~5星)',
`content` varchar(255) NULL DEFAULT NULL COMMENT '选购要点说明',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 COMMENT = '服装搜索-专家解答文章-选购要点';
CREATE TABLE `core_clothes_expert_tips_article_sub_item` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`article_id` int(11) NOT NULL COMMENT '专家解答文章id',
`item_id` int(11) NOT NULL COMMENT '选购要点记录id',
`name` varchar(30) NOT NULL COMMENT '名称',
`image_url` varchar(255) NOT NULL COMMENT '图片地址',
`content` text NOT NULL COMMENT '选购要点子项内容',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 COMMENT = '服装搜索-专家解答文章-选购要点子项';
2、测试数据
INSERT INTO `core_clothes_expert_article` VALUES (1, 1, '如何挑选男士牛仔裤', '这里显示文章摘要 这里显示文章摘要 这里显示文章摘要这里显示文章摘要这里显示文章摘要这里显示', '2021-02-19 16:10:39', '111');
INSERT INTO `core_clothes_expert_article` VALUES (2, 2, '如何挑选儿童牛仔裤', '这里显示文章摘要 这里显示文章摘要 这里显示文章摘要这里显示文章摘要这里显示文章摘要这里显示', '2021-02-19 16:10:39', '111');
INSERT INTO `core_clothes_expert_article_item` VALUES (1, 1, '版型', 5, '牛仔裤的版型就是牛仔裤的灵魂,版型在裁剪上需要密切关注,最不容易出错且百搭的就选直筒型。');
INSERT INTO `core_clothes_expert_article_item` VALUES (2, 1, '面料', 4, '有些牛仔裤洗一次就打回原形,是因为定型剂用得不好,而好的牛仔裤除了好具备好的透气性之外还需具备良好的定型。');
INSERT INTO `core_clothes_expert_article_item` VALUES (3, 1, '工艺', 3, '有些牛仔裤洗一次就打回原形,是因为定型剂用得不好,而好的牛仔裤除了好具备好的透气性之外还需具备良好的定型。');
INSERT INTO `core_clothes_expert_article_item` VALUES (4, 2, '版型', 5, '牛仔裤的版型就是牛仔裤的灵魂,版型在裁剪上需要密切关注,最不容易出错且百搭的就选直筒型。');
INSERT INTO `core_clothes_expert_article_item` VALUES (5, 2, '面料', 4, '有些牛仔裤洗一次就打回原形,是因为定型剂用得不好,而好的牛仔裤除了好具备好的透气性之外还需具备良好的定型。');
INSERT INTO `core_clothes_expert_article_item` VALUES (6, 2, '工艺', 3, '有些牛仔裤洗一次就打回原形,是因为定型剂用得不好,而好的牛仔裤除了好具备好的透气性之外还需具备良好的定型。');
INSERT INTO `core_clothes_expert_article_sub_item` VALUES (2, 1, 1, '铅笔型', 'https://img11.360buyimg.com/n1/s350x449_jfs/t1/90206/27/1058/173640/5db9415cE8f1c0fa6/87aba75f7f2c7cd7.jpg!cc_350x449.jpg', '子项内容');
INSERT INTO `core_clothes_expert_article_sub_item` VALUES (3, 1, 1, '修身型', 'https://img11.360buyimg.com/n1/s350x449_jfs/t1/90206/27/1058/173640/5db9415cE8f1c0fa6/87aba75f7f2c7cd7.jpg!cc_350x449.jpg', '子项内容');
INSERT INTO `core_clothes_expert_article_sub_item` VALUES (4, 1, 1, '收脚型', 'https://img11.360buyimg.com/n1/s350x449_jfs/t1/90206/27/1058/173640/5db9415cE8f1c0fa6/87aba75f7f2c7cd7.jpg!cc_350x449.jpg', '子项内容');
三、代码实现
1、mapper.xml
重点是ClothesExpertArticleItemViewResultMap、ClothesExpertArticleSubItemViewResultMap定义(别名和sql语句的别名保持一致即可)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bizranking.synchronization.mapper.ClothesExpertArticleMapper">
<resultMap id="BaseResultMap" type="com.bizranking.common.search.domain.ClothesExpertArticleView">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="comparison_category_id" jdbcType="INTEGER" property="comparisonCategoryId" />
<result column="title" jdbcType="VARCHAR" property="title" />
<result column="summary" jdbcType="VARCHAR" property="summary" />
<collection property="clothesExpertArticleItemViews" resultMap="ClothesExpertArticleItemViewResultMap" />
</resultMap>
<resultMap id="ClothesExpertArticleItemViewResultMap" type="com.bizranking.common.search.domain.ClothesExpertArticleView$ClothesExpertArticleItemView">
<id column="itemId" jdbcType="INTEGER" property="itemId" />
<result column="itemName" jdbcType="VARCHAR" property="itemName" />
<result column="star" jdbcType="INTEGER" property="star" />
<result column="itemContent" jdbcType="VARCHAR" property="itemContent" />
<collection property="clothesExpertArticleSubItemViews" resultMap="ClothesExpertArticleSubItemViewResultMap" />
</resultMap>
<resultMap id="ClothesExpertArticleSubItemViewResultMap" type="com.bizranking.common.search.domain.ClothesExpertArticleView$ClothesExpertArticleSubItemView">
<id column="subItemId" jdbcType="INTEGER" property="subItemId" />
<result column="subItemName" jdbcType="VARCHAR" property="subItemName" />
<result column="imageUrl" jdbcType="VARCHAR" property="imageUrl" />
<result column="subItemContent" jdbcType="LONGVARCHAR" property="subItemContent" />
</resultMap>
<select id="getClothesExpertArticleByIds" resultMap="BaseResultMap">
SELECT
ca.id as id,
ca.comparison_category_id,
ca.title AS title,
ca.summary AS summary,
cai.id AS itemId,
cai.star AS star,
cai.NAME AS itemName,
cai.content AS itemContent,
casi.id AS subItemId,
casi.NAME AS subItemName,
casi.image_url AS imageUrl,
casi.content subItemContent
FROM
core_clothes_expert_article ca
LEFT JOIN core_clothes_expert_article_item cai ON ca.id = cai.article_id
LEFT JOIN core_clothes_expert_article_sub_item casi ON cai.id = casi.item_id
<where>
<if test="ids != null and ids.size() > 0">
ca.id in
<foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</if>
</where>
</select>
</mapper>
2、接收实体(VO)
**
* 服装搜索-专家解答文章
*/
@Data
@Accessors(chain = true)
@Document(indexName = "clothes_expert_article_index")
public class ClothesExpertArticleView implements Serializable {
/**
* 主键
*/
@Id
private Integer id;
/**
* 比价三级分类id
*/
@Field(type = FieldType.Keyword)
private Integer comparisonCategoryId;
/**
* 文章标题
*/
@Field(type = FieldType.Text, analyzer = "ik_max_word", searchAnalyzer = "ik_smart")
private String title;
/**
* 文章摘要
*/
@Field(type = FieldType.Keyword, index = false)
private String summary;
@Field(type = FieldType.Object, index = false)
private List<ClothesExpertArticleItemView> clothesExpertArticleItemViews;
@Data
public static class ClothesExpertArticleItemView {
/**
* 选购要点记录id
*/
@Field(type = FieldType.Keyword, index = false)
private Integer itemId;
/**
* 名称
*/
@Field(type = FieldType.Keyword, index = false)
private String itemName;
/**
* 重要程度(1~5星)
*/
@Field(type = FieldType.Integer, index = false)
private Integer star;
/**
* 选购要点说明
*/
@Field(type = FieldType.Keyword, index = false)
private String itemContent;
@Field(type = FieldType.Object, index = false)
private List<ClothesExpertArticleSubItemView> clothesExpertArticleSubItemViews;
}
@Data
public static class ClothesExpertArticleSubItemView {
/**
* id
*/
@Field(type = FieldType.Keyword, index = false)
private Integer subItemId;
/**
* 名称
*/
@Field(type = FieldType.Keyword, index = false)
private String subItemName;
/**
* 图片地址
*/
@Field(type = FieldType.Keyword, index = false)
private String imageUrl;
/**
* 选购要点子项内容
*/
@Field(type = FieldType.Keyword, index = false)
private String subItemContent;
}
private static final long serialVersionUID = 1L;
}
3、mapper类
@Mapper
public interface ClothesExpertArticleMapper {
/**
* 通过ids获取【文章-选购要点-选购子项】列表
*
* @param ids 文章ids
*/
List<ClothesExpertArticleView> getClothesExpertArticleByIds(@Param("ids") Set<Integer> ids);
}
四、效果演示
1、演示效果
通过打断点调试,mybatis已将嵌套对象填充到ClothesExpertArticleView的内部类中:
2、总结
优点:
- 将“一对多”中的多的一方处理集中在一个方法内,业务层调用方便
- sql撰写变得简单
- 无须在service类中分组处理一对多之间的关系
缺点:
- resultMap定义中的属性与接收对象属性、sql字段别名绑定了,任一处修改都需要注意其他另外两处的改动