天天看點
傳回

MySQL+Java實作父子級聯查詢

概述

在做背景網站(平台/系統)業務開發時，經常遇到層級概念。比如我最近在全權負責（開發+測試+産品）的一款資料産品就有分類的層級概念，層級有3層；另外産品有資料集、圖表、看闆、組合看闆、資料推送等功能點（概念），這些功能點名稱都有層級的概念。

舉個例子：建立一個一級分類（最頂級）資料集。背景知識：資料集其實就是多段SQL，SQL裡面可以有删表後建表的語句（drop then create table），那我可以在這個SQL裡面建立一個最基礎的表（table），隻不過SQL的最後一個子句必須得是查詢字句（資料集概念展現點）。然後我可以再建立一個二級分類的資料集，然後這個資料集的SQL可以使用一級分類資料集SQL裡面的表（table），查詢這個table，用來做圖表。三級分類類推。

MySQL+Java實作父子級聯查詢

上圖中，以​

​/​

​形式拼接傳回多級分類名稱，并給出層級的實作，參考附錄。

分類表設計：

create table category(
    category_id      bigint auto_increment   primary key,
    category_name    varchar(100)            not null,
    type             int(1)                  not null comment '1:資料集 2：圖表 3：看闆 4:組合看闆',
    isactive         tinyint(1) default 1    not null comment '邏輯删除',
    parent_id        bigint                  null comment '父級id'
);

資料準備：

INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (869, '圖表分類A', 2, 1, 898);
INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (882, '圖表分類B', 2, 1, 869);
INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (888, '圖表分類1', 2, 1, 898);
INSERT INTO category (category_id, category_name, type, isactive, parent_id) VALUES (898, '圖表分類', 2, 1, null);

圖表的表設計：

create table widget (
    widget_id         bigint auto_increment primary key,
    widget_name       varchar(100)          not null comment '圖表名稱',
    category_id       bigint                not null comment '分類id',
    isactive          tinyint(1) default 1  not null comment '邏輯删除字段'
);

問題

如何選擇一級分類時，查詢下面的二級以及三級分類呢？具體來說，查詢條件裡面指定圖表的一級分類ID，如何查詢其下的二級和三級分類的圖表？即所謂的MySQL級聯（父子）查詢。

MySQL+Java實作父子級聯查詢

實作

在構思實作方案前先Google一下，發現級聯查詢有兩種情況：自下向上和自上向下。

自下向上

即：給定子級查詢父級。每個子級肯定隻有一個父級，實作起來相對容易些。這裡直接給出實作SQL：

SELECT category_id
FROM (
         SELECT @r           AS _id,
                (
                    SELECT @r := parent_id
                    FROM category
                    WHERE category_id = _id
                )            AS parent_id,
                @l := @l + 1 AS lvl
         FROM (SELECT @r := 893, @l := 0) vars,
              category h
         WHERE @r <> 0
     ) T1
         JOIN category T2 ON T1._id = T2.category_id;

自上向下

即：給定父級查詢全部子級。由于父級含有若幹個子級，每個子級又有若幹個子級，即形成一顆樹的概念。

MySQL實作

通過SQL不好實作。可以通過定義函數的方式，不建議使用函數。

Java實作

實體類定義：

@Data
public class DashboardCategory {
    private Long categoryId;
    private String categoryName;
    private Integer type;
    private Boolean isactive;
    private Long parentId;
    /**
     * 非DB字段,表示第幾級
     */
    private Integer level;
}

​

​CategoryServiceImpl.java​

​實作類，由于最多隻有3級，故而可以兩層for循環嵌套實作，參考下面的附錄，業務代碼在儲存分類時有個數限制。是以for循環嵌套情況下，性能絕對不是問題：

/**
 * 根據分類ID查詢子級分類ID
 *
 * @param categoryId 分類ID
 * @return 清單形式
 */
public List<Long> getChildIds(Long categoryId) {
    List<DashboardCategory> categoryList = categoryMapper.getCategoryListByParentId(categoryId);
    if (CollectionUtils.isEmpty(categoryList)) {
        return Lists.newArrayList(categoryId);
    }
    List<Long> result = Lists.newArrayList(categoryId);
    for (DashboardCategory it : categoryList) {
        result.add(it.getCategoryId());
        List<DashboardCategory> sonCategoryList = categoryMapper.getCategoryListByParentId(it.getCategoryId());
        for (DashboardCategory item : sonCategoryList) {
            result.add(item.getCategoryId());
        }
    }
    return result;
}

​

​CategoryMapper.java​

​接口定義：

List<DashboardCategory> getCategoryListByParentId(Long parentId);

​

​CategoryMapper.xml​

​mapper定義：

<select id="getCategoryListByParentId" resultType="com.xy.cloudiview.common.model.DashboardCategory">
    SELECT category_id categoryId, parent_id parentId FROM category
    WHERE isactive = 1 AND parent_id = #{parentId}
</select>

附錄

儲存分類 

@Value("${category.level.one:15}")
private Integer levelOne;
@Value("${category.level.two:10}")
private Integer levelTwo;
@Value("${category.level.three:10}")
private Integer levelThree;

public String saveCategory(JSONObject jsonObject) {
    try {
        DashboardCategory dashboardCategory = new DashboardCategory();
        Long levelOneId = jsonObject.getLong("levelOneId");
        Long levelTwoId = jsonObject.getLong("levelTwoId");
        Integer type = jsonObject.getInteger("type");
        if (levelOneId == null && levelTwoId != null) {
            return JSONObject.toJSONString(ServiceUtil.returnError("非法情況：一級為空，二級不為空"));
        }
        // 一級分類ID為空,則建立一級分類,先判斷一級分類個數
        if (null == levelOneId) {
            int categoryCount = categoryMapper.selectFirstLevelCategoryCount(type);
            if (categoryCount >= levelOne) {
                return JSONObject.toJSONString(ServiceUtil.returnError(String.format("一級分類不得超過%d個！", levelOne)));
            }
            // 分類名重複校驗
            List<DashboardCategory> list = categoryMapper.getCategoryListByCondition(jsonObject);
            if (CollectionUtils.isNotEmpty(list)) {
                return JSONObject.toJSONString(ServiceUtil.returnError("一級分類名不能重複"));
            }
            // 注意加上else
        } else if (null == levelTwoId) {
            // 一級分類ID不為空,二級分類ID為空,則建立二級分類,先判斷所選一級分類下已有二級分類個數
            int categoryCount = categoryMapper.selectCategoryCountByParentId(levelOneId, type);
            if (categoryCount >= levelTwo) {
                return JSONObject.toJSONString(ServiceUtil.returnError(String.format("二級分類不得超過%d個！", levelTwo)));
            }
            List<DashboardCategory> list = categoryMapper.getCategoryListByCondition(jsonObject);
            if (CollectionUtils.isNotEmpty(list)) {
                return JSONObject.toJSONString(ServiceUtil.returnError("二級分類名不能重複"));
            }
            dashboardCategory.setParentId(levelOneId);
        }

        // 一級二級分類ID都不為空,則建立一個三級分類,父類ID,為二級分類ID
        if (null != levelOneId && null != levelTwoId) {
            int categoryCount = categoryMapper.selectCategoryCountByParentId(levelTwoId, type);
            if (categoryCount >= levelThree) {
                return JSONObject.toJSONString(ServiceUtil.returnError(String.format("三級分類不得超過%d個！", levelThree)));
            }
            List<DashboardCategory> list = categoryMapper.getCategoryListByCondition(jsonObject);
            if (CollectionUtils.isNotEmpty(list)) {
                return JSONObject.toJSONString(ServiceUtil.returnError("三級分類名不能重複"));
            }
            dashboardCategory.setParentId(levelTwoId);
        }
        dashboardCategory.setUserId(jsonObject.getString("userId"));
        dashboardCategory.setCategoryName(jsonObject.getString("categoryName"));
        dashboardCategory.setUpdateUserName(jsonObject.getString("updateUserName"));
        dashboardCategory.setType(type);
        int num = categoryMapper.insertSelective(dashboardCategory);
        if (num > 0) {
            return JSONObject.toJSONString(ServiceUtil.returnSuccess());
        } else {
            return JSONObject.toJSONString(ServiceUtil.returnError("添加分類失敗！"));
        }
    } catch (Exception e) {
        logger.error("saveCategory error:{}", e.toString());
        return JSONObject.toJSONString(ServiceUtil.returnError(e.getMessage()));
    }
}

查詢分類

​

​categoryMapper.getCategoryById(id);​

​，根據主鍵，即category_id查詢，省略代碼。

public String getCategoryList(JSONObject jsonObject) {
    try {
        // 分頁
        PageHelper.startPage(jsonObject.getInteger("pageNo"), jsonObject.getInteger("pageSize"));
        // 代碼省略
        List<DashboardCategory> list = categoryMapper.getCategoryList(jsonObject);
        list.forEach(x -> {
            x.setCategoryName(this.getParentCategoryById(x.getCategoryId()).getT2());
            if (x.getParentId() == null) {
                x.setLevel(1);
            } else if (this.isLevelTwo(x)) {
                x.setLevel(2);
            } else {
                x.setLevel(3);
            }
        });
        PageInfo<DashboardCategory> pageInfo = new PageInfo<>(list);
        return JSONObject.toJSONString(ServiceUtil.returnSuccessData(pageInfo));
    } catch (Exception e) {
        logger.error("getCategoryList error:{}", e.toString());
        return JSONObject.toJSONString(ServiceUtil.returnError(e.getMessage()));
    }
}

/**
 * tuple.t1 為分類ID
 * tuple.t2 為分類名稱
 */
public Tuple<String, String> getParentCategoryById(Long categoryId) {
    DashboardCategory result = categoryMapper.getCategoryById(categoryId);
    Long parentId = result.getParentId();
    Tuple<String, String> tuple = new Tuple<>();
    // 當父級ID為空時,此時為一級分類
    if (null == parentId) {
        tuple.setT1(Collections.singletonList(categoryId).toString());
        tuple.setT2(result.getCategoryName());
        return tuple;
    } else {
        DashboardCategory parentResult = categoryMapper.getCategoryById(parentId);
        // 表明parentResult是一級分類,result為二級分類
        if (null == parentResult.getParentId()) {
            tuple.setT1(Arrays.asList(parentResult.getCategoryId(), categoryId).toString());
            tuple.setT2(parentResult.getCategoryName().concat("/").concat(result.getCategoryName()));
            return tuple;
        } else {
            // 用parentResult的parentId當做categoryId去查詢資訊,lastResult為一級,parentResult為二級,result為三級
            DashboardCategory lastResult = categoryMapper.getCategoryById(parentResult.getParentId());
            tuple.setT1(Arrays.asList(lastResult.getCategoryId(), parentResult.getCategoryId(), categoryId).toString());
            tuple.setT2(lastResult.getCategoryName().concat("/").concat(parentResult.getCategoryName()).concat("/").concat(result.getCategoryName()));
            return tuple;
        }
    }
}

/**
 * 判斷是否是二級
 */
private boolean isLevelTwo(DashboardCategory item) {
    if (item.getParentId() == null) {
        return false;
    }
    DashboardCategory po = categoryMapper.getCategoryById(item.getParentId());
    if (po == null) {
        return false;
    } else {
        return po.getParentId() == null;
    }
}      
@Data
public class Tuple<T1, T2> {
    private T1 t1;
    private T2 t2;
}

參考

mysql json 二級 sql
上一篇: python列出目錄檔案_Python列出指定目錄下的子目錄/檔案或者遞歸列出
下一篇: Java 讀取 Excel 模闆，将資料填入Excel表格，後轉換為PDF檔案（實用）

繼續閱讀