天天看點
傳回

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中多條件模糊查詢、拼音碼建立使用*****

繼續閱讀