Recursive query cascade information

Time:2021-10-15

1. Demand background

On many occasions, we need to recursively query the data in the table. If:

1In menu classification, we often need to obtain the corresponding sub menu from the primary menu.

id name pid
1 books 0
2 clothing 0
3 Children’s books 1
4 magazine 1
5 Cartoon 3
6 Bedtime story 3

We hope to get the following results from the book:

{books: [magazines,
          {children's books: [cartoon, bedtime story]}
         ]        
}

2. when querying with dependencies like the above, we abstract the parent-child dependency into the following fields:

col col_parent
value1 value2
value2 value3

Query col by value1 in col_ The parent value is Value2;
Then, Value2 is used as a col query to find value3.
Note: parent dependency or child dependency are just different names.

  • All parent dependencies of value1

value1-->value2-->value3

  • All parent dependencies of Value2

value2-->value3


2. Implementation plan

To solve the above problems, this paper proposes two solutions.

  • Method 1: use the collection tag in mybatis
    advantage: the framework has been encapsulated, and there is no need to go deep into the implementation principle.
    shortcoming: the structure of the returned result is fixed and cannot be handled flexibly. The analysis of the structure is also complex. Poor scalability
  • Method 2: according to only one query statementselect col_parent from table_name where col=#{col}And your own code
    advantage: high flexibility. The return structure is extensible
    difficulty: need to understand the implementation principle.

Demo description
For the following data table structure in MySQL:

id code parent_code

Objective: we ask to find the left and right parent codes (including grandparent codes) through code

2.1 method 1: implementation of collection tag in mybatis

Query code implementation

Core code (other implementation codes are not shown)

  • dao
@Mapper
public interface RelationTreeDao {
    List<RelationTreeDTO> selectAllParentsByCode(String code);
  • dto
public class RelationTreeDTO {
    private String code;
    private String parentCode;
    private List<RelationTreeDTO> parentCodeList; // The list nested itself to load the parent node information
   // getter and setter
}
  • mapper.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.***.dao.RelationTreeDao">
    <!---->
    <resultMap id="relationTreeMap" type="com.***.dto.RelationTreeDTO">
        <result column="task_code" property="taskCode" jdbcType="VARCHAR" javaType="String"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR" javaType="String"/>
        <collection column="parent_code" property="parentCodeList"
                select="selectAllParentsByCode">
         </collection>
    </resultMap>

    <!--  Select all parent nodes in the relationship table -- >
    <select id="selectAllParentsByCode"  parameterType="java.lang.String" resultMap="relationTreeMap">
        SELECT
            `code`,`parent_code`
        FROM
            `relation`
        WHERE
            `code` = #{code}
        AND
            `parent_code` is not NULL
    </select>
</mapper>

explain:

  • RelationTreeDTOAs the mapping object of query results, you need to define a self nested list
  • The select in mapper is also a simple query, but the mapping result resultmap has a collection tag. Willcolumn="parent_code"Then as a parameter#{code}Circular query.

result:

  • relationTreeDao.selectAllParentsByCode("yourCode");The query results will be displayed inRelationTreeDTOObject is returned. If there are multiple parent dependencies, they will be displayed in the nested list.
[
    {
        "code": ***,
        "parentCode": ***,
        "parentCodeList": [
            {
               "code": ***,
                "parentCode": ***,
                "parentCodeList": []
            },
            ...
           ]
    }
]

Result analysis

For the above results, we often need to further obtain useful information. If only one list is required:

[code, parentCode, parentCode, parentCode,...]

becauseRelationTreeDTOIs a tree structure, which involves tree traversal. Here, toDepth first search algorithm for tree, get the above list.

/**
     *Description: depth first search all parent nodes in dto
     * @author wanghongbing [email protected]
     *@ param treedto relationtreedto tree structure object to be resolved
     *@ return list [0] saves code and [1] starts saving parents
     *There must be a parent node with list length > = 2
     */
    @Override
    public List<String> depthFirst(RelationTreeDTO treeDTO) {

        //List [0] saves code, and [1] starts saving parents
        List<String> list = new ArrayList<>();
        list.add(treeDTO.getCode()); //list[0]
        
        ArrayDeque<RelationTreeDTO> stack = new ArrayDeque();
        stack.push(treeDTO);

        while (!stack.isEmpty()){
            RelationTreeDTO node =stack.pop();
            list.add(node.getParentCode());
            //Get nested nodes
            List<RelationTreeDTO> parents = node.getParentCodeList();
            if(parents !=null && parents.size() >0){
                for (int i = parents.size()-1; i >=0 ; i--) {
                    stack.push(parents.get(i));
                }
            }
        }
        return list;
    }

At this point, the cascading query of this method ends.
The above implementation,collectionThe result is a fixed tree structure. When parsing, algorithms (such asDFS)Obtain node information of tree species. Although the cascade structure is obtained at one time during mapper query, the subsequent parsing is still complex. The recommended methods are described below.

2.2 method 2: implement cascading query by customization

  • dao
@Mapper
public interface RelationDao {
    List<TaskRelationDTO> selectParentByCode(String code);
   //Other tables
    List<TaskRelationDTO> selectOtherParentByCode(String code);
}
  • Dto (or entity, if corresponding to the database)
public class TaskRelationDTO {
    private String code;
    private String parentCode;
    // getter and setter
}
  • Mapper.xml (assuming that there are relation tables and other_relation tables with the same fields, the two tables are completely for the purpose of showing the extension)
<?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" >
<!-- recommended not modified but can be added -->
<mapper namespace="com.***.dao.RelationDao">
    <!--tag-->
    <resultMap id="relationMap" type="com.***.dto.RelationDTO">
        <result column="code" property="code" jdbcType="VARCHAR" javaType="String"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR" javaType="String"/>
    </resultMap>

    <!--  a_ Select the parent node of the current code in the relation table -- >
    <select id="selectParentByCode"  parameterType="java.lang.String" resultMap="relationMap">
        SELECT
            `code`,`parent_code`
        FROM
            `relation`
        WHERE
            `code` = #{code}
        AND
            `parent_code` is not NULL
    </select>
    <!--  other_ Select the parent node of the current code in the relation table -- >
    <select id="selectOtherParentByCode"  parameterType="java.lang.String" resultMap="relationMap">
        SELECT
            `code`,`parent_code`
        FROM
            `other_relation`
        WHERE
            `code` = #{code}
        AND
            `parent_code` is not NULL
    </select>
</mapper>

Note: the above query is only the simplest SQL query. We write the recursive query in the business method.

  • service
/**
     *
     *@ param codesubtask of parent task to be found
     *@ return returned list. Size() > = 2 list [0] current code, [1] unordered parentscode after de duplication in the future
     *For example: [tag-dependent-2, path-dependent-0-p, path-dependent-2, tag-dependent-0, path-dependent-0]
     */
    @Override
    public List<String> getAllParentsByCode(String code) {
        List<String> list = new ArrayList<>();
        Set<String> parentSet = new HashSet<>();
        ArrayDeque<String> stack = new ArrayDeque();
        int count = 0;
        final int MAX_LOOP_COUNT = 50;

        //Initialize the stack and put the code into the stack
        stack.push(code);
        //Add code to the list. If the final list. Isempty () indicates that there is no parent node, empty it. Therefore, the shortest length of the list is 2
        list.add(code);

        while (!stack.isEmpty()){
            //If you stack too many times, it indicates that ring dependency may occur. force finish 
            if(count++ > MAX_LOOP_COUNT){
                Logger. Error ("the number of parent tasks of [" + code + "] task with code exceeds" + max_loop_count + ", please check whether there is ring dependency");
                list.addAll(parentSet);
                //When there is only taskcode but no parentcode, the list will be cleared
                if(list.size() == 1){
                    list.clear();
                }
                return list;
            }
            String childCode = stack.pop();
/**
There may be cross dependency between two tables, so there is otherrelation
*/
            List<RelationDTO> relation =relationDao.selectTagParentByCode(childCode);
            List<TaskRelationDTO> otherRelation =relationDao.selectOtherParentByCode(childCode);

            //Find the parent task of Pop () element from the relationship table and add it to the stack
            if(!relation.isEmpty()){
                for (int i = 0; i < relation.size(); i++) {
                    String parent = relation.get(i).getParentCode();
                    //This parent is required, and it should be put into the stack at the same time
                    parentSet.add(parent);
                    stack.push(parent);
                }
            }
            //Find the parent task of Pop () element from the otherrelation table and add it to the stack
            if(!otherRelation.isEmpty()){
                for (int i = 0; i < otherRelation.size(); i++) {
                    String parent = otherRelation.get(i).getParentCode();
                    //This parent is required, and it should be put into the stack at the same time
                    parentSet.add(parent);
                    stack.push(parent);
                }
            }
        }
        list.addAll(parentSet);
        //When there is only taskcode but no parentcode, the list will be cleared
        if(list.size() == 1){
            list.clear();
        }
        return list;
    }

principle

Note: the above principle is applicableStack(recursion can also be called recursion, which is nothing more than entering and leaving the stack) to query circularly. During initialization, the to be queriedcodeOn the stack, when querying for the first time, thecodeOut of the stack as a parameter query. If there are query results (one or more), put the query results into the stack (put them into the stack and take them out for the next cycle count as a parameter input!).
As mentioned above, the query of two tables is flexible.


summary

  1. mybatisMediumcollectionLabel, not recommended. I have changed from this mode to mode 2 in project practice.
  2. Loop through the query resultsparentCodeAscodeQuery again, it seems complex, and it is simple to understand the principle.
  3. Stack usage. Can replace recursion, clear thinking.
  4. The above codes are project codes and sensitive information has been removed. May not run directly. If the problem cannot be solved, please contact the mailbox in the code.

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]