问题记录
数据库多表删除的问题
在项目中,我们需要删除一张表中的某些数据,而这些数据同时在其他表中也有,如何实现多表的删除是一个问题。
目前,我只学会了一种方法,就是在创建表时在表的外键上添加级联删除属性。
举一个项目中遇到的一个涉及三表删除的问题
创建用户表
create table users(
id varchar2(32) default SYS_GUID() PRIMARY KEY,
email varchar2(50) unique not null,
username varchar2(50),
password varchar2(50),
phoneNum varchar2(20),
status int
);
insert into users (email,username,password,phoneNum,status) values
('1012456991@qq.com','吴许东','jy123456','18305168570',1);
insert into users (email,username,password,phoneNum,status) values
('101245w6991@qq.com','吴许东weq','jy123s456','1830568570',0);
创建角色表
create table role(
id varchar2(32) default SYS_GUID() primary key,
roleName varchar2(50),
roleDesc varchar2(50)
);
insert into role (rolename,roledesc) values ('栋哥','牛逼,天下无敌');
insert into role (rolename,roledesc) values ('英杰','是个傻吊');
由于用户和角色是多对多的关系,因此需要一张中间表user_role
create table user_role(
userId varchar2(32),
roleId varchar2(32),
primary key (userId,roleId),
foreign key (userId) REFERENCES USERS (ID),
foreign key (roleId) REFERENCES ROLE (ID) on delete cascade
);
insert into user_role values ('877C81BBCF094AB3BE5B98237A1E4172','1683F055B08A42D0BDD86CB8AE76CC65');
insert into user_role values ('877C81BBCF094AB3BE5B98237A1E4172','5705585B8C814B8DA8DA42A1B1354130');
insert into user_role values ('877C81BBCF094AB3BE5B98237A1E4172','85F432BBD7184B70A166774CD936F516');
insert into user_role values ('9C8FFBFAA26542D0A4B13F663DF8507B','1683F055B08A42D0BDD86CB8AE76CC65');
insert into user_role values ('9C8FFBFAA26542D0A4B13F663DF8507B','5705585B8C814B8DA8DA42A1B1354130');
insert into user_role values ('9C8FFBFAA26542D0A4B13F663DF8507B','85F432BBD7184B70A166774CD936F516');
on delete cascade属性就代表了级联删除
delete from role where rolename='英杰';
因此,我们可以删除用户下的角色了
MyBatis注解中dao层获取方法的输入的问题
问题描述
在一个jsp页面中,有个超链接并携带了两个数据
/user_role/addRoleByUserId.do?userId=${userInfo.id}&roleId=${role.id}
相应的Controller类中代码是
@RequestMapping("/addRoleByUserId.do")
public ModelAndView addRoleByUserId(@RequestParam(name = "userId",required = true) String userId, @RequestParam(name = "roleId",required = true)String roleId) throws Exception{
System.out.println(userId+"....."+roleId);
ModelAndView modelAndView=new ModelAndView();
User_Role user_role=new User_Role();
user_role.setUserId(userId);
user_role.setRoleId(roleId);
user_roleService.addRoleById(user_role);
UserInfo userInfo= userService.findById(userId);
modelAndView.addObject("user",userInfo);
modelAndView.setViewName("user-role-list");
return modelAndView;
}
一开始,我没有将这两个数据封装为实体类对象,在调用dao层的代码是一直获取不到传入的参数。
@Insert("insert into user_role (userId,roleId) values (#{userId},#{roleId})")
void addRoleById(User_Role user_role) throws Exception;
后来我讲这两个数据封装为对象后就能获取到了,现在我还不能理解是为什么,以后有空在研究吧。