一条sql其实就可以将表中的数据全部展示出来,但是要想以树结构的形式展现,必须使用递归的方式。
表 :
CREATE TABLE `tbl_enum` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`enumCode` varchar(50) DEFAULT '' COMMENT '枚举值',
`enumName` varchar(50) DEFAULT '' COMMENT '描述',
`parentCode` varchar(50) DEFAULT '' COMMENT '父类码',
`sort` int(11) DEFAULT '0' COMMENT '排序',
`remark` varchar(100) DEFAULT '' COMMENT '备注',
`isLeaf` smallint(6) DEFAULT NULL COMMENT '是否叶节点',
`createDate` datetime DEFAULT NULL COMMENT '创建日期',
`creatorId` int(11) DEFAULT '0' COMMENT '创建人ID',
`creator` varchar(50) DEFAULT '' COMMENT '创建人名称',
`modifyerId` int(11) DEFAULT '0' COMMENT '修改人ID',
`modifyer` varchar(50) DEFAULT '' COMMENT '修改人名称',
`modifyDate` datetime DEFAULT NULL COMMENT '修改日期',
`state` smallint(6) DEFAULT '0' COMMENT '状态',
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE KEY `Index_1` (`enumCode`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='枚举表';
实体:
public class SrmEnumTree implements Serializable {
private static final long serialVersionUID = 1L;
private String enumCode;
private String enumName;
private Integer isLeaf;
private Integer state;
private String remark;
private List<SrmEnumTree> childs = new ArrayList<>();
(getter & setter方法)
}
Controller :
@GetMapping("/tree")
@ApiOperation(value = "获取枚举树列表")
public QueryResultModel<List<SrmEnumTree>> getEnumTree(@RequestBody SrmEnumTree param) {
List<SrmEnumTree> result = service.getEnumTree(param);
return ResultUtil.success(1, result);
}
Service :
/**
* 获取枚举树
* @return
*/
List<SrmEnumTree> getEnumTree(SrmEnumTree enumTree);
SeviceImpl :
@Override
public List<SrmEnumTree> getEnumTree(SrmEnumTree enumTree) {
List<SrmEnumTree> result = new ArrayList<>();
// 查询所有顶级
Wrapper<TblEnum> wrap = new EntityWrapper<>();
if (DataUtil.isEmpty(enumTree.getEnumCode())) {
wrap.eq("parentCode", enumTree.getEnumCode()).orderBy("sort,ID");
} else {
wrap.eq("enumCode", enumTree.getEnumCode()).orderBy("sort,ID");
}
List<TblEnum> enumList = enumMapper.selectList(wrap);
SrmEnumTree param = null;
for (TblEnum entity : enumList) {
param = new SrmEnumTree();
param.setEnumCode(entity.getEnumCode());
param.setEnumName(entity.getEnumName());
param.setState(entity.getState());
param = recursive(entity.getEnumCode(), param);
result.add(param);
}
return result;
}
/**
* 递归获取枚举树
* @param flag
* @param param
* @return
*/
private SrmEnumTree recursive(String flag, SrmEnumTree param) {
List<SrmEnumTree> mid = enumMapper.querySub(flag);
for (SrmEnumTree entity : mid) {
param.getChilds().add(entity);
if (entity.getIsLeaf() == 1) {
recursive(entity.getEnumCode(), entity);
}
}
return param;
}
Mapper.java
List<SrmEnumTree> querySub(@Param("flag") String flag);
Mapper.xml
<select id="querySub" resultType="com.lesso.srmentity.SrmEnumTree">
SELECT
a.enumCode,
a.enumName,
a.isLeaf,
a.state,
a.remark
FROM
tbl_enum a,
tbl_enum b
WHERE
a.parentCode = b.enumCode
AND b.enumCode = #{flag}
ORDER BY a.sort,a.ID
</select>
效果: