自定義RBAC（4）
2022-12-12 09:00:58
您好，我是湘王，這是我的部落格園，歡迎您來，歡迎您再來～
前面把RBAC的許可權系統設計過程都講清楚了，現在就來實現它。大致分這麼幾個步驟：
1、先定義出完整的許可權系統表結構；
2、實現Entity、Dao、Service等類程式碼；
3、實現自定義攔截器；
4、實現Controller，完成許可權驗證。
為了節約時間，就不再詳細說明表結構的設計了（根據表結構可以自行逆向分析），而且為了實現最精簡的功能（在不影響效果的情況下，儘可能地少寫程式碼），資料表中的中間實體類也不再定義。
-- ----------------------------
-- Table structure for sys_branch
-- ----------------------------
DROP TABLE IF EXISTS sys_branch;
CREATE TABLE sys_branch (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '機構編碼',
parentid int(11) NOT NULL DEFAULT '0' COMMENT '父級編碼',
parentids varchar(256) NOT NULL DEFAULT '' COMMENT '所有父級編碼',
name varchar(64) NOT NULL DEFAULT '' COMMENT '機構名稱',
type tinyint(1) NOT NULL DEFAULT '0' COMMENT '機構型別，0：集團；1：公司；2：分支機構；3：部門；4：小組',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=MyISAM CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '分支機構表';
-- ----------------------------
-- Records of sys_branch
-- ----------------------------
INSERT INTO sys_branch VALUES (1, 0, '0,', '集團', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (2, 1, '0,1,', '子公司1', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (3, 1, '0,1,', '子公司2', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (4, 2, '0,1,2,', '北京辦公室', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (5, 3, '0,1,3,', '杭州辦公室', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (6, 4, '0,1,2,4,', '技術部', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (7, 4, '0,1,2,4,', '市場部', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (8, 5, '0,1,3,5,', '運營部', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (9, 5, '0,1,3,5,', '財務部', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (10, 5, '0,1,3,5,', '倉儲部', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_branch VALUES (11, 6, '0,1,2,4,6,', '運維組', 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_config
-- ----------------------------
DROP TABLE IF EXISTS sys_authorize_config;
CREATE TABLE sys_authorize_config (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '設定編碼',
clazz varchar(64) NOT NULL DEFAULT '' COMMENT '設定類別',
rule varchar(64) NOT NULL DEFAULT '' COMMENT '設定規則',
remark varchar(64) NOT NULL DEFAULT '' COMMENT '設定說明',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '規則設定表';
-- ----------------------------
-- Records of sys_config
-- ----------------------------
INSERT INTO sys_authorize_config VALUES (1, 'role', '7|8#9|10', '角色互斥', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_authorize_config VALUES (2, 'user has role number', '5', '使用者可擁有的角色數量限制', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_authorize_config VALUES (3, 'role was assigned number', '5', '角色可被分配的使用者數量限制', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_group
-- ----------------------------
DROP TABLE IF EXISTS sys_group;
CREATE TABLE sys_group (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '組編碼',
parentid int(11) NOT NULL DEFAULT '0' COMMENT '父級編碼',
parentids varchar(256) NOT NULL DEFAULT '' COMMENT '所有父級編碼',
name varchar(64) NOT NULL DEFAULT '' COMMENT '組名稱',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '組表';
-- ----------------------------
-- Records of sys_group
-- ----------------------------
INSERT INTO sys_group VALUES (10001, 0, '0,', '運營組', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group VALUES (10002, 0, '0,', '後勤組', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_group_permission
-- ----------------------------
DROP TABLE IF EXISTS sys_group_permission;
CREATE TABLE sys_group_permission (
gid int(11) NOT NULL COMMENT '組編碼',
pid int(11) NOT NULL COMMENT '許可權編碼',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (gid, pid) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '組許可權表';
-- ----------------------------
-- Table structure for sys_group_role
-- ----------------------------
DROP TABLE IF EXISTS sys_group_role;
CREATE TABLE sys_group_role (
gid int(11) NOT NULL COMMENT '組編碼',
rid int(11) NOT NULL COMMENT '角色編碼',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (gid, rid) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '組角色表';
-- ----------------------------
-- Records of sys_group_role
-- ----------------------------
INSERT INTO sys_group_role VALUES (10001, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group_role VALUES (10001, 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group_role VALUES (10001, 6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group_role VALUES (10002, 7, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group_role VALUES (10002, 8, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group_role VALUES (10002, 9, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_group_role VALUES (10002, 10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_permission_auto
-- ----------------------------
DROP TABLE IF EXISTS sys_permission_auto;
CREATE TABLE sys_permission_auto (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '許可權編碼',
parentid int(11) NOT NULL DEFAULT '0' COMMENT '父級編碼',
parentids varchar(256) NOT NULL DEFAULT '' COMMENT '所有父級編碼',
name varchar(32) NOT NULL DEFAULT '' COMMENT '許可權名稱',
level int(11) NOT NULL DEFAULT '0' COMMENT '許可權層級',
path varchar(256) DEFAULT '' COMMENT '連結路徑',
createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='許可權表';
-- ----------------------------
-- Records of sys_permission_auto
-- ----------------------------
INSERT INTO sys_permission_auto VALUES (1, 0, '0,', '系統管理', 1, '/api/v1.0.0/system', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (2, 0, '0,', '使用者管理', 1, '/api/v1.0.0/user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (3, 0, '0,', '商品管理', 1, '/api/v1.0.0/good', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (4, 0, '0,', '訂單管理', 1, '/api/v1.0.0/order', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (5, 0, '0,', '財務管理', 1, '/api/v1.0.0/finance', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (6, 0, '0,', '庫存管理', 1, '/api/v1.0.0/stock', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 系統管理
INSERT INTO sys_permission_auto VALUES (7, 1, '0,1,', '應用設定', 2, '/api/v1.0.0/system/setting', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (8, 7, '0,1,7,', '修改密碼', 3, '/api/v1.0.0/system/setting/password', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (9, 7, '0,1,7,', '更改頭像', 3, '/api/v1.0.0/system/setting/avatar', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (10, 7, '0,1,7,', '安全設定', 3, '/api/v1.0.0/system/setting/safe', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (11, 7, '0,1,7,', '通用設定', 3, '/api/v1.0.0/system/setting/normal', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (12, 1, '0,1,', '角色管理', 2, '/api/v1.0.0/system/role', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (13, 12, '0,1,12,', '建立角色', 3, '/api/v1.0.0/system/role/create', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (14, 12, '0,1,12,', '更新角色', 3, '/api/v1.0.0/system/role/update', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (15, 12, '0,1,12,', '刪除角色', 3, '/api/v1.0.0/system/role/remove', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (16, 12, '0,1,12,', '分配角色', 3, '/api/v1.0.0/system/role/assign', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (17, 1, '0,1,', '使用者設定', 2, '/api/v1.0.0/system/user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (18, 1, '0,1,', '商品設定', 2, '/api/v1.0.0/system/good', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (19, 1, '0,1,', '訂單設定', 2, '/api/v1.0.0/system/order', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (20, 1, '0,1', '財務設定', 2, '/api/v1.0.0/system/finance', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (21, 1, '0,1,', '庫存設定', 2, '/api/v1.0.0/system/stock', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 使用者管理
INSERT INTO sys_permission_auto VALUES (22, 2, '0,2,', '使用者列表', 2, '/api/v1.0.0/user/list', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (23, 2, '0,2,', '使用者檢索', 2, '/api/v1.0.0/user/search', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (24, 2, '0,2,', '使用者詳情', 2, '/api/v1.0.0/user/details', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (25, 2, '0,2,', '使用者統計', 2, '/api/v1.0.0/user/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (26, 2, '0,2,', '凍結解凍', 2, '/api/v1.0.0/user/endis', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (27, 2, '0,2,', '貼打標籤', 2, '/api/v1.0.0/user/tags', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 商品管理
INSERT INTO sys_permission_auto VALUES (28, 3, '0,3,', '商品列表', 2, '/api/v1.0.0/good/list', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (29, 3, '0,3,', '商品檢索', 2, '/api/v1.0.0/good/search', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (30, 3, '0,3,', '商品詳情', 2, '/api/v1.0.0/good/details', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (31, 3, '0,3,', '商品編排', 2, '/api/v1.0.0/good/layout', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (32, 3, '0,3,', '商品統計', 2, '/api/v1.0.0/good/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (33, 3, '0,3,', '上架下架', 2, '/api/v1.0.0/good/endis', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 訂單管理
INSERT INTO sys_permission_auto VALUES (34, 4, '0,4,', '訂單列表', 2, '/api/v1.0.0/order/list', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (35, 4, '0,4,', '訂單檢索', 2, '/api/v1.0.0/order/search', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (36, 4, '0,4,', '訂單詳情', 2, '/api/v1.0.0/order/details', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (37, 4, '0,4,', '訂單統計', 2, '/api/v1.0.0/order/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (38, 4, '0,4,', '訂單合併', 2, '/api/v1.0.0/order/merge', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (39, 4, '0,4,', '凍結解凍', 2, '/api/v1.0.0/order/endis', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (40, 4, '0,4,', '取消訂單', 2, '/api/v1.0.0/order/cancel', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 財務管理
INSERT INTO sys_permission_auto VALUES (41, 5, '0,5,', '登賬', 2, '/api/v1.0.0/finance/account', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (42, 5, '0,5,', '出報表', 2, '/api/v1.0.0/finance/report', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (43, 5, '0,5,', '稽核報銷單', 2, '/api/v1.0.0/finance/reimburse', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (44, 5, '0,5,', '報稅', 2, '/api/v1.0.0/finance/collect', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (45, 5, '0,5,', '往來結算', 2, '/api/v1.0.0/finance/pay', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (46, 5, '0,5,', '現金收付', 2, '/api/v1.0.0/finance/cash', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (47, 5, '0,5,', '現金盤存', 2, '/api/v1.0.0/finance/tax', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 庫存管理
INSERT INTO sys_permission_auto VALUES (48, 6, '0,6,', '庫存入庫', 2, '/api/v1.0.0/stock/input', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (49, 6, '0,6,', '庫存出庫', 2, '/api/v1.0.0/stock/output', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (50, 6, '0,6,', '庫內盤點', 2, '/api/v1.0.0/stock/check', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (51, 6, '0,6,', '庫存統計', 2, '/api/v1.0.0/stock/cost', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (52, 6, '0,6,', '計費管理', 2, '/api/v1.0.0/stock/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission_auto VALUES (53, 6, '0,6,', '區域配送', 2, '/api/v1.0.0/stock/dispatch', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_permission
-- ----------------------------
DROP TABLE IF EXISTS sys_permission;
CREATE TABLE sys_permission (
id int(11) NOT NULL COMMENT '許可權編碼',
parentid int(11) NOT NULL DEFAULT '0' COMMENT '父級編碼',
parentids varchar(256) NOT NULL DEFAULT '' COMMENT '所有父級編碼',
name varchar(32) NOT NULL DEFAULT '' COMMENT '許可權名稱',
level int(11) NOT NULL DEFAULT '0' COMMENT '許可權層級',
path varchar(256) DEFAULT '' COMMENT '連結路徑',
createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='許可權表';
-- ----------------------------
-- Records of sys_permission
-- ----------------------------
INSERT INTO sys_permission VALUES (1, 0, '0,', '系統管理', 1, '/api/v1.0.0/system', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (2, 0, '0,', '使用者管理', 1, '/api/v1.0.0/user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (3, 0, '0,', '商品管理', 1, '/api/v1.0.0/good', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4, 0, '0,', '訂單管理', 1, '/api/v1.0.0/order', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5, 0, '0,', '財務管理', 1, '/api/v1.0.0/finance', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (6, 0, '0,', '庫存管理', 1, '/api/v1.0.0/stock', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 系統管理
INSERT INTO sys_permission VALUES (1010000, 1, '0,1,', '應用設定', 2, '/api/v1.0.0/system/setting', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1010100, 1010000, '0,1,1010000,', '修改密碼', 3, '/api/v1.0.0/system/setting/password', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1010200, 1010000, '0,1,1010000,', '更改頭像', 3, '/api/v1.0.0/system/setting/avatar', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1010300, 1010000, '0,1,1010000,', '安全設定', 3, '/api/v1.0.0/system/setting/safe', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1010400, 1010000, '0,1,1010000,', '通用設定', 3, '/api/v1.0.0/system/setting/normal', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1020000, 1, '0,1,', '角色管理', 2, '/api/v1.0.0/system/role', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1020100, 1020000, '0,1,1020000,', '建立角色', 3, '/api/v1.0.0/system/role/create', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1020200, 1020000, '0,1,1020000,', '更新角色', 3, '/api/v1.0.0/system/role/update', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1020300, 1020000, '0,1,1020000,', '刪除角色', 3, '/api/v1.0.0/system/role/remove', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1020400, 1020000, '0,1,1020000,', '分配角色', 3, '/api/v1.0.0/system/role/assign', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1030000, 1, '0,1,', '使用者設定', 2, '/api/v1.0.0/system/user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1040000, 1, '0,1,', '商品設定', 2, '/api/v1.0.0/system/good', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1050000, 1, '0,1,', '訂單設定', 2, '/api/v1.0.0/system/order', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1060000, 1, '0,1', '財務設定', 2, '/api/v1.0.0/system/finance', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (1070000, 1, '0,1,', '庫存設定', 2, '/api/v1.0.0/system/stock', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 使用者管理
INSERT INTO sys_permission VALUES (2010000, 2, '0,2,', '使用者列表', 2, '/api/v1.0.0/user/list', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (2020000, 2, '0,2,', '使用者檢索', 2, '/api/v1.0.0/user/search', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (2030000, 2, '0,2,', '使用者詳情', 2, '/api/v1.0.0/user/details', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (2040000, 2, '0,2,', '使用者統計', 2, '/api/v1.0.0/user/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (2050000, 2, '0,2,', '凍結解凍', 2, '/api/v1.0.0/user/endis', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (2060000, 2, '0,2,', '貼打標籤', 2, '/api/v1.0.0/user/tags', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 商品管理
INSERT INTO sys_permission VALUES (3010000, 3, '0,3,', '商品列表', 2, '/api/v1.0.0/good/list', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (3020000, 3, '0,3,', '商品檢索', 2, '/api/v1.0.0/good/search', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (3030000, 3, '0,3,', '商品詳情', 2, '/api/v1.0.0/good/details', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (3040000, 3, '0,3,', '商品編排', 2, '/api/v1.0.0/good/layout', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (3050000, 3, '0,3,', '商品統計', 2, '/api/v1.0.0/good/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (3060000, 3, '0,3,', '上架下架', 2, '/api/v1.0.0/good/endis', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 訂單管理
INSERT INTO sys_permission VALUES (4010000, 4, '0,4,', '訂單列表', 2, '/api/v1.0.0/order/list', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4020000, 4, '0,4,', '訂單檢索', 2, '/api/v1.0.0/order/search', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4030000, 4, '0,4,', '訂單詳情', 2, '/api/v1.0.0/order/details', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4040000, 4, '0,4,', '訂單統計', 2, '/api/v1.0.0/order/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4050000, 4, '0,4,', '訂單合併', 2, '/api/v1.0.0/order/merge', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4060000, 4, '0,4,', '凍結解凍', 2, '/api/v1.0.0/order/endis', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (4070000, 4, '0,4,', '取消訂單', 2, '/api/v1.0.0/order/cancel', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 財務管理
INSERT INTO sys_permission VALUES (5010000, 5, '0,5,', '登賬', 2, '/api/v1.0.0/finance/account', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5020000, 5, '0,5,', '出報表', 2, '/api/v1.0.0/finance/report', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5030000, 5, '0,5,', '稽核報銷單', 2, '/api/v1.0.0/finance/reimburse', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5040000, 5, '0,5,', '報稅', 2, '/api/v1.0.0/finance/collect', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5050000, 5, '0,5,', '往來結算', 2, '/api/v1.0.0/finance/pay', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5060000, 5, '0,5,', '現金收付', 2, '/api/v1.0.0/finance/cash', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (5070000, 5, '0,5,', '現金盤存', 2, '/api/v1.0.0/finance/tax', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 庫存管理
INSERT INTO sys_permission VALUES (6010000, 6, '0,6,', '庫存入庫', 2, '/api/v1.0.0/stock/input', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (6020000, 6, '0,6,', '庫存出庫', 2, '/api/v1.0.0/stock/output', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (6030000, 6, '0,6,', '庫內盤點', 2, '/api/v1.0.0/stock/check', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (6040000, 6, '0,6,', '庫存統計', 2, '/api/v1.0.0/stock/cost', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (6050000, 6, '0,6,', '計費管理', 2, '/api/v1.0.0/stock/count', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_permission VALUES (6060000, 6, '0,6,', '區域配送', 2, '/api/v1.0.0/stock/dispatch', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS sys_role;
CREATE TABLE sys_role (
id int(11) NOT NULL COMMENT '角色編碼',
parentid int(11) NOT NULL DEFAULT '0' COMMENT '父級編碼',
parentids varchar(256) NOT NULL DEFAULT '' COMMENT '所有父級編碼',
name varchar(64) NOT NULL DEFAULT '' COMMENT '角色名稱',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '角色表';
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO sys_role VALUES (1, 0, '0,', '公共角色', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (2, 0, '0,', '超級管理員', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (3, 1, '0,', '系統管理員', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (4, 1, '0,1,', '客服', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (5, 1, '0,1,', '產品', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (6, 1, '0,1,', '運營', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (7, 1, '0,1,', '會計', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (8, 1, '0,1,', '出納', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (9, 1, '0,1,', '庫管', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (10, 1, '0,1,', '配送', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (11, 1, '0,1,', '使用者管理員', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (12, 1, '0,1,', '商品管理員', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (13, 1, '0,1,', '訂單管理員', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (14, 1, '0,1,7,8,', '財務經理', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role VALUES (15, 1, '0,1,9,10,', '倉儲負責人', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_role_permission
-- ----------------------------
DROP TABLE IF EXISTS sys_role_permission;
CREATE TABLE sys_role_permission (
rid int(11) NOT NULL COMMENT '角色編碼',
pid int(11) NOT NULL COMMENT '許可權編碼',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (rid, pid) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '角色許可權表';
-- ----------------------------
-- Records of sys_role_permission
-- ----------------------------
-- 核公基角色
INSERT INTO sys_role_permission VALUES (1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (1, 1010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (1, 1010100, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (1, 1010200, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (1, 1010300, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (1, 1010400, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 超級管理員
INSERT INTO sys_role_permission VALUES (2, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1000000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1010100, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1010200, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1010300, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1010400, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1020100, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1020200, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1020300, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1020400, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 1070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 2060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 3060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 4070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 5070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (2, 6060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 系統管理員
INSERT INTO sys_role_permission VALUES (3, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1020100, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1020200, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1020300, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1020400, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (3, 1070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 客服
INSERT INTO sys_role_permission VALUES (4, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 2020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 2030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 3020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 3030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 4020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 4030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (4, 4050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 產品
INSERT INTO sys_role_permission VALUES (5, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 2010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 2040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 3010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 3050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 4010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (5, 4040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 運營
INSERT INTO sys_role_permission VALUES (6, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 2020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 2040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 2050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 2060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 3020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 3040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 3050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 3060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 4020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 4040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 4060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 4070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (6, 6040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 會計
INSERT INTO sys_role_permission VALUES (7, 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (7, 5010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (7, 5020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (7, 5030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (7, 5040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 出納
INSERT INTO sys_role_permission VALUES (8, 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (8, 5050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (8, 5060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (8, 5070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 庫管
INSERT INTO sys_role_permission VALUES (9, 6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (9, 6010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (9, 6020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (9, 6030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (9, 6040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 配送
INSERT INTO sys_role_permission VALUES (10, 6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (10, 6050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (10, 6060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 使用者管理員
INSERT INTO sys_role_permission VALUES (11, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (11, 2010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (11, 2020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (11, 2030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (11, 2040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (11, 2050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (11, 2060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 商品管理員
INSERT INTO sys_role_permission VALUES (12, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (12, 3010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (12, 3020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (12, 3030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (12, 3040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (12, 3050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (12, 3060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- 訂單管理員
INSERT INTO sys_role_permission VALUES (13, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4010000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4020000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4030000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4040000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4050000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4060000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_role_permission VALUES (13, 4070000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '使用者編碼',
bid int(11) NOT NULL DEFAULT '1' COMMENT '機構編碼',
username varchar(32) NOT NULL COMMENT '使用者名稱',
password varchar(32) NOT NULL COMMENT '密碼',
scope tinyint(1) NOT NULL DEFAULT '0' COMMENT '0：全部，1：部門及以下，2：僅個人' COMMENT '角色範圍',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '使用者表';
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO sys_user VALUES (1, 1, '超級管理員', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (2, 1, '董事長', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (3, 2, '張總', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (4, 3, '蔡總', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (5, 4, '李主任', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (6, 6, '趙部長', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (7, 7, '小林', '123456', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (8, 7, '小美', '123456', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (9, 8, '小黃', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (10, 8, '小郭', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (11, 9, '範出納', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (12, 9, '錢會計', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (13, 10, '老劉', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (14, 10, '老魏', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (15, 11, '小平', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user VALUES (16, 11, '小馮', '123456', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_user_group
-- ----------------------------
DROP TABLE IF EXISTS sys_user_group;
CREATE TABLE sys_user_group (
uid int(11) NOT NULL COMMENT '使用者編碼',
gid int(11) NOT NULL COMMENT '組編碼',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (uid, gid) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '使用者組表';
-- ----------------------------
-- Records of sys_user_group
-- ----------------------------
INSERT INTO sys_user_group VALUES (3, 10001, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_group VALUES (4, 10002, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Table structure for sys_user_permission
-- ----------------------------
DROP TABLE IF EXISTS sys_user_permission;
CREATE TABLE sys_user_permission (
uid int(11) NOT NULL COMMENT '使用者編碼',
pid int(11) NOT NULL COMMENT '許可權編碼',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (uid, pid) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '使用者許可權表';
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role (
uid int(11) NOT NULL COMMENT '使用者編碼',
rid int(11) NOT NULL COMMENT '角色編碼',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (uid, rid) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=Dynamic COMMENT '角色表';
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO sys_user_role VALUES (1, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (2, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (7, 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (8, 5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (9, 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (10, 6, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (11, 7, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (12, 8, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (13, 9, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (14, 10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sys_user_role VALUES (15, 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
-- ----------------------------
-- Function structure for getChildList
-- ----------------------------
DROP FUNCTION IF EXISTS getChildList;
delimiter ;;
CREATE DEFINER=root@localhost FUNCTION getChildList(nodeid INT) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE childList VARCHAR(1000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # 返回葉子節點結果集
DECLARE tempChild VARCHAR(1000, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # 臨時存放子節點
SET childList='';
SET tempChild=CAST(nodeid AS CHAR, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # 將int型別轉換為String
WHILE tempChild IS NOT NULL DO # 迴圈，用於查詢節點下所有的子節點
SET childList=CONCAT(childList, ',', tempChild, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # 存入到返回結果中
SELECT sys_group_CONCAT(id) INTO tempChild FROM sys_branch WHERE FIND_IN_SET(pid, tempChild) > 0; # 查詢節點下所有子節點
END WHILE;
RETURN SUBSTRING(childList, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); # 將返回結果處理，擷取掉結果集前面的逗號
END
;;
delimiter ;
然後修改POM檔案：
修改屬性檔案連線MySQL：
## MYSQL
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://ip:port/dbname?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=123456
這裡的ip、port和dbname都用自己資料庫的實際連線資料替換就行了。
建立SysBranch實體類：
/**
* 分支機構entity
*
* @author 湘王
*/
public class SysBranch implements Serializable, RowMapper<SysBranch> {
private static final long serialVersionUID = -1214743110268373599L;
private int id;
private int parentid;
private String parentids;
private String name;
private int type; // 機構型別，0：集團；1：公司；2：分支機構；3：部門；4：小組
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date createtime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date updatetime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getParentid() {
return parentid;
}
public void setParentid(int parentid) {
this.parentid = parentid;
}
public String getParentids() {
return parentids;
}
public void setParentids(String parentids) {
this.parentids = parentids;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Date getUpdatetime() {
return updatetime;
}
public void setUpdatetime(Date updatetime) {
this.updatetime = updatetime;
}
@Override
public SysBranch mapRow(ResultSet result, int i) throws SQLException {
SysBranch user = new SysBranch();
user.setId(result.getInt("id"));
user.setParentid(result.getInt("parentid"));
user.setParentids(result.getString("parentids"));
user.setName(result.getString("name"));
user.setType(result.getInt("type"));
user.setCreatetime(result.getTimestamp("createtime"));
user.setUpdatetime(result.getTimestamp("updatetime"));
return user;
}
}
建立SysGroup實體類：
/**
* 組entity
*
* @author 湘王
*/
public class SysGroup implements Serializable, RowMapper<SysGroup> {
private static final long serialVersionUID = -1214743110268373599L;
private int id;
private int parentid;
private String parentids;
private String name;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date createtime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date updatetime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getParentid() {
return parentid;
}
public void setParentid(int parentid) {
this.parentid = parentid;
}
public String getParentids() {
return parentids;
}
public void setParentids(String parentids) {
this.parentids = parentids;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Date getUpdatetime() {
return updatetime;
}
public void setUpdatetime(Date updatetime) {
this.updatetime = updatetime;
}
@Override
public SysGroup mapRow(ResultSet result, int i) throws SQLException {
SysGroup user = new SysGroup();
user.setId(result.getInt("id"));
user.setParentid(result.getInt("parentid"));
user.setParentids(result.getString("parentids"));
user.setName(result.getString("name"));
user.setCreatetime(result.getTimestamp("createtime"));
user.setUpdatetime(result.getTimestamp("updatetime"));
return user;
}
}
建立SysUser實體類：
/**
* 使用者entity
*
* @author 湘王
*/
public class SysUser implements Serializable, RowMapper<SysUser> {
private static final long serialVersionUID = -1214743110268373599L;
private int id;
private int bid;
private String username;
private String password;
private int scope; // 0：全部，1：部門及以下，2：僅個人
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date createtime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date updatetime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getBid() {
return bid;
}
public void setBid(int bid) {
this.bid = bid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@JsonIgnore
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getScope() {
return scope;
}
public void setScope(int scope) {
this.scope = scope;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Date getUpdatetime() {
return updatetime;
}
public void setUpdatetime(Date updatetime) {
this.updatetime = updatetime;
}
@Override
public SysUser mapRow(ResultSet result, int i) throws SQLException {
SysUser user = new SysUser();
user.setId(result.getInt("id"));
user.setUsername(result.getString("username"));
user.setPassword(result.getString("password"));
user.setCreatetime(result.getTimestamp("createtime"));
user.setUpdatetime(result.getTimestamp("updatetime"));
return user;
}
}
建立SysRole實體類：
/**
* 角色entity
*
* @author 湘王
*/
public class SysRole implements Serializable, RowMapper<SysRole> {
private static final long serialVersionUID = 6980192718775578676L;
private int id;
private int parentid;
private String parentids;
private String name;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date createtime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date updatetime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getParentid() {
return parentid;
}
public void setParentid(int parentid) {
this.parentid = parentid;
}
public String getParentids() {
return parentids;
}
public void setParentids(String parentids) {
this.parentids = parentids;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Date getUpdatetime() {
return updatetime;
}
public void setUpdatetime(Date updatetime) {
this.updatetime = updatetime;
}
@Override
public SysRole mapRow(ResultSet result, int i) throws SQLException {
SysRole role = new SysRole();
role.setId(result.getInt("id"));
role.setParentid(result.getInt("parentid"));
role.setParentids(result.getString("parentids"));
role.setName(result.getString("name"));
role.setCreatetime(result.getTimestamp("createtime"));
role.setUpdatetime(result.getTimestamp("updatetime"));
return role;
}
}
建立SysPermission實體類：
/**
* 許可權entity
*
* @author 湘王
*/
public class SysPermission implements Serializable, RowMapper<SysPermission> {
private static final long serialVersionUID = 4121559180789799491L;
private int id;
private int parentid;
private String parentids;
private String name;
private int level;
private String path;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date createtime;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
protected Date updatetime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getParentid() {
return parentid;
}
public void setParentid(int parentid) {
this.parentid = parentid;
}
public String getParentids() {
return parentids;
}
public void setParentids(String parentids) {
this.parentids = parentids;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getLevel() {
return level;
}
public void setLevel(int level) {
this.level = level;
}
public String getPath() {
return path;
}
public void setPath(String path) {
this.path = path;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Date getUpdatetime() {
return updatetime;
}
public void setUpdatetime(Date updatetime) {
this.updatetime = updatetime;
}
@Override
public SysPermission mapRow(ResultSet result, int i) throws SQLException {
SysPermission permission = new SysPermission();
permission.setId(result.getInt("id"));
permission.setParentid(result.getInt("parentid"));
permission.setParentids(result.getString("parentids"));
permission.setName(result.getString("name"));
permission.setLevel(result.getInt("level"));
permission.setPath(result.getString("path"));
permission.setCreatetime(result.getTimestamp("createtime"));
permission.setUpdatetime(result.getTimestamp("updatetime"));
return permission;
}
}
可以接著使用之前在Spring Security中使用過的MySQLDao，接著再定義各種Service。
建立UserService：
/**
* 使用者Service
*
* @author 湘王
*/
@Service
public class UserService {
@Autowired
private MySQLDao mySQLDao;
public SysUser queryById(int id) {
String sql = "SELECT id, username, password, createtime, updatetime FROM sys_user WHERE id = ?";
return (SysUser) mySQLDao.findOne(sql, new SysUser(), id);
}
public SysUser queryByUsername(String username) {
String sql = "SELECT id, username, password, createtime, updatetime FROM sys_user WHERE username = ?";
return (SysUser) mySQLDao.findOne(sql, new SysUser(), username);
}
public List<SysUser> queryAll() {
String sql = "SELECT id, username, password, createtime, updatetime FROM sys_user";
return mySQLDao.find(sql, new SysUser());
}
}
建立GroupService：
/**
* 使用者組Service
*
* @author 湘王
*/
@Service
public class GroupService {
@Autowired
private MySQLDao mySQLDao;
public SysGroup queryById(int id) {
String sql = "SELECT id, parentid, parentids, name, createtime, updatetime FROM sys_group WHERE id = ?";
return (SysGroup) mySQLDao.findOne(sql, new SysGroup(), id);
}
public SysGroup queryByName(String name) {
String sql = "SELECT id, parentid, parentids, name, createtime, updatetime FROM sys_group WHERE name = ?";
return (SysGroup) mySQLDao.findOne(sql, new SysGroup(), name);
}
// 使用者所屬的組
public List<SysGroup> queryByUserid(int uid) {
String sql = "SELECT g.id, g.parentid, g.parentids, g.name, g.createtime, g.updatetime " +
"FROM sys_user AS u, sys_user_group AS ug, sys_group AS g " +
"WHERE u.id = ? AND u.id = ug.uid AND ug.gid = g.id";
return (List<SysGroup>) mySQLDao.find(sql, new SysGroup(), name);
}
// 查詢組所屬的子組
public List<SysGroup> queryChildrenById(int id) {
String sql = "SELECT id, parentid, parentids, name, createtime, updatetime FROM sys_group WHERE parentids LIKE '%" + id + ",%'";
return (List<SysGroup>) mySQLDao.find(sql, new SysGroup(), id);
}
// 查詢組的父組
public List<SysGroup> queryParentsById(String parentids) {
String sql = "SELECT id, parentid, parentids, name, createtime, updatetime FROM sys_group WHERE id IN(" + parentids + ")";
return (List<SysGroup>) mySQLDao.find(sql, new SysGroup(), parentids);
}
}
建立RoleService：
/**
* 角色Service
*
* @author 湘王
*/
@Service
public class RoleService {
@Autowired
private MySQLDao roleDao;
// 使用者-組-角色
public List<SysRole> queryUGRByUserId(int uid) {
String sql = "SELECT r.id, r.parentid, r.parentids, r.name, r.createtime, r.updatetime " +
"FROM sys_user AS u, sys_user_group AS ug, sys_group AS g, sys_group_role AS gr, sys_role AS r " +
"WHERE u.id = ? AND u.id = ug.uid AND ug.gid = g.id AND g.id = gr.gid AND gr.rid = r.id";
return (List<SysRole>) roleDao.find(sql, new SysRole(), uid);
}
// 使用者-角色
public List<SysRole> queryURByUserId(int uid) {
String sql = "SELECT r.id, r.parentid, r.parentids, r.name, r.createtime, r.updatetime " +
"FROM sys_user AS u, sys_user_role AS ur, sys_role AS r " +
"WHERE u.id = ? AND u.id = ur.uid AND ur.rid = r.id";
return (List<SysRole>) roleDao.find(sql, new SysRole(), uid);
}
// 組-角色
public List<SysRole> queryGRByGroupId(int gid) {
String sql = "SELECT r.id, r.parentid, r.parentids, r.name, r.createtime, r.updatetime " +
"FROM sys_group AS g, sys_group_role AS gr, sys_role AS r " +
"WHERE g.id = ? AND g.id = gr.gid AND gr.rid = r.id";
return (List<SysRole>) roleDao.find(sql, new SysRole(), gid);
}
// 查詢角色的子角色
public List<SysRole> queryChildrenById(int id) {
String sql = "SELECT id, parentid, parentids, name, createtime, updatetime " +
"FROM sys_role WHERE parentids LIKE '%" + id + ",%'";
return (List<SysRole>) roleDao.find(sql, new SysRole());
}
// 查詢角色的父角色
public List<SysRole> queryParentsById(final String parentids) {
String sql = "SELECT id, parentid, parentids, name, createtime, updatetime " +
"FROM sys_role WHERE id IN(" + parentids.substring(0, parentids.length() - 1) + ")";
return (List<SysRole>) roleDao.find(sql, new SysRole());
}
}
建立PermissionService：
/**
* 許可權Service
*
* @author 湘王
*/
@Service
public class PermissionService {
@Autowired
private MySQLDao mySQLDao;
// 查詢使用者-組-角色-許可權
@SuppressWarnings("unchecked")
public List<SysPermission> queryUGRPByUserId(final int uid) {
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_user AS u, sys_user_group AS ug, sys_group AS g, sys_group_role AS gr, " +
"sys_role AS r, sys_role_permission AS rp, sys_permission AS p " +
"WHERE u.id = ? AND u.id = ug.uid AND ug.gid = g.id AND g.id = gr.gid AND gr.rid = r.id " +
"AND r.id = rp.rid AND rp.pid = p.id AND p.level >= 2";
return (List<SysPermission>) mySQLDao.find(sql, new SysPermission(), uid);
}
// 查詢使用者-角色-許可權
@SuppressWarnings("unchecked")
public List<SysPermission> queryURPByUserId(final int uid) {
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_user AS u, sys_user_role AS ur, sys_role AS r, sys_role_permission AS rp, sys_permission AS p " +
"WHERE u.id = ? AND u.id = ur.uid AND ur.rid = r.id AND r.id = rp.rid AND rp.pid = p.id AND p.level >= 2";
return (List<SysPermission>) mySQLDao.find(sql, new SysPermission(), uid);
}
// 查詢使用者-許可權
public List<SysPermission> queryUPByUserId(final int uid) {
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_user AS u, sys_user_permission AS up, sys_permission AS p " +
"WHERE u.id = ? AND u.id = up.uid AND up.pid = p.id AND p.level >= 2";
return (List<SysPermission>) mySQLDao.find(sql, new SysPermission(), uid);
}
// 查詢組-角色-許可權
@SuppressWarnings("unchecked")
public List<SysPermission> queryGRPByGropuId(final int gid) {
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_group AS g, sys_group_role AS gr, sys_role AS r, sys_role_permission AS rp, sys_permission AS p " +
"WHERE g.id = ? AND g.id = gr.gid AND gr.rid = r.id AND r.id = rp.rid AND rp.pid = p.id AND p.level >= 2";
return (List<SysPermission>) mySQLDao.find(sql, new SysPermission(), gid);
}
// 查詢組-許可權
public List<SysPermission> queryByGroupId(final int gid) {
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_group AS u, sys_group_permission AS gp, sys_permission AS p " +
"WHERE g.id = ? AND g.id = gp.gid AND gp.pid = p.id AND p.level >= 2";
return (List<SysPermission>) mySQLDao.find(sql, new SysPermission(), gid);
}
// 查詢角色-許可權
public List<SysPermission> queryByRoleId(final int rid) {
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_role AS r, sys_role_permission AS rp, sys_permission AS p " +
"WHERE r.id = ? AND r.id = rp.rid AND rp.pid = p.id AND p.level >= 2";
return mySQLDao.find(sql, new SysPermission(), rid);
}
// 查詢多個角色-許可權
public List<SysPermission> queryByMultiRoleIds(final String rids) {
String ids = rids.substring(0, rids.length() - 1);
String sql = "SELECT p.id, p.parentid, p.parentids, p.name, p.level, p.path, p.createtime, p.updatetime " +
"FROM sys_role AS r, sys_role_permission AS rp, sys_permission AS p " +
"WHERE r.id IN(" + ids + ") AND r.id = rp.rid AND rp.pid = p.id AND p.level >= 2";
return mySQLDao.find(sql, new SysPermission());
}
}
感謝您的大駕光臨！諮詢技術、產品、運營和管理相關問題，請關注後留言。歡迎騷擾，不勝榮幸～