区域的增删改查及上下移动

区域新增

entity

/**
 * 实体类
 *
 * @author 
 * @since 2020-05-20
 */
@Data
@TableName("region")
@ApiModel(value = "Region对象", description = "Region对象")
public class Region implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * 区域id
	 */
	@ApiModelProperty(value = "区域id")
	@TableId(value = "id", type = IdType.ID_WORKER)
	@JsonSerialize(using = ToStringSerializer.class)
	private Long id;
	/**
	 * 地区代码
	 */
	@ApiModelProperty(value = "地区代码")
	private String code;
	/**
	 * 区域名称
	 */
	@ApiModelProperty(value = "区域名称")
	private String title;
	/**
	 * 父级行政区划
	 */
	@ApiModelProperty(value = "父级行政区划")
	@JsonSerialize(using = ToStringSerializer.class)
	private Long parentId;
	/**
	 * 是否已删除
	 */
	@TableLogic
	@ApiModelProperty(value = "是否已删除")
	private Integer isDeleted;
	/**
	 * 区域类型
	 */
	@ApiModelProperty(value = "区域类型")
	private Integer regionType;
	/**
	 * 备注
	 */
	@ApiModelProperty(value = "备注")
	private String remarks;
	/**
	 * 是否有子级（0:无，1:有）
	 */
	@ApiModelProperty(value = "是否有子级（0:无，1:有）")
	private Boolean hasChildren;

	/**
	 * 区域排序
	 */
	@ApiModelProperty(value = "区域排序")
	private Integer sort;

RegionVO

/**
 * 视图实体类
 *
 * @author 
 * @since 2020-05-20
 */
@Data
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "RegionVO对象", description = "RegionVO对象")
public class RegionVO extends Region implements INode{
	private static final long serialVersionUID = 1L;

	/**
	 * 主键ID
	 */
	@JsonSerialize(using = ToStringSerializer.class)
	private Long id;

	/**
	 * 父节点ID
	 */
	@JsonSerialize(using = ToStringSerializer.class)
	private Long parentId;

	/**
	 * 区域类型名称
	 */
	@JsonSerialize(using = ToStringSerializer.class)
	private String regionTypeName;

	/**
	 * 子孙节点
	 */
	@JsonInclude(JsonInclude.Include.NON_EMPTY)
	private List<INode> children;


	@Override
	public List<INode> getChildren(){
		if(this.children == null){
			this.children = new ArrayList<>();
		}
		return this.children;
	}


}

ToolsConstant

/**
 * @ClassName ToolsConstant
 * @Description TODO
 * @Author Lxq
 * @Date 2020/5/21 0021 09:29
 * @Version 3.0
 **/
public class ToolsConstant {
	/**
	 * 区域类型
	 */
	public static class REGION_TYPE{
		//0-国家
		public static final Integer COUNTRY=0;
		//1-省、直辖市
		public static final Integer PROVINCE=1;
		//2-地市
		public static final Integer CITY=2;
		//3-区县
		public static final Integer AREA=3;
	}
}

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aa.modules.system.mapper.RegionMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="regionResultMap" type="com.aa.modules.system.entity.Region">
        <id column="id" property="id"/>
        <result column="code" property="code"/>
        <result column="title" property="title"/>
        <result column="parent_id" property="parentId"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="region_type" property="regionType"/>
        <result column="remarks" property="remarks"/>
        <result column="has_children" property="hasChildren"/>
        <result column="sort" property="sort"/>
    </resultMap>
    <resultMap id="treeNodeResultMap" type="org.springblade.core.tool.node.TreeNode">
        <id column="id" property="id"/>
        <result column="parent_id" property="parentId"/>
        <result column="title" property="title"/>
        <result column="value" property="value"/>
        <result column="key" property="key"/>
    </resultMap>


    <select id="selectRegionPage" resultMap="regionResultMap">
        select * from blade_region where is_deleted = 0
    </select>
    <!--显示区域树结构start-->
    <select id="getTreeNode" resultMap="treeNodeResultMap">
        SELECT id, parent_id, title, id as "value", id as "key" from blade_region a where a.is_deleted = 0
        <if test="title != null and title !=''">
            and title like CONCAT('%','${title}','%')
        </if>
    </select>
    <!--根据title重复校验-->
    <select id="getTitle" resultMap="regionResultMap">
        SELECT id, parent_id, title, id as "value", id as "key" from blade_region a where a.is_deleted = 0
        <if test="title != null and title !=''">
            and title = #{title}
        </if>
    </select>
    <!--显示区域树结构end-->
    <select id="findMaxSortByParentId" resultType="java.lang.Integer">
        select max(sort) from blade_region where parent_id = #{region.parentId}
    </select>
    <!--根据parentId查询出正序区域排序-->
    <select id="findSortsByParentId" resultType="java.lang.String">
        SELECT GROUP_CONCAT(sort ORDER BY sort) FROM blade_region where parent_id = #{region.parentId}
    </select>

</mapper>

controller

/*
	* @Description：//TODO 新增或修改
	* @param: [region]
	* @return: 
	* @author: Lxq
	* @Date: 2020/5/21 0021
	*/
	@PostMapping("/submit")
	@ApiOperationSupport(order = 6)
	@ApiOperation(value = "新增或修改区域", notes = "传入region")
	@CacheEvict(cacheNames = {SYS_CACHE}, allEntries = true)
	public R submit(@Valid @RequestBody Region region) {
		if(Func.isEmpty(region.getTitle())){
			return R.fail("区域名称不允许为空");
		}else{
			List<Region> treeTitle = regionService.getTitle(region.getTitle());
			if(treeTitle.size()>0){
				if(Func.isEmpty(region.getId())){
						return R.fail("false");
				}else{
					for (Region title:treeTitle) {
						Region pare = regionService.getById(region.getId());
						if(!pare.getTitle().equals(title.getTitle())){
							return R.fail("false");
						}
					}
				}
			}
		}
//		if(Func.isEmpty(region.getCode())){return R.fail("区域编码不允许为空");}
		if(Func.isNotEmpty(region.getCode()) && region.getCode().trim().length() != 6){return R.fail("请输入合法的区域编码");}
		if(Func.isEmpty(region.getParentId())){
			region.setParentId(0L);
			region.setRegionType(ToolsConstant.REGION_TYPE.PROVINCE);
		}
		if(Func.isNotEmpty(region.getParentId()) && Func.isEmpty(region.getId())){
			Region pare = regionService.getById(region.getParentId());
			if(pare != null){
				switch (pare.getRegionType()){
					case 1:
						region.setRegionType(ToolsConstant.REGION_TYPE.CITY);
						break;
					case 2:
						region.setRegionType(ToolsConstant.REGION_TYPE.AREA);
						break;
					case 3:
						break;
				}
			}else{
				region.setRegionType(ToolsConstant.REGION_TYPE.PROVINCE);
			}
		}
		return regionService.saveOrUpdateRegion(region);
	}

	/*
	* @Description：//TODO 显示区域树结构
	* @param: [title]
	* @return: 
	* @author: Lxq
	* @Date: 2020/5/20 0020
	*/
	@GetMapping("/tree")
	@ApiOperationSupport(order = 8)
	@ApiOperation(value = "区域树形结构", notes = "树形结构")
	public R<List<RegionVO>> tree(String title) {
		List<RegionVO> tree = regionService.getTreeNode(title);
		return R.data(tree);
	}

serviceImpl

@Override
	@Transactional(rollbackFor = Exception.class)
	public R saveOrUpdateRegion(Region region){
		try{
			//查询出父级节点下最大排序，新增时默认填报在后面
			Integer maxSort = baseMapper.findMaxSortByParentId(region);
			if (Func.isNotEmpty(maxSort)){
				region.setSort(maxSort+1);
			}else {
				region.setSort(1);
			}
			//如果区域类型为区县就没有子级
			if(Func.isNotEmpty(region.getId())){
				Region oldRegion = baseMapper.selectById(region.getId());
				region.setRegionType(oldRegion.getRegionType());
			}
			if (region.getRegionType() != 3){

				region.setHasChildren(true);
			}else {
				region.setHasChildren(false);
			}
			region.setIsDeleted(BladeConstant.DB_NOT_DELETED);
			if (Func.isNotEmpty(region.getId())){
				baseMapper.updateById(region);
				return R.success("操作成功");
			}else{
				baseMapper.insert(region);
				return R.success("操作成功");
			}
		}catch (Exception e){
			return R.fail("操作失败");
		}
	}
	@Override
	public List<RegionVO> getTreeNode(String title) {
		List<RegionVO> treeNode = baseMapper.getTreeNode(title);
		return ForestNodeMerger.merge(treeNode);
	}
	/*
	* @Description：//TODO 区域上移
	* @param: [regionId]
	* @return: 
	* @author: Lxq
	* @Date: 2020/5/21 0021
	*/
	@Override
	@Transactional(rollbackFor = Exception.class)
	public R upRegion(Long regionId) {
		try{
			Region region = baseMapper.selectById(regionId);
			String sorts = baseMapper.findSortsByParentId(region);
			String[] sort = sorts.split(",");
			int k = 0;
			for (int i = 0,length=sort.length; i <length ; i++) {
				if (sort[i].equals(region.getSort().toString())){
					k = i;
				}
			}
			int beforeSort = 0;
			if(k >= 1){
				beforeSort = Integer.parseInt(sort[k-1]);
				Region beforeRegion = new Region();
				beforeRegion.setParentId(region.getParentId());
				beforeRegion.setSort(beforeSort);
				beforeRegion = baseMapper.selectOne(Condition.getQueryWrapper(beforeRegion));
				beforeRegion.setSort(region.getSort());
				region.setSort(beforeSort);
				int update = baseMapper.updateById(region);
				int update1 = baseMapper.updateById(beforeRegion);
				if(retBool(update) && retBool(update1)){
					return R.success("操作成功");
				}else{
					return R.fail("操作失败");
				}
			}else if(Func.equals(k,0)){
				return R.fail("不能继续上移");
			}else{
				return R.fail("操作失败");
			}
		}catch (Exception e){
			e.printStackTrace();
			return R.fail("操作失败");
		}
	}
	/*
	* @Description：//TODO 区域下移
	* @param: [regionId]
	* @return: 
	* @author: Lxq
	* @Date: 2020/5/21 0021
	*/
	@Override
	@Transactional(rollbackFor = Exception.class)
	public R downRegion(Long regionId) {
		try{
			Region region = baseMapper.selectById(regionId);
			String sorts = baseMapper.findSortsByParentId(region);
			String[] sort = sorts.split(",");
			int k = 0;
			int afterSort = 0;
			for (int i = 0,length=sort.length;i <length ; i++) {
				if (sort[i].equals(region.getSort().toString())){
					k = i;
				}
			}
			Region afterRegion = new Region();
			if (k < sort.length -1) {
				afterSort = Integer.parseInt(sort[k+1]);
				afterRegion.setParentId(region.getParentId());
				afterRegion.setSort(afterSort);
				afterRegion = baseMapper.selectOne(Condition.getQueryWrapper(afterRegion));
				afterRegion.setSort(region.getSort());
				region.setSort(afterSort);
				int update = baseMapper.updateById(afterRegion);
				int update1 = baseMapper.updateById(region);
				if(retBool(update) && retBool(update1)){
					return R.success("操作成功");
				}else{
					return R.fail("操作失败");
				}
			}else if(Func.equals(k,sort.length-1)){
				return R.fail("不能继续下移");
			}else{
				return R.fail("操作失败");
			}
		}catch (Exception e){
			e.printStackTrace();
			return R.fail("操作失败");
		}
	}

sql脚本

DROP TABLE IF EXISTS `region`;

CREATE TABLE `region` (
  `id` bigint(20) NOT NULL COMMENT '区域id',
  `code` varchar(10) NOT NULL COMMENT '地区代码',
  `title` varchar(50) DEFAULT NULL COMMENT '区域名称',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父级行政区划',
  `is_deleted` int(11) DEFAULT NULL COMMENT '是否已删除',
  `region_type` int(11) DEFAULT NULL COMMENT '区域类型',
  `remarks` varchar(255) DEFAULT NULL COMMENT '备注',
  `has_children` int(11) DEFAULT NULL COMMENT '是否有子级（0:无，1:有）',
  `sort` int(11) DEFAULT NULL COMMENT '区域排序',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

/*Data for the table `blade_region` */

insert  into `region`(`id`,`code`,`title`,`parent_id`,`is_deleted`,`region_type`,`remarks`,`has_children`,`sort`) values (1268383575049293826,'100001','中国',0,0,0,'',1,10),(1268383919233880066,'110001','北京',1268383575049293826,0,1,'',1,1),(1268388598286176258,'001223','朝阳',1268383919233880066,0,3,'',0,3),(1268389136574763010,'112222','昌平',1268383919233880066,0,3,'',0,1),(1268390232982605825,'123456','通州',1268383919233880066,0,3,'',0,2),(1268465305798098946,'123456','美国',0,1,0,'',1,11);