级联选择器的应用相对广泛,以前端组件库ElementUI中的Cascader级联选择器为例,可对层级结构的数据集合进行查看并选择。本文使用SpringBoot+MyBatis框架,以“套娃式”继承的方式实现级联选择器后端数据接口,返回层级结构的数据集合。
图1 级联选择器示例
1、数据库表结构
所需数据库表结构如图2、图3、图4和图5所示,表示四层结构,其他更深层结构类似。
图2 factory表
图3 line表
图4 region表
图5 position表
对应SQL创建语句如下:
-- ----------------------------
-- Table structure for factory
-- ----------------------------
DROP TABLE IF EXISTS `factory`;
CREATE TABLE `factory` (
`id` int NOT NULL AUTO_INCREMENT,
`factoryName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb3;
-- ----------------------------
-- Records of factory
-- ----------------------------
INSERT INTO `factory` VALUES ('1', '工厂A');
INSERT INTO `factory` VALUES ('2', '工厂B');
INSERT INTO `factory` VALUES ('3', '工厂C');
-- ----------------------------
-- Table structure for line
-- ----------------------------
DROP TABLE IF EXISTS `line`;
CREATE TABLE `line` (
`id` int NOT NULL AUTO_INCREMENT,
`factoryId` int NOT NULL,
`lineName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`,`factoryId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
-- ----------------------------
-- Records of line
-- ----------------------------
INSERT INTO `line` VALUES ('1', '1', '生产线A1');
INSERT INTO `line` VALUES ('2', '1', '生产线A2');
INSERT INTO `line` VALUES ('3', '2', '生产线B1');
-- ----------------------------
-- Table structure for region
-- ----------------------------
DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
`id` int NOT NULL AUTO_INCREMENT,
`lineId` int NOT NULL,
`regionName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`,`lineId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
-- ----------------------------
-- Records of region
-- ----------------------------
INSERT INTO `region` VALUES ('1', '1', '区域A1-1');
INSERT INTO `region` VALUES ('2', '1', '区域A1-2');
INSERT INTO `region` VALUES ('3', '2', '区域A2-1');
-- ----------------------------
-- Table structure for position
-- ----------------------------
DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
`id` int NOT NULL AUTO_INCREMENT,
`regionId` int NOT NULL,
`positionName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`,`regionId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;
-- ----------------------------
-- Records of position
-- ----------------------------
INSERT INTO `position` VALUES ('1', '1', '职位A1-1-1');
INSERT INTO `position` VALUES ('2', '1', '职位A1-1-2');
INSERT INTO `position` VALUES ('3', '2', '职位A1-2-1');
2、数据实体类“套娃式”继承
父类NameId,声明String类型的name和id属性。
public class NameId implements Serializable {
private static final long serialVersionUID = 1L;
private String name;
private String id;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
@Override
public String toString() {
return "NameId{" +
"name='" + name + '\'' +
", id='" + id + '\'' +
'}';
}
}
子类ChildrenNameId,继承父类NameId,并声明List<NameId>类型的childrenList属性。
public class ChildrenNameId extends NameId implements Serializable {
private static final long serialVersionUID = 1L;
private List<NameId> childrenList;
public List<NameId> getChildrenList() {
return childrenList;
}
public void setChildrenList(List<NameId> childrenList) {
this.childrenList = childrenList;
}
@Override
public String toString() {
return "ChildrenNameId{" +
super.toString() +
"childrenList=" + childrenList +
'}';
}
}
3、查询实现过程
Dao
@Repository
public interface NameIdDao {
public List<ChildrenNameId> getNameIdOfFL();
public List<ChildrenNameId> getNameIdOfFLR();
public List<ChildrenNameId> getNameIdOfFLRP();
}
Mapper
<?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.xxx.dao.NameIdDao">
<resultMap id="NameIdOfFLMap" type="com.xxx.bean.utils.ChildrenNameId">
<result property="id" column="factoryId"/>
<result property="name" column="factoryName"/>
<collection property="childrenList" javaType="ArrayList" ofType="com.xxx.bean.utils.NameId">
<!-- column对应查询返回的列,property对应返回的对象的属性-->
<result property="id" column="lineId"/>
<result property="name" column="lineName"/>
</collection>
</resultMap>
<select id="getNameIdOfFL" resultMap="NameIdOfFLMap">
SELECT f.id factoryId, f.factoryName factoryName,
l.id lineId, l.lineName lineName
from
factory f
left join
line l
on f.id = l.factoryId
order by f.id, l.id
</select>
<resultMap id="NameIdOfFLRMap" type="com.xxx.bean.utils.ChildrenNameId">
<result property="id" column="factoryId"/>
<result property="name" column="factoryName"/>
<collection property="childrenList" javaType="ArrayList" ofType="com.xxx.bean.utils.ChildrenNameId">
<result property="id" column="lineId"/>
<result property="name" column="lineName"/>
<collection property="childrenList" javaType="ArrayList" ofType="com.xxx.bean.utils.NameId">
<result property="id" column="regionId"/>
<result property="name" column="regionName"/>
</collection>
</collection>
</resultMap>
<select id="getNameIdOfFLR" resultMap="NameIdOfFLRMap">
SELECT f.id factoryId, f.factoryName factoryName,
l.id lineId, l.lineName lineName,
r.id regionId, r.regionName regionName
from
factory f
left join
line l
on f.id = l.factoryId
left join
region r
on l.id = r.lineId
order by f.id, l.id, r.id
</select>
<resultMap id="NameIdOfFLRPMap" type="com.xxx.bean.utils.ChildrenNameId">
<result property="id" column="factoryId"/>
<result property="name" column="factoryName"/>
<collection property="childrenList" javaType="ArrayList" ofType="com.xxx.bean.utils.ChildrenNameId">
<result property="id" column="lineId"/>
<result property="name" column="lineName"/>
<collection property="childrenList" javaType="ArrayList" ofType="com.xxx.bean.utils.ChildrenNameId">
<result property="id" column="regionId"/>
<result property="name" column="regionName"/>
<collection property="childrenList" javaType="ArrayList" ofType="com.xxx.bean.utils.NameId">
<result property="id" column="positionId"/>
<result property="name" column="positionName"/>
</collection>
</collection>
</collection>
</resultMap>
<select id="getNameIdOfFLRP" resultMap="NameIdOfFLRPMap">
SELECT f.id factoryId, f.factoryName factoryName,
l.id lineId, l.lineName lineName,
r.id regionId, r.regionName regionName,
p.id positionId, p.positionName positionName
from
factory f
left join
line l
on f.id = l.factoryId
left join
region r
on l.id = r.lineId
left join
position p
on r.id = p.regionId
order by f.id, l.id, r.id, p.id
</select>
</mapper>
Controller
@RequestMapping("/common")
@RestController
public class NameIdController {
private static Map<String, Object> map = new HashMap<>();
@Autowired
private NameIdDao nameIdDao;
@GetMapping("/getNameIdOfFL")
public Result<?> getNameIdOfFL(){
List<ChildrenNameId> nameIdList = nameIdDao.getNameIdOfFL();
map.clear();
map.put("nameIdList",nameIdList);
return Result.success(map,"请求成功!");
}
@GetMapping("/getNameIdOfFLR")
public Result<?> getNameIdOfFLR(){
List<ChildrenNameId> nameIdList = nameIdDao.getNameIdOfFLR();
map.clear();
map.put("nameIdList",nameIdList);
return Result.success(map,"请求成功!");
}
@GetMapping("/getNameIdOfFLRP")
public Result<?> getNameIdOfFLRP(){
List<ChildrenNameId> nameIdList = nameIdDao.getNameIdOfFLRP();
map.clear();
map.put("nameIdList",nameIdList);
return Result.success(map,"请求成功!");
}
}
4、接口请求响应结果
http://127.0.0.1:8888/common/getNameIdOfFL,两层结构接口请求响应结果如下:
{
"code": "200",
"msg": "请求成功!",
"data": {
"nameIdList": [
{
"name": "工厂A",
"id": "1",
"childrenList": [
{
"name": "生产线A1",
"id": "1"
},
{
"name": "生产线A2",
"id": "2"
}
]
},
{
"name": "工厂B",
"id": "2",
"childrenList": [
{
"name": "生产线B1",
"id": "3"
}
]
},
{
"name": "工厂C",
"id": "3",
"childrenList": []
}
]
}
}
http://127.0.0.1:8888/common/getNameIdOfFLR,三层结构接口请求响应结果如下:
{
"code": "200",
"msg": "请求成功!",
"data": {
"nameIdList": [
{
"name": "工厂A",
"id": "1",
"childrenList": [
{
"name": "生产线A1",
"id": "1",
"childrenList": [
{
"name": "区域A1-1",
"id": "1"
},
{
"name": "区域A1-2",
"id": "2"
}
]
},
{
"name": "生产线A2",
"id": "2",
"childrenList": [
{
"name": "区域A2-1",
"id": "3"
}
]
}
]
},
{
"name": "工厂B",
"id": "2",
"childrenList": [
{
"name": "生产线B1",
"id": "3",
"childrenList": []
}
]
},
{
"name": "工厂C",
"id": "3",
"childrenList": []
}
]
}
}
http://127.0.0.1:8888/common/getNameIdOfFLRP,四层结构接口请求响应结果如下:
{
"code": "200",
"msg": "请求成功!",
"data": {
"nameIdList": [
{
"name": "工厂A",
"id": "1",
"childrenList": [
{
"name": "生产线A1",
"id": "1",
"childrenList": [
{
"name": "区域A1-1",
"id": "1",
"childrenList": [
{
"name": "职位A1-1-1",
"id": "1"
},
{
"name": "职位A1-1-2",
"id": "2"
}
]
},
{
"name": "区域A1-2",
"id": "2",
"childrenList": [
{
"name": "职位A1-2-1",
"id": "3"
}
]
}
]
},
{
"name": "生产线A2",
"id": "2",
"childrenList": [
{
"name": "区域A2-1",
"id": "3",
"childrenList": []
}
]
}
]
},
{
"name": "工厂B",
"id": "2",
"childrenList": [
{
"name": "生产线B1",
"id": "3",
"childrenList": []
}
]
},
{
"name": "工厂C",
"id": "3",
"childrenList": []
}
]
}
}
5、总结
本文详细阐述了两层、三层和四层结构数据集合一对多的查询过程,其他更深层结构的查询方式同上类似。因作者技术能力有限,文中不足之处还望各位大佬直抒己见、批评指正,反馈邮箱:yourshare@foxmail.com。