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實現讀取樹結構數據實例代碼，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對武林網網站的支持！