高級(jí)課程文檔常智多4 rbac5 根據(jù)角色展示菜單rbac

1、 添加字段-實(shí)現(xiàn)樹形結(jié)構(gòu)1SqlALTER TABLE t_menu ADD pMenuId INT(9) NOT NULL;二 準(zhǔn)備數(shù)據(jù)1為菜單表準(zhǔn)備數(shù)據(jù)1.1 Sql#頂層菜單ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (系統(tǒng)設(shè)置,/sys,0); ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (基本設(shè)置,/base,0);ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (欄目管理,/category,0);#系統(tǒng)設(shè)置子菜單ERT

2、 INTO t_menu (menuname,menuUrl,pMenuId) VALUES (用戶管理,/sys/user,1);#基本設(shè)置子菜案ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (網(wǎng)站設(shè)置,/base/website,2); ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (單頁(yè)管理,/base/single,2); ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (首頁(yè)輪播,/base/foreach,2); ERT I

3、NTO t_menu (menuname,menuUrl,pMenuId) VALUES (留言管理,/base/note,2);#欄目管理子菜案ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (內(nèi)容管理,/category/content,3);ERTINTOt_menu(menuname,menuUrl,pMenuId)VALUES(添加內(nèi)容,/category/contentAdd,3);ERT INTO t_menu (menuname,menuUrl,pMenuId) VALUES (分類管理,/category/item,3);為

4、角色表準(zhǔn)備數(shù)據(jù)22.1Sql為角色菜單表準(zhǔn)備數(shù)據(jù)33.1Sql為用戶角色表添加數(shù)據(jù)44.1前提條件通過系統(tǒng)，已經(jīng)添加兩個(gè)用戶，分別為：bjsxt、adminBjsxt 用戶的編號(hào)為 5，admin 用戶的編號(hào)為 6ERT INTO t_role_menu (roleId,menuId) VALUES (1,1); ERT INTO t_role_menu (roleId,menuId) VALUES (1,2); ERT INTO t_role_menu (roleId,menuId) VALUES (1,3); ERT INTO t_role_menu (roleId,menuId) VAL

5、UES (1,4); ERT INTO t_role_menu (roleId,menuId) VALUES (1,5); ERT INTO t_role_menu (roleId,menuId) VALUES (1,6); ERT INTO t_role_menu (roleId,menuId) VALUES (1,7); ERT INTO t_role_menu (roleId,menuId) VALUES (1,8); ERT INTO t_role_menu (roleId,menuId) VALUES (1,9); ERT INTO t_role_menu (roleId,menuI

6、d) VALUES (1,10); ERT INTO t_role_menu (roleId,menuId) VALUES (1,11);ERT INTO t_role_menu (roleId,menuId) VALUES (2,2); ERT INTO t_role_menu (roleId,menuId) VALUES (2,5); ERT INTO t_role_menu (roleId,menuId) VALUES (2,6); ERT INTO t_role_menu (roleId,menuId) VALUES (2,7); ERT INTO t_role_menu (roleI

7、d,menuId) VALUES (2,8);ERT INTO t_role (rolename) VALUE (管理員); ERT INTO t_role (rolename) VALUE (普通用戶);4.2Sql1:表示超級(jí)管理員的編號(hào)2:表示普通用戶的編號(hào)如何根據(jù)用戶編號(hào)查詢出對(duì)應(yīng)菜單？三多個(gè) sql 語(yǔ)句實(shí)現(xiàn)1一條 sql 語(yǔ)句22.1子查詢SELECT roleId FROM t_user_role WHERE userId = 5);2.2聯(lián)合查詢select m.menuId,m.menuname,m.menuUrl from t_menu m,t_role_menu rm,t

8、_user_role urwhere m.menuId = rm.menuId and rm.roleId = ur.roleId#userId=5#1.根據(jù)用于id，查詢用戶角色關(guān)聯(lián)表，查詢出角色編號(hào)SELECT roleId FROM t_user_role WHERE userId=5;#得到角色編號(hào)為 1 #2.根據(jù)角色id，查詢角色菜單表，查詢出菜單編號(hào)SELECT menuId FROM t_role_menu WHERE roleId = 1;#得到菜單編號(hào)：1-11 #3.根據(jù)菜單id，查詢菜單表，查詢出菜單信息SELECT menuId,menuname,menuUrl FR

9、OM t_menu WHERE menuId IN (1,2,3,4,5,6,7,8,9,10,11);ERT INTO t_user_role (userId,roleId) VALUES (5,1);ERT INTO t_user_role (userId,roleId) VALUES (6,2); and ur.userId = 5;2.3Join 查詢實(shí)現(xiàn)用戶登錄展示不同菜單四Pojo1Menu.javaMapper2MenuMapper.javaMenuMapper.xmlSELECT m.menuId,m.menuname,m.menuUrl FROM t_menu m INNER JOIN t_role_menu rmON m.menuId =