Using mybatis to easily realize recursive query and stored procedure call

Time:2022-2-12

VHR department management module updated! In order to let the partners quickly understand the implementation idea of the department management module, I want to introduce you to the general implementation idea and core code through three short articles.

Project address: https://github.com/lenve/vhr

It is recommended that you read the previous article first, which will help you understand this article.

1. The front and back ends of springboot + Vue are separated, and springsecurity is used to perfectly handle the permission problem (I)
2. The front and back ends of springboot + Vue are separated, and springsecurity is used to perfectly handle the permission problem (2)
3. Password salt in springsecurity and exception handling in springboot
4. Axios request encapsulation and exception handling
5. Dynamically load Vue components in the permission management module
6. The front and back ends of springboot + Vue are separated, and springsecurity is used to perfectly handle the permission problem (6)
7. VHR department management database design and programming

This paper mainly introduces the background program of department management function, which is actually conventional code. We focus on two points: 1 Recursive call, 2 Stored procedure call

Recursive call

Because the hierarchy of departments is uncontrollable, if I want to obtain the complete JSON of all departments, I need to use recursive call. Using java code to deal with recursion is a little low. It happens that the collection in the resultmap of mybatis can easily solve this problem. The core code is as follows:

<resultMap id="BaseResultMap" type="org.sang.bean.Department">
    <id property="id" column="id"/>
    <result column="name" property="name"/>
    <result column="parentId" property="parentId"/>
    <result column="isParent" property="isParent"/>
    <collection property="children" ofType="org.sang.bean.Department" select="org.sang.mapper.DepartmentMapper.getDepByPid" column="id">
    </collection>
</resultMap>
<select id="getDepByPid" resultMap="BaseResultMap">
    select d1.*from department d1 where d1.`parentId`=#{pid} AND d1.enabled=true;
</select>

Each department has a children attribute. The return result of the getdepbypid method is a baseresultmap, and the collection in the baseresultmap will call the getdepbypid method. In this way, we can quickly realize a recursive call. Mapper only needs to define the following methods:

List<Department> getDepByPid(Long pid);

The query results are as follows (part):

[
    {
        "id": 1,
        "Name": "shareholders' meeting",
        "parentId": -1,
        "enabled": true,
        "children": [
            {
                "id": 4,
                "Name": "chairman",
                "parentId": 1,
                "enabled": true,
                "children": [
                    {
                        "id": 5,
                        "Name": "general manager",
                        "parentId": 4,
                        "enabled": true,
                        "children": [
                            {
                                "id": 8,
                                "Name": "finance department",
                                "parentId": 5,
                                "enabled": true,
                                "children": [],
                                "parent": false
                            }],
                        "parent": true
                    }
                ],
                "parent": true
            }
        ],
        "parent": true
    }
]

Stored procedure call

The stored procedure call is relatively simple. Take adding a department as an example, as follows:

1. Add the following methods to mapper:

void addDep(@Param("dep") Department department);

2. XML is written as follows:

<select id="addDep" statementType="CALLABLE">
    call addDep(#{dep.name,mode=IN,jdbcType=VARCHAR},#{dep.parentId,mode=IN,jdbcType=INTEGER},#{dep.enabled,mode=IN,jdbcType=BOOLEAN},#{dep.result,mode=OUT,jdbcType=INTEGER},#{dep.id,mode=OUT,jdbcType=BIGINT})
</select>

Note that the statementtype call indicates that this is a stored procedure, mode = in indicates that this is an input parameter, and mode = out indicates that this is an output parameter. After the call is successful, get the ID and result fields of the Department in the service to get the corresponding call result.

Some other trivial technologies are not worth introducing. We study them in the source code. If you have any questions, please leave a message for discussion.

Attention to the official account can receive the latest articles in time.

Using mybatis to easily realize recursive query and stored procedure call