优化 - 不要在for循环中调用数据库

文章目录

- - 1. 循环调用数据库案例1
  - - 1. 改进之前
    - 2. 第一次改进后
    - 3. 第二次改进后
    - 4. 测试根据部门层级路径获取获取部门id接口
  - 2. 循环调用数据库案例 2
  - 3. 循环调用数据库改进案例 3
  - - 1. 改进之前
    - 2. 改进之后

循环调用数据库会导致性能很差，所以千万不要循环调用数据库，对循环调用数据库的改进方法，就是把数据全部查出来，然后组装或者批量查询。

1. 循环调用数据库案例1 1. 改进之前

存在的问题：for循环中存在批量查询数据库的情况，需要批量查询数据库处理掉

private void saveMemberToDb(List importMemberList) {
    // 对账号的查询放到了数据库外面，避免在for循环中重复调用
    List userInfoVos = authFeignClient.getUserList().getData();
    Map userInfoMap 
        = userInfoVos.stream()
        .collect(Collectors.toMap(UserInfoVo::getName, UserInfoVo::getId));
    
    List departmentMembers 
        = importMemberList.stream().map(departmentMemberInput -> {
        DepartmentMember departmentMember = new DepartmentMember();     
        // setDeptId
        String deptNameImport = departmentMemberInput.getDepartment().getName();
        // 存在循环调用数据库问题
        Integer rootId = departmentDao.findIdByPidAndName(
            GroupConstant.BUILT_IN_ROOT_DEPARTMENT_PARENT_ID,
            GroupConstant.BUILT_IN_ROOT_DEPARTMENT_NAME
        );
        // 存在循环调用数据库问题
        Integer defaultDeptId = departmentDao.findIdByPidAndName(rootId, GroupConstant.BUILT_IN_DEPARTMENT_NAME);
        if (StringUtils.isEmpty(deptNameImport)) {
            departmentMember.setDeptId(defaultDeptId);
        } else {
            String deptName = departmentMemberInput.getDepartment().getName();
            // 存在的问题：在获取部门id的方法中存在循环调用数据库的问题，需要继续改进
            int deptId = getDeptId(deptName);
            if (deptId == -1) {
                departmentMember.setDeptId(defaultDeptId);
            } else {
                departmentMember.setDeptId(deptId);
            }
        }
        // setAccountId
        departmentMember.setAccountId(userInfoMap.get(departmentMemberInput.getAccountName()));
        return departmentMember;
    }).collect(Collectors.toList());

    if (!CollectionUtils.isEmpty(departmentMembers)) {
        departmentMemberDao.insertBatch(departmentMembers);
    }
}

// 根据部门层级路径获取部门id，比如根据 “部门/研发部/SIR” 获取SIR的部门id 
// 该方法存在循环调用数据库的问题
public int getDeptId(String deptName) {
    String[] deptPathNameImport = deptName.split("/");
    int index = deptPathNameImport.length - 1;
    // 根据最后一级部门名称查找部门
    List departmentList = departmentDao.findByName(deptPathNameImport[index]);
    if (CollectionUtils.isEmpty(departmentList)) {
        return -1;
    }
    for (Department department : departmentList) {
        // 暂存deptId
        int deptId = department.getId();
        // 如果当前部门的父级部门不是-1，寻找部门的父级部门
        while (index > 0 && department != null && department.getPid() != GroupConstant.BUILT_IN_ROOT_DEPARTMENT_PARENT_ID) {
            department = departmentDao.findById(department.getPid());
            if (department != null && !department.getName().equals(deptPathNameImport[--index])) {
                // 如果父级部门不等于excel中导入的父级部门
                break;
            }
            if (index == 0) {
                return deptId;
            }
        }
    }
    return -1;
}

2. 第一次改进后

存在的问题：可以不要在for循环中组装map，而是放在for循环外组装好，在for循环中调用

private void saveMemberToDb(List importMemberList) {
    
    // 对账号的查询,避免在for循环中调用
    List userInfoVos = authFeignClient.getUserList().getData();
    Map userInfoMap 
        = userInfoVos.stream()
        .collect(Collectors.toMap(UserInfoVo::getName, UserInfoVo::getId));
    
    // 查询 “部门” id，避免在for循环中调用
    Integer rootId 
        = departmentDao.findIdByPidAndName(GroupConstant.BUILT_IN_ROOT_DEPARTMENT_PARENT_ID, GroupConstant.BUILT_IN_ROOT_DEPARTMENT_NAME);
    
    // 查询 “默认部门” id，避免在for循环中调用
    Integer defaultDeptId 
        = departmentDao.findIdByPidAndName(rootId, GroupConstant.BUILT_IN_DEPARTMENT_NAME);
    
    // 查询所有部门，避免在for循环中调用
    List departments = departmentDao.findAll();
    List departmentMembers = importMemberList.stream().map(departmentMemberInput -> {
        DepartmentMember departmentMember = new DepartmentMember();
        // setDeptId
        String deptNameImport = departmentMemberInput.getDepartment().getName();

        if (StringUtils.isEmpty(deptNameImport)) {
            departmentMember.setDeptId(defaultDeptId);
        } else {
            // 改进获取部门id的方法，不在循环调用数据库，而是查询所有数据后组装数据
            int deptId = getDepartmentId(deptNameImport,departments);
            if (deptId == -1) {
                departmentMember.setDeptId(defaultDeptId);
            } else {
                departmentMember.setDeptId(deptId);
            }
        }
		// setAccountId
        departmentMember.setAccountId(userInfoMap.get(departmentMemberInput.getAccountName()));
        return departmentMember;
    }).collect(Collectors.toList());
    
    if (!CollectionUtils.isEmpty(departmentMembers)) {
        departmentMemberDao.insertBatch(departmentMembers);
    }
}

private int getDepartmentId(String deptPathName, List departments) {
    Map< String, Integer> departmentId2NamesMap = new HashMap<>();
    Map id2DepartmentMap = departments.stream().collect(Collectors.toMap(Department::getId, k -> k, (k1, k2) -> k2));
    Set deptIds = id2DepartmentMap.keySet();
    for (Integer deptId : deptIds) {
        // 获取当前deptId的所属层级部门
        departmentId2NamesMap.put(getDeptPathName(deptId, id2DepartmentMap), deptId);
    }
    return departmentId2NamesMap.getOrDefault(deptPathName,-1);
}

private String getDeptPathName(Integer deptId, Map id2DepartmentMap) {
    StringBuilder stringBuilder = new StringBuilder();
    while (id2DepartmentMap.containsKey(deptId)) {
        Department department = id2DepartmentMap.get(deptId);
        if (Objects.nonNull(department)) {
            stringBuilder.insert(0,department.getName());
            stringBuilder.insert(0,"/");
            deptId = department.getPid();
        } else {
            break;
        }
    }
    return stringBuilder.deleteCharAt(0).toString();
}

3. 第二次改进后

private void saveMemberToDb(List importMemberList) {
    
    // 对账号的查询,避免在for循环中调用
    List userInfoVos = authFeignClient.getUserList().getData();
    Map userInfoMap 
        = userInfoVos.stream()
        .collect(Collectors.toMap(UserInfoVo::getName, UserInfoVo::getId));
    
    // 查询 “部门” id，避免在for循环中调用
    Integer rootId 
        = departmentDao.findIdByPidAndName(GroupConstant.BUILT_IN_ROOT_DEPARTMENT_PARENT_ID, GroupConstant.BUILT_IN_ROOT_DEPARTMENT_NAME);
    
    // 查询 “默认部门” id，避免在for循环中调用
    Integer defaultDeptId 
        = departmentDao.findIdByPidAndName(rootId, GroupConstant.BUILT_IN_DEPARTMENT_NAME);
    
    // 查询所有部门，避免在for循环中调用
    List departments = departmentDao.findAll();
    
    // 查询每个部门的层级路径，避免在for循环中调用
    Map departmentPathMap = getDepartmentPathMap(departments);
    
    List departmentMembers = importMemberList.stream().map(departmentMemberInput -> {
        DepartmentMember departmentMember = new DepartmentMember();
        // setDeptId
        String deptNameImport = departmentMemberInput.getDepartment().getName();

        if (StringUtils.isEmpty(deptNameImport)) {
            departmentMember.setDeptId(defaultDeptId);
        } else {
            // 直接从map中调用
            int deptId = departmentPathMap.getOrDefault(deptNameImport,-1);
            if (deptId == -1) {
                departmentMember.setDeptId(defaultDeptId);
            } else {
                departmentMember.setDeptId(deptId);
            }
        }
        // setAccountId
        departmentMember.setAccountId(userInfoMap.get(departmentMemberInput.getAccountName()));
        return departmentMember;
    }).collect(Collectors.toList());

    if (!CollectionUtils.isEmpty(departmentMembers)) {
        departmentMemberDao.insertBatch(departmentMembers);
    }
}

private Map< String, Integer> getDepartmentPathMap(List departments) {
    Map< String, Integer> departmentId2NamesMap = new HashMap<>();
    Map id2DepartmentMap = departments.stream().collect(Collectors.toMap(Department::getId, k -> k, (k1, k2) -> k2));
    Set deptIds = id2DepartmentMap.keySet();
    for (Integer deptId : deptIds) {
        departmentId2NamesMap.put(getDeptPathName(deptId, id2DepartmentMap), deptId);
    }
    return departmentId2NamesMap;
}

private String getDeptPathName(Integer deptId, Map id2DepartmentMap) {
    StringBuilder stringBuilder = new StringBuilder();
    while (id2DepartmentMap.containsKey(deptId)) {
        Department department = id2DepartmentMap.get(deptId);
        if (Objects.nonNull(department)) {
            stringBuilder.insert(0,department.getName());
            stringBuilder.insert(0,"/");
            deptId = department.getPid();
        } else {
            break;
        }
    }
    return stringBuilder.deleteCharAt(0).toString();
}

4. 测试根据部门层级路径获取获取部门id接口

比如根据 “部门/研发部/SIR” 获取SIR的部门id

public class Main {
    public static void main(String[] args) {
        String deptPathName = "部门/研发部/SIC";
        Department department1 = new Department(1,"部门",-1);
        Department department2 = new Department(2,"默认部门",1);
        Department department3 = new Department(3,"研发部",1);
        Department department4 = new Department(4,"SID",1);
        Department department5 = new Department(5,"SIC",3);
        Department department6 = new Department(6,"SOC",4);

        List list = new ArrayList<>();
        list.add(department1);
        list.add(department2);
        list.add(department3);
        list.add(department4);
        list.add(department5);
        list.add(department6);

        System.out.println(getDepartmentId(deptPathName, list));
    }

    private static int getDepartmentId(String deptPathName, List departments) {
        Map< String, Integer> map = new HashMap<>();
        Map departmentMap = departments.stream()
                .collect(Collectors.toMap(Department::getId, k -> k, (k1, k2) -> k2));
        Set deptIds = departmentMap.keySet();
        for (Integer deptId : deptIds) {
            map.put(getDeptPathName(deptId, departmentMap), deptId);
        }
        return map.getOrDefault(deptPathName,-1);
    }

    
    private static String getDeptPathName(Integer deptId, Map departmentMap) {
        StringBuilder stringBuilder = new StringBuilder();
        while (departmentMap.containsKey(deptId)) {
            Department department = departmentMap.get(deptId);
            if (Objects.nonNull(department)) {
                stringBuilder.insert(0,department.getName());
                stringBuilder.insert(0,"/");
                deptId = department.getPid();
            } else {
                break;
            }
        }
        return stringBuilder.deleteCharAt(0).toString();
    }
}

查询出所有部门，然后组装数据获取每个部门的层级路径

2. 循环调用数据库案例 2

将原来的遍历departmentMemberInputsFilterDb集合，判断每一条数据是否符合条件的循环调用数据库思路进行优化，
优化思路就是查询所有数据，批量判断与数据库中数据是否重复，批量排出，而不是一个一个排出

List successMemberList = result.getList();

// 1. 过滤出List集合中姓名和手机号不重复的数据
List departmentMemberInputsFilterPhone = new ArrayList<>();
for (DepartmentMemberInput departmentMemberInput : successMemberList) {
    if (!departmentMemberInputsFilterPhone.contains(departmentMemberInput)) {
        departmentMemberInputsFilterPhone.add(departmentMemberInput);
    }
}
// 2. 过滤出list集合中账号不重复的数据
List departmentMemberInputsFilterAccount = departmentMemberInputsFilterPhone
    .stream()
    .collect(
    Collectors.collectingAndThen(
        Collectors.toCollection(
            () -> new TreeSet<>(Comparator.comparing(DepartmentMemberInput::getAccountName))
        ),
        ArrayList::new
    )
);

// 将原来的遍历departmentMemberInputsFilterDb集合，判断每一条数据是否符合条件，然后排出，今天优化
// 优化思路就是查询所有数据，批量判断与数据库中数据是否重复，批量排出，而不是一个一个排出

List departmentMemberInputsFilterDb = new ArrayList<>(departmentMemberInputsFilterAccount);
// 查询所有的账号
List userInfoVos = authFeignClient.getUserList().getData();
if(CollectionUtil.isNotEmpty(userInfoVos)){
    List allAccountNames = userInfoVos.stream().map(UserInfoVo::getName).collect(Collectors.toList());
    // 1. 排除数据库中账号不存在的数据
    List accountsRepeatWithDb= departmentMemberInputsFilterAccount.stream()
        .filter(departmentMemberInput -> !allAccountNames.contains(departmentMemberInput.getAccountName())).collect(Collectors.toList());
    departmentMemberInputsFilterDb.removeAll(accountsRepeatWithDb);

    // 2. 排除与数据库中人员账号重复的数据
    List accountNames = departmentMemberInputsFilterAccount.stream()
        .map(DepartmentMemberInput::getAccountName).collect(Collectors.toList());
    Map map = userInfoVos.stream().collect(Collectors.toMap(UserInfoVo::getName,UserInfoVo::getId));
    List accountIds = new ArrayList<>();
    for (String accountName : accountNames) {
        accountIds.add(map.get(accountName));
    }
    List departmentMembers = departmentMemberDao.findByAccountIds(accountIds);
    departmentMembers.stream().map(departmentMember -> {
        DepartmentMemberInput departmentMemberInput = new DepartmentMemberInput();
        BeanUtils.copyProperties(departmentMember, departmentMemberInput);
        departmentMemberInputsFilterDb.remove(departmentMemberInput);
        return departmentMemberInput;
    }).collect(Collectors.toList());

    // 3. 排除与数据库中手机号和姓名重复的人员
    departmentMembers = departmentMemberDao.findAll();
    departmentMembers.stream().map(departmentMember -> {
        DepartmentMemberInput departmentMemberInput = new DepartmentMemberInput();
        BeanUtils.copyProperties(departmentMember, departmentMemberInput);
        departmentMemberInputsFilterDb.remove(departmentMemberInput);
        return departmentMemberInput;
    }).collect(Collectors.toList());
}

3. 循环调用数据库改进案例 3 1. 改进之前

@Override
public GroupTreeVo getList() {
    // 获取所有的工作组分类
    List groupCategoryList = groupCategoryDao.findAll();
    if (CollectionUtils.isEmpty(groupCategoryList)) {
        return null;
    }
    // 设置工作组分类
    List groupCategoryVos = new ArrayList<>();
    for (GroupCategory groupCategory : groupCategoryList) {
        GroupCategoryVo groupCategoryVo = new GroupCategoryVo();
        BeanUtils.copyProperties(groupCategory, groupCategoryVo);
        // 存在循环查数据库问题
        List groupVos = getGroupVos(groupCategory);
        groupCategoryVo.setChildren(groupVos);
        groupCategoryVos.add(groupCategoryVo);
    }
    // 添加顶级节点“全部”
    return setGroupTree(groupCategoryVos);
}

private List getGroupVos(GroupCategory groupCategory) {
    // 工作组
    List groupList = groupDao.findByCategoryId(groupCategory.getId());
    List groupVos = new ArrayList<>();
    if (!CollectionUtils.isEmpty(groupList)) {
        for (Group group : groupList) {
            GroupVo groupVo = new GroupVo();
            BeanUtils.copyProperties(group, groupVo);
            groupVos.add(groupVo);
        }
    }
    return groupVos;
}

private GroupTreeVo setGroupTree(List groupCategoryVos) {
    GroupTreeVo groupTreeVo = new GroupTreeVo();
    groupTreeVo.setId(-1);
    groupTreeVo.setName("全部");
    groupTreeVo.setChildren(groupCategoryVos);
    return groupTreeVo;
}

2. 改进之后

@Override
public List getGroupCategoryTree() {
    List categoryTrees = new ArrayList<>();
    // 获取所有的工作组分类
    List categories = groupCategoryDao.findAll();
    if (!CollectionUtils.isEmpty(categories)) {
        // 所有工作组分类的ids
        List categoryIds 
            = categories.stream().map(GroupCategory::getId).collect(Collectors.toList());
        // 所有工作组分类
        Map map = new HashMap<>(categories.size());
        for (GroupCategory category : categories) {
            GroupCategoryTree groupCategoryTree = new GroupCategoryTree(category);
            map.put(category.getId(), groupCategoryTree);
        }

        // 根据ids查出所有的工作组
        List groups = groupDao.findByCategoryIds(categoryIds);
        if (!CollectionUtils.isEmpty(groups)) {
            for (Group group : groups) {
                GroupCategoryTree groupCategoryTree = new GroupCategoryTree(group);
                GroupCategoryTree categoryTree = map.get(group.getCategoryId());
                categoryTree.getChildren().add(groupCategoryTree);
            }
        }
        for (Integer categoryId : map.keySet()) {
            categoryTrees.add(map.get(categoryId));
        }
    }
    return categoryTrees;
}

优化 - 不要在for循环中调用数据库

Java相关栏目本月热门文章