Mybatis之高级映射collection （递归查出树形数据之查询部门及部门下所有人员 二叉树）

第一步：创建树形数据Bean

public class DeptTree {private String id;private String name;private List<DeptTree> childrenList;//当前部门下的子部门集合private List<UserVo> userList;//当前部门下的部门人员集合public List<UserVo> getUserList() {return userList;}public void setUserList(List<UserVo> userList) {this.userList = userList;}public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public List<DeptTree> getChildrenList() {return childrenList;}public void setChildrenList(List<DeptTree> childrenList) {this.childrenList = childrenList;}}

第二步：mybatis相应的sql.xml文件配置

<!-- 查询所有部门（含部门下的人员） --><!-- 初始化部门树 --><resultMap type="com.lilosoft.cospace.sys.bean.DeptTree" id="deptTreeV"><result column="DEPT_ID" property="id" javaType="java.lang.String" /><result column="DEPT_NAME" property="name" javaType="java.lang.String" /><collection column="DEPT_ID" property="childrenList" ofType="DeptTree" javaType="java.util.ArrayList" select="selectDeptChildrenByIdV"/><collection column="DEPT_ID" property="userList" ofType="UserVo" javaType="java.util.ArrayList" select="selectUserByDeptId"/></resultMap><resultMap type="com.lilosoft.cospace.sys.bean.UserVo" id="userVo"><result column="deptId" property="deptId" javaType="java.lang.String" /><result column="deptName" property="deptName" javaType="java.lang.String" /><result column="userId" property="userId" javaType="java.lang.String" /><result column="userName" property="userName" javaType="java.lang.String" /></resultMap><!-- 根据前端页面传过来的deptId，查出所有部门 --><select id="queryDeptUserTreeList" resultMap="deptTreeV">select dept_id,dept_name from sys_dept  where is_use='0' and dept_id= #{deptId}</select><!-- 查询出部门下的所有子部门 --><select id="selectDeptChildrenByIdV" resultMap="deptTreeV" parameterType="string">select dept_id,dept_name from sys_dept  where is_use='0' and parent_id= #{DEPT_ID}</select><!-- 查询当前部门下的所有人员 --><select id="selectUserByDeptId" resultMap="userVo" parameterType="string">SELECT a.dept_id as "deptId",a.dept_name as "deptName",b.user_id as "userId",c.user_name as "userName"FROM sys_dept aleft join sys_deptmember b on a.dept_id =b.dept_idleft join sys_user c on b.user_id =c.user_idwhere a.is_use='0' AND a.dept_id=#{DEPT_ID}</select>

第三步：（dao和service层代码略）控制器调用

List<DeptTree> dList=deptService.queryDeptUserTreeList(Map<String,Object> paraMaps);//前端可以传deptId 部门id

第四步：树形数据展示(每个部门的子部门存放在childrenList中，每个部门的人员放在userList中)

this.treeData = [    {        id: 0,        name: '总部门',        type: 'dept',        childrenList: [            {                id: 1,                name: '部门1',                         type: 'dept',                 childrenList:[                        {                          id:d1,                          name:'部门1-1'                          type:dept                          childrenList:[                          ]                          userList:[                          ]                         },                        {                          id:d2,                          name:'部门1-2'                          type:dept                          childrenList:[                          ]                          userList:[                          ]                         },                         //子部门....                ],                userList:[                    {                        id:r1,                        name:'jay'                        type:user                    },                    {                        id:r2,                        name:'xxm'                        type:user                    },                    //部门人员                ]            },            {                id: 2,                name: '部门2',                         type: 'dept',                 childrenList:[                ],                userList:[                    {                        id:r1,                        name:'jay'                        type:user                    },                    {                        id:r2,                        name:'xxm'                        type:user                    },                    {                        id:r3,                        name:'alen'                        type:user                    }                ]             }        ],        userList:[            {                id:r1,                name:'jay'                type:user            },            {                id:r2,                name:'xxm'                type:user            },            {                id:r3,                name:'alen'                type:user            }        ]     }]