一、使用场景
- 比如有一个大类,大类里面有多个小类,类似于下面这种。这些都是通过后台配置的,先配置小类,然后再配置大类,大类里面再配置相对用的小类。
二、数据库表
- new_product_type 产品类型表
CREATE TABLE `new_product_type` (
`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ID',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
`state` int(1) NULL DEFAULT NULL COMMENT '状态:0.禁用、1.启用',
`site_type` int(1) NULL DEFAULT NULL COMMENT '站点分类:0.新产品新技术馆 1.创新产品馆 2.应用场景馆 3.需求信息',
`sort` int(10) NULL DEFAULT NULL COMMENT '排序',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`creater` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
`site` int(5) NULL DEFAULT NULL COMMENT '站点',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '产品分类字典表' ROW_FORMAT = DYNAMIC;
- new_homepage_type 大类表
CREATE TABLE `new_homepage_type` (
`id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'ID',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '首页分类名称',
`type_ids` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品分类ids',
`state` int(1) NULL DEFAULT NULL COMMENT '状态:0.禁用、1.启用',
`site_type` int(1) NULL DEFAULT NULL COMMENT '站点分类:0.新产品新技术馆 1.创新产品馆 2.应用场景馆 3.需求信息',
`sort` int(10) NULL DEFAULT NULL COMMENT '排序',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`creater` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',
`site` int(5) NULL DEFAULT NULL COMMENT '站点',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '首页分类管理表' ROW_FORMAT = DYNAMIC;
三、代码案例(新增和详情)
1、controller
/**
* 添加首页分类管理表
*
* @param param 首页分类管理表添加参数
* return 首页分类管理表操作结果信息返回
*/
@ApiOperation(value = "添加首页分类管理表", notes = "添加首页分类管理表", httpMethod = "POST")
@PostMapping(value = "/newHomepageType/add")
public Result addNewHomepageType (@Validated @RequestBody @ApiParam(value = "添加首页分类管理表参数") NewHomepageTypeAdd param) {
newHomepageTypeService.addNewHomepageType(param);
return Result.SUCCESS;
}
/**
* 首页分类管理表详情信息
*
* @param param 首页分类管理表主键对象
* @return 首页分类管理表详情信息返回
*/
@ApiOperation(value = "首页分类管理表详情信息", notes = "首页分类管理表详情信息", httpMethod = "POST")
@PostMapping(value = "/newHomepageType/info")
public ResultData<NewHomepageTypeInfo> infoNewHomepageType (@Validated @RequestBody @ApiParam(value = "首页分类管理表详情参数") Id param) {
NewHomepageTypeInfo data = newHomepageTypeService.infoNewHomepageType(param.getId());
return new ResultData(data);
}
2、实体类
@ApiModel(value = "首页分类管理表添加参数")
@Setter
@Getter
@SuppressWarnings("serial")
public class NewHomepageTypeAdd implements Serializable {
private static final long serialVersionUID = 482254711642823740L;
/**
* ID
*/
@ApiModelProperty(value = "ID", example = "")
private String id;
/**
* 首页分类名称
*/
@ApiModelProperty(value = "首页分类名称", example = "")
private String name;
/**
* 产品分类ids
*/
@ApiModelProperty(value = "产品分类ids", example = "")
private String typeIds;
/**
* 状态:0.禁用、1.启用
*/
@ApiModelProperty(value = "状态:0.禁用、1.启用", example = "")
private Integer state;
/**
* 站点分类
*/
@ApiModelProperty(value = "站点分类", example = "")
private Integer siteType;
/**
* 产品分类ids列表
*/
@ApiModelProperty(value = "产品分类ids列表")
private List<String> typeIdss;
/**
* 排序
*/
@ApiModelProperty(value = "排序", example = "")
private Integer sort;
/**
* 创建时间
*/
@ApiModelProperty(value = "创建时间", example = "")
private Date createTime;
/**
* 创建人
*/
@ApiModelProperty(value = "创建人", example = "")
private String creater;
/**
* 站点ID
*/
@NotNull(message = "站点ID不能为空")
@ApiModelProperty(value = "站点ID")
private Integer site;
}
@ApiModel(value = "首页分类管理表详情返回")
@Setter
@Getter
@SuppressWarnings("serial")
public class NewHomepageTypeInfo implements Serializable {
private static final long serialVersionUID = 327830863245135900L;
/**ID*/
@ApiModelProperty(value = "ID", example = "")
private String id;
/**首页分类名称*/
@ApiModelProperty(value = "首页分类名称", example = "")
private String name;
/**产品分类ids*/
@ApiModelProperty(value = "产品分类ids", example = "")
private String typeIds;
/**状态:0.禁用、1.启用*/
@ApiModelProperty(value = "状态:0.禁用、1.启用", example = "")
private Integer state;
/**
* 站点分类
*/
@ApiModelProperty(value = "站点分类", example = "")
private Integer siteType;
/**产品分类ids列表*/
@ApiModelProperty(value = "产品分类ids列表")
private List<String> typeIdss;
/**产品分类名称*/
@ApiModelProperty(value = "产品分类名称", example = "")
private String typeNames;
/**排序*/
@ApiModelProperty(value = "排序", example = "")
private Integer sort;
/**
* 创建时间
*/
@ApiModelProperty(value = "创建时间", example = "")
private Date createTime;
/**
* 创建人
*/
@ApiModelProperty(value = "创建人", example = "")
private String creater;
}
3、service
NewHomepageType addNewHomepageType(NewHomepageTypeAdd newHomepageTypeAdd);
NewHomepageTypeInfo infoNewHomepageType(String id);
4、impl
@Override
@Transactional(rollbackFor = Exception.class, timeout = Constants.TIMEOUT)
public NewHomepageType addNewHomepageType(NewHomepageTypeAdd param) {
NewHomepageType bean = new NewHomepageType();
BeanUtils.copyProperties(param, bean);
bean.setId(StringUtil.uuid());
if (param.getTypeIdss() != null && param.getTypeIdss().size() > 0) {
bean.setTypeIds(String.join(",", param.getTypeIdss()));
}
AdminUserDetails adminUserDetails = (AdminUserDetails) SecurityContextHolder.getContext().getAuthentication()
.getPrincipal();
bean.setId(StringUtil.uuid());
bean.setCreateTime(new Date());
bean.setCreater(adminUserDetails.getUsername());
this.newHomepageTypeDao.addNewHomepageType(bean);
return bean;
}
@Override
public NewHomepageTypeInfo infoNewHomepageType(String id){
NewHomepageTypeInfo newHomepageTypeInfo = newHomepageTypeDao.infoNewHomepageType(id);
return newHomepageTypeInfo;
}
5、dao
void addNewHomepageType(NewHomepageType newHomepageType);
NewHomepageTypeInfo infoNewHomepageType(String id);
6、xml
<!-- 添加NewHomepageType -->
<insert id="addNewHomepageType">
insert into new_homepage_type(id,name, type_ids, state,site_type,sort,create_time,creater,site)
values (#{id},#{name}, #{typeIds}, #{state},#{siteType},#{sort},#{createTime},#{creater},#{site})
</insert>
<!-- 查询单个NewHomepageType实体对象 -->
<select id="infoNewHomepageType" resultType="com.yeyoo.mall.beans.NewHomepageType.NewHomepageTypeInfo">
select a.*,c.typeNames
from new_homepage_type a
left join (select a.id,GROUP_CONCAT(b.name)as typeNames from new_homepage_type a left join new_product_type b on FIND_IN_SET(b.id,a.type_ids) GROUP BY a.id ) c on a.id=c.id
where a.id = #{id}
</select>
四、后台配置页面
五、总结
1、点击编辑,回显数据查询详情,sql 解读
select a.*,c.typeNames
FROM new_homepage_type a
LEFT JOIN (select a.id,GROUP_CONCAT(b.name)as typeNames
FROM new_homepage_type a
LEFT JOIN new_product_type b
ON FIND_IN_SET(b.id,a.type_ids) GROUP BY a.id ) c
ON a.id=c.id
WHERE a.id = '610838c6bcda491696ae4119cf005e08';
- 首先分析最里面的这个sql
- from:对左表new_homepage_type和new_product_type执行笛卡尔积,产生虚拟表v1
- on:对虚拟表v1进行on筛选,只有符合(FIND_IN_SET函数查询 b.id 是否在 a.type_ids 中,如果在则返回该行结果)插入到虚拟表v2中
- join:指定了left join,那么保留表中未匹配的行作为外部行添加到虚拟表v2,产生虚拟表v3
- group by:根据group by子句中的列,对虚拟表v3进行分组操作,产生虚拟表v4
- select:对虚拟表v4执行select操作,选择指定的列,GROUP_CONCAT函数将group by产生的同一个分组中的值连接起来,返回一个字符串结果,将小类名字进行拼接,自动以逗号隔开,插入到虚拟表v5
select a.id,GROUP_CONCAT(b.name)as typeNames
FROM new_homepage_type a
LEFT JOIN new_product_type b
ON FIND_IN_SET(b.id,a.type_ids) GROUP BY a.id
- 最外成的sql就好理解了
六、代码中用到一个方法 String.join
bean.setTypeIds(String.join(",", param.getTypeIdss()));
- 新增时,前台传typeIdss小类集合,后台接收将该集合以逗号拼接,存入到数据库 type_ids 字符串字段中