您当前的位置： 首页 >  mybatis

springmvc+mybatis 无极限树形结构 Mapperxml 映射方法

发布时间：2017-05-10 12:00:32 ，浏览量：0

项目中我们可能经常有这样的需求，需要返回二级或三级的菜单，返回一个树形结构，面试中也可能经常被问到。

最近的项目中就用到了，这里整理分享一下。

应用场景：

地区树，国家，省，市，区县共4级。

表结构：

建表语句： 
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `base_area`
-- ----------------------------
DROP TABLE IF EXISTS `base_area`;
CREATE TABLE `base_area` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AREA_NAME` varchar(255) DEFAULT NULL COMMENT '地区名称',
  `AREA_CODE` varchar(255) DEFAULT NULL COMMENT '地区编码',
  `PARENT_ID` bigint(20) DEFAULT NULL,
  `PLAT_MARK` bigint(20) DEFAULT NULL COMMENT '区域标识，也就是平台标识',
  `LEVEL` tinyint(4) DEFAULT '1' COMMENT '层',
  `STATUS` tinyint(4) DEFAULT '1' COMMENT '是否可用、是否显示',
  `EXPAND` tinyint(4) DEFAULT '0' COMMENT '是否展开子节点，非0为展开。',
  PRIMARY KEY (`ID`),
  KEY `index2` (`PLAT_MARK`)
) ENGINE=InnoDB AUTO_INCREMENT=3514 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;
返回JSON结果示例：

数据比较多，这里只展示一部分，有需要完整表数据的小伙伴，稍后提供下载地址。 

{"result":[{"createTimeString":"","updateTimeString":"","level":3,"platMark":100001001000000,"parentId":1,"areaCode":"110000","expand":0,"areaName":"北京市","id":2,"childrenList":[{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001001000,"parentId":2,"areaCode":"110101","expand":0,"areaName":"东城区","id":4,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001002000,"parentId":2,"areaCode":"110102","expand":0,"areaName":"西城区","id":5,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001003000,"parentId":2,"areaCode":"110105","expand":0,"areaName":"朝阳区","id":6,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001004000,"parentId":2,"areaCode":"110106","expand":0,"areaName":"丰台区","id":7,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001005000,"parentId":2,"areaCode":"110107","expand":0,"areaName":"石景山区","id":8,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001006000,"parentId":2,"areaCode":"110108","expand":0,"areaName":"海淀区","id":9,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001007000,"parentId":2,"areaCode":"110109","expand":0,"areaName":"门头沟区","id":10,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001008000,"parentId":2,"areaCode":"110111","expand":0,"areaName":"房山区","id":11,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001009000,"parentId":2,"areaCode":"110112","expand":0,"areaName":"通州区","id":12,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001010000,"parentId":2,"areaCode":"110113","expand":0,"areaName":"顺义区","id":13,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001011000,"parentId":2,"areaCode":"110114","expand":0,"areaName":"昌平区","id":14,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001012000,"parentId":2,"areaCode":"110115","expand":0,"areaName":"大兴区","id":15,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001013000,"parentId":2,"areaCode":"110116","expand":0,"areaName":"怀柔区","id":16,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001014000,"parentId":2,"areaCode":"110117","expand":0,"areaName":"平谷区","id":17,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001015000,"parentId":2,"areaCode":"110228","expand":0,"areaName":"密云县","id":18,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001016000,"parentId":2,"areaCode":"110229","expand":0,"areaName":"延庆县","id":19,"childrenList":[]}]},{"createTimeString":"","updateTimeString":"","level":3,"platMark":100002001000000,"parentId":1,"areaCode":"120000","expand":0,"areaName":"天津市","id":20,"childrenList":[{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001001000,"parentId":20,"areaCode":"120101","expand":0,"areaName":"和平区","id":22,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001002000,"parentId":20,"areaCode":"120102","expand":0,"areaName":"河东区","id":23,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001003000,"parentId":20,"areaCode":"120103","expand":0,"areaName":"河西区","id":24,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001004000,"parentId":20,"areaCode":"120104","expand":0,"areaName":"南开区","id":25,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001005000,"parentId":20,"areaCode":"120105","expand":0,"areaName":"河北区","id":26,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001006000,"parentId":20,"areaCode":"120106","expand":0,"areaName":"红桥区","id":27,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001007000,"parentId":20,"areaCode":"120110","expand":0,"areaName":"东丽区","id":28,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001008000,"parentId":20,"areaCode":"120111","expand":0,"areaName":"西青区","id":29,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001009000,"parentId":20,"areaCode":"120112","expand":0,"areaName":"津南区","id":30,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001010000,"parentId":20,"areaCode":"120113","expand":0,"areaName":"北辰区","id":31,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001011000,"parentId":20,"areaCode":"120114","expand":0,"areaName":"武清区","id":32,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001012000,"parentId":20,"areaCode":"120115","expand":0,"areaName":"宝坻区","id":33,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001013000,"parentId":20,"areaCode":"120116","expand":0,"areaName":"滨海新区","id":34,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001014000,"parentId":20,"areaCode":"120221","expand":0,"areaName":"宁河县","id":35,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001015000,"parentId":20,"areaCode":"120223","expand":0,"areaName":"静海县","id":36,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100002001016000,"parentId":20,"areaCode":"120225","expand":0,"areaName":"蓟县","id":37,"childrenList":[]}]}]}
实体类： 
/**
 * 表名：base_area
 * 备注：BaseArea
 */
@SuppressWarnings("serial")
public class BaseArea {
		
	//date formats
	
	//columns START
	//id
	private Long id;
	//地区名称
	private String areaName;
	//地区编码
	private String areaCode;
	//parentId
	private Long parentId;
	//区域标识，也就是平台标识
	private Long platMark;
	//层
	private Byte level;
	//是否可用、是否显示
	private Integer status;
	//是否展开子节点，非0为展开。
	private Byte expand;
	//columns END
	
	//extend columns START
	//子节点列表
	ListchildrenList;
	//extend columns END

	public ListgetChildrenList() {
		return childrenList;
	}

	public void setChildrenList(ListchildrenList) {
		this.childrenList = childrenList;
	}

	public BaseArea(){
	}

	public BaseArea(Long id){
		this.id = id;
	}

	/**
	 * id
	 * @return
	 */
	public Long getId() {
		return this.id;
	}
	
	/**
	 * id
	 * @param value
	 */
	public void setId(Long value) {
		this.id = value;
	}
	/**
	 * 地区名称
	 * @return
	 */
	public String getAreaName() {
		return this.areaName;
	}
	
	/**
	 * 地区名称
	 * @param value
	 */
	public void setAreaName(String value) {
		this.areaName = value;
	}
	/**
	 * 地区编码
	 * @return
	 */
	public String getAreaCode() {
		return this.areaCode;
	}
	
	/**
	 * 地区编码
	 * @param value
	 */
	public void setAreaCode(String value) {
		this.areaCode = value;
	}
	/**
	 * parentId
	 * @return
	 */
	public Long getParentId() {
		return this.parentId;
	}
	
	/**
	 * parentId
	 * @param value
	 */
	public void setParentId(Long value) {
		this.parentId = value;
	}
	/**
	 * 区域标识，也就是平台标识
	 * @return
	 */
	public Long getPlatMark() {
		return this.platMark;
	}
	
	/**
	 * 区域标识，也就是平台标识
	 * @param value
	 */
	public void setPlatMark(Long value) {
		this.platMark = value;
	}
	/**
	 * 层
	 * @return
	 */
	public Byte getLevel() {
		return this.level;
	}
	
	/**
	 * 层
	 * @param value
	 */
	public void setLevel(Byte value) {
		this.level = value;
	}
	/**
	 * 是否可用、是否显示
	 * @return
	 */
	public Integer getStatus() {
		return this.status;
	}
	
	/**
	 * 是否可用、是否显示
	 * @param value
	 */
	public void setStatus(Integer value) {
		this.status = value;
	}
	/**
	 * 是否展开子节点，非0为展开。
	 * @return
	 */
	public Byte getExpand() {
		return this.expand;
	}
	
	/**
	 * 是否展开子节点，非0为展开。
	 * @param value
	 */
	public void setExpand(Byte value) {
		this.expand = value;
	}
}
Mapperxml 映射方法 




delete from base_area where ID IN#{ids}delete from base_area where LOCAL_ID in#{ids}SELECTSELECTFROM base_area WHERE ID IN#{ids}AND ID = #{id}AND AREA_NAME = #{areaName}AND AREA_CODE = #{areaCode}AND PARENT_ID = #{parentId}AND PLAT_MARK = #{platMark}AND LEVEL = #{level}AND STATUS = #{status}AND EXPAND = #{expand}SELECT count(*) FROM base_areaSELECTFROM base_areaORDER BY ${sortColumns}SELECTFROM base_area 
		WHERE PARENT_ID = #{id}
		ORDER BY ID ASC
Mapperxml 解析

1、主查询语句为： pageSelect 2、结果映射 resultMap id="baseAreaResult" 

这里的关键在于： 


		

			collection 标签中定义属性名称为 childrenList，对应实体类中的：childrenList
		

		

			属性类型为：java.util.ArrayList
		

		

			
		

		

			column="id" 将 id 列的值做为参数传递给子查询
		

		

			ofType 定义List 中保存的数据类型
		

		

			select 定义子查询
		

		

			3、注意子查询 childrenSelect 对应的结果映射 childrenResult ，又包含了 collection 标签，形成了循环递归调用
		

		
			小结
		
		

			不知道我有没有描述清楚，有不明白的地方请留言。
		

		

			强烈不推荐这种循环递归调用的写法，因为性能非常差。
		

		

			最好是有几级就写几级，也就是写几个 resultMap。
		

		

			本粟中是演示同一个表相同实体类的实现方式，同样也可以不同的表不同的实体类，只要把子节点类型修改一下就可以了。
		

		

			为了性能，这种查询最好配合缓存使用。
		


======================文档信息======================

			


版权声明：非商用自由转载-保持署名-注明出处

		


署名(BY) ：testcs_dn(微wx笑)

			


文章出处：[无知人生，记录点滴](http://blog.csdn.NET/testcs_dn)
		


==============本文首发于个人微信订阅号（微wx笑）============
			



		

	






            


            

                
关注

                
打赏

            

            

                

				    

				        

				            
				            1688896170
    				    

	    			    

		    		        
			    	        
				        

            	    

            	

    	        

			                	    

	    		
查看更多评论

            

        

    

    

        

            

                
                

                    

                    
暂无认证

                

            

            

                
    
                    
  • 
                        
    0浏览
    
                        
    0关注
    
                        
    106166博文
    
                        
    0收益
    
                    
    • 

                    
  • 
                        
    0浏览
    
                        
    0点赞
    
                        
    0打赏
    
                        
    0留言
    
                    
    • 
                

            

            

                
私信

                
关注

            


        

        

            
热门博文

            
        

    

    









    
[ 申请 ]友情链接：

    

        快连
        快连vpn
        搜外友链
        笔趣阁
        爱思助手
        ClashX教程
        绘画宝宝
        配音宝宝
    









    

        立即登录/注册
        
    

    

        
        
微信扫码登录

    



















0.0521s