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"><cacheeviction="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 {@Autowiredprivate LscrmFunctionPrivilegeDao lscrmFunctionPrivilegeDao;@Overridepublic 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实现读取树结构数据实例代码，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对武林网网站的支持！