Recursive resolution of the problem of traversing the permissions tree menu according to the parent node--------tree table in MySQL data

I almost did not write a blog, feel the knowledge to learn and to do too many things, no time. But found that the blog record their learning experience is very good, take time to write points for their future reference also share to some people, I have limited level, please more advice!

Recently tried to develop a small OA system on its own to consolidate its own learning of the Java SSH framework. Here is a record of the development of some of the experience.

I put the system menu into a tree, with a table storage, each menu item is a parent-child relationship, when we want to go to the database according to user permissions to fetch menu items, we found that the SQL statement is not good solution. Before reviewing some posts on the Internet, I found that there are special SQL statements for this requirement in Oracle: like this

Select *  from  with =  the  by = a.super_entity_id;

But I used the MySQL did not, some of the article said with the MySQL stored procedures to solve, I just debut rookie feeling too complicated, did not try. The last thought can be solved directly with recursive algorithm:

Menu table:

Entity Class Code:

1  Packagecom.xuwei.oa.entity;2 3 Importjava.io.Serializable;4 ImportJava.util.Set;5 6 ImportJavax.persistence.Column;7 Importjavax.persistence.Entity;8 ImportJavax.persistence.GeneratedValue;9 ImportJavax.persistence.GenerationType;Ten Importjavax.persistence.Id; One ImportJavax.persistence.ManyToMany; A Importjavax.persistence.Table; -  - ImportOrg.apache.struts2.json.annotations.JSON; the ImportOrg.hibernate.annotations.GenericGenerator; -  - /** - * Menu Management entity class +  * @authorDavid -  * +  */ A @Entity at@Table (name= "T_menu") -  Public classMenuImplementsserializable{ -@Id//Set Primary key generation policy to manually assign -@GenericGenerator (name= "Menugenerator", strategy= "Assigned") -@GeneratedValue (generator= "Menugenerator") -     PrivateLong mid;//PRIMARY Key in@Column (name = "Name", length = 15) -     PrivateString name;//The name of the node on the tree to@Column (name = "Isparent") +     PrivateBoolean isparent;//is a folder node -@Column (name = "Icon", length = 100) the     PrivateString icon;//the path of the icon picture *@Column (name = "Checked", length = 5) $     PrivateBoolean checked;Panax Notoginseng@Column (name = "url", length = 60) -     PrivateString URL;//Click menu to jump to action the@Column (name = "target", length = 15) +     PrivateString Target;//Page Display Location A@Column (name = "pid", length = 5) the     PrivateLong pid;//parent Node ID +      -      PublicLong Getmid () { $         returnmid; $     } -      Public voidSetmid (Long mid) { -          This. mid =mid; the     } -      PublicLong getpid () {Wuyi         returnpid; the     } -      Public voidsetpid (Long pid) { Wu          This. PID =pid; -     } About      PublicString GetName () { $         returnname; -     } -      Public voidsetName (String name) { -          This. Name =name; A     } +      PublicBoolean getisparent () { the         returnisparent; -     } $      Public voidsetisparent (Boolean isparent) { the          This. isparent =isparent; the     } the      PublicString GetIcon () { the         returnicon; -     } in      Public voidSetIcon (String icon) { the          This. Icon =icon; the     } About      PublicBoolean getchecked () { the         returnchecked; the     } the      Public voidsetchecked (Boolean checked) { +          This. checked =checked; -     } the      PublicString GetUrl () {Bayi         returnURL; the     } the      Public voidseturl (String url) { -          This. url =URL; -     } the      PublicString Gettarget () { the         returnTarget; the     } the      Public voidsettarget (String target) { -          This. target =Target; the     } the      the@ManyToMany (mappedby = "Menus")  94     PrivateSet<user> users;//Role Set Collection the@JSON (serialize=false) the      PublicSet<user>getusers () { the         returnusers;98     } About      Public voidSetusers (set<user>users) { -          This. Users =users;101     }102     103 104}

Unit Test Code:

1     PrivateSession session;2     PrivateList<menu> listmenu=NewArraylist<menu>();3 4 5 @Test6      Public voidTestqueryalltreenodesbyrootpid () {7List<menu> res=NewArraylist<menu>();8 session.begintransaction ();9 //visttreenodesbypid (6L);TenVisttreenodesbypid (6L, res); One          for(Menu m:res) { ASYSTEM.OUT.PRINTLN ("* * * *" +m.getname ()); -         } - session.gettransaction (). commit (); the     } -      -      Public voidVisttreenodesbypid (Long pid,list<menu>Res) { - //String hql= "from the menu where pid=? order by mid"; +String hql= "from menu m inner join fetch m.users u where m.pid=? and u.uid=? ORDER BY M.mid "; -Query query=session.createquery (HQL); +Query.setlong (0, PID); AQuery.setlong (1, 16L); atList<menu> list=query.list (); -          for(Menu m:list) { -System.out.println (M.getmid () + "--" +m.getname ()); - //This.listMenu.add (m); - Res.add (m); -         } in          for(Menu m:list) { -Long tmp_pid=M.getmid (); to             if(tmp_pid!=NULL){ + visttreenodesbypid (tmp_pid,res); -             } the         } *}

This makes it easy to solve the problem.

Recursive resolution of the problem of traversing the permissions tree menu according to the parent node--------tree table in MySQL data