JSP登录验证代码报错求助：数据库用户登录信息验证问题

尝试将用户登录信息与数据库中的数据进行验证，但运行代码时持续报错。使用表中的CID作为用户名进行用户名和密码验证，需修改login.jsp代码使其正常运行。

原始代码 #

<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%!
    Connection con;
    PreparedStatement ps1, ps2;
    public void jspInit()
    {
        try
        {
            //loading the driver
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //establish the connection
            con = DriverManager.getConnection("jdbc:derby://localhost:1527/ProjectDB", "admin1", "1234");
            //create statement object
            ps1 = con.prepareStatement("select count(*) from customers where username = ? and password=?");
            ps2 = con.prepareStatement("select * from customers");
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
%>
<%
    String param = request.getParameter("s1");
    if(param =="link")
    {
        ResultSet rs = ps2.executeQuery();
        out.println("<table>");
        while(rs.next())
        {
            out.println("<tr>");
            out.println("<td>"+rs.getString(1)+"</td>");
            out.println("<td>"+rs.getString(2)+"</td");
            out.println("</tr>");
        }
        out.println("</table>");
        rs.close();
    }
    else
    {
        //write jdbc code for authentication
        String user = request.getParameter("uname");
        String pass = request.getParameter("pwd");
        //set form data as param value
        ps1.setString(1,user);
        ps1.setString(2,pass);
        //excute the query
        ResultSet rs = ps1.executeQuery();
        int cnt = 0;
        if (rs.next())
            cnt = rs.getInt(1);
        if(cnt == 0)
            out.println("<b><i><font color=red>Invalid credential</fonr></i></b>");
        else
        {
            out.println("<form><fieldset style= width:25%; >");
            out.println("<b><i><font color=red>valid credential..</fonr></i></b><br>");
            out.println("<b><i><font size=6 color=blue>Welcome to My Page</fonr></i></b>");
            out.println("</fieldset></form>");
        }
    }
%>
<%!
    public void jspDestroy()
    {
        try
        {
            //colse
            ps1.close();
            ps2.close();
            con.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
%>
    </body>
</html>

核心问题与修改步骤 #

• 驱动与数据库URL不匹配：代码加载Oracle驱动却连接Derby数据库，需替换为Derby驱动org.apache.derby.jdbc.ClientDriver
• SQL查询字段错误：需求用CID作为用户名，原SQL查询username字段，需修改为select count(*) from customers where CID = ? and password=?
• 字符串比较错误：Java中不能用==比较字符串，需改为"link".equals(param)
• 多线程安全问题：全局共享Connection和PreparedStatement会引发线程冲突，应在每次请求中创建并关闭资源
• HTML标签语法错误：修复</td"改为</td>，</fonr>改为</font>的拼写错误
• 空指针防御：对request.getParameter返回值做判空处理，避免空指针异常

修改后的完整代码 #

<%@ page import="java.sql.*"%>
<%@ page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>登录验证</title>
</head>
<body>
<%
    String param = request.getParameter("s1");
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        // 加载Derby驱动
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        // 建立数据库连接
        con = DriverManager.getConnection("jdbc:derby://localhost:1527/ProjectDB", "admin1", "1234");

        if ("link".equals(param)) {
            // 查询所有用户
            ps = con.prepareStatement("select * from customers");
            rs = ps.executeQuery();
            out.println("<table border='1'>");
            while (rs.next()) {
                out.println("<tr>");
                out.println("<td>" + rs.getString("CID") + "</td>");
                out.println("<td>" + rs.getString("password") + "</td>");
                out.println("</tr>");
            }
            out.println("</table>");
        } else {
            // 登录验证逻辑
            String user = request.getParameter("uname");
            String pass = request.getParameter("pwd");

            // 判空处理
            if (user == null || pass == null || user.trim().isEmpty() || pass.trim().isEmpty()) {
                out.println("<b><i><font color=red>用户名或密码不能为空</font></i></b>");
                return;
            }

            // 使用CID作为用户名查询
            ps = con.prepareStatement("select count(*) from customers where CID = ? and password = ?");
            ps.setString(1, user.trim());
            ps.setString(2, pass.trim());
            rs = ps.executeQuery();

            int cnt = 0;
            if (rs.next()) {
                cnt = rs.getInt(1);
            }

            if (cnt == 0) {
                out.println("<b><i><font color=red>无效的凭证</font></i></b>");
            } else {
                out.println("<form><fieldset style='width:25%;'>");
                out.println("<b><i><font color=green>凭证有效..</font></i></b><br>");
                out.println("<b><i><font size=6 color=blue>欢迎来到我的页面</font></i></b>");
                out.println("</fieldset></form>");
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
        out.println("<b><i><font color=red>服务器错误：" + ex.getMessage() + "</font></i></b>");
    } finally {
        // 关闭资源，避免内存泄漏
        try {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
%>
</body>
</html>

内容的提问来源于stack exchange，提问作者Jamal Crawford