【多级分销，如何更有效的查找上级/下级】

1 根据分享用户ID，创建用户和用户关系

@Transactional(rollbackFor = Exception.class)
@Override
public Long create(User entity) throws CodeException {
    UserRelation userRelation = new UserRelation();
    Long refereeUserId = entity.getRefereeUserId();
    userRelation.setUserId(refereeUserId);
    UserRelation relation = userRelationRepository.selectOne(userRelation);
    if (relation == null) {
        throw new CodeException(String.format("用户关系不存在[%s]", refereeUserId));
    }
    Date today = new Date();
    entity.setCreateTime(today);
    entity.setUpdateTime(today);
    this.userRepository.insertReturnId(entity);
    Long userId = entity.getId();
    UserRelation userRelationEntity = new UserRelation();
    userRelationEntity.setUserId(userId);
    userRelationEntity.setDepth(relation.getDepth() + 1);
    userRelationEntity.setParentUserId(refereeUserId);
    userRelationEntity.setStatus(UserRelation.STATUS_ENABLE);
    String refereePath = relation.getPath();
    String path = refereePath + "/" + relation.getDepth() + refereeUserId;
    userRelationEntity.setPath(path);
    userRelationEntity.setCreateTime(entity.getCreateTime());
    userRelationEntity.setUpdateTime(entity.getUpdateTime());
    userRelationEntity.setUsername(entity.getUsername());
    userRelationRepository.insert(userRelationEntity);
    return userId;
}
    
/**
* 2 根据当前用户的路径，分隔得到所有上级用户的路径（路径方式适合层级固定，长度才可以固定）
* <p>
* 例如：/01/16/28/320->[/01/16/28/320, /01/16/28, /01/16, /01]
* </p>
*
* @param path
* @return
*/
public static List<String> pathSubstring(String path) {
    List<String> result = new ArrayList<String>();
    result.add(path);
    while (path.lastIndexOf(ConfigConstant.PATH_SPLIT) > 0) {
        String substring = path.substring(0, path.lastIndexOf(ConfigConstant.PATH_SPLIT));
        result.add(substring);
        path = substring;
    }
    return result;
}
    
3 根据当前用户的路径，查找上级用户
<select id="findByPathSubstring" resultMap="UserRefereeResultMap">
    SELECT a.user_id,a.username,b.vip_level,CONCAT(a.path,'/',a.depth,a.user_id) as path
    FROM ABC_USER_RELATION a
    LEFT JOIN ABC_USER b ON a.user_id=b.id
    WHERE CONCAT(path,'/',depth,user_id)
    in
    <foreach collection="list" item="i" index="index" open="(" close=")" separator=",">
        #{i}
    </foreach>
    ORDER BY user_id desc
</select>

4 查找下级：

@Override
public PageInfo<UserRelation> findByPath(String path, int page, int pageSize) {
    Example example = new Example(UserRelation.class);
    example.orderBy("depth").asc();
    example.createCriteria().andLike("path", path + "%");
    PageHelper.startPage(page, pageSize);
    List<UserRelation> data = this.getRepository().selectByExample(example);
    return new PageInfo(data);
}

无限极分销，参考通过闭包表解决无限极代理分销-CSDN博客