Apache Shiro simplecaptcha验证码MySQL实现 JSP RBAC

亦凉 2023-09-27 11:27 22阅读 0赞

ER图

![dc16805f4da24a84ae935e44f04f5f84.png][]

MySQL数据库脚本

-- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        8.0.22 - MySQL Community Server - GPL
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  11.3.0.6295
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!50503 SET NAMES utf8mb4 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    
    -- 导出 shiro 的数据库结构
    CREATE DATABASE IF NOT EXISTS `shiro` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
    USE `shiro`;
    
    -- 导出  表 shiro.permissions 结构
    CREATE TABLE IF NOT EXISTS `permissions` (
      `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
      `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    -- 正在导出表  shiro.permissions 的数据：~20 rows (大约)
    /*!40000 ALTER TABLE `permissions` DISABLE KEYS */;
    INSERT IGNORE INTO `permissions` (`name`, `description`) VALUES
    	('permission-create', 'permission-create'),
    	('permission-delete', 'permission-delete'),
    	('permission-index', 'permission-index'),
    	('permission-update', 'permission-update'),
    	('role-create', 'role-create'),
    	('role-delete', 'role-delete'),
    	('role-index', 'role-index'),
    	('role-permission-create', 'role-permission-create'),
    	('role-permission-delete', 'role-permission-delete'),
    	('role-permission-index', 'role-permission-index'),
    	('role-permission-update', 'role-permission-update'),
    	('role-update', 'role-update'),
    	('user-create', 'user-create'),
    	('user-delete', 'user-delete'),
    	('user-index', 'user-index'),
    	('user-role-create', 'user-role-create'),
    	('user-role-delete', 'user-role-delete'),
    	('user-role-index', 'user-role-index'),
    	('user-role-update', 'user-role-update'),
    	('user-update', 'user-update');
    /*!40000 ALTER TABLE `permissions` ENABLE KEYS */;
    
    -- 导出  表 shiro.roles 结构
    CREATE TABLE IF NOT EXISTS `roles` (
      `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
      `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    -- 正在导出表  shiro.roles 的数据：~3 rows (大约)
    /*!40000 ALTER TABLE `roles` DISABLE KEYS */;
    INSERT IGNORE INTO `roles` (`name`, `description`) VALUES
    	('ADMIN', 'Administrator role'),
    	('USER_P1', 'Perfil 1'),
    	('USER_P2', 'Perfil 2');
    /*!40000 ALTER TABLE `roles` ENABLE KEYS */;
    
    -- 导出  表 shiro.roles_permissions 结构
    CREATE TABLE IF NOT EXISTS `roles_permissions` (
      `role_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
      `permission` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
      PRIMARY KEY (`role_name`,`permission`),
      KEY `RP_1` (`role_name`),
      KEY `RP_2` (`permission`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    -- 正在导出表  shiro.roles_permissions 的数据：~23 rows (大约)
    /*!40000 ALTER TABLE `roles_permissions` DISABLE KEYS */;
    INSERT IGNORE INTO `roles_permissions` (`role_name`, `permission`) VALUES
    	('ADMIN', 'permission-create'),
    	('ADMIN', 'permission-delete'),
    	('ADMIN', 'permission-index'),
    	('ADMIN', 'permission-update'),
    	('ADMIN', 'role-create'),
    	('ADMIN', 'role-delete'),
    	('ADMIN', 'role-index'),
    	('ADMIN', 'role-permission-create'),
    	('ADMIN', 'role-permission-delete'),
    	('ADMIN', 'role-permission-index'),
    	('ADMIN', 'role-permission-update'),
    	('ADMIN', 'role-update'),
    	('ADMIN', 'user-create'),
    	('ADMIN', 'user-delete'),
    	('ADMIN', 'user-index'),
    	('ADMIN', 'user-role-create'),
    	('ADMIN', 'user-role-delete'),
    	('ADMIN', 'user-role-index'),
    	('ADMIN', 'user-role-update'),
    	('ADMIN', 'user-update'),
    	('USER_P1', 'permission-index'),
    	('USER_P1', 'role-index'),
    	('USER_P1', 'role-permission-index'),
    	('USER_P1', 'user-index'),
    	('USER_P1', 'user-role-index'),
    	('USER_P2', 'user-index');
    /*!40000 ALTER TABLE `roles_permissions` ENABLE KEYS */;
    
    -- 导出  表 shiro.users 结构
    CREATE TABLE IF NOT EXISTS `users` (
      `username` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
      `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      PRIMARY KEY (`username`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    -- 正在导出表  shiro.users 的数据：~2 rows (大约)
    /*!40000 ALTER TABLE `users` DISABLE KEYS */;
    INSERT IGNORE INTO `users` (`username`, `password`) VALUES
    	('admin', '$shiro1$SHA-256$500000$N96V7blDKhZUkEo7OrWBJQ==$yqeiYAelWwG7D3XnpdKyRunMfjI65C3T+9+5zEaerpw='),
    	('u1', '$shiro1$SHA-256$500000$QmLtx8PaCMe72i+yVuqH+A==$P5ohK5uWi30u38ujuTnmmeUK2gPwqhxTnke2wd9fZXw='),
    	('u2', '$shiro1$SHA-256$500000$QmLtx8PaCMe72i+yVuqH+A==$P5ohK5uWi30u38ujuTnmmeUK2gPwqhxTnke2wd9fZXw=');
    /*!40000 ALTER TABLE `users` ENABLE KEYS */;
    
    -- 导出  表 shiro.users_roles 结构
    CREATE TABLE IF NOT EXISTS `users_roles` (
      `username` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
      `role_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
      PRIMARY KEY (`username`,`role_name`),
      KEY `UR_1` (`username`),
      KEY `UR_2` (`role_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    -- 正在导出表  shiro.users_roles 的数据：~3 rows (大约)
    /*!40000 ALTER TABLE `users_roles` DISABLE KEYS */;
    INSERT IGNORE INTO `users_roles` (`username`, `role_name`) VALUES
    	('admin', 'ADMIN'),
    	('u1', 'USER_P1'),
    	('u2', 'USER_P2');
    /*!40000 ALTER TABLE `users_roles` ENABLE KEYS */;
    
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

shiro.ini

[main]
    shiro.loginUrl = /index.jsp
    shiro.unauthorizedUrl=/unauthorized.jsp
    
    
    # password matcher
    passwordMatcher = org.apache.shiro.authc.credential.PasswordMatcher
    passwordService = org.apache.shiro.authc.credential.DefaultPasswordService
    passwordMatcher.passwordService = $passwordService
    
    
    ds = com.mysql.cj.jdbc.MysqlDataSource
    ds.url=jdbc:mysql://localhost:3306/shiro?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    ds.user = root
    ds.password = root
    
    jdbcRealm = org.apache.shiro.realm.jdbc.JdbcRealm
    jdbcRealm.permissionsLookupEnabled = true
    
    # If not filled, subclasses of JdbcRealm assume "select password from users where username = ?"
    # first result column is password, second result column is salt
    jdbcRealm.authenticationQuery = select password from users where username = ?
    
    # If not filled, subclasses of JdbcRealm assume "select role_name from users_roles where username = ?"
    jdbcRealm.userRolesQuery = select role_name from users_roles where username = ?
    
    # If not filled, subclasses of JdbcRealm assume "select permission from roles_permissions where role_name = ?"
    jdbcRealm.permissionsQuery = select permission from roles_permissions where role_name = ?
    
    
    jdbcRealm.credentialsMatcher = $passwordMatcher
    jdbcRealm.dataSource=$ds
    securityManager.realms = $jdbcRealm
    
    #cacheManager = org.apache.shiro.cache.ehcache.EhCacheManager
    #securityManager.cacheManager = $cacheManager
    #jdbcRealm.authenticationCachingEnabled = true
    
    [urls]
    /bootstrap-5.1.3-dist/**=anon
    /bootstrap-icons-1.8.3/**=anon
    /unauthorized.jsp=anon
    /index.jsp=anon
    /login.jsp=anon
    /logout.jsp=authc
    /validate.jsp=anon
    /simpleCaptcha.jpg=anon
    /user-create-process.jsp=perms["user-create"]
    /user-create.jsp=perms["user-create"]
    /user-delete.jsp=perms["user-delete"]
    /user-index.jsp=perms["user-index"]
    /user-update-process.jsp=perms["user-update"]
    /user-update.jsp=perms["user-update"]
    /role-create-process.jsp=perms["role-create"]
    /role-create.jsp=perms["role-create"]
    /role-delete.jsp=perms["role-delete"]
    /role-index.jsp=perms["role-index"]
    /role-update-process.jsp=perms["role-update"]
    /role-update.jsp=perms["role-update"]
    /permission-create-process.jsp=perms["permission-create"]
    /permission-create.jsp=perms["permission-create"]
    /permission-delete.jsp=perms["permission-delete"]
    /permission-index.jsp=perms["permission-index"]
    /permission-update-process.jsp=perms["permission-update"]
    /permission-update.jsp=perms["permission-update"]
    /user-role-create-process.jsp=perms["user-role-create"]
    /user-role-create.jsp=perms["user-role-create"]
    /user-role-delete.jsp=perms["user-role-delete"]
    /user-role-index.jsp=perms["user-role-index"]
    /user-role-update-process.jsp=perms["user-role-update"]
    /user-role-update.jsp=perms["user-role-update"]
    /role-permission-create-process.jsp=perms["role-permission-create"]
    /role-permission-create.jsp=perms["role-permission-create"]
    /role-permission-delete.jsp=perms["role-permission-delete"]
    /role-permission-index.jsp=perms["role-permission-index"]
    /role-permission-update-process.jsp=perms["role-permission-update"]
    /role-permission-update.jsp=perms["role-permission-update"]
    /**=perms["deny"]

web.xml

<?xml version="1.0" encoding="UTF-8"?>
    <web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
        <display-name>captcha</display-name>
        <servlet>
            <servlet-name>SimpleCaptcha</servlet-name>
            <servlet-class>nl.captcha.servlet.SimpleCaptchaServlet</servlet-class>
        </servlet>
        <servlet-mapping>
            <servlet-name>SimpleCaptcha</servlet-name>
            <url-pattern>/simpleCaptcha.jpg</url-pattern>
        </servlet-mapping>
    
        <listener>
            <listener-class>org.apache.shiro.web.env.EnvironmentLoaderListener</listener-class>
        </listener>
    
        <filter>
            <filter-name>ShiroFilter</filter-name>
            <filter-class>org.apache.shiro.web.servlet.ShiroFilter</filter-class>
        </filter>
        <filter-mapping>
            <filter-name>ShiroFilter</filter-name>
            <url-pattern>/*</url-pattern>
        </filter-mapping>
    
        
    
    </web-app>

index.jsp

<%@page import="java.net.HttpCookie"%>
    <%@ page language="java" pageEncoding="UTF-8"%>
    <%@ page contentType="text/html;charset=UTF-8"%>
    <%
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
    %>
    
    
    
    <!DOCTYPE html>
    <html>
        <head>
            <title>请登录</title>
    
            <script src="jsencrypt.min.js"></script>
    
            <script type="text/javascript">
                function reloadImg() {
                    var timestamp = new Date().getTime();
                    document.getElementById("captcha").src = "simpleCaptcha.jpg?"
                            + timestamp;
                }
    
    
            </script>
    
            <meta charset="UTF-8">
            <meta http-equiv="X-UA-Compatible" content="IE=edge">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <meta http-equiv="Cache-Control" content="no-cache, no-store, must-revalidate" />
            <meta http-equiv="Pragma" content="no-cache" />
            <meta http-equiv="Expires" content="0" />
            <link href="bootstrap-5.1.3-dist/css/bootstrap.min.css" rel="stylesheet" >
            <script src="bootstrap-5.1.3-dist/js/bootstrap.bundle.min.js"></script>
            <link href="bootstrap-icons-1.8.3/bootstrap-icons.css" rel="stylesheet">
        </head>
        <body>
            <form autocomplete="off" id="form" method="post" action="<%=response.encodeURL("validate.jsp")%>"
                  >
    
                <div class="container-fluid vh-100" style="margin-top:50px">
                    <div class="" style="margin-top:50px">
                        <div class="rounded d-flex justify-content-center">
                            <div class=" col-md-4 col-sm-12 shadow-lg p-5 bg-light">
                                <div class="text-center">
                                    <h3 class="text-primary">请登录</h3>
                                </div>
                                <div class="p-4">
    
                                    <div class="input-group mb-3">
                                        <span class="input-group-text bg-secondary"><i
                                                class="bi bi-person-fill text-white"></i></span>
                                        <input id="username" type="text" name="username" required class="form-control" placeholder="用户名">
                                    </div>
                                    <div class="input-group mb-3">
                                        <span class="input-group-text bg-secondary"><i
                                                class="bi bi-key-fill text-white"></i></span>
                                        <input id="password" type="password" name="password"
                                               required class="form-control" placeholder="密码">
                                    </div>
                                    <div class="input-group mb-3">
                                        <span class="input-group-text bg-secondary"><i
                                                class="bi bi-lock-fill text-white"></i></span>
                                        <input type="text" name="answer" class="form-control" placeholder="输入下图中的校验码">
                                    </div>
                                    <div class="input-group mb-3">
                                        <span class="input-group-text bg-secondary"><i
                                                class="bi bi-image-fill text-white"></i></span>
                                        <img id="captcha" src="simpleCaptcha.jpg" alt="CAPTCHA Image" class="img-responsive captchaImg"><button type="button" onclick="javascript:reloadImg()" class="btn btn-outline-blue"><i
                                                class="bi bi-arrow-repeat text-black"></i>
                                        </button>
                                    </div>
                                    <div class="col-12">
                                        <button type="submit" class="btn btn-primary px-4 float-end mt-4">登录</button>
                                    </div>
    
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
    
    
            </form>
    
        </body>
    </html>

validate.jsp

<%@page language="java" pageEncoding="UTF-8"%>
    <%@page contentType="text/html;charset=UTF-8"%>
    <%    request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
    %>
    
    <%@page import="nl.captcha.Captcha"%>
    <%@ page import="org.apache.shiro.SecurityUtils" %>
    <%@ page import ="org.apache.shiro.authc.AuthenticationException" %>
    <%@ page import ="org.apache.shiro.authc.UsernamePasswordToken" %>
    <%@ page import ="org.apache.shiro.subject.Subject" %>
    
    
    <%
        Captcha captcha = (Captcha) session.getAttribute(Captcha.NAME);
        System.out.println(captcha.getAnswer());
        request.setCharacterEncoding("UTF-8");
        String answer = request.getParameter("answer");
        boolean result = captcha.isCorrect(answer);
        session.removeAttribute(Captcha.NAME);
    
        if (result) {
    
            String username = request.getParameter("username");
            String password = request.getParameter("password");
    
            Subject subject = SecurityUtils.getSubject();
            UsernamePasswordToken token = new UsernamePasswordToken(username, password);
            try {
                subject.login(token);
                request.getSession().setAttribute("username", username);
                response.sendRedirect(response.encodeURL("user-index.jsp"));
            } catch (AuthenticationException e) {
                response.sendRedirect(response.encodeURL("index.jsp"));
            }
        } else {
            response.sendRedirect(response.encodeURL("index.jsp"));
        }
    %>

user-create-process.jsp

<%@page import="org.apache.shiro.authc.credential.DefaultPasswordService"%>
    <%@include file="config.jsp"%>
    <%@ page language="java" pageEncoding="UTF-8"%>
    <%@ page contentType="text/html;charset=UTF-8"%>
    <%    request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
    %>
    
    <!DOCTYPE html>
    <html>
        <head>
            <title>用户管理</title>
            <meta charset="UTF-8">
            <meta http-equiv="X-UA-Compatible" content="IE=edge">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <link href="bootstrap-5.1.3-dist/css/bootstrap.min.css" rel="stylesheet" >
            <script src="bootstrap-5.1.3-dist/js/bootstrap.bundle.min.js"></script>
            <link href="bootstrap-icons-1.8.3/bootstrap-icons.css" rel="stylesheet">
            <link href="style.css" rel="stylesheet">
    
    
    
        </head>
        <body>
            <%@include file="header.jsp"%>
            <h1>用户管理</h1>
    
            <%@page import="java.sql.*,java.util.*"%>
            <%@page import="java.security.MessageDigest"%>
            <%@page import="java.security.NoSuchAlgorithmException"%>
            <%@page import="java.security.NoSuchProviderException"%>
            <%@page import="java.security.SecureRandom"%>
    
            <%@page import="org.apache.shiro.authc.credential.PasswordService"%>
    
    
    
    
            <%            String username = request.getParameter("username");
                String password = request.getParameter("password");
                PasswordService passwordService = new DefaultPasswordService();
                String securePassword = passwordService.encryptPassword(password);
    
                System.out.println(securePassword);
    
                Connection con = null;
                PreparedStatement ps = null;
                int result = 0;
    
                try {
                    // Register JDBC driver
                    Class.forName(DB_DRIVER_CLASS_NAME);
    
                    // establish the connection
                    con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
    
                    // compile SQL query and
                    // store it in PreparedStatemet object
                    if (con != null) {
                        String sql = "insert into users(username,password)values(?,?)";
                        ps = con.prepareStatement(sql);
                    }
    
                    // read multiple set of inputs from end-users
                    // set input values and execute the query
                    if (ps != null) {
    
                        // set input values to query parameters
                        ps.setString(1, username);
                        ps.setString(2, securePassword);
    
                        // execute the query
                        result = ps.executeUpdate();
                    }
    
                    // process the result
                    if (result == 0) {
                        out.println("未增加记录");
                    } else {
                        out.println("增加记录成功");
                    }
    
                } catch (SQLException se) {
                    se.printStackTrace();
                } catch (Exception e) {
                    e.printStackTrace();
                } // end of try-catch block
                finally {
                    // close JDBC objects
                    try {
                        if (ps != null) {
                            ps.close();
                        }
                    } catch (SQLException se) {
                        se.printStackTrace();
                    }
                    try {
                        if (con != null) {
                            con.close();
                        }
                    } catch (SQLException se) {
                        se.printStackTrace();
                    }
    
                }
            %>
            <%@include file="footer.jsp"%>
        </body>
    </html>

role-permission-create.jsp

<%@include file="config.jsp"%>
    <%@ page language="java" pageEncoding="UTF-8"%>
    <%@ page contentType="text/html;charset=UTF-8"%>
    <%    request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");
    %>
    <%@ page import="java.sql.*"%>
    <%@ page import="java.io.*"%>
    <%@page import="java.sql.DriverManager"%>
    <%@page import="java.sql.ResultSet"%>
    <%@page import="java.sql.Statement"%>
    <%@page import="java.sql.Connection"%>
    <%@page import="org.apache.commons.text.StringEscapeUtils"%>
    <!DOCTYPE html>
    <html>
        <head>
            <title>角色权限管理</title>
    
            <meta charset="UTF-8">
            <meta http-equiv="X-UA-Compatible" content="IE=edge">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <link href="bootstrap-5.1.3-dist/css/bootstrap.min.css" rel="stylesheet" >
            <script src="bootstrap-5.1.3-dist/js/bootstrap.bundle.min.js"></script>
            <link href="bootstrap-icons-1.8.3/bootstrap-icons.css" rel="stylesheet">
            <link href="style.css" rel="stylesheet">
    
    
    
        </head>
        <body>
            <%@include file="header.jsp"%>
            <h1>角色权限管理</h1>
    
            <form method="post" action="<%=response.encodeURL("role-permission-create-process.jsp")%>">
                <center>
                    <table border="1" cellpadding="5" cellspacing="2">
                        <thead>
                            <tr>
                                <th colspan="2">添加角色权限</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>角色</td>
                                <td>
                                    <select name="role_name">
                                        <%
    
                                            Connection con = null;
                                            PreparedStatement ps = null;
                                            ResultSet rs = null;
                                            try {
                                                // Register JDBC driver
                                                Class.forName(DB_DRIVER_CLASS_NAME);
    
                                                // Open a connection
                                                System.out.println("Connecting to database...");
                                                con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
    
                                                // Execute a query
                                                System.out.println("Creating statement...");
    
                                                // compile SQL query and
                                                // store it in PreparedStatemet object
                                                if (con != null) {
                                                    String sql = "select * from roles";
                                                    ps = con.prepareStatement(sql);
                                                }
                                                // set input value to query parameter
    
                                                // execute the query
                                                if (ps != null) {
                                                    rs = ps.executeQuery();
                                                }
                                                // process the result
                                                if (rs != null) {
    
                                                    while (rs.next()) {
                                        %>
                                        <option value="<%=StringEscapeUtils.escapeHtml4(rs.getString("name"))%>"><%=StringEscapeUtils.escapeHtml4(rs.getString("name"))%></option>
                                        <%
                                                    }
                                                }
                                                // Clean-up environment
                                                rs.close();
                                                ps.close();
                                                con.close();
                                            } catch (SQLException se) {
                                                se.printStackTrace();
                                            } catch (Exception e) {
                                                e.printStackTrace();
                                            } // end of try-catch block
                                            finally {
                                                // close JDBC objects
                                                try {
                                                    if (rs != null) {
                                                        rs.close();
                                                    }
                                                } catch (SQLException se) {
                                                    se.printStackTrace();
                                                }
                                                try {
                                                    if (ps != null) {
                                                        ps.close();
                                                    }
                                                } catch (SQLException se) {
                                                    se.printStackTrace();
                                                }
                                                try {
                                                    if (con != null) {
                                                        con.close();
                                                    }
                                                } catch (SQLException se) {
                                                    se.printStackTrace();
                                                }
    
                                            }
                                        %>
                                    </select>
                                </td>
                            </tr>
                            <tr>
                                <td>权限</td>
                                <td><select name="permission">
                                        <%
                                            Connection con2 = null;
                                            PreparedStatement ps2 = null;
                                            ResultSet rs2 = null;
                                            try {
                                                // Register JDBC driver
                                                Class.forName(DB_DRIVER_CLASS_NAME);
    
                                                // Open a connection
                                                System.out.println("Connecting to database2...");
                                                con2 = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
    
                                                // Execute a query
                                                System.out.println("Creating statement2...");
    
                                                // compile SQL query and
                                                // store it in PreparedStatemet object
                                                if (con2 != null) {
                                                    String sql2 = "select * from permissions";
                                                    ps2 = con2.prepareStatement(sql2);
                                                }
                                                // set input value to query parameter
    
                                                // execute the query
                                                if (ps2 != null) {
                                                    rs2 = ps2.executeQuery();
                                                }
                                                System.out.println("execute the query2 end...");
                                                // process the result
                                                if (rs2 != null) {
                                                    System.out.println("process the result2 ...");
                                                    while (rs2.next()) {
                                        %>
                                        <option value="<%=StringEscapeUtils.escapeHtml4(rs2.getString("name"))%>"><%=StringEscapeUtils.escapeHtml4(rs2.getString("name"))%></option>
                                        <%
                                                    }
                                                }
                                                // Clean-up environment
                                                rs2.close();
                                                ps2.close();
                                                con2.close();
                                            } catch (SQLException se) {
                                                se.printStackTrace();
                                            } catch (Exception e) {
                                                e.printStackTrace();
                                            } // end of try-catch block
                                            finally {
                                                // close JDBC objects
                                                try {
                                                    if (rs2 != null) {
                                                        rs2.close();
                                                    }
                                                } catch (SQLException se) {
                                                    se.printStackTrace();
                                                }
                                                try {
                                                    if (ps2 != null) {
                                                        ps2.close();
                                                    }
                                                } catch (SQLException se) {
                                                    se.printStackTrace();
                                                }
                                                try {
                                                    if (con2 != null) {
                                                        con2.close();
                                                    }
                                                } catch (SQLException se) {
                                                    se.printStackTrace();
                                                }
    
                                            }
                                        %>
                                    </select></td>
                            </tr>
                            <tr>
                                <td colspan="2" align="center"><input type="submit"
                                                                      value="添加" />    <input type="reset" value="重置" /></td>
                            </tr>
                        </tbody>
                    </table>
                </center>
            </form>
        </body>
    </html>

![b314112b05d74d42ad47e4096ec5350e.png][]

![5aa9d9f4557140fca4fb3526098be0c2.png][]

![41d4cb885fd04419942ac3ae09ef14cd.png][]

完整源码：[https://github.com/allwaysoft/Apache-shiro-rbac-JdbcRealm-MySQL-custom-login-logout-jsp][https_github.com_allwaysoft_Apache-shiro-rbac-JdbcRealm-MySQL-custom-login-logout-jsp]

[dc16805f4da24a84ae935e44f04f5f84.png]: https://img-blog.csdnimg.cn/dc16805f4da24a84ae935e44f04f5f84.png
[b314112b05d74d42ad47e4096ec5350e.png]: https://img-blog.csdnimg.cn/b314112b05d74d42ad47e4096ec5350e.png
[5aa9d9f4557140fca4fb3526098be0c2.png]: https://img-blog.csdnimg.cn/5aa9d9f4557140fca4fb3526098be0c2.png
[41d4cb885fd04419942ac3ae09ef14cd.png]: https://img-blog.csdnimg.cn/41d4cb885fd04419942ac3ae09ef14cd.png
[https_github.com_allwaysoft_Apache-shiro-rbac-JdbcRealm-MySQL-custom-login-logout-jsp]: https://github.com/allwaysoft/Apache-shiro-rbac-JdbcRealm-MySQL-custom-login-logout-jsp