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实现读取树结构数据实例代码,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!
赞 (0)