基于系统数据过滤权限设计之----lft, rgt的无限分类算法

     系统权限设计中的一般常见会有:

        用户表

        角色表

        用户与角色关联表

        资源表

        角色与资源关联表。

       当然这里介绍是笼统的，有些系统中会有用户组表或者角色组表，甚至为了把菜单资源和按钮资源分开，也创建单独的表。这里只介绍一般情况。

      而在这里一般情况中，通常会出现用户属于那个组织机构。传统的组织机构以树形结构方式展示，借助组织机构编码进行过滤数据，从而来过滤整个系统的数据。也就是类似这样一种

这样一种组织机构的在进行数据过滤时，通常使用code like  '%'方式。这样做什么问题呢？

答：我们知道系统的优化其中最重要的一点就是sql优化，而使用like的方式很不利于优化，影响检索效率；其二如果我们的组织机构很庞大，也就造成了code过长，这个过程会造成在Where表达式的效率。

那么有没有其他好的解决方案呢？

在https://blog.csdn.net/lifetragedy/article/details/7734864 提到穿线树无限分类。我在做这一块时，去寻找网上相关的文档，发现了这样两篇文章：

  http://zhanglun1225.iteye.com/blog/602979

  https://blog.csdn.net/joph_csu/article/details/54178359

首先这里不做攻击，只做阐述。这两篇文档第一对于快速编码很不方便，篇章一：有些地方叙述过于繁杂，按照其方式书写有问题，需要自行调整。篇章二：使用jFinal框架编写，但是很多项目至今还是任然处于MVC框架模式编写。且两篇文档都没有对等组织机构当前所在等级和等级所在的排序。

于是乎，我建立了这样的表:

DROP TABLE IF EXISTS `sys_organization`;
CREATE TABLE `sys_organization` (
  `id` varchar(36) NOT NULL COMMENT '主键id',
  `name` varchar(64) NOT NULL COMMENT '组织名',
  `address` longtext COMMENT '地址',
  `code` varchar(250) NOT NULL COMMENT '编号',
  `icon` varchar(32) DEFAULT NULL COMMENT '图标',
  `pid` varchar(36) DEFAULT NULL COMMENT '父级主键',
  `seq` tinyint(2) NOT NULL DEFAULT '0' COMMENT '排序',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `lft` tinyint(2) DEFAULT NULL COMMENT '左范围',
  `rgt` tinyint(2) DEFAULT NULL COMMENT '右范围',
  `level` tinyint(2) DEFAULT NULL COMMENT '等级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='组织机构';
SET FOREIGN_KEY_CHECKS=1;

借助静态类实现A001这一类编码方式:

package com.huixin.sys.commons.utils;


/**
 * 流水号生成规则(按默认规则递增，数字从1-99开始递增，数字到99，递增字母;位数不够增加位数)
 * A001
 * A001A002
 *
 */
public final class OrganizationCodeUtils {
	
	//组织机构起始状态下lft左范围值
	public static final int ORGANIZATION_LFT=2;
	
	// 数字位数(默认生成3位的数字)
	private static final int numLength = 3;//代表数字位数

	public static final int zhanweiLength = 1+numLength;
	
	public static final String ZIMUA="A";

	/**
	 * 根据前一个code，获取同级下一个code
	 * 例如:当前最大code为D01A04，下一个code为：D01A05
	 * 
	 * @param code
	 * @return
	*/
	public static synchronized String getNextOrganizationCode(String code) {
		String newcode = "";
		if (code == null || code =="") {
			String zimu = ZIMUA;
			String num = getStrNum(1);
			newcode = zimu + num;
		} else {
			String before_code = code.substring(0, code.length() - 1- numLength);
			String after_code = code.substring(code.length() - 1 - numLength,code.length());
			char after_code_zimu = after_code.substring(0, 1).charAt(0);
			Integer after_code_num = Integer.parseInt(after_code.substring(1));
			System.out.println(after_code);
			System.out.println(after_code_zimu);
			System.out.println(after_code_num);

			String nextNum = "";
			char nextZimu = 'A';
			// 先判断数字等于999*，则计数从1重新开始，递增
			if (after_code_num == getMaxNumByLength(numLength)) {
				nextNum = getNextStrNum(0);
			} else {
				nextNum = getNextStrNum(after_code_num);
			}
			// 先判断数字等于999*，则字母从A重新开始,递增
			if(after_code_num == getMaxNumByLength(numLength)) {
				nextZimu = getNextZiMu(after_code_zimu);
			}else{
				nextZimu = after_code_zimu;
			}

			// 例如Z99，下一个code就是Z99A01
			if ('Z' == after_code_zimu && getMaxNumByLength(numLength) == after_code_num) {
				newcode = code + (nextZimu + nextNum);
			} else {
				newcode = before_code + (nextZimu + nextNum);
			}
		}
		return newcode;
	}

	/**
	 * 根据父亲code,获取下级的下一个code
	 * 
	 * 例如：父亲CODE:A01
	 *       当前CODE:A01B03
	 *       获取的code:A01B04
	 *       
	 * @param parentCode   上级code
	 * @param localCode    同级code
	 * @return
	 */
	public static synchronized String getSubOrganizationCode(String parentCode,String localCode) {
		if(localCode!=null && localCode!=""){
//			return parentCode + getNextOrganizationCode(localCode);
			return getNextOrganizationCode(localCode);
		}else{
			parentCode = parentCode + ZIMUA+ getNextStrNum(0);
		}
		return parentCode;
	}

	/**
	 * 将数字前面位数补零
	 * 
	 * @param num
	 * @return
	*/
	private static String getNextStrNum(int num) {
		return getStrNum(getNextNum(num));
	}

	/**
	 * 将数字前面位数补零
	 * 
	 * @param num
	 * @return
	*/
	private static String getStrNum(int num) {
		String s = String.format("%0" + numLength + "d", num);
		return s;
	}

	/**
	 * 递增获取下个数字
	 * 
	 * @param num
	 * @return
	*/
	private static int getNextNum(int num) {
		num++;
		return num;
	}

	/**
	 * 递增获取下个字母
	 * 
	 * @param num
	 * @return
	*/
	private static char getNextZiMu(char zimu) {
		if (zimu == 'Z') {
			return 'A';
		}
		zimu++;
		return zimu;
	}
		
	/**
	 * 根据数字位数获取最大值
	 * @param length
	 * @return
	*/
	private static int getMaxNumByLength(int length){
		if(length==0){
			return 0;
		}
		String max_num = "";
		for (int i=0;i<length;i++){
			max_num = max_num + "9";
		}
		return Integer.parseInt(max_num);
	}
	
	/**
	 * 剪贴
	 * @param code
	 * @return
	 */
	public static String[] cutOrganizationCode(String code){
		if(code==null||StringUtils.isEmpty(code)){
			return null;
		}else{
			//获取标准长度为numLength+1,截取的数量为code.length/numLength+1
			int c = code.length()/(numLength+1);
			String[] cutcode = new String[c];
			for(int i =0 ; i <c;i++){
				cutcode[i] = code.substring(0,(i+1)*(numLength+1));
			}
			return cutcode;
		}
	}
		
	public static void main(String[] args) {
		//System.out.println(getNextZiMu('C'));
		//System.out.println(getNextNum(8));
		System.out.println(getSubOrganizationCode("C099A001","B003"));
//		System.out.println(cutYouBianCode("C99A01B01")[2]);
	}

}

废话不说，上代码，造轮子：

@Override
    public Integer saveOrganization(SysOrganization organization){
    	if(StringUtils.isBlank(organization.getPid())){   //pid为空
    		organization.setPid(null);
    		String localMaxCode  = getMaxOrganizationCode(null);
    		organization.setCode(OrganizationCodeUtils.getNextOrganizationCode(localMaxCode));
    		Integer seq=organizationMapper.getMaxSeqOrganization(null);
    		if(seq==null){
    			organization.setSeq(1);
    		}else{
    			organization.setSeq(seq+1);
    		}
    		Integer lft=organizationMapper.getMaxLftOrganization(null);
    		if(lft==null){
    			organization.setLft(OrganizationCodeUtils.ORGANIZATION_LFT);
    			organization.setRgt(OrganizationCodeUtils.ORGANIZATION_LFT+1);
    		}else{
    			organization.setLft(lft+1);
    			organization.setRgt(lft+2);
    		}
    	}else{  //pid不为空
    		SysOrganization organizationParent=organizationMapper.selectById(organization.getPid());
    		String localMaxCode  = getMaxOrganizationCode(organizationParent.getCode());
    		organization.setCode(OrganizationCodeUtils.getSubOrganizationCode(organizationParent.getCode(), localMaxCode));
    		//还需要处理seq和level
            handleSeq(organization);
            //新增节点
            //1.查找当前插入节点的父节点的lft值
            Integer lft=organizationParent.getLft();
            //2.将树形中所有lft和rgt节点大于父节点左值的节点都+2
            organizationMapper.updateAllLft(2, lft);
            organizationMapper.updateAllRgt(2, lft);
            //3.将父节点左值+1，左值+2分别作为当前节点的lft和rgt
            organization.setLft(lft+1);
            organization.setRgt(lft+2);
    	}
    	organization.setLevel(organization.getCode().length()/OrganizationCodeUtils.zhanweiLength-1);
        organization.setCreateTime(new Date());
        return organizationMapper.insert(organization);
    }
    
    @Override
    public Integer updateOrganization(SysOrganization organization){
    	if(StringUtils.isNotBlank(organization.getPid())){
    		SysOrganization organizationParent=organizationMapper.selectById(organization.getPid());
    		String localMaxCode  = getMaxOrganizationCode(organizationParent.getCode());
    		organization.setCode(OrganizationCodeUtils.getSubOrganizationCode(organizationParent.getCode(), localMaxCode));
    		//还需要处理seq和level
            handleSeq(organization);
            //在修改lft和rgt之前，当前节点的父节点id已经改变
        	//1.查出当前节点的左右节点（nodelft、nodergt），并nodergt-nodelft+1 = span，获取父节点的左节点parentlft
            Integer nodelft=organization.getLft();
            Integer nodergt=organization.getRgt();
            Integer span=nodergt-nodelft+1;
            Integer parentlft=organizationParent.getLft();
        	//2.将所有大于parentlft的lft(左节点)、rgt(右节点)的值+span
            organizationMapper.updateAllLft(span, parentlft);
            organizationMapper.updateAllRgt(span, parentlft);
        	//3.查找当前节点的左右节点（nodelft、nodergt），并parentlft-nodelft+1 = offset
            SysOrganization currentOrganization=organizationMapper.selectById(organization.getId());
            nodelft=currentOrganization.getLft();
            nodergt=currentOrganization.getRgt();
            Integer offset = parentlft - nodelft + 1;
        	//4.将所有lft(左节点) between nodelft and nodergt的值+offset
            organizationMapper.updateLftRgtWhereLftBetweenAnd(offset, nodelft, nodergt);
        	//5.将所有大于nodergt的lft(左节点)、rgt(右节点)的值-span
            organizationMapper.updateAllLftSubtraction(span, nodergt);
            organizationMapper.updateAllRgtSubtraction(span, nodergt);
            //新的lft rgt
            SysOrganization organizationParentChanaged=organizationMapper.selectById(organization.getOrgPid());
            organization.setLft(organizationParentChanaged.getLft()+1);
            organization.setRgt(organizationParentChanaged.getLft()+span);
    	}else{
    		organization.setPid(null);
    	}
    	organization.setLevel(organization.getCode().length()/OrganizationCodeUtils.zhanweiLength-1);
        organization.setCreateTime(new Date());
    	return organizationMapper.updateById(organization);
    }
    
    public Integer deleteOrganization(String id){
    	Integer row=organizationMapper.selectCurrentNodeIsExistParent(id);
    	if(row==0){
    		//删除节点
        	//1. 查找要删除节点的lft值
        	SysOrganization organization=organizationMapper.selectById(id);
        	Integer lft=organization.getLft();
        	//2.将所有lft和rgt大于删除节点lft值的都-2
        	organizationMapper.updateAllLftSubtraction(2, lft);
        	organizationMapper.updateAllRgtSubtraction(2, lft);
        	return organizationMapper.deleteById(id);
    	}else{
    		return -1;
    	}
    }
    

    /**
	 * 得到最大的组织机构code
	 * @param parentCodeLength
	 * @param parentCode
	 * @return
	 */
	protected String getMaxOrganizationCode(String parentCode){
    	if(StringUtils.isBlank(parentCode)){
			parentCode = "";
		}
		int localCodeLength = parentCode.length() + OrganizationCodeUtils.zhanweiLength;
		List<String> listStr=organizationMapper.getMaxOrganizationCode(localCodeLength, OrganizationCodeUtils.ZIMUA, parentCode);
		if(listStr!=null && listStr.size()>0){
			return listStr.get(0);
		}else{
			return null;
		}
	}
	
	/**
	 * 处理level等级和seq
	 * @param organization
	 */
	protected void handleSeq(SysOrganization organization){
		Integer seq=organizationMapper.getMaxSeqOrganization(organization.getPid());
		if(seq!=null){
			seq=seq+1;
		}else{
			seq=1;
		}
		organization.setSeq(seq);
	}

而在Mapper中：我这里使用注解和XML的混合方式编码

	/**
	 * 通过pid得到最大的seq
	 * @param Pid
	 * @return
	 */
	Integer getMaxSeqOrganization(@Param("Pid")String Pid);
	
	/**
	 * 通过pid得到最大的lft
	 * @param Pid
	 * @return
	 */
	Integer getMaxLftOrganization(@Param("Pid")String Pid);
	
	/**
	 * 更新所有的左范围值
	 * @param span
	 * @param lft
	 * @return
	 */
	Integer updateAllLft(@Param("span")Integer span, @Param("lft")Integer lft);
	
	/**
	 * 更新所有的右范围值
	 * @param span
	 * @param rgt
	 * @return
	 */
	Integer updateAllRgt(@Param("span")Integer span, @Param("rgt")Integer rgt);
	
	/**
	 * 将所有lft(左节点) between nodelft and nodergt的值+offset
	 * @param offset
	 * @param nodeLft
	 * @param nodeRgt
	 * @return
	 */
	@Update("UPDATE sys_organization SET lft = lft + #{offset},rgt = rgt + #{offset} WHERE lft BETWEEN #{nodeLft} AND #{nodeRgt}")
	Integer updateLftRgtWhereLftBetweenAnd(@Param("offset")Integer offset, @Param("nodeLft")Integer nodeLft, @Param("nodeRgt")Integer nodeRgt);
	
	/**
	 * 将所有大于nodergt的lft(左节点)、rgt(右节点)的值-span[清除位置]
	 * @param span
	 * @param nodergt
	 * @return
	 */
	@Update("UPDATE sys_organization SET lft = lft - #{span} WHERE lft > #{nodergt}")
	Integer updateAllLftSubtraction(@Param("span")Integer span, @Param("nodergt")Integer nodergt);
	
	/**
	 * 将所有大于nodergt的lft(左节点)、rgt(右节点)的值-span[清除位置]
	 * @param span
	 * @param nodergt
	 * @return
	 */
	@Update("UPDATE sys_organization SET rgt = rgt - #{span} WHERE rgt > #{nodergt}")
	Integer updateAllRgtSubtraction(@Param("span")Integer span, @Param("nodergt")Integer nodergt);
	
	/**
	 * 当前节点下是否有子节点
	 * @param id
	 * @return
	 */
	@Select("SELECT COUNT(*) FROM sys_organization WHERE pid = #{id}")
	Integer selectCurrentNodeIsExistParent(@Param("id")String id);

xml中:

    <!-- 获取最大的组织机构code -->
    <select id="getMaxOrganizationCode" resultType="java.lang.String">
    	SELECT 
    		MAX(code) AS code
    	FROM sys_organization
    	<where>
    		<if test="parentCodeLength!=null">
    			LENGTH(code) = #{parentCodeLength}
    		</if>
    		<if test="zimuA!=null and zimuA.trim()!=''">
    			AND LEFT(code,1) = #{zimuA}
    		</if>
    		<if test="parentCode!=null and parentCode.trim()!=''">
    			AND code LIKE CONCAT('', #{parentCode}, '%')
    		</if>
    	</where>
    </select>

	<!-- 得到最大的seq -->
	<select id="getMaxSeqOrganization" resultType="java.lang.Integer">
		SELECT 
    		MAX(seq) AS seq
    	FROM sys_organization
    	<where>
    		<if test="Pid!=null and Pid.trim()!=''">
    			pid = #{Pid}
    		</if>
    		<if test="Pid==null or Pid.trim()==''">
    			pid is NULL OR pid =''
    		</if>
    	</where>
	</select>
	
	<!-- 得到最大的lft -->
	<select id="getMaxLftOrganization" resultType="java.lang.Integer">
		SELECT 
    		MAX(lft) AS lft
    	FROM sys_organization
    	<where>
    		<if test="Pid!=null and Pid.trim()!=''">
    			pid = #{pid}
    		</if>
    		<if test="Pid==null or Pid.trim()==''">
    			pid is NULL OR pid =''
    		</if>
    	</where>
	</select>
	
    <!-- 更新左右范围 -->
    <update id="updateOrganizationLftRgt" parameterType="java.lang.Integer">
    	UPDATE sys_organization
    	<set>
    		<if test="lft!=null">
    			lft = #{lft}
    		</if>
    		<if test="rgt!=null">
    			rgt = #{rgt}
    		</if>
    	</set>
    </update>
    
    <!-- 更新所有的左范围值 -->
    <update id="updateAllLft" parameterType="java.lang.Integer">
    	UPDATE sys_organization
    	SET lft = lft + #{span}
    	<where>
    		<if test="lft!=null">
    			lft > #{lft}
    		</if>
    	</where>
    </update>
    
    <!-- 更新所有的右范围值 -->
    <update id="updateAllRgt" parameterType="java.lang.Integer">
    	UPDATE sys_organization
    	SET rgt = rgt + #{span}
    	<where>
    		<if test="rgt!=null">
    			rgt > #{rgt}
    		</if>
    	</where>
    </update>