Shulou(Shulou.com)06/03 Report--

This article mainly shows you "MyBatis how to query tree data", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MyBatis how to query tree data" this article.

Method 1: use nested result sets to implement 1, prepare for the work

(1) suppose we have the following menu table menu, in which the child menu is associated with the id of the parent menu through parendId:

(2) the corresponding entity classes are as follows:

@ Setter@Getterpublic class Menu {private Integer id; private String name; private List children;} 2, implementation code

(1) assume that the current menu has only two levels, and the MyBatis statement is as follows. The principle is to query the data at one time through the associated query, and then transform it according to the configuration of resultMap to build the target entity class.

Advantages: only because this method needs to access the database once, it will not cause serious database access consumption.

Select m1.id as id, m1.name as name, m2.id as id2, m2.name as name2 from menu M1 menus m2 where m1.`id` = m2.`parentId`

The final results are as follows:

(2) if the menu has three levels, modify the MyBatis statement as follows, and add a nested result level:

Select m1.id as id, m1.name as name, m2.id as id2, m2.name as name2, m3.id as id3, m3.name as name3 from menu M1 people menus m2je menu m3 where m1.`id` = m2.`parentId`and m2.`id` = m3.`parentId`

(3) if the menu level is uncertain, there may be only one level, or there may be two levels, or there may be three levels (up to three levels). You can change the SQL statement slightly to the left connection:

Select m1.id as id, m1.name as name, m2.id as id2, m2.name as name2, m3.id as id3, m3.name as name3 from menu m1 left join menu m2 on m1.id=m2.parentId left join menu m3 on m2.id=m3.parentId where m1.parentId=0

Method 2: use recursive query to implement

(1) the following code uses recursion to query all menus (no matter how deep the level is):

The advantage of recursive query is that it is easy to understand and the goal can be achieved through simple configuration. The deficiency is that due to the need to query the database many times, if the number of result set records is too large, it will cause a large consumption of database access.

Select * from menu where parentId = 0 select * from menu where parentId = # {id}

(2) the associated query can also pass multiple parameters. In this case, the value of part of the column passed is multiple key-value pairs (since the name passed here is not really used, it is just a demonstration. The query result below is the same as the previous one:

Select * from menu where parentId = 0 select * from menu where parentId = # {id} these are all the contents of the article "how to query Tree data in MyBatis". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.