mybatis实现读取树结构数据实例代码

mybatis实现读取树结构数据详细介绍如下所示：

表结构

create table `lscrm_function_privilege` (
`id` int(10) unsigned not null auto_increment comment '编号',
`create_id` varchar(30) not null default 'sys',
`update_id` varchar(30) not null default 'sys',
`create_time` timestamp not null default current_timestamp comment '创建时间',
`update_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间',
`validity` tinyint(3) unsigned not null default '1' comment '有效性 1.有效 0. 无效',
`code` varchar(50) not null default '' comment '编码',
`function_name` varchar(50) not null default '' comment '名称',
`parent_id` tinyint(3) unsigned not null default '0' comment '父节点',
`is_leaf_node` tinyint(3) unsigned not null default '1' comment '是否叶子节点(叶子结点 就是度为0的结点 就是没有子结点的结点),在添加子节点时,需要将parent_id is_leaf_node 设置成0',
`sub_system_id` int(10) unsigned not null default '0' comment '所属子系统',
`is_hidden` tinyint(3) unsigned not null default '0' comment 'ui是否隐藏,ui上不展示',
primary key (`id`)
) engine=innodb auto_increment=22 default charset=utf8 comment='子code 按照父code 来创建\r\n如: customer.add';

构造数据

insert into `lscrm_function_privilege` values ('1', 'sys', 'sys', '2016-09-07 15:20:40', '2016-09-07 15:21:17', '1', 'web.permission', '权限管理', '0', '0', '1', '0');
insert into `lscrm_function_privilege` values ('2', 'sys', 'sys', '2016-09-07 15:21:12', '2016-09-07 15:21:40', '1', 'web.permission.user-mgmt', '用户管理', '1', '0', '1', '0');
insert into `lscrm_function_privilege` values ('3', 'sys', 'sys', '2016-09-07 15:22:16', '2016-09-07 15:22:35', '1', 'web.permission.user-mgmt.add', '新增用户', '2', '1', '1', '0');
insert into `lscrm_function_privilege` values ('4', 'sys', 'sys', '2016-09-07 15:23:17', '2016-09-07 15:23:17', '1', 'web.permission.user-mgmt.modify', '修改用户', '2', '1', '1', '0');
insert into `lscrm_function_privilege` values ('5', 'sys', 'sys', '2016-09-07 16:10:37', '2016-09-07 16:10:42', '1', 'app.customer', '客户管理', '0', '0', '2', '0');
insert into `lscrm_function_privilege` values ('6', 'sys', 'sys', '2016-09-07 16:11:06', '2016-09-07 16:11:20', '1', 'app.customer.add', '添加客户', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('7', 'sys', 'sys', '2016-09-07 16:11:06', '2016-09-07 16:11:20', '1', 'app.customer.modify', '修改客户', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('8', 'sys', 'sys', '2016-09-07 16:12:33', '2016-09-07 16:14:10', '1', 'app.customer.viewdetail', '查看客户详情', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('9', 'sys', 'sys', '2016-09-07 16:12:33', '2016-09-07 16:12:33', '1', 'app.customer.addvisit', '添加拜访', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('10', 'sys', 'sys', '2016-09-07 16:13:59', '2016-09-07 16:14:17', '1', 'app.customer.viewdemand', '查看客户采购需求', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('11', 'sys', 'sys', '2016-09-07 16:13:59', '2016-09-07 16:14:17', '1', 'app.customer.vieworder', '查看客户订单', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('12', 'sys', 'sys', '2016-09-07 16:13:59', '2016-09-07 16:14:17', '1', 'app.customer.viewshop', '查看客户订单', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('13', 'sys', 'sys', '2016-09-07 16:17:28', '2016-09-07 16:18:39', '1', 'app.customer.viewreply', '查看抢单列表', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('14', 'sys', 'sys', '2016-09-07 16:19:32', '2016-09-07 16:23:51', '1', 'app.customer.fastopenshop', '快捷开店', '5', '1', '2', '0');
insert into `lscrm_function_privilege` values ('15', 'sys', 'sys', '2016-09-07 16:22:33', '2016-09-07 16:22:33', '1', 'app.demand', '需求管理', '0', '0', '2', '0');
insert into `lscrm_function_privilege` values ('17', 'sys', 'sys', '2016-09-07 16:23:21', '2016-09-07 16:23:21', '1', 'app.demand.private', '私海需求', '15', '1', '2', '0');
insert into `lscrm_function_privilege` values ('18', 'sys', 'sys', '2016-09-07 16:23:58', '2016-09-07 16:23:58', '1', 'app.demand.feedback', '找版反馈', '15', '1', '2', '0');
insert into `lscrm_function_privilege` values ('19', 'sys', 'sys', '2016-09-07 16:24:47', '2016-09-07 16:24:47', '1', 'app.demand.pushsearch', '推送卖家搜索', '15', '1', '2', '0');
insert into `lscrm_function_privilege` values ('20', 'sys', 'sys', '2016-09-07 16:25:56', '2016-09-07 16:25:56', '1', 'app.kpi', '绩效管理', '0', '0', '2', '0');
insert into `lscrm_function_privilege` values ('21', 'sys', 'sys', '2016-09-07 16:27:02', '2016-09-07 16:27:02', '1', 'app.kpi.viewdata', '查看绩效数据', '20', '1', '2', '0');

实体bean

package com.lianshang.crm.biz.entity;
import com.google.common.base.moreobjects;
import java.util.arraylist;
import java.util.date;
import java.util.list;
public class lscrmfunctionprivilegeentity {
/**编号**/
private int id;
private string createid;
private string updateid;
/**创建时间**/
private date createtime;
/**修改时间**/
private date updatetime=new date();
/**有效性 1.有效 0. 无效**/
private int validity=1;
/**编码**/
private string code;
/**名称**/
private string functionname;
/**父节点**/
private int parentid;
/**是否叶子节点(叶子结点 就是度为0的结点 就是没有子结点的结点),在添加子节点时,需要将parent_id is_leaf_node 设置成0**/
private int isleafnode;
/**所属子系统**/
private int subsystemid;
/**ui是否隐藏,ui上不展示**/
private int ishidden=1;
private list<lscrmfunctionprivilegeentity> privilegelist = new arraylist<>();
//get()/set()省略。。。
}
package com.lianshang.crm.biz.entity;
import java.util.arraylist;
import java.util.date;
import java.util.list;
/**
* created by zhenyu on 2016/9/8.
*/
public class lscrmfunctionprivilegetree {
/**编号**/
private int id;
private string createid;
private string updateid;
/**创建时间**/
private date createtime;
/**修改时间**/
private date updatetime=new date();
/**有效性 1.有效 0. 无效**/
private int validity=1;
/**编码**/
private string code;
/**名称**/
private string functionname;
/**父节点**/
private int parentid;
/**是否叶子节点(叶子结点 就是度为0的结点 就是没有子结点的结点),在添加子节点时,需要将parent_id is_leaf_node 设置成0**/
private int isleafnode;
/**所属子系统**/
private int subsystemid;
/**ui是否隐藏,ui上不展示**/
private int ishidden=1;
private list<lscrmfunctionprivilegeentity> privilegelist = new arraylist<>();
}

mapper配置

<?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.lianshang.crm.biz.dao.lscrmfunctionprivilegedao">
<cache
eviction="fifo"
flushinterval="60000"
size="1024"
readonly="true"/>
<resultmap id="lscrmfunctionprivilegeresult" type="lscrmfunctionprivilegeentity">
<id property="id" column="id"/>
<result property="createid" column="create_id"/>
<result property="updateid" column="update_id"/>
<result property="createtime" column="create_time"/>
<result property="updatetime" column="update_time"/>
<result property="validity" column="validity"/>
<result property="code" column="code"/>
<result property="functionname" column="function_name"/>
<result property="parentid" column="parent_id"/>
<result property="isleafnode" column="is_leaf_node"/>
<result property="subsystemid" column="sub_system_id"/>
<result property="ishidden" column="is_hidden"/>
</resultmap>
<resultmap id="subprivilegesresult" type="lscrmfunctionprivilegeentity" extends="lscrmfunctionprivilegeresult">
<collection property="privilegelist" javatype="java.util.arraylist" column="id"
oftype="lscrmfunctionprivilegeentity" select="selectsubprivileges"></collection>
</resultmap>
<resultmap id="lscrmfunctionprivilegetreeresult" type="lscrmfunctionprivilegetree"
extends="subprivilegesresult">
<!--<association property="functionprivilege" column="id" javatype="lscrmfunctionprivilegeentity" resultmap="lscrmfunctionprivilegeresult"/>-->
<collection property="privilegelist" javatype="java.util.arraylist" column="id"
oftype="lscrmfunctionprivilegeentity" select="selectsubprivileges"></collection>
</resultmap>
<sql id="tbl_name">
lscrm_function_privilege
</sql>
<sql id="role_privilege_tbl_name">
lscrm_role_privilege
</sql>
<sql id="select_sql">
select id, create_id, update_id, create_time, update_time, validity, code, function_name, parent_id,
is_leaf_node, sub_system_id, is_hidden from
<include refid="tbl_name"/>
</sql>
<select id="readallprivileges" resultmap="lscrmfunctionprivilegetreeresult" usecache="true">
<include refid="select_sql"/>
where parent_id = 0
<if test="subsystemid > 0 ">
and sub_system_id= #{subsystemid}
</if>
</select>
<select id="selectsubprivileges" resultmap="subprivilegesresult">
<include refid="select_sql"/>
where parent_id = #{id}
</select>
</mapper>
package com.lianshang.crm.biz.dao;
import com.lianshang.crm.api.dto.lscrmfunctionprivilege;
import com.lianshang.crm.biz.entity.lscrmfunctionprivilegeentity;
import com.lianshang.crm.biz.entity.lscrmfunctionprivilegetree;
import org.apache.ibatis.annotations.param;
import java.util.list;
public interface lscrmfunctionprivilegedao {
list<lscrmfunctionprivilegetree> readallprivileges(@param("subsystemid")int subsystemid);
}

service

package com.lianshang.crm.biz.service.impl;
import com.alibaba.fastjson.json;
import com.google.common.base.function;
import com.lianshang.common.utils.general.generalresult;
import com.lianshang.common.utils.general.guavautil;
import com.lianshang.common.utils.general.stringutil;
import com.lianshang.crm.api.dto.lscrmfunctionprivilege;
import com.lianshang.crm.api.service.lscrmfunctionprivilegeservice;
import com.lianshang.crm.biz.dao.lscrmfunctionprivilegedao;
import com.lianshang.crm.biz.entity.lscrmfunctionprivilegeentity;
import com.lianshang.crm.biz.entity.lscrmfunctionprivilegetree;
import org.springframework.beans.factory.annotation.autowired;
import java.util.arraylist;
import java.util.date;
import java.util.list;
import static com.google.common.collect.fluentiterable.from;
public class lscrmfunctionprivilegeserviceimpl implements lscrmfunctionprivilegeservice {
@autowired
private lscrmfunctionprivilegedao lscrmfunctionprivilegedao;
@override
public generalresult<string> readallprivileges(int subsystemid) {
list<lscrmfunctionprivilegetree> privilegetrees = lscrmfunctionprivilegedao.readallprivileges(subsystemid);
return new generalresult<>(json.tojsonstring(privilegetrees));
}
}

返回结果

[
{
"code": "web.permission",
"createid": "sys",
"createtime": 1473232840000,
"functionname": "权限管理",
"hidden": 0,
"id": 1,
"leafnode": 0,
"parentid": 0,
"privilegelist": [
{
"code": "web.permission.user-mgmt",
"createid": "sys",
"createtime": 1473232872000,
"functionname": "用户管理",
"hidden": 0,
"id": 2,
"leafnode": 0,
"parentid": 1,
"privilegelist": [
{
"code": "web.permission.user-mgmt.add",
"createid": "sys",
"createtime": 1473232936000,
"functionname": "新增用户",
"hidden": 0,
"id": 3,
"leafnode": 1,
"parentid": 2,
"privilegelist": [],
"subsystemid": 1,
"updateid": "sys",
"updatetime": 1473232955000,
"validity": 1
},
{
"code": "web.permission.user-mgmt.modify",
"createid": "sys",
"createtime": 1473232997000,
"functionname": "修改用户",
"hidden": 0,
"id": 4,
"leafnode": 1,
"parentid": 2,
"privilegelist": [],
"subsystemid": 1,
"updateid": "sys",
"updatetime": 1473232997000,
"validity": 1
}
],
"subsystemid": 1,
"updateid": "sys",
"updatetime": 1473232900000,
"validity": 1
}
],
"subsystemid": 1,
"updateid": "sys",
"updatetime": 1473232877000,
"validity": 1
}
]

以上所述是小编给大家介绍的mybatis实现读取树结构数据实例代码，希望对大家有所帮助