mybatis学习-树形案例（三）

目录

一、部门树（自关联查询）

1.1 数据表设计

1.2 业务代码实现

 二 部门和员工关系树（一对多案例）

2.1 数据库设计

 2.2 业务代码实现

---

 

 

一、部门树（自关联查询）

        最终效果如下：

{
    "code":0,
    "msg":"Success",
    "data":[
        {
            "id":"1",
            "deptName":"总经理部",
            "deptCode":"111",
            "parentId":"0",
            "children":[
                {
                    "id":"2",
                    "deptName":"技术部",
                    "deptCode":"22",
                    "parentId":"1",
                    "children":[
                        {
                            "id":"33",
                            "deptName":"技术分部",
                            "deptCode":"3423",
                            "parentId":"2",
                            "children":[

                            ]
                        }
                    ]
                },
                {
                    "id":"3",
                    "deptName":"大数据部",
                    "deptCode":"23",
                    "parentId":"1",
                    "children":[

                    ]
                }
            ]
        }
    ]
}

 实现步骤

1.1 数据表设计

        部门表和员工表（一个部门对应多个员工，一个员工只能属于一个部门 属于一对多的关系）

部门表：

1.2 业务代码实现

DeptMapper.xml文件：

<mapper namespace="com.test.dao.DeptMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.test.entity.Dept">
        <id column="id" property="id" />
        <result column="dept_name" property="deptName" />
        <result column="dept_code" property="deptCode" />
        <result column="parent_id" property="parentId" />
        <collection property="children" ofType="com.test.entity.Dept" column="id"
                    select="com.test.dao.DeptMapper.listDeptByParentId" />
    </resultMap>

    <select id="listDeptByParentId" resultMap="BaseResultMap">
        SELECT
            *
        FROM
            sys_dept
        WHERE
            parent_id = #{parentId}
    </select>
</mapper>

DeptMapper.java文件：

List<Dept> listDeptByParentId(String parentId);

controller入口：

@GetMapping("tree")
public R tree(String id){
    List<Dept> list = deptMapper.listDeptByParentId(id);
    return R.ok(list);
}

---

 二 部门和员工关系树（一对多案例）

         一个部门对应多个员工，一个员工只能属于一个部门 属于一对多的关系

效果图：最终返回数据格式：

{
    "code":0,
    "msg":"Success",
    "data":[
        {
            "id":"2",
            "deptName":"技术部",
            "parentId":"1",
            "children":[
                {
                    "id":"33",
                    "deptName":"技术分部",
                    "parentId":"2",
                    "children":[

                    ],
                    "userchildren":[

                    ]
                }
            ],
            "userchildren":[
                {
                    "username":"查三",
                    "userId":"1",
                    "deptId":"2"
                },
                {
                    "username":"李四",
                    "userId":"2",
                    "deptId":"2"
                }
            ]
        },
        {
            "id":"3",
            "deptName":"大数据部",
            "parentId":"1",
            "children":[

            ],
            "userchildren":[

            ]
        }
    ]
}

2.1 数据库设计

部门表数据：

员工表数据：

 

 2.2 业务代码实现

实体类：

 

部门实体：

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("sys_dept")
public class Dept extends Model<Dept> {

    private static final long serialVersionUID = 1L;

    @TableId(type = IdType.ID_WORKER)
    private String id;

    private String deptName;

    private String parentId;

    @TableField(exist = false)
    private List<Dept> children;

    @TableField(exist = false)
    private List<UserVo> userchildren;

    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

用户实体：

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class UserVo {

    private static final long serialVersionUID = 1L;

    private String username;

    private String userId;

    private String deptId;
}

xml文件：

 

<?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.test.dao.DeptMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.test.entity.Dept">
        <id column="id" property="id" />
        <result column="dept_name" property="deptName" />
        <result column="parent_id" property="parentId" />
        <collection property="children" ofType="com.test.entity.Dept" column="id"
                    select="com.test.dao.DeptMapper.listDeptByParentId" />
        <collection property="userchildren" resultMap="userResultMap"/>
    </resultMap>

    <resultMap id="userResultMap" type="com.test.entity.UserVo">
        <id column="userId" property="userId" />
        <result column="username" property="username" />
        <result column="deptId" property="deptId" />
    </resultMap>

    <select id="listDeptByParentId" resultMap="BaseResultMap">
        SELECT
            a.*,
            b.id as userId,
            b.username as username,
            b.dept_id as deptId
        FROM
            sys_dept a left join sys_user b on a.id = b.dept_id
        WHERE
            a.parent_id = #{parentId}
    </select>
</mapper>
 

 

---