(一)案例背景
有三张表,菜单表“sys_menu”、动作表“sys_action”和分配动作表”sys_group_action”。其中动作表“sys_action”中有一个“menuId”字段,外键引用菜单表“sys_menu”,表示动作属于哪一个菜单。分配动作表”sys_group_action”主要有两个字段,“group_id”:用户组,“action_id”:动作,这张表表示该动作分配给了哪个用户组。
1.动作表“sys_action”
CREATE TABLE `sys_action` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`menu_id` int(11) DEFAULT NULL COMMENT '菜单id',
`name` varchar(50) DEFAULT NULL COMMENT '操作名',
`url` varchar(200) DEFAULT NULL COMMENT '访问地址',
`method` varchar(50) DEFAULT NULL COMMENT '操作',
PRIMARY KEY (`id`),
key fk_menu_id (menu_id),
constraint fk_menu_id foreign key(menu_id) references sys_menu(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.分配动作表”sys_group_action”
CREATE TABLE `sys_group_action` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`group_id` int(11) DEFAULT NULL COMMENT '用户组id',
`action_id` int(11) DEFAULT NULL COMMENT '操作id',
PRIMARY KEY (`id`),
KEY `fk_group_id` (`group_id`),
KEY `fk_action_id` (`action_id`),
CONSTRAINT `fk_action_id` FOREIGN KEY (`action_id`) REFERENCES `sys_action` (`id`),
CONSTRAINT `fk_group_id` FOREIGN KEY (`group_id`) REFERENCES `sys_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
要求:根据指定的menuId删除该菜单下辖的所有Action,并删除这些Action在分配动作表”sys_group_action”中的相关记录。
(二)解决办法
在这里面需要做两件事,先删除这些Action在分配动作表”sys_group_action”中的相关记录,再删除这些Action。
重难点:在于“删除这些Action在分配动作表”sys_group_action”中的相关记录”。
初步想法:我初步想法是,先根据“menuId”找到所有的Action,然后根据这些Action的ID组成数组:Integer[] ids,然后一个批量删除即可。
缺点:需要查询数据库两次,第一次找到所有的Action,第二次删除这些Action在分配动作表”sys_group_action”中的相关记录”。中间还加上组装这些Action的ID数组:Integer[] ids。
改进的想法:能不能一次连接数据库,搞定所有操作?
优化初步想法:使用多表联查,然后批量删除。具体来说就是联合动作表“sys_action”和分配动作表”sys_group_action”,指定“menuId”,即可得到“action_id”集合,然后设置删除条件为这个集合。
具体Sql语句:
<delete id="deleteGroupActionByMenuId" parameterType="int">
DELETE FROM sys_group_action
WHERE action_id IN (
SELECT * FROM (SELECT action_id
FROM sys_group_action ga,sys_action a
WHERE ga.action_id=a.id AND a.menu_id=#{menuId})a
);
</delete>
备注:这个sql语句有两个注意事项:
一、IN():后面不需要跟表,就一个查询出来的结果集就行
二:删除的表和查询的表不能是同一张表,所以需要用一个临时表做伪装