天天看点
返回

MySQL递归查询，Oracle递归查询，MyBatis+MySQL实现递归查询

    递归查询用于查询树形结构的列表，比如行政区列表。包括向下递归查询：根据父级查询子级；向上查询：根据子级查询父级。mysql需要使用存储函数，oracle可以使用connect by语句直接查询。

MySQL递归查询

    首先创建一张表

DROP TABLE IF EXISTS `tb_base_region`;
CREATE TABLE `tb_base_region` (
  `PKID` int(10) NOT NULL AUTO_INCREMENT,
  `REGION_CODE` varchar(16) NOT NULL,
  `REGION_NAME` varchar(40) NOT NULL,
  `SHORT_NAME` varchar(20) DEFAULT NULL,
  `PARENT_CODE` varchar(16) DEFAULT NULL,
  `REGION_LEVEL` int(2) DEFAULT NULL,
  `STATUS` varchar(2) DEFAULT NULL,
  `REMARKS` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`PKID`)
) ENGINE=InnoDB AUTO_INCREMENT=385 DEFAULT CHARSET=utf8;

    插入数据

INSERT INTO `tb_base_region` VALUES (601, '01', '中华人民共和国', '中国', NULL, 1, '1', NULL);
INSERT INTO `tb_base_region` VALUES (602, '0101', '河北省', '冀', '01', 2, '1', NULL);
INSERT INTO `tb_base_region` VALUES (603, '0102', '河南省', '豫', '01', 2, '1', NULL);
INSERT INTO `tb_base_region` VALUES (604, '010101', '石家庄市', '石家庄', '0101', 3, '1', NULL);
INSERT INTO `tb_base_region` VALUES (605, '010102', '保定市', '保定', '0101', 3, '1', NULL);
INSERT INTO `tb_base_region` VALUES (606, '010201', '郑州市', '郑州', '0102', 3, '1', NULL);
INSERT INTO `tb_base_region` VALUES (607, '010202', '新乡市', '新乡', '0102', 3, '1', NULL);

    MySQL递归查询需要用到存储函数，使用存储函数查询出符合要求的数据的region_code集合，以逗号分隔拼接成字符串。然后使用FIND_IN_SET(region_code,存储函数返回结果)函数查询出需要的数据。

    MySQL递归查询存储函数如下：

-- 创建向下查询的存储函数，查询出符合要求的数据的region_code集合，以逗号拼接成字符串。
CREATE FUNCTION getRegionCodeListForDown(upCode VARCHAR(10))
RETURNS VARCHAR(4096)
BEGIN
DECLARE returnCodeList VARCHAR(4096);
DECLARE selectCode VARCHAR(4096);
SET returnCodeList = '';
SET selectCode = upCode;
WHILE selectCode IS NOT NULL DO
SET returnCodeList = CONCAT(returnCodeList,',',selectCode);
select GROUP_CONCAT(region_code) INTO selectCode FROM tb_base_region WHERE FIND_IN_SET(parent_code,selectCode) > 0;
END WHILE;
RETURN returnCodeList;
END;
-- 创建向上查询的存储函数，查询出符合要求的数据的region_code集合。
CREATE FUNCTION getRegionCodeListForUp(downCode VARCHAR(10))
RETURNS VARCHAR(4096)
BEGIN
DECLARE returnCodeList VARCHAR(4096);
DECLARE selectCode VARCHAR(4096);
SET returnCodeList = '';
SET selectCode = downCode;
-- WHILE selectCode IS NOT NULL DO  -- 判断条件视情况而定
WHILE selectCode >= 0 DO
SET returnCodeList = CONCAT(returnCodeList,',',selectCode);
SELECT parent_code into selectCode from tb_base_region WHERE region_code = selectCode;
END WHILE;
RETURN returnCodeList;
END;

    语句中 concat(a,b,c) 函数可以将a、b、c三个字符串做拼接；

    group_concat(a) into a 语句可以将 a 字段查询结果以逗号为分隔符做拼接，拼接结果放入 a 字段。

    group_concat(a) as a .... group by b 语句可以按 b 字段分组，将 a 字段查询结果以逗号分割符做拼接，拼接结果放入 a 字段。

    find_in_set(a,b) > 0 语句可以筛选出符合“ a 字段中的数据存在于 b 参数列举的值中 ”条件的数据。

    这样一来就可以使用存储函数实现递归查询

-- 向下查询（根据父级查询子级）
select * from tb_base_region WHERE FIND_IN_SET(region_code,getRegionCodeListForDown('01'));
-- 向上查询（根据子级查询父级）
select * from tb_base_region WHERE FIND_IN_SET(region_code,getRegionCodeListForUp('010101'));

Oracle递归查询

    oracle提供了递归查询的语句：start with ... connect by ... prior

    start with：递归查询的起始节点

    connect by：递归查询的连接条件

    prior：prior和放在parent_code前代表向上查询；prior放在code前代表向下查询；

        connect by prior region_doce = parent_code：向下查询；

        connect by region_code = prior parent_code：向上查询。

-- 向下查询
select * from tb_base_region start with region_code = '01' connect by prior region_code = parent_code;
-- 向上查询
select * from tb_base_region start with region code = '010101' connect by region_code = prior parent_code;

MyBatis+MySQL实现递归查询

    方法一：如果我们需要向前端提供一个List集合，前端可以使用tree插件实现树形列表的展示，这种方法可以结合MySQL的存储函数实现递归查询。

    方法二：如果我们需要在后端查询出一个树形结构的对象，前端可以使用html标签实现层级关系列表的展示，这种方法不用使用MySQL的存储函数，而是在mapper.xml的结果集<association>标签或<collection>标签中使用select属性实现递归查询，并使用column属性指定递归查询的参数。

方法一：输出一个List集合（结合MySQL的存储过程实现递归查询）

    mapper.xml

<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.shiningcity.company.pojo.BaseRegion">
    <id column="PKID" property="pkid" />
    <result column="REGION_CODE" property="regionCode" />
    <result column="REGION_NAME" property="regionName" />
    <result column="SHORT_NAME" property="shortName" />
    <result column="PARENT_CODE" property="parentCode" />
    <result column="REGION_LEVEL" property="regionLevel" />
    <result column="STATUS" property="status" />
    <result column="REMARKS" property="remarks" />
</resultMap>
    
<!-- 递归查询（向下查询）在sql语句中调用存储过程，根据存储过程返回的id集合查询数据集合 -->
<select id="selectRegionListDownProcedure"
    resultMap="BaseResultMap" statementType="CALLABLE">
    select * from tb_base_region WHERE
    FIND_IN_SET(region_code,getRegionCodeListForDown(#{upCode}));
</select>
    
<!-- 递归查询（向上查询）在sql语句中调用存储过程，根据存储过程返回的id集合查询数据集合 -->
<select id="selectRegionListUpProcedure"
    resultMap="BaseResultMap" statementType="CALLABLE">
    select * from tb_base_region WHERE
    FIND_IN_SET(region_code,getRegionCodeListForUp(#{downCode}));
</select>

    mapper.java

// 向下查询（根据父级查询子级）
List<BaseRegion> selectRegionListDownProcedure(String upCode);
// 向上查询（根据子级查询父级）
List<BaseRegion> selectRegionListUpProcedure(String downCode);

    service层调用mapper接口

@Service
public class BaseCompanyServiceImpl extends SuperServiceImpl<BaseCompanyMapper, BaseCompany> implements BaseCompanyService {
    Logger logger = LoggerFactory.getLogger("BaseCompanyServiceImpl");
    @Autowired
    private BaseCompanyMapper companyMapper;
    @Autowired
    private BaseRegionMapper regionMapper;
    
    @Override
    public String testProcedure() {
        // 向下查询
        List<BaseRegion> regionList1 = regionMapper.selectRegionListDownProcedure("01");
        // 向上查询
        List<BaseRegion> regionList2 = regionMapper.selectRegionListUpProcedure("010101");
        logger.warn(JSON.toJSONString(regionList1));
        logger.warn(JSON.toJSONString(regionList2));
        return "OK";
    }
}

    查询结果转成JSON后结果如下所示，list集合查询结果适用于前端使用tree插件，根据regionCode和parentCode字段可以自动加载成树形结构

// 向下查询
[{"pkid":601,"regionCode":"01","regionLevel":1,"regionName":"中华人民共和国","shortName":"中国","status":"1"},
{"parentCode":"01","pkid":602,"regionCode":"0101","regionLevel":2,"regionName":"河北省","shortName":"冀","status":"1"},
{"parentCode":"01","pkid":603,"regionCode":"0102","regionLevel":2,"regionName":"河南省","shortName":"豫","status":"1"},
{"parentCode":"0101","pkid":604,"regionCode":"010101","regionLevel":3,"regionName":"石家庄市","shortName":"石家庄","status":"1"},
{"parentCode":"0101","pkid":605,"regionCode":"010102","regionLevel":3,"regionName":"保定市","shortName":"保定","status":"1"},
{"parentCode":"0102","pkid":606,"regionCode":"010201","regionLevel":3,"regionName":"郑州市","shortName":"郑州","status":"1"},
{"parentCode":"0102","pkid":607,"regionCode":"010202","regionLevel":3,"regionName":"新乡市","shortName":"新乡","status":"1"}]

// 向上查询
[{"pkid":601,"regionCode":"01","regionLevel":1,"regionName":"中华人民共和国","shortName":"中国","status":"1"},
{"parentCode":"01","pkid":602,"regionCode":"0101","regionLevel":2,"regionName":"河北省","shortName":"冀","status":"1"},
{"parentCode":"0101","pkid":604,"regionCode":"010101","regionLevel":3,"regionName":"石家庄市","shortName":"石家庄","status":"1"}]

方法二：输出一个带层级关系的对象（使用结果集中<association>或<collection>标签的select属性实现递归查询）

    如果是向下查询，BaseRegion类中需要有一个childList字段，存放子级对象集合；如果是向上查询，BaseRegion类中需要有一个parentInfo字段，存放父级对象，但是这样查询结果是倒序的，需要经过java代码转换，将子级对象存放到childList字段中。

    BaseRegion.java

/*
本案例使用MybatisPlus，实体类需要使用@TableName注解匹配数据库表，使用@TableId注解匹配数据库主键，
并继承了MybatisPlus中com.baomidou.mybatisplus.extension.activerecord.Model<>类
*/
@TableName("tb_base_region")
public class BaseRegion extends Model<BaseRegion> {
    private static final long serialVersionUID=1L;
    @TableId(value = "PKID", type = IdType.AUTO)
    private Integer pkid;
    private String regionCode;
    private String regionName;
    private String shortName;
    private String parentCode;
    private Integer regionLevel;
    private String status;
    private String remarks;
    // 子级对象集合，一对多关系
    private List<BaseRegion> childList;
    // 父级对象，一对一关系
    private BaseRegion parentInfo;

    public Integer getPkid() {
        return pkid;
    }
    public void setPkid(Integer pkid) {
        this.pkid = pkid;
    }
    public String getRegionCode() {
        return regionCode;
    }
    public void setRegionCode(String regionCode) {
        this.regionCode = regionCode;
    }
    public String getRegionName() {
        return regionName;
    }
    public void setRegionName(String regionName) {
        this.regionName = regionName;
    }
    public String getShortName() {
        return shortName;
    }
    public void setShortName(String shortName) {
        this.shortName = shortName;
    }
    public String getParentCode() {
        return parentCode;
    }
    public void setParentCode(String parentCode) {
        this.parentCode = parentCode;
    }
    public Integer getRegionLevel() {
        return regionLevel;
    }
    public void setRegionLevel(Integer regionLevel) {
        this.regionLevel = regionLevel;
    }
    public String getStatus() {
        return status;
    }
    public void setStatus(String status) {
        this.status = status;
    }
    public String getRemarks() {
        return remarks;
    }
    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }
    public List<BaseRegion> getChildList() {
        return childList;
    }
    public void setChildList(List<BaseRegion> childList) {
        this.childList = childList;
    }
    public BaseRegion getParentInfo() {
        return parentInfo;
    }
    public void setParentInfo(BaseRegion parentInfo) {
        this.parentInfo = parentInfo;
    }
}

    mapper.xml

    MyBatis中使用结果集中的 <collection>（一对多）或 <association>（一对一）标签可以轻松实现递归查询，其中 <collection> 标签中 ofType 属性或 <association> 标签中 javaType 属性指定了子级或父级对象的类依然是 BaseRegion 类，select 属性指定递归查询使用的方法，column 字段指定递归查询方法中的参数。

<!-- 向下递归查询映射结果 -->
<resultMap id="forEacheResultMapDown" type="com.shiningcity.company.pojo.BaseRegion">
    <id column="PKID" property="pkid" />
    <result column="REGION_CODE" property="regionCode" />
    <result column="REGION_NAME" property="regionName" />
    <result column="SHORT_NAME" property="shortName" />
    <result column="PARENT_CODE" property="parentCode" />
    <result column="REGION_LEVEL" property="regionLevel" />
    <result column="STATUS" property="status" />
    <result column="REMARKS" property="remarks" />
<!-- 使用select属性调用selectRegionListHaveChild2接口实现递归查询，ofType指定递归查询结果的类，column指定递归查询使用的参数 -->
    <collection property="childList"
        ofType="com.shiningcity.company.pojo.BaseRegion"
        select="com.shiningcity.company.mapper.BaseRegionMapper.selectRegionListHaveChild2"
        column="REGION_CODE">
    </collection>
</resultMap>
    
<!-- 向上递归查询映射结果 -->
<resultMap id="forEacheResultMapUp" type="com.shiningcity.company.pojo.BaseRegion">
    <id column="PKID" property="pkid" />
    <result column="REGION_CODE" property="regionCode" />
    <result column="REGION_NAME" property="regionName" />
    <result column="SHORT_NAME" property="shortName" />
    <result column="PARENT_CODE" property="parentCode" />
    <result column="REGION_LEVEL" property="regionLevel" />
    <result column="STATUS" property="status" />
    <result column="REMARKS" property="remarks" />
<!-- 使用select属性调用selectRegionListHaveParent接口实现递归查询，javaType指定递归查询结果的类，column指定递归查询使用的参数 -->
    <association property="parentInfo"
        javaType="com.shiningcity.company.pojo.BaseRegion"
        select="com.shiningcity.company.mapper.BaseRegionMapper.selectRegionListHaveParent"
        column="PARENT_CODE">
    </association>
</resultMap>

<!-- 带层级关系的递归查询（向下查询） -->
<!-- 查询第一层数据的sql语句 -->
<select id="selectRegionListHaveChild1" resultMap="forEacheResultMapDown">
    select * from tb_base_region WHERE REGION_CODE = #{regionCode};
</select>
<!-- 递归查询的sql语句 -->
<select id="selectRegionListHaveChild2" resultMap="forEacheResultMapDown">
    select * from tb_base_region WHERE PARENT_CODE = #{regionCode};
</select>
    
<!-- 带层级关系的递归查询（向上查询） -->
<!-- 查询第一层和递归查询使用相同的sql语句 -->
<select id="selectRegionListHaveParent" resultMap="forEacheResultMapUp">
    select * from tb_base_region WHERE REGION_CODE = #{regionCode};
</select>

    mapper.java

// 向下查询（根据父级查询子级）
BaseRegion selectRegionListHaveChild1(String regionCode);
List<BaseRegion> selectRegionListHaveChild2(String regionCode);
// 向上查询（根据子级查询父级）
BaseRegion selectRegionListHaveParent(String regionCode);

    service层调用mapper接口

@Service
public class BaseCompanyServiceImpl extends SuperServiceImpl<BaseCompanyMapper, BaseCompany> implements BaseCompanyService {
    Logger logger = LoggerFactory.getLogger("BaseCompanyServiceImpl");
    @Autowired
    private BaseCompanyMapper companyMapper;
    @Autowired
    private BaseRegionMapper regionMapper;
    
    @Override
    public String testProcedure() {
        // 向下查询
        BaseRegion regionList3 = regionMapper.selectRegionListHaveChild1("01");
        // 向上查询
        BaseRegion regionList4 = regionMapper.selectRegionListHaveParent("010101");

        // 由于向上查询的结果是倒序的（子级对象中包含父级对象），所以这里要做一个转换，转成正序结果（父级对象中包含子级对象）。
        while (regionList4.getParentCode()!=null) {
            // 构建子级对象集合
            List<BaseRegion> childList = new ArrayList<BaseRegion>();
            BaseRegion info = new BaseRegion();
            info.setParentCode(regionList4.getParentCode());
            info.setPkid(regionList4.getPkid());
            info.setRegionCode(regionList4.getRegionCode());
            info.setRegionLevel(regionList4.getRegionLevel());
            info.setRegionName(regionList4.getRegionName());
            info.setShortName(regionList4.getShortName());
            info.setStatus(regionList4.getStatus());
            info.setChildList(regionList4.getChildList());
            childList.add(info);
            // 当前对象升级为父级对象
            regionList4 = regionList4.getParentInfo();
            // 将子级对象集合加入当前对象
            regionList4.setChildList(childList);
            // 直到当前对象升级为顶级对象后，while循环结束，实现正序结果的转换
        }
        logger.warn(JSON.toJSONString(regionList3));
        logger.warn(JSON.toJSONString(regionList4));
        return "OK";
    }
}

    查询结果转成JSON后结果如下所示，我们向前端提供一个带层级关系的对象，前端只需使用html标签即可展示层级关系的列表。

// 向下查询
{"pkid":601,"regionCode":"01","regionLevel":1,"regionName":"中华人民共和国","shortName":"中国","status":"1",
"childList":[
    {"parentCode":"01","pkid":602,"regionCode":"0101","regionLevel":2,"regionName":"河北省","shortName":"冀","status":"1",
    "childList":[
        {"childList":[],"parentCode":"0101","pkid":604,"regionCode":"010101","regionLevel":3,"regionName":"石家庄市","shortName":"石家庄","status":"1"},
        {"childList":[],"parentCode":"0101","pkid":605,"regionCode":"010102","regionLevel":3,"regionName":"保定市","shortName":"保定","status":"1"}
    ]},
    {"parentCode":"01","pkid":603,"regionCode":"0102","regionLevel":2,"regionName":"河南省","shortName":"豫","status":"1",
    "childList":[
        {"childList":[],"parentCode":"0102","pkid":606,"regionCode":"010201","regionLevel":3,"regionName":"郑州市","shortName":"郑州","status":"1"},
        {"childList":[],"parentCode":"0102","pkid":607,"regionCode":"010202","regionLevel":3,"regionName":"新乡市","shortName":"新乡","status":"1"}
    ]}
]}

// 向上查询
{"pkid":601,"regionCode":"01","regionLevel":1,"regionName":"中华人民共和国","shortName":"中国","status":"1",
"childList":[
    {"parentCode":"01","pkid":602,"regionCode":"0101","regionLevel":2,"regionName":"河北省","shortName":"冀","status":"1",
    "childList":[
        {"parentCode":"0101","pkid":604,"regionCode":"010101","regionLevel":3,"regionName":"石家庄市","shortName":"石家庄","status":"1"}
    ]}
]}
递归查询
上一篇: GitHub标星73.5K的MySQL索引原理以及查询优化
下一篇: *****Oracle中多条件模糊查询、拼音码创建使用*****

继续阅读