mybatis实现读取树结构数据实例代码-php教程-PHP中文网

首页

后端开发

php教程

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

高洛峰

Jan 17, 2017 pm 01:44 PM

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

表结构

CREATE TABLE `lscrm_function_privilege` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT &#39;编号&#39;,
`create_id` varchar(30) NOT NULL DEFAULT &#39;sys&#39;,
`update_id` varchar(30) NOT NULL DEFAULT &#39;sys&#39;,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT &#39;创建时间&#39;,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT &#39;修改时间&#39;,
`validity` tinyint(3) unsigned NOT NULL DEFAULT &#39;1&#39; COMMENT &#39;有效性 1.有效 0. 无效&#39;,
`code` varchar(50) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;编码&#39;,
`function_name` varchar(50) NOT NULL DEFAULT &#39;&#39; COMMENT &#39;名称&#39;,
`parent_id` tinyint(3) unsigned NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;父节点&#39;,
`is_leaf_node` tinyint(3) unsigned NOT NULL DEFAULT &#39;1&#39; COMMENT &#39;是否叶子节点(叶子结点 就是度为0的结点 就是没有子结点的结点),在添加子节点时,需要将parent_id is_leaf_node 设置成0&#39;,
`sub_system_id` int(10) unsigned NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;所属子系统&#39;,
`is_hidden` tinyint(3) unsigned NOT NULL DEFAULT &#39;0&#39; COMMENT &#39;UI是否隐藏,ui上不展示&#39;,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COMMENT=&#39;子code 按照父code 来创建\r\n如: customer.add&#39;;

登录后复制

构造数据

INSERT INTO `lscrm_function_privilege` VALUES (&#39;1&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 15:20:40&#39;, &#39;2016-09-07 15:21:17&#39;, &#39;1&#39;, &#39;WEB.PERMISSION&#39;, &#39;权限管理&#39;, &#39;0&#39;, &#39;0&#39;, &#39;1&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;2&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 15:21:12&#39;, &#39;2016-09-07 15:21:40&#39;, &#39;1&#39;, &#39;WEB.PERMISSION.USER-MGMT&#39;, &#39;用户管理&#39;, &#39;1&#39;, &#39;0&#39;, &#39;1&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;3&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 15:22:16&#39;, &#39;2016-09-07 15:22:35&#39;, &#39;1&#39;, &#39;WEB.PERMISSION.USER-MGMT.ADD&#39;, &#39;新增用户&#39;, &#39;2&#39;, &#39;1&#39;, &#39;1&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;4&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 15:23:17&#39;, &#39;2016-09-07 15:23:17&#39;, &#39;1&#39;, &#39;WEB.PERMISSION.USER-MGMT.MODIFY&#39;, &#39;修改用户&#39;, &#39;2&#39;, &#39;1&#39;, &#39;1&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;5&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:10:37&#39;, &#39;2016-09-07 16:10:42&#39;, &#39;1&#39;, &#39;APP.CUSTOMER&#39;, &#39;客户管理&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;6&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:11:06&#39;, &#39;2016-09-07 16:11:20&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.ADD&#39;, &#39;添加客户&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;7&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:11:06&#39;, &#39;2016-09-07 16:11:20&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.MODIFY&#39;, &#39;修改客户&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;8&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:12:33&#39;, &#39;2016-09-07 16:14:10&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.VIEWDETAIL&#39;, &#39;查看客户详情&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;9&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:12:33&#39;, &#39;2016-09-07 16:12:33&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.ADDVISIT&#39;, &#39;添加拜访&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;10&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:13:59&#39;, &#39;2016-09-07 16:14:17&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.VIEWDEMAND&#39;, &#39;查看客户采购需求&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;11&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:13:59&#39;, &#39;2016-09-07 16:14:17&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.VIEWORDER&#39;, &#39;查看客户订单&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;12&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:13:59&#39;, &#39;2016-09-07 16:14:17&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.VIEWSHOP&#39;, &#39;查看客户订单&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;13&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:17:28&#39;, &#39;2016-09-07 16:18:39&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.VIEWREPLY&#39;, &#39;查看抢单列表&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;14&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:19:32&#39;, &#39;2016-09-07 16:23:51&#39;, &#39;1&#39;, &#39;APP.CUSTOMER.FASTOPENSHOP&#39;, &#39;快捷开店&#39;, &#39;5&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;15&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:22:33&#39;, &#39;2016-09-07 16:22:33&#39;, &#39;1&#39;, &#39;APP.DEMAND&#39;, &#39;需求管理&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;17&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:23:21&#39;, &#39;2016-09-07 16:23:21&#39;, &#39;1&#39;, &#39;APP.DEMAND.PRIVATE&#39;, &#39;私海需求&#39;, &#39;15&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;18&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:23:58&#39;, &#39;2016-09-07 16:23:58&#39;, &#39;1&#39;, &#39;APP.DEMAND.FEEDBACK&#39;, &#39;找版反馈&#39;, &#39;15&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;19&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:24:47&#39;, &#39;2016-09-07 16:24:47&#39;, &#39;1&#39;, &#39;APP.DEMAND.PUSHSEARCH&#39;, &#39;推送卖家搜索&#39;, &#39;15&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;20&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:25:56&#39;, &#39;2016-09-07 16:25:56&#39;, &#39;1&#39;, &#39;APP.KPI&#39;, &#39;绩效管理&#39;, &#39;0&#39;, &#39;0&#39;, &#39;2&#39;, &#39;0&#39;);
INSERT INTO `lscrm_function_privilege` VALUES (&#39;21&#39;, &#39;sys&#39;, &#39;sys&#39;, &#39;2016-09-07 16:27:02&#39;, &#39;2016-09-07 16:27:02&#39;, &#39;1&#39;, &#39;APP.KPI.VIEWDATA&#39;, &#39;查看绩效数据&#39;, &#39;20&#39;, &#39;1&#39;, &#39;2&#39;, &#39;0&#39;);

登录后复制

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

更多mybatis实现读取树结构数据实例代码相关文章请关注PHP中文网！

本站声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn