递归删除-优化

问题:最近学习时,当使用递归删除时,刚开始使用的是,直接递归删除,这样调用sql语句过多,效率大大的低;
解决:使用先递归查询,在通过delete from table where id in(1,2,3);这样的sql语句进行删除.

代码演示

直接递归删除:

/**
     * 方式一:删除内容类目(直接递归删除,删除语句多,不好)
     * 
     * @param parentId
     *            父内容类目id
     * @param id
     *            主键
     * @return
     */
    public Boolean deletContentCategory(Long parentId, Long id) {
        // 迭代删除所有子类目

        int count = deletIterator(id);

        if (count != 1) {
            throw new RuntimeException("删除类目失败,但没有异常");
        }

        // 查看该父类下是否还有子类
        ContentCategory record = new ContentCategory();
        record.setParentId(parentId);
        List<ContentCategory> queryListByWhere = super.queryListByWhere(record);
        if (queryListByWhere != null && !queryListByWhere.isEmpty()) {
            // 进来表示父类还有子类,不做更改,直接返回
            return true;
        }
        // 父类类目的isParent属性更改为false
        ContentCategory parent = super.queryById(parentId);
        if (parent.getIsParent()) {
            parent.setIsParent(false);
            // mybatis没有快照机制?
            super.updateSelective(parent);
        }
        return true;
    }

    /**
     * 递归删除
     * 
     * @param id
     * @return
     */
    private int deletIterator(Long id) {
        int count = super.deleteById(id);
        ContentCategory condition = new ContentCategory();
        condition.setParentId(id);
        List<ContentCategory> list = super.queryListByWhere(condition);
        for (ContentCategory contentCategory : list) {
            deletIterator(contentCategory.getId());
        }
        return count;
    }

通过日记，一共执行了多条sql语句：

15:20:33,322 DEBUG SpringManagedTransaction:86 - JDBC Connection [ConnectionHandle{url=jdbc:mysql://127.0.0.1:3306/taotao?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true, user=root, debugHandle=null, lastResetAgoInSec=47, lastUsedAgoInSec=37, creationTimeAgoInSec=47}] will be managed by Spring
15:20:33,323 DEBUG deleteByPrimaryKey:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE (ID = ? ) 
15:20:33,323 DEBUG deleteByPrimaryKey:139 - ==> Parameters: 19(Long)
15:20:33,342 DEBUG deleteByPrimaryKey:139 - <==    Updates: 1
15:20:33,342 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,343 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,344 DEBUG select:139 - ==>  Preparing: SELECT SORT_ORDER SORTORDER,UPDATED,PARENT_ID PARENTID,NAME,IS_PARENT ISPARENT,ID,STATUS,CREATED FROM tb_content_category WHERE PARENT_ID = ? 
15:20:33,344 DEBUG select:139 - ==> Parameters: 19(Long)
15:20:33,348 DEBUG select:139 - <==      Total: 1
15:20:33,350 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,350 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,350 DEBUG deleteByPrimaryKey:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE (ID = ? ) 
15:20:33,351 DEBUG deleteByPrimaryKey:139 - ==> Parameters: 20(Long)
15:20:33,352 DEBUG deleteByPrimaryKey:139 - <==    Updates: 1
15:20:33,352 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,353 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,353 DEBUG select:139 - ==>  Preparing: SELECT SORT_ORDER SORTORDER,UPDATED,PARENT_ID PARENTID,NAME,IS_PARENT ISPARENT,ID,STATUS,CREATED FROM tb_content_category WHERE PARENT_ID = ? 
15:20:33,354 DEBUG select:139 - ==> Parameters: 20(Long)
15:20:33,360 DEBUG select:139 - <==      Total: 1
15:20:33,360 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,360 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,361 DEBUG deleteByPrimaryKey:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE (ID = ? ) 
15:20:33,361 DEBUG deleteByPrimaryKey:139 - ==> Parameters: 21(Long)
15:20:33,362 DEBUG deleteByPrimaryKey:139 - <==    Updates: 1
15:20:33,362 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,363 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,363 DEBUG select:139 - ==>  Preparing: SELECT SORT_ORDER SORTORDER,UPDATED,PARENT_ID PARENTID,NAME,IS_PARENT ISPARENT,ID,STATUS,CREATED FROM tb_content_category WHERE PARENT_ID = ? 
15:20:33,364 DEBUG select:139 - ==> Parameters: 21(Long)
15:20:33,366 DEBUG select:139 - <==      Total: 1
15:20:33,366 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,367 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,367 DEBUG deleteByPrimaryKey:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE (ID = ? ) 
15:20:33,368 DEBUG deleteByPrimaryKey:139 - ==> Parameters: 22(Long)
15:20:33,369 DEBUG deleteByPrimaryKey:139 - <==    Updates: 1
15:20:33,369 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,369 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,370 DEBUG select:139 - ==>  Preparing: SELECT SORT_ORDER SORTORDER,UPDATED,PARENT_ID PARENTID,NAME,IS_PARENT ISPARENT,ID,STATUS,CREATED FROM tb_content_category WHERE PARENT_ID = ? 
15:20:33,371 DEBUG select:139 - ==> Parameters: 22(Long)
15:20:33,373 DEBUG select:139 - <==      Total: 1
15:20:33,373 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,374 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,374 DEBUG deleteByPrimaryKey:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE (ID = ? ) 
15:20:33,374 DEBUG deleteByPrimaryKey:139 - ==> Parameters: 23(Long)
15:20:33,375 DEBUG deleteByPrimaryKey:139 - <==    Updates: 1
15:20:33,375 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,376 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,376 DEBUG select:139 - ==>  Preparing: SELECT SORT_ORDER SORTORDER,UPDATED,PARENT_ID PARENTID,NAME,IS_PARENT ISPARENT,ID,STATUS,CREATED FROM tb_content_category WHERE PARENT_ID = ? 
15:20:33,377 DEBUG select:139 - ==> Parameters: 23(Long)
15:20:33,379 DEBUG select:139 - <==      Total: 1
15:20:33,379 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42]
15:20:33,379 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ffd8d42] from current transaction
15:20:33,380 DEBUG deleteByPrimaryKey:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE (ID = ? ) 
15:20:33,380 DEBUG deleteByPrimaryKey:139 - ==> Parameters: 24(Long)
15:20:33,381 DEBUG deleteByPrimaryKey:139 - <==    Updates: 1

先查询再删除：

    /**
     * 先递归查询,在一起删除
     * @param parentId
     * @param id
     * @return
     */
    public Boolean deletContentCategory2(Long parentId, Long id) {
        List<Object> contentCategoryIds = new ArrayList<>();
        contentCategoryIds.add(id);
        getIdList(contentCategoryIds, id);

        super.deleteByIds(ContentCategory.class, "id",
                contentCategoryIds);

        // 查看该父类下是否还有子类
        ContentCategory record = new ContentCategory();
        record.setParentId(parentId);
        List<ContentCategory> queryListByWhere = super.queryListByWhere(record);
        if (queryListByWhere != null && !queryListByWhere.isEmpty()) {
            // 进来表示父类还有子类,不做更改,直接返回
            return true;
        }
        // 父类类目的isParent属性更改为false
        ContentCategory parent = super.queryById(parentId);
        if (parent.getIsParent()) {
            parent.setIsParent(false);
            super.updateSelective(parent);
        }
        return true;
    }

    /**
     * 递归查询
     * @param ids
     * @param parentId
     */
    private void getIdList(List<Object> ids, Long parentId) {
        ContentCategory record = new ContentCategory();
        record.setParentId((Long) parentId);
        List<ContentCategory> list = super.queryListByWhere(record);
        if (list == null) {
            return;
        }
        for (ContentCategory contentCategory : list) {
            if (contentCategory.getIsParent()) {
                ids.add(contentCategory.getId());
                getIdList(ids, contentCategory.getId());
            }
        }
    }

通过日记，可以看到，只执行了一条删除语句：

15:25:16,593 DEBUG SqlSessionUtils:163 - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5615513]
15:25:16,601 DEBUG SqlSessionUtils:97 - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5615513] from current transaction
15:25:16,668 DEBUG deleteByExample:139 - ==>  Preparing: DELETE FROM tb_content_category WHERE ( ID in(?,?,?,?,?) ) 
15:25:16,669 DEBUG deleteByExample:139 - ==> Parameters: 25(Long), 26(Long), 27(Long), 28(Long), 29(Long)
15:25:16,672 DEBUG deleteByExample:139 - <==    Updates: 5

这种通过先查询再一起删除，效率大大的提高了