SpringBoot+MyBatis实现级联选择器后端多层结构数据接口

       级联选择器的应用相对广泛,以前端组件库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。 

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 2

打赏作者

YourShare

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值