Spring boot+MyBatis implements five-table cascade query (implemented through annotations!!!)

Table of Contents

Business scene

Entity design

General idea

Abstraction into code implementation

Summarize

The summary is in one step, if you don’t want to see the implementation! ! !

Business Scenario

After the user clicks to enter a certain package, he needs to find out the inspection group included in the package, what inspection items are included in the inspection group, and the specific interpretation of the inspection items.

Entity Class Design

First, the user selects a package from the package list to enter:

When you click on a package, the front end will send a request to query the detailed information of the corresponding package:

The page contains the project name, project content, and project interpretation:

Query project name (check group name): A package contains multiple check groups, so the List checkGroups attribute is defined in the Setmeal entity class.

public class SetMeal implements Serializable {
    private int id;
    private String name;
    private String code;
    private String helpCode;
    private String sex;
    private String age;
    private double price;
    private String remark;
    private String attention;
    private String img;
    private List<CheckGroup> checkGroups;
}

Project content: A check group contains multiple check items, so the List checkItems attribute is defined in the CheckGroup check group object.

public class CheckGroup implements Serializable {
    private int id;
    private String code;
    private String name;
    private String helpCode;
    private String sex;
    private String remark;
    private String attention;
    private List<CheckItem> checkItems;
}

The project is interpreted as the remark attribute in the CheckGroup class.

General idea

Here, Springboot is used to integrate the MyBatis framework, and the information of other classes contained in the entity class is found step by step through annotations and cascading queries.

Actually, I really like joint query. It’s a bit annoying to have to do it in several steps to do everything at once. However, the annotation format will not achieve one-to-many, one-to-one is fine. I have no choice but to use cascade query honestly. .

Closer to home, the general idea is:

  1. Query the corresponding basic package information based on the incoming package ID;
  2. Then use the package ID to find out the corresponding inspection group IDs in the package-inspection group relationship table, and then use these inspection group IDs to find out the basic information of the corresponding inspection group in the inspection group table. The solution is actually to connect the two tables through the relationship table;
  3. Finally, use the inspection group IDs just now to find out the IDs of multiple inspection items corresponding to each inspection group in the inspection group-inspection item relationship table, and then use these inspection item IDs to find out the IDs of each inspection item in the inspection item table. Basic information about each inspection item.

Abstract into code implementation

  1. Use @Select to write SQL statements;
  2. Use @Results annotation to map entity classes, which contains multiple @Result annotations to map attributes in entity classes and fields in tables;
  3. Design a layered method, and the above annotations are modified on the method.

First design the first level method:

SetMeal findSetmealDetail(int setMealId);

Use @Select to write SQL statements:

@Select("SELECT t_setmeal.* " +
            "FROM t_setmeal\\
" +
            "where t_setmeal.id=#{setMealId}")

The point is here! ! !

Use @Results containing multiple @Result annotations to map attributes in entity classes and fields in tables:

When mapping checkGroups, use the javaType attribute to specify the Java type of the class attribute. Since there is a one-to-many relationship between them, use the many attribute and @Many annotation to call the next query method;

We need to associate the package table and the check group table through the package-check group relationship table, then we need to pass the package ID to the next query method to unite the package table and the relationship table (my understanding)

@Results(value = {
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "code", column = "code"),
            @Result(property = "helpCode", column = "helpCode"),
            @Result(property = "sex", column = "sex"),
            @Result(property = "age", column = "age"),
            @Result(property = "price", column = "price"),
            @Result(property = "remark", column = "remark"),
            @Result(property = "attention", column = "attention"),
            @Result(property = "img", column = "img"),
            @Result(property = "checkGroups",javaType = List.class,many = @Many(select = "findGroupDetail"),column = "id")
    })

The second level method queries the inspection group corresponding to the package:

Method design: One package corresponds to multiple groups, so use List to receive it, which corresponds to the checkGroups attribute in the package.

List<CheckGroup> findGroupDetail(int setmealId);

Write SQL: Find the Id of the corresponding group in the relationship table based on the Id passed by the upper-level method, and then query the basic information of each group through the group Id in the relationship table.

@Select("SELECT t_checkgroup.* " +
            "FROM t_checkgroup\\
" +
            "join t_setmeal_checkgroup on t_checkgroup.id=t_setmeal_checkgroup.checkgroup_id and t_setmeal_checkgroup.setmeal_id=#{setmealId}")

Mapping: The found group ID is also passed to the next query method, which is used to query the ID of the corresponding item in the relationship table.

@Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "code", column = "code"),
            @Result(property = "helpCode", column = "helpCode"),
            @Result(property = "remark", column = "remark"),
            @Result(property = "attention", column = "attention"),
            @Result(property = "sex", column = "sex"),
            @Result(property = "checkItems",javaType = List.class,many = @Many(select = "findItemDetail"),column = "id")
    })

The third-level query method is used to query multiple inspection items corresponding to each group:

Method design: Same one-to-many, using List to receive.

List<CheckItem> findItemDetail(int groupId);

Write a SQL statement: Query the ID of the corresponding item through the relational table based on the passed ID, and then query the basic information of multiple items corresponding to each group.

@Select("SELECT t_checkitem.*\\
" +
            "FROM t_checkitem\\
" +
            "join t_checkgroup_checkitem on t_checkgroup_checkitem.checkgroup_id=#{groupId} and t_checkgroup_checkitem.checkitem_id=t_checkitem.id")

Mapping: Cascade query completed

@Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "code", column = "code"),
            @Result(property = "sex", column = "sex"),
            @Result(property = "age", column = "age"),
            @Result(property = "price", column = "price"),
            @Result(property = "remark", column = "remark"),
            @Result(property = "attention", column = "attention")
    })

Summary

  • The above cascade query process is all from a personal perspective. In order to facilitate your own understanding of the process and future application, please correct me in time if there is anything wrong.
  • In short, the Id of the current table is passed into the next-level method, the next-level method joins the relationship table, and uses on to associate the two tables, and then a normal query will work, and so on. The same steps are followed. . .