基于jsp+servlet+mysql+bootstrap的权限设计_lightyear.notify-优快云博客

本文链接：https://blog.youkuaiyun.com/biancheng4/article/details/126809251

1.数据库设计采用RABC(基于角色的访问控制)

	# Host: localhost  (Version: 5.7.26)
# Date: 2022-09-11 20:45:00
# Generator: MySQL-Front 5.3  (Build 4.234)

/*!40101 SET NAMES utf8 */;

#
# Structure for table "sys_permission"
#

DROP TABLE IF EXISTS `sys_permission`;
CREATE TABLE `sys_permission` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `keyword` varchar(64) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='权限';

#
# Data for table "sys_permission"
#

/*!40000 ALTER TABLE `sys_permission` DISABLE KEYS */;
INSERT INTO `sys_permission` VALUES (1,'菜单添加','MENU_ADD',NULL);
/*!40000 ALTER TABLE `sys_permission` ENABLE KEYS */;

#
# Structure for table "sys_role"
#

DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL COMMENT '角色名称',
  `keyword` varchar(64) DEFAULT NULL COMMENT '角色字段',
  `description` varchar(128) DEFAULT NULL COMMENT '角色描述',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='角色表';

#
# Data for table "sys_role"
#

/*!40000 ALTER TABLE `sys_role` DISABLE KEYS */;
INSERT INTO `sys_role` VALUES (1,'管理员','ROLE_ADMIN',NULL),(2,'教师','ROLE_TEACHER',NULL);
/*!40000 ALTER TABLE `sys_role` ENABLE KEYS */;

#
# Structure for table "sys_role_permission"
#

DROP TABLE IF EXISTS `sys_role_permission`;
CREATE TABLE `sys_role_permission` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL DEFAULT '0' COMMENT '角色id',
  `permission_id` int(11) NOT NULL DEFAULT '0' COMMENT '权限id',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='角色与权限的连接';

#
# Data for table "sys_role_permission"
#

/*!40000 ALTER TABLE `sys_role_permission` DISABLE KEYS */;
INSERT INTO `sys_role_permission` VALUES (1,1,1);
/*!40000 ALTER TABLE `sys_role_permission` ENABLE KEYS */;

#
# Structure for table "sys_user"
#

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `gender` tinyint(1) DEFAULT NULL COMMENT '性别 0女 1男',
  `createBy` varchar(255) DEFAULT NULL COMMENT '创建者',
  `state` tinyint(1) DEFAULT NULL COMMENT '状态 0正常 1封禁',
  `creationDate` date DEFAULT NULL COMMENT '创建时间',
  `modifyBy` varchar(255) DEFAULT NULL COMMENT '修改者',
  `modifyDate` date DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

#
# Data for table "sys_user"
#

/*!40000 ALTER TABLE `sys_user` DISABLE KEYS */;
INSERT INTO `sys_user` VALUES (1,'admin','123456',1,'admin',0,NULL,NULL,NULL),(3,'test1','12345',1,'admin',1,'2022-09-08','admin','2022-09-08'),(4,'admin','123456',1,'admin',0,NULL,'',NULL),(5,'test1','12345',1,'admin',1,'2022-09-08','admin','2022-09-08'),(6,'admin','123456',1,'admin',0,NULL,'',NULL),(7,'test1','12345',1,'admin',1,'2022-09-08','admin','2022-09-08'),(8,'admin','123456',1,'admin',0,NULL,'',NULL),(9,'test1','12345',1,'admin',1,'2022-09-08','admin','2022-09-08'),(10,'admin','123456',1,'admin',0,NULL,'',NULL),(11,'test1','12345',1,'admin',1,'2022-09-08','admin','2022-09-08'),(12,'admin','123456',1,'admin',0,NULL,'',NULL),(13,'test1','12345',1,'admin',1,'2022-09-08','admin','2022-09-08');
/*!40000 ALTER TABLE `sys_user` ENABLE KEYS */;

#
# Structure for table "sys_user_role"
#

DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户主键',
  `role_id` int(11) NOT NULL DEFAULT '0' COMMENT '角色主键',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='用户与角色链接';

#
# Data for table "sys_user_role"
#

/*!40000 ALTER TABLE `sys_user_role` DISABLE KEYS */;
INSERT INTO `sys_user_role` VALUES (2,3,1),(3,5,1),(4,5,2),(7,6,1),(8,6,2),(10,8,1);
/*!40000 ALTER TABLE `sys_user_role` ENABLE KEYS */;

2.查询并保存权限的servlet

@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {


    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        String autoLogin = req.getParameter("autoLogin");
        ServletContext context = this.getServletContext();
        String contextPath = context.getContextPath();

        if (username == null){
            req.setAttribute("error", "用户名不能为空");
            req.getRequestDispatcher(contextPath + "/login.jsp").forward(req, resp);
        }

        SysUser user = getUser(username);

        if (user == null){
            req.setAttribute("error", "无此用户");
            req.getRequestDispatcher(contextPath + "/login.jsp").forward(req, resp);
        }

        boolean b = passwordsThan(password, user);

        if (!b){
            req.setAttribute("error", "用户名或密码错误");
            req.getRequestDispatcher(contextPath + "/login.jsp").forward(req, resp);
        }

        Set<String> roleManger = new HashSet<>();
        Set<String> permissionManger = new HashSet<>();

        Set<SysRole> role = new HashSet<>();
        if (user != null) {
            role = getRole(user.getId());
        }
        for (SysRole sysRole : role) {
            roleManger.add(sysRole.getKeyword());
            Set<SysPermission> permission = getPermission(sysRole.getId());
            for (SysPermission sysPermission : permission) {
                permissionManger.add(sysPermission.getKeyword());
            }
        }



        //将用户信息存储在会话中
        req.getSession().setAttribute(ConstantUtil.SESSION_USER, user);
        req.getSession().setAttribute(ConstantUtil.SESSION_ROLE, roleManger);
        req.getSession().setAttribute(ConstantUtil.SESSION_PERMISSION, permissionManger);
        SysUser user1 = (SysUser) req.getSession().getAttribute("user");
        if (ConstantUtil.ON.equals(autoLogin)){
            rememberMe(user1);
        }
        //跳转首页

        resp.sendRedirect(contextPath + "/jsp/index.jsp");
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }

    /**
     * 通过用户名获取用户信息
     * @param username 用户名
     * @return SysUser
     */
    private SysUser getUser(String username){
        UserServiceImpl userService = new UserServiceImpl();
        return userService.loginByUsername(username);
    }

    /**
     * 通过用户id获取角色
     * @param userId
     * @return
     */
    private Set<SysRole> getRole(Integer userId){
        RoleServiceImpl roleService = new RoleServiceImpl();
        return roleService.findRoleByUserId(userId);
    }

    /**
     * 通过角色id获取权限
     * @param roleId
     * @return
     */
    private Set<SysPermission> getPermission(Integer roleId){
        PermissionServiceImpl permission = new PermissionServiceImpl();
        return permission.findPermissionByRoleId(roleId);
    }

    /**
     * 密码比对
     * @param password  密码
     * @param user      用户
     * @return  真值/假值
     */
    private boolean passwordsThan(String password, SysUser user){

        if (user == null){
            return false;
        }

        if (password.equals(user.getPassword())){
            return true;
        }
        return false;
    }

    /**
     * 记住我功能
     * @param user 用户信息
     */
    private void rememberMe(SysUser user){
        Cookie cookie = new Cookie("rememberMe", user.getUsername());
        cookie.setMaxAge(ConstantUtil.TIME);
    }

}

3.userService用户接口类

public interface UserService {

    /**
     * 登录
     * @param username 用户名
     * @return 用户信息 SysUser
     */
    SysUser loginByUsername(String username);

    /**
     * 查询所有用户
     * @return
     */
    List<SysUser> getList();

    /**
     * 存储用户
     * @param user
     * @return
     */
    boolean saveUser(SysUser user);

    /**
     * 更新用户
     * @param user
     * @return
     */
    boolean updateUserByUserId(SysUser user);

    /**
     * 删除用户
     * @param userId
     * @return
     */
    boolean deleteUser(Integer userId);

    /**
     * 关联角色与用户
     * @param userId
     * @param roleId
     * @return
     */
    boolean relevanceUserRole(Integer userId, Integer roleId);

    /**
     * 删除角色与用户关联
     * @param userId
     * @param roleId
     * @return
     */
    boolean removeUserRole(Integer userId, Integer roleId);

    /**
     * 分页查询所有数据
     * @param current
     * @param pagerNumber
     * @return
     */
    List<SysUser> getListByPage(Integer current, Integer pagerNumber);
}

4.UserServiceImpl用户接口实现类

public class UserServiceImpl implements UserService{

    private UserDao userDao;

    public UserServiceImpl() {
        userDao = new UserDaoImpl();
    }

    @Override
    public SysUser loginByUsername(String username) {
        Connection connection = null;
        SysUser user = null;

        try {
            connection = BaseDao.getConnection();
            user = userDao.getLoginUserByUsername(connection, username);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }

        return user;
    }

    @Override
    public List<SysUser> getList() {
        Connection connection = null;
        List<SysUser> user = null;

        try {
            connection = BaseDao.getConnection();
            user = userDao.getList(connection);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }

        return user;
    }

    @Override
    public boolean saveUser(SysUser user) {
        Connection connection = null;
        boolean flag = false;
        connection = BaseDao.getConnection();
        int i = userDao.saveUser(connection, user);
        if (i > 0){
            flag = true;
        }

        return flag;
    }

    @Override
    public boolean updateUserByUserId(SysUser user) {
        Connection connection = null;
        boolean flag = false;
        connection = BaseDao.getConnection();
        int i = userDao.updateUserByUserId(connection, user);
        if (i > 0){
            flag = true;
        }

        return flag;
    }

    @Override
    public boolean deleteUser(Integer userId) {
        Connection connection = null;
        boolean flag = false;
        connection = BaseDao.getConnection();
        int i = userDao.deleteUser(connection, userId);
        if (i > 0){
            flag = true;
        }

        return flag;
    }

    @Override
    public boolean relevanceUserRole(Integer userId, Integer roleId) {
        Connection connection = null;
        boolean flag = false;
        connection = BaseDao.getConnection();
        int i = userDao.relevanceUserRole(connection, userId, roleId);
        if (i > 0){
            flag = true;
        }
        return flag;
    }

    @Override
    public boolean removeUserRole(Integer userId, Integer roleId) {
        Connection connection = null;
        boolean flag = false;
        connection = BaseDao.getConnection();
        int i = userDao.removeUserRole(connection, userId, roleId);
        if (i > 0){
            flag = true;
        }
        return flag;
    }

    @Override
    public List<SysUser> getListByPage(Integer current, Integer pagerNumber) {
        Connection connection = null;
        List<SysUser> user = null;
        try {
            connection = BaseDao.getConnection();
            user = userDao.getListByPage(connection, current, pagerNumber);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }
        return user;
    }
}

5.UserDao用户数据库接口类

public interface UserDao {

    /**
     * 通过用户名获取用户
     * @param connection  数据库连接对象
     * @param username    用户名
     * @return SysUser
     */
    SysUser getLoginUserByUsername(Connection connection, String username);

    /**
     * 查询所有用户
     * @param connection
     * @return
     */
    List<SysUser>  getList(Connection connection);

    /**
     * 存储用户
     * @param user
     * @param connection
     * @return
     */
    int saveUser(Connection connection, SysUser user);

    /**
     * 更新用户
     * @param connection
     * @param user
     * @return
     */
    int updateUserByUserId(Connection connection, SysUser user);

    /**
     * 删除用户
     * @param userId
     * @param connection
     * @return
     */
    int deleteUser(Connection connection, Integer userId);

    /**
     * 关联角色与用户
     * @param connection
     * @param userId
     * @param roleId
     * @return
     */
    int relevanceUserRole(Connection connection, Integer userId, Integer roleId);

    /**
     * 删除角色与用户关联
     * @param connection
     * @param userId
     * @param roleId
     * @return
     */
    int removeUserRole(Connection connection, Integer userId, Integer roleId);

    /**
     * 分页查询用户
     * @param connection
     * @param current
     * @param pageNumber
     * @return
     */
    List<SysUser> getListByPage(Connection connection, Integer current, Integer pageNumber);
}

6.UserDaoImpl数据库设计

public class UserDaoImpl implements UserDao{

    @Override
    public SysUser getLoginUserByUsername(Connection connection, String username) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        SysUser user = null;
        String sql = "select * from sys_user where username = ?";
        Object[] params = {username};

        try {
            rs = BaseDao.execute(connection, sql, params, preparedStatement, rs);
            if (rs.next()){
                user = new SysUser();
                user.setId(rs.getInt("Id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setGender(rs.getInt("gender"));
                user.setCreateBy(rs.getString("createBy"));
                user.setState(rs.getInt("state"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getString("modifyBy"));
                user.setModifyDate(rs.getDate("modifyDate"));
            }
            BaseDao.closeResource(null, preparedStatement, rs);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return user;
    }

    @Override
    public List<SysUser> getList(Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        SysUser user;
        String sql = "select * from sys_user";

        Object[] params = new Object[0];
        List<SysUser> users = new ArrayList<>();
        try {
            rs = BaseDao.execute(connection, sql, params, preparedStatement, rs);
            while (rs.next()){
                user = new SysUser();
                user.setId(rs.getInt("Id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setGender(rs.getInt("gender"));
                user.setCreateBy(rs.getString("createBy"));
                user.setState(rs.getInt("state"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getString("modifyBy"));
                user.setModifyDate(rs.getDate("modifyDate"));
                users.add(user);
            }
            BaseDao.closeResource(null, preparedStatement, rs);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return users;
    }

    @Override
    public int saveUser(Connection connection, SysUser user) {
        PreparedStatement preparedStatement = null;
        String sql = "INSERT INTO sys_user(username, `password`, gender, createBy, state, creationDate, modifyBy, modifyDate) VALUES(?,?,?,?,?,?,?,?)";
        Object[] params = {user.getUsername(), user.getPassword(), user.getGender(), user.getCreateBy(), user.getState(), user.getCreationDate(), user.getModifyBy(), user.getModifyDate()};
        int execute = 0;
        try {
            execute = BaseDao.execute(connection, sql, params, preparedStatement);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        BaseDao.closeResource(null, preparedStatement, null);
        return execute;
    }

    @Override
    public int updateUserByUserId(Connection connection, SysUser user) {
        PreparedStatement preparedStatement = null;
        String sql = "UPDATE sys_user set username = ?, `password` = ?, gender = ?, createBy = ?, state = ?, creationDate =?, modifyBy = ?, modifyDate = ? where Id = ?";
        Object[] params = {user.getUsername(), user.getPassword(), user.getGender(), user.getCreateBy(), user.getState(), user.getCreationDate(), user.getModifyBy(), user.getModifyDate(), user.getId()};
        int execute = 0;
        try {
            execute = BaseDao.execute(connection, sql, params, preparedStatement);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        BaseDao.closeResource(null, preparedStatement, null);
        return execute;
    }

    @Override
    public int deleteUser(Connection connection, Integer userId) {
        PreparedStatement preparedStatement = null;
        String sql = "delete from sys_user where Id = ?";
        Object[] params = {userId};
        int execute = 0;
        try {
            execute = BaseDao.execute(connection, sql, params, preparedStatement);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        BaseDao.closeResource(null, preparedStatement, null);
        return execute;
    }

    @Override
    public int relevanceUserRole(Connection connection, Integer userId, Integer roleId) {
        PreparedStatement preparedStatement = null;
        String sql = "INSERT INTO sys_user_role(user_id, role_id) VALUES(?,?)";
        Object[] params = {userId, roleId};
        int execute = 0;
        try {
            execute = BaseDao.execute(connection, sql, params, preparedStatement);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        BaseDao.closeResource(null, preparedStatement, null);
        return execute;
    }

    @Override
    public int removeUserRole(Connection connection, Integer userId, Integer roleId) {
        PreparedStatement preparedStatement = null;
        String sql = "delete from sys_user_role where user_id = ? and role_id = ?";
        Object[] params = {userId, roleId};
        int execute = 0;
        try {
            execute = BaseDao.execute(connection, sql, params, preparedStatement);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        BaseDao.closeResource(null, preparedStatement, null);
        return execute;
    }

    @Override
    public List<SysUser> getListByPage(Connection connection, Integer current, Integer pageNumber) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        SysUser user;
        String sql = "select * from sys_user limit ?, ?";
        Object[] params = {current,pageNumber};
        List<SysUser> users = new ArrayList<>();
        try {
            rs = BaseDao.execute(connection, sql, params, preparedStatement, rs);
            while (rs.next()){
                user = new SysUser();
                user.setId(rs.getInt("Id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setGender(rs.getInt("gender"));
                user.setCreateBy(rs.getString("createBy"));
                user.setState(rs.getInt("state"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getString("modifyBy"));
                user.setModifyDate(rs.getDate("modifyDate"));
                users.add(user);
            }
            BaseDao.closeResource(null, preparedStatement, rs);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return users;
    }
}

7.角色

7.1.角色接口类

public interface RoleService {

    /**
     * 通过用户id查角色
     * @param userId
     * @return
     */
    Set<SysRole> findRoleByUserId(Integer userId);

    /**
     * 获取所有角色
     * @return
     */
    List<SysRole> getList();

    /**
     * 通过角色名获取id
     * @param roleName
     * @return
     */
    SysRole getIdByName(String roleName);
}

7.2 角色接口实现类

public class RoleServiceImpl implements RoleService{

    private RoleDao roleDao;

    public RoleServiceImpl() {
        roleDao = new RoleDaoImpl();
    }

    @Override
    public Set<SysRole> findRoleByUserId(Integer userId) {
        Connection connection = null;
        Set<SysRole> roles = null;
        try {
            connection = BaseDao.getConnection();
            roles = roleDao.findRoleByUserId(connection, userId);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }

        return roles;
    }

    @Override
    public List<SysRole> getList() {
        Connection connection = null;
        List<SysRole> roles = null;

        try {
            connection = BaseDao.getConnection();
            roles = roleDao.getList(connection);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }

        return roles;
    }

    @Override
    public SysRole getIdByName(String roleName) {
        Connection connection = null;
        SysRole role = null;

        try {
            connection = BaseDao.getConnection();
            role = roleDao.getRoleByRoleName(connection, roleName);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }

        return role;
    }
}

7.3角色数据库接口类

public interface RoleDao {

    /**
     * 通过userid获得角色
     * @param userId
     * @param connection
     * @return
     */
    Set<SysRole> findRoleByUserId(Connection connection, Integer userId);

    /**
     * 获取所有角色
     * @param connection
     * @return
     */
    List<SysRole> getList(Connection connection);

    /**
     * 通过角色名查询角色id
     * @param connection
     * @param roleName
     * @return
     */
    SysRole getRoleByRoleName(Connection connection, String roleName);
}

7.4角色数据库接口实现类

public class RoleDaoImpl implements RoleDao{


    @Override
    public Set<SysRole> findRoleByUserId(Connection connection, Integer userId) {
        PreparedStatement statement = null;
        ResultSet rs = null;
        Object[] params = {userId};
        String sql = "select r.* from sys_role r,sys_user_role ur where r.id = ur.role_id and ur.user_id = ?";
        Set<SysRole> roles = new HashSet<>();

        try {
            rs = BaseDao.execute(connection, sql, params, statement, rs);
            while (rs.next()){
                SysRole sysRole = new SysRole();
                sysRole.setId(rs.getInt("Id"));
                sysRole.setName(rs.getString("name"));
                sysRole.setKeyword(rs.getString("keyword"));
                sysRole.setDescription(rs.getString("description"));
                roles.add(sysRole);

            }
            BaseDao.closeResource(null, statement, rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return roles;
    }

    @Override
    public List<SysRole> getList(Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        SysRole role;
        String sql = "select * from sys_role";

        Object[] params = new Object[0];
        List<SysRole> roles = new ArrayList<>();
        try {
            rs = BaseDao.execute(connection, sql, params, preparedStatement, rs);
            while (rs.next()){
                role = new SysRole();
                role.setId(rs.getInt("Id"));
                role.setName(rs.getString("name"));
                role.setKeyword(rs.getString("keyword"));
                role.setDescription(rs.getString("description"));
                roles.add(role);
            }
            BaseDao.closeResource(null, preparedStatement, rs);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return roles;
    }

    @Override
    public SysRole getRoleByRoleName(Connection connection, String roleName) {
        PreparedStatement statement = null;
        ResultSet rs = null;
        Object[] params = {roleName};
        String sql = "select * from sys_role where name = ?";
        SysRole sysRole = null;
        try {
            rs = BaseDao.execute(connection, sql, params, statement, rs);
            while (rs.next()){
                sysRole = new SysRole();
                sysRole.setId(rs.getInt("Id"));
                sysRole.setName(rs.getString("name"));
                sysRole.setKeyword(rs.getString("keyword"));
                sysRole.setDescription(rs.getString("description"));
            }
            BaseDao.closeResource(null, statement, rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return sysRole;
    }
}

8.权限

8.1权限接口类

public interface PermissionService {

    /**
     * 通过角色id查权限
     * @param roleId
     * @return
     */
    Set<SysPermission> findPermissionByRoleId(Integer roleId);
}

8.2权限接口实现类

public class PermissionServiceImpl implements PermissionService{

    private PermissionDao permissionDao;

    public PermissionServiceImpl() {
        permissionDao = new PermissionDaoImpl();
    }

    @Override
    public Set<SysPermission> findPermissionByRoleId(Integer roleId) {
        Connection connection = null;
        Set<SysPermission> permissionSet = null;
        try {
            connection = BaseDao.getConnection();
            permissionSet = permissionDao.findPermissionByRole(connection, roleId);
        } finally {
            BaseDao.closeResource(connection, null, null);
        }

        return permissionSet;
    }

}

8.3权限数据库接口类

public interface PermissionDao {

    /**
     * 通过角色id查看角色
     * @param roleId
     * @param connection
     * @return
     */
    Set<SysPermission> findPermissionByRole(Connection connection, Integer roleId);
}

8.4权限数据库接口实现类

public class PermissionDaoImpl implements PermissionDao{


    @Override
    public Set<SysPermission> findPermissionByRole(Connection connection, Integer roleId) {
        PreparedStatement statement = null;
        ResultSet rs = null;
        Object[] params = {roleId};
        String sql = "SELECT p.* FROM sys_permission p, sys_role_permission rp WHERE rp.permission_id = p.Id AND rp.role_id = ?";
        Set<SysPermission> permissionSet = new HashSet<>();

        try {
            rs = BaseDao.execute(connection, sql, params, statement, rs);
            while (rs.next()){
                SysPermission sysPermission = new SysPermission();
                sysPermission.setId(rs.getInt("Id"));
                sysPermission.setName(rs.getString("name"));
                sysPermission.setKeyword(rs.getString("keyword"));
                sysPermission.setDescription(rs.getString("description"));
                permissionSet.add(sysPermission);
            }
            BaseDao.closeResource(null, statement, rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return permissionSet;
    }
}

9.拦截器

9.1编码拦截器

public class CharacterEncodingFilter implements Filter {

    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        servletRequest.setCharacterEncoding("utf-8");
        servletResponse.setCharacterEncoding("utf-8");
        filterChain.doFilter(servletRequest, servletResponse);
    }

    @Override
    public void destroy() {

    }
}

9.2url拦截（判断是否登录）

@WebFilter(filterName = "intercept", urlPatterns = "/jsp/*")
public class JavaWebStartFilter implements Filter {


    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        HttpServletRequest req = (HttpServletRequest) request;
        //记住我
//        Cookie[] cookies = req.getCookies();
//        boolean flag = true;
//        if (SIGN.equals(req.getContextPath())){
//            for (Cookie cookie : cookies) {
//                if (AUTO_LOGIN.equals(cookie.getName())){
//                    flag = false;
//                    break;
//                }
//            }
//        }


        SysUser attribute = (SysUser) req.getSession().getAttribute(ConstantUtil.SESSION_USER);

        if (attribute != null){
            chain.doFilter(request, response);
        } else {
            HttpServletResponse rep = (HttpServletResponse) response;
            rep.sendRedirect(req.getContextPath() + "/");
        }
    }

    @Override
    public void destroy() {

    }
}

10.jsp根据权限判断显隐

    <% if (role.contains("ROLE_ADMIN")) { %>
                        <li class="nav-item nav-item-has-subnav">
                            <a href="javascript:void(0)"><i class="mdi mdi-palette"></i> <span>UI 元素</span></a>
                            <ul class="nav nav-subnav">
                                <li> <a class="multitabs" href="lyear_ui_buttons.html">按钮</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_cards.html">卡片</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_grid.html">格栅</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_icons.html">图标</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_tables.html">表格</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_modals.html">模态框</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_tooltips_popover.html">提示 / 弹出框</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_alerts.html">警告框</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_pagination.html">分页</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_progress.html">进度条</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_tabs.html">标签页</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_typography.html">排版</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_step.html">步骤</a> </li>
                                <li> <a class="multitabs" href="lyear_ui_other.html">其他</a> </li>
                            </ul>
                        </li>
                        <% } %>

                        <% if (role.contains("ROLE_ALL")) { %>
                            <li class="nav-item nav-item-has-subnav">
                                <a href="javascript:void(0)"><i class="mdi mdi-format-align-justify"></i> <span>表单</span></a>
                                <ul class="nav nav-subnav">
                                    <li> <a class="multitabs" href="lyear_forms_elements.html">基本元素</a> </li>
                                    <li> <a class="multitabs" href="lyear_forms_radio.html">单选框</a> </li>
                                    <li> <a class="multitabs" href="lyear_forms_checkbox.html">复选框</a> </li>
                                    <li> <a class="multitabs" href="lyear_forms_switch.html">开关</a> </li>
                                </ul>
                            </li>
                        <% } %>

11.页面显示

在这里插入图片描述

12.用户授权设计

@WebServlet("/user/roleConnect")
public class UserRoleServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String userId = req.getParameter("user_id");
        String role = req.getParameter("role_name");
        String wu = "无";

        String[] split = role.split(",");

        RoleServiceImpl roleService = new RoleServiceImpl();
        Set<SysRole> roleByUserId = roleService.findRoleByUserId(Integer.valueOf(userId));

        //查询原先拥有的权限
        List<Integer> hasRoleId = new ArrayList<>();
        for (SysRole sysRole : roleByUserId) {
            hasRoleId.add(sysRole.getId());
        }




        if (wu.equals(split[0])){
            for (Integer integer : hasRoleId) {
                removeUserRole(Integer.valueOf(userId), integer);
            }
        } else {

            //新选择的权限
            List<Integer> ids = new ArrayList<>();
            for (String s : split) {
                Integer roleIdByName = getRoleIdByName(s);
                ids.add(roleIdByName);
            }

            if (hasRoleId.size() == 0){
                for (Integer id : ids) {
                    connectUserRole(Integer.valueOf(userId), id);
                }
            }

            System.out.println("用户id" + userId);
            System.out.println("角色" + role);


            //相同的权限id
            List<Integer> simile = new ArrayList<>();


            //去除与原先不同的
            for (Integer integer : hasRoleId) {
                if (ids.contains(integer)){
                    simile.add(integer);
                } else {
                    removeUserRole(Integer.valueOf(userId), integer);
                }
            }

            System.out.println("相同" + simile.size());

            if (simile.size() == 0){
                System.out.println("没有相同的");
                for (Integer integer : hasRoleId) {
                    removeUserRole(Integer.valueOf(userId), integer);
                }
            } else {
                //将新权限剩下的插入
                for (Integer id : ids) {
                    if (!simile.contains(id)){
                        connectUserRole(Integer.valueOf(userId), id);
                    }
                }
            }
        }



    }

    /**
     * 连接用户与角色
     * @param userId
     * @param roleId
     * @return
     */
    private boolean connectUserRole(Integer userId, Integer roleId){
        UserServiceImpl userService = new UserServiceImpl();
        return userService.relevanceUserRole(userId, roleId);
    }

    /**
     * 清除用户与角色
     * @param userId
     * @param roleId
     * @return
     */
    private boolean removeUserRole(Integer userId, Integer roleId){
        UserServiceImpl userService = new UserServiceImpl();
        return userService.removeUserRole(userId, roleId);
    }

    /**
     * 通过角色名称获取id
     * @param roleName
     * @return
     */
    private Integer getRoleIdByName(String roleName){
        RoleServiceImpl roleService = new RoleServiceImpl();
        SysRole idByName = roleService.getIdByName(roleName);
        return idByName.getId();
    }
}

13.jsp授权

 let userId;
    let value;

    function submitRole(){
        value = $('#sel').val()
        console.log("选择的" + value)
        if (value == null){
            value = ['无']
        }
        $.ajax({
            url: '/user/roleConnect',
            type: 'POST',
            data:{
                user_id: userId,
                role_name: value.toString()
            },
            success: function (data){
                console.log(data)
                lightyear.notify('授权成功', 'success', 3000);
                location.reload();
            },
            error: function (){
                console.log("错误")
            }
        })
    }

    $('#exampleModal').on('show.bs.modal', function (event) {
        var button = $(event.relatedTarget)
        var recipient = button.data('whatever')
        var modal = $(this)
        userId = button.data("id"); //122
        getRole(userId)
    })

    function getRole(userId){
        $.ajax({
            url: '/role/getRole',
            type: 'POST',
            data:{
                userId: userId,
            },
            success: function (data){
                let newRole = []
                let roles = data.role
                newRole = roles.map((item) => {
                    return item.name
                })
                console.log(newRole)
                showRoleList(newRole)
            },
            error: function (){
                console.log("错误")
            }
        })
    }

    function showRoleList(newRole){
        $('.selectpicker').selectpicker('val', newRole);
    }

html

<div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                    <h4 class="modal-title" id="exampleModalLabel">授权角色</h4>
                </div>
                <div class="modal-body">
                    <form>
                        <label>角色选择</label>
                        <select id="sel" class="selectpicker" multiple title="请选择">
                            <c:forEach items="${roleList}" var="role">
                                <option>${role.name}</option>
                            </c:forEach>
                        </select>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
                    <button onclick="submitRole()" type="button" data-dismiss="modal" class="btn btn-success">确认</button>
                </div>
            </div>
        </div>
    </div>
    
    ```