前言
前面我们做的小项目都是一个表的,业务代码也相对简单。现在我们来做一个权限管理系统,体验一下多表的业务逻辑,顺便巩固一下过滤器的知识!
目的
现在我有一个管理商品、订单的页面。当用户点击某个超链接时,过滤器会检测该用户是否有权限!
需求分析
按照面向对象的思想,我们至少应该有权限(Privilege)和用户(User)实体。两个实体足够了吗?细想一下,如果我们有多个用户,多个用户也有多个权限,当要为用户授权的时候,这样子就会非常麻烦!所以我们应该引入角色(Role)这个实体!
引入角色(Role)这个实体方便在哪呢??把权限赋给角色(比如:把删除、修改的权限给管理员这个角色),管理员这个角色再赋给用户,那么该用户就有了修改、删除的权限了!
权限和角色是多对多的关系,角色和用户也是多对多的关系!
开发实体
用户实体
public class User {
private String id;
private String username;
private String password;
//记住角色
private Set<Role> roles = new HashSet<>();
//各种getter和setter.....
}
角色实体
public class Role {
private String id;
private String name;
private String description;
//记住所有的用户
private Set<User> users = new HashSet<>();
//记住所有的权限
private Set<Privilege> privileges = new HashSet<>();
//各种getter和setter.....
}
权限实体
public class Privilege {
private String id;
private String name;
private String description;
//记住所有的角色
private Set<Role> roles = new HashSet<>();
//各种getter和setter.....
}
改良
用户和角色、角色和权限都是多对多的关系,这是毋庸置疑的!我们也按照面向对象的方法来设计,用集合来记住另一方的数据!
但是呢,我们想想:
- 在权限的Dao中,在查看权限的时候,有必要列出相对应的角色吗??
- 在角色的Dao中,在查看角色的时候,有必要列出相对应的用户吗??
答案是没有的,一般我们都不会显示出来。所以,权限的实体没必要使用Set集合来记住角色,角色实体没必要使用Set集合来记住用户!
改良后的权限实体
public class Privilege {
private String id;
private String name;
private String description;
//各种setter和getter方法
}
改良后的角色实体
public class Role {
private String id;
private String name;
private String description;
//记住所有的权限
private Set<Privilege> privileges = new HashSet<>();
//各种setter和getter方法
}
在数据库中建表
user表
CREATE TABLE user (
id VARCHAR(20) PRIMARY KEY,
username VARCHAR(20) NOT NULL,
password VARCHAR(20) NOT NULL
);
role表
CREATE TABLE role (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
description VARCHAR(255)
);
privilege表
CREATE TABLE privilege (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
description VARCHAR(255)
);
user和role的关系表
CREATE TABLE user_role (
user_id VARCHAR(20),
role_id VARCHAR(20),
PRIMARY KEY (user_id, role_id),
CONSTRAINT user_id_FK FOREIGN KEY (user_id) REFERENCES user (id),
CONSTRAINT role_id_FK FOREIGN KEY (role_id) REFERENCES role (id)
);
role和privilege的关系表
CREATE TABLE role_privilege (
role_id VARCHAR(20),
privilege_id VARCHAR(20),
PRIMARY KEY (role_id, privilege_id),
CONSTRAINT role_id_FK1 FOREIGN KEY (role_id) REFERENCES role (id),
CONSTRAINT privilege_id_FK FOREIGN KEY (privilege_id) REFERENCES privilege (id)
);
注意:user和role的关系表、role和privilege的关系都有role_id作为外键,外键的名称是不能一样的!
开发DAO
PrivilegeDao
/**
* 权限的管理应该有以下的功能:
* 1.添加权限
* 2.查看所有权限
* 3.查找某个权限
*
* */
public class PrivilegeDao {
/*添加权限*/
public void addPrivilege(Privilege privilege) {
try {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "INSERT INTO privilege (id, name, description) VALUE (?, ?, ?)";
queryRunner.update(sql, new Object[]{privilege.getId(), privilege.getName(), privilege.getDescription()});
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("添加权限失败了!");
}
}
/*查找权限*/
public Privilege findPrivilege(String id) {
try {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT *FROM privilege WHERE id = ?";
Privilege privilege = (Privilege) queryRunner.query(sql, new BeanHandler(Privilege.class), new Object[]{id});
return privilege;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("查找权限失败了!");
}
}
/*获取所有的权限*/
public List<Privilege> getAllPrivileges() {
try {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT * FROM privilege ";
List<Privilege> privileges = (List<Privilege>) queryRunner.query(sql, new BeanListHandler(Privilege.class));
return privileges;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("查找权限失败了!");
}
}
}
测试PrivilegeDao的功能
为了测试方便,添加有参构造函数到Privilege对象中
public class PrivilegeDaoTest {
PrivilegeDao privilegeDao = new PrivilegeDao();
@Test
public void add() {
Privilege privilege = new Privilege("2", "修改", "修改功能");
privilegeDao.addPrivilege(privilege);
}
@Test
public void getAll() {
List<Privilege> list = privilegeDao.getAllPrivileges();
for (Privilege privilege : list) {
System.out.println(privilege.getId());
}
}
@Test
public void find() {
String id = "2";
Privilege privilege = privilegeDao.findPrivilege(id);
System.out.println(privilege.getName());
}
}
UserDao
public class UserDao {
public void addUser(User user) {
try {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "INSERT INTO user (id,username,password) VALUES(?,?,?)";
queryRunner.update(sql, new Object[]{user.getId(), user.getUsername(), user.getPassword()});
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("添加权限失败了!");
}
}
public User find(String id) {
try {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT * FROM user WHERE id=?";
User user = (User) queryRunner.query(sql, new BeanHandler(User.class), new Object[]{id});
return user;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("添加权限失败了!");
}
}
public List<User> getAll() {
try {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "SELECT * FORM user";
List<User> users = (List<User>) queryRunner.query(sql, new BeanListHandler(User.class));
return users;
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("添加权限失败了!");
}
}
}
測試UserDao
public class UserDaoTest {
UserDao userDao = new UserDao();
@Test
public void add() {
User user = new User();
user.setId("2");
user.setUsername("qqq");
user.setPassword("123");
userDao.addUser(user);
}
@Test
public void find() {
String id = "1";
User user = userDao.find(id);
System.out.println(user.getUsername());
}
@Test
public void findALL() {
List<User> userList = userDao.getAll();
for (User user : userList) {
System.out.println(user.getUsername());
}
}
}
RoleDao
public void add(Role role){
try{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into role(id,name,description) values(?,?,?)";
Object params[] = {role.getId(),role.getName(),role.getDescription()};
runner.update(sql, params);
}catch (Exception e) {
throw new RuntimeException(e);
}
}
public Role find(String id){
try{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from role where id=?";
return (Role) runner.query(sql, id, new BeanHandler(Role.class));
}catch (Exception e) {
throw new RuntimeException(e);
}
}
//得到所有角色
public List<Role> getAll(){
try{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from role";
return (List<Role>) runner.query(sql, new BeanListHandler(Role.class));
}catch (Exception e) {
throw new<