项目中我们可能经常有这样的需求,需要返回二级或三级的菜单,返回一个树形结构,面试中也可能经常被问到。
最近的项目中就用到了,这里整理分享一下。
应用场景:
地区树,国家,省,市,区县共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 //子节点列表 List<BaseArea> childrenList; //extend columns END public List<BaseArea> getChildrenList() { return childrenList; } public void setChildrenList(List<BaseArea> childrenList) { 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 映射方法
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"> <!-- 不使用namespace的话sql搜索定位会比较方便 --> <!-- BaseArea --> <mapper namespace="BaseArea"> <resultMap id="baseAreaResult" type="com.wanyu.smarthome.model.BaseArea"> <id property="id" column="ID"/> <result property="areaName" column="AREA_NAME"/> <result property="areaCode" column="AREA_CODE"/> <result property="parentId" column="PARENT_ID"/> <result property="platMark" column="PLAT_MARK"/> <result property="level" column="LEVEL"/> <result property="status" column="STATUS"/> <result property="expand" column="EXPAND"/> <collection property="childrenList" javaType="java.util.ArrayList" column="id" ofType="com.wanyu.smarthome.model.BaseArea" select="childrenSelect"></collection> </resultMap> <resultMap id="childrenResult" type="com.wanyu.smarthome.model.BaseArea"> <id property="id" column="ID"/> <result property="areaName" column="AREA_NAME"/> <result property="areaCode" column="AREA_CODE"/> <result property="parentId" column="PARENT_ID"/> <result property="platMark" column="PLAT_MARK"/> <result property="level" column="LEVEL"/> <result property="status" column="STATUS"/> <result property="expand" column="EXPAND"/> <collection property="childrenList" javaType="java.util.ArrayList" column="id" ofType="com.wanyu.smarthome.model.BaseArea" select="childrenSelect"></collection> </resultMap> <!-- 用于select查询公用抽取的列 --> <sql id="commonColumns"> <![CDATA[ ID, AREA_NAME, AREA_CODE, PARENT_ID, PLAT_MARK, LEVEL, STATUS, EXPAND ]]> </sql> <sql id="commonAliasColumns"> <![CDATA[ x.ID, x.AREA_NAME, x.AREA_CODE, x.PARENT_ID, x.PLAT_MARK, x.LEVEL, x.STATUS, x.EXPAND ]]> </sql> <!-- useGeneratedKeys="true" keyProperty="xxx" for sqlserver and mysql --> <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.wanyu.smarthome.model.BaseArea"> <![CDATA[ INSERT INTO base_area ( ID, AREA_NAME, AREA_CODE, PARENT_ID, PLAT_MARK, LEVEL, STATUS, EXPAND ) VALUES ( #{id}, #{areaName}, #{areaCode}, #{parentId}, #{platMark}, #{level}, #{status}, #{expand} ) ]]> </insert> <update id="update" parameterType="com.wanyu.smarthome.model.BaseArea"> <![CDATA[ UPDATE base_area SET AREA_NAME = #{areaName}, AREA_CODE = #{areaCode}, PARENT_ID = #{parentId}, PLAT_MARK = #{platMark}, LEVEL = #{level}, STATUS = #{status}, EXPAND = #{expand} WHERE ID = #{id} ]]> </update> <delete id="delete" parameterType="map"> <![CDATA[ delete from base_area ]]> <include refid="dynamicWhere"/> </delete> <delete id="batchDelete" parameterType="list"> delete from base_area where ID IN <foreach collection="list" item="ids" open="(" separator="," close=")"> #{ids} </foreach> </delete> <delete id="batchDeleteByLocalId" parameterType="list"> delete from base_area where LOCAL_ID in <foreach collection="list" item="ids" open="(" separator="," close=")"> #{ids} </foreach> </delete> <select id="getById" resultMap="baseAreaResult"> SELECT <include refid="commonColumns" /> <![CDATA[ FROM base_area WHERE ID = #{id} ]]> </select> <select id="getByIds" resultMap="baseAreaResult"> SELECT <include refid="commonColumns" /> FROM base_area WHERE ID IN <foreach collection="list" item="ids" open="(" separator="," close=")"> #{ids} </foreach> </select> <sql id="dynamicWhere"> <!-- ognl访问静态方法的表达式 为@class@method(args),以下为调用rapid中的Ognl.isNotEmpty()方法,还有其它方法如isNotBlank()可以使用,具体请查看Ognl类 --> <where> <if test="@Ognl@isNotEmpty(id)"> AND ID = #{id} </if> <if test="@Ognl@isNotEmpty(areaName)"> AND AREA_NAME = #{areaName} </if> <if test="@Ognl@isNotEmpty(areaCode)"> AND AREA_CODE = #{areaCode} </if> <if test="@Ognl@isNotEmpty(parentId)"> AND PARENT_ID = #{parentId} </if> <if test="@Ognl@isNotEmpty(platMark)"> AND PLAT_MARK = #{platMark} </if> <if test="@Ognl@isNotEmpty(level)"> AND LEVEL = #{level} </if> <if test="@Ognl@isNotEmpty(status)"> AND STATUS = #{status} </if> <if test="@Ognl@isNotEmpty(expand)"> AND EXPAND = #{expand} </if> </where> </sql> <select id="count" resultType="long"> SELECT count(*) FROM base_area <include refid="dynamicWhere"/> </select> <!-- 分页查询已经使用Dialect进行分页,也可以不使用Dialect直接编写分页 因为分页查询将传 offset,pageSize,lastRows 三个参数,不同的数据库可以根于此三个参数属性应用不同的分页实现 --> <select id="pageSelect" resultMap="baseAreaResult"> SELECT <include refid="commonColumns" /> FROM base_area <include refid="dynamicWhere"/> <if test="@Ognl@isNotEmpty(sortColumns)"> ORDER BY ${sortColumns} </if> </select> <select id="childrenSelect" resultMap="childrenResult"> SELECT <include refid="commonColumns" /> FROM base_area WHERE PARENT_ID = #{id} ORDER BY ID ASC </select> </mapper>
Mapperxml 解析
1、主查询语句为: pageSelect
2、结果映射 resultMap id="baseAreaResult"
这里的关键在于:
<id property="id" column="ID"/> <collection property="childrenList" javaType="java.util.ArrayList" column="id" ofType="com.wanyu.smarthome.model.BaseArea" select="childrenSelect"></collection>
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笑)============