无法使用JSP和TOMCAT 7连接MYSQL数据库

Question

当我尝试使用登录 jsp 登录时，它不会检查 mysql 数据库。有什么建议吗？

我的登录Jsp------------->

JSP

<table border="0" cellpadding="0" cellspacing="0" width=0% style="font-size: 8pt;">

<%if (session.getAttribute("userName")==null) {%>
    <form method="post" action="/web/login.do">
        <input type="hidden" name="option" value="login">
        <tr>
          <td>Login:</td>
          <td><input name="u_id" type="text" id="u_id" size="20"></td>
        </tr>
        <tr> 
          <td>Password:</td>
          <td><input name="u_pw" type="password" id="u_pw" size="20"> 
          </td>
        </tr>
        <tr>
          <td></td>
          <td>
            <a href="/web/index.jsp">Home</a> |
            <a href="/web/register.jsp">Register</a> |
            <input type="submit" value="Log In">
          </td>
        </tr>
    </form>
<%}
else {
    String username=session.getAttribute("username").toString();%>
    <tr><td>Login: <b><%=userName%></b></td></tr>
    <tr><td>
      <a href="/web/index.jsp">Home</a> |
      <a href="/web/cart/cart.jsp">Cart</a> |


<%    if (session.getAttribute("login").toString() {%>
        <a href="/web/index.jsp">Admin Portal</a>
<%        }
       |
      <a href="/web/log.do?option=logout">Logout</a>
    </td></tr>
<%}%>

</table> 
</div>

我的WEB XML ---------------------->

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">

    <servlet>
      <servlet-name>LoginLogout</servlet-name>
      <servlet-class>LoginLogoutServlet</servlet-class>
    </servlet>


     <servlet-mapping>
            <servlet-name>LoginLogout</servlet-name>
            <url-pattern>/login.do</url-pattern>
       </servlet-mapping>

    </web-app>

My Context XML -------------->

Context docBase="web" path="/web" workDir="work\Catalina\localhost\web"
  Resource name="jdbc/myDB" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" password="" maxIdle="2" maxWait="5000" username="root" url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true" maxActive="4"/
</Context>

My LoginLogout Servlet ---------------------> 
Java

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;

public class LoginLogoutServlet extends HttpServlet {
    /**
    *This method handles the request passed in from the interface using POST method.
    */
     public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException {
        login(req,res);
     }
    /**
    *This method handles the request passed in from the interface using GET method.
    */
     public void doGet(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException {
        doPost(req,res);
     }
    /**
    *This method handles the login and logout of User.
    */
    public void login(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        ArrayList ex = new ArrayList();
        String option = request.getParameter("option");
        String uid = null;
        String pw = null;

        if(option.equalsIgnoreCase("login")){
            uid = request.getParameter("u_id");
            pw = request.getParameter("u_pw");

            UserDAO user = null;
            ArrayList userDB = null;

            try {
                user = new UserDAO();
                userDB = user.retrieve();
            }catch(Exception e){
                ex.add(e);
            }

            boolean ufound = false;

            HttpSession session = request.getSession();
            if(ex.size()==0 && !uid.equals("") && !pw.equals("") ){
                //checks for staff in the database
                for(int i = 0; i < userDB.size(); i++){
                    User s = (User)userDB.get(i);
                    String login = s.getUserName();
                    String password = s.getPassword();
                    if((uid.trim().equalsIgnoreCase(login)) && (pw.trim().equalsIgnoreCase(password))){
                        ufound = true;
                        session.setAttribute("userName",uid);
                    }
                }

                /*//checks for User in the database
                for(int i = 0; i < userDB.size(); i++){
                    User c = (User)userDB.get(i);
                    String email = c.getEmailAddr();
                    String password = c.getPasswd();
                    if((uid.equalsIgnoreCase(email)) && (pw.equalsIgnoreCase(password))){
                        ufound = true;
                        session.setAttribute("userName",uid);
                        session.setAttribute("login","customer");
                        session.setAttribute("customerObj",c);
                        //assign shopping cart to customer
                        session.setAttribute("ShoppingCart", new ArrayList());

                        //checks which page did the customer login from
                        if(request.getRequestURI().equals("main.html")){
                            //display main page
                            //RequestDispatcher rd = request.getRequestDispatcher("main.html");
                        }else{
                            //RequestDispatcher rd = request.getRequestDispatcher("shoppingcart.html");
                        }
                    }
                }*/
            }else{
                ex.add(new Exception("Please complete all fields!"));
            }
            if(!ufound){
                ex.add(new Exception("No such User found!"));
                request.setAttribute("userName","notFound");
                request.setAttribute("login","notFound");
            }if(ufound){
                session.setAttribute("login","User");
            }
            try {
                user.close();
            }catch(Exception e){
                ex.add(e);
            }
        }else if(option.equalsIgnoreCase("logout")){
            HttpSession session = request.getSession();
            String login=(String) session.getAttribute("login");
            if(login.equals("User")){
                session.removeAttribute("userName");
                //request.setAttribute("Remove","removedStaff");
            }else if(login.equals("customer")){
                session.removeAttribute("userName");
                session.removeAttribute("cart");
                //request.setAttribute("Remove","removedCust");
            }
            session.invalidate();
        }

        //assign request attributes for jsp output
        request.setAttribute("option",option);
        request.setAttribute("exceptions",ex);
        RequestDispatcher view=null;
        response.sendRedirect("/web");
        out.close();
    }
}

用户 DAO。

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.*;
import java.util.*;

/**
 *This class allows eStoreServlet to communicate with the database, myDB, through connection pooling.
 *This class handles the CRUD operations of the Users entity.
 */
public class UserDAO{
    private DataSource ds;
    private Connection con;

    /**
    *Constructor gets a connection from connection pool.
    */
    public UserDAO() throws Exception{
        try {
            Context ctx = new InitialContext();
            if(ctx == null )
                throw new Exception("Can't create initial context");
            if(ds == null)
                ds = (DataSource) ctx.lookup(eSpaceStatic.daoDS_name);
            con = ds.getConnection();
        } catch (NamingException e){
            e.printStackTrace();
            throw new Exception(e+": User"+eSpaceStatic.daoEM_cp);
        }
    }

    /**
    *Method to add a User to the database.
    *@param c This is the User object.
    *@return Returns an int, if -1, means User is not added to the database. Otherwise, the id of the User will be returned.
    */
    public int add(User c) throws Exception{
        int result = 0;
        try{
            PreparedStatement stmt = con.prepareStatement("insert into User(name, username, password) values(?,?,?)");

            stmt.setString(1, c.getName());
            stmt.setString(2, c.getUserName());
            stmt.setString(3, c.getPassword());

            int rownum = stmt.executeUpdate();

            if(rownum == 0){
                result = -1;
            }else{
                ResultSet rs = stmt.getGeneratedKeys();
                if(rs.next()){
                    result = rs.getInt(1);
                }
            }
            stmt.close();
        }catch(SQLException se){
            throw new SQLException(se+": Item"+eSpaceStatic.daoEM_add);
        }
        return result;
    }


    /**
    *Method to retrieve all User from the database.
    *@return Returns an arraylist which contains all the User objects.
    */
    public ArrayList retrieve() throws Exception {
        ArrayList cl = null;
        try{
            cl = new ArrayList();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("Select * from User");
            if(rs!=null){
                while(rs.next()){
                    User c = new User();
                    c.setUserId(rs.getInt("userId"));
                    c.setName(rs.getString("name"));
                    c.setUsername(rs.getString("username"));
                    c.setPassword(rs.getString("password"));
                    cl.add(c);
                }
            }
            st.close();
        }
        catch(SQLException se){
            System.out.println(se+": User"+eSpaceStatic.daoEM_rtr);
        }
        return cl;
    }

    /**
    *Method to retrieve a User from the database.
    *@param userId This is the User Id.
    *@return Returns a User object.
    */
    public User retrieve(int userId) throws Exception {
        User ret = null;
        try{
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("Select * from User where userId = "+userId);
            if(rs!=null){
                while(rs.next()){
                    User c = new User();
                    c.setUserId(rs.getInt("userId"));
                    c.setName(rs.getString("name"));
                    c.setUsername(rs.getString("username"));
                    c.setPassword(rs.getString("password"));
                }
            }
            st.close();
            rs.close();
        }
        catch(SQLException se){
            throw new Exception(se+": "+eSpaceStatic.daoEM_cp);
        }
        return ret;
    }

    /**
    *Method to update a User in the database.
    *@param c This is the User object.
    *@param userId This is the User id.
    *@return Returns a boolean. If true, User is updated. If false, User is not updated.
    */
    public boolean update(User c, int userId) throws Exception {
        boolean updated = false;
        try{
            PreparedStatement pstmt = con.prepareStatement("update User set (name = ?, username = ?, password = ?) where userId = ?");
            pstmt.setString(1, c.getName());
            pstmt.setString(2, c.getUserName());
            pstmt.setString(3, c.getPassword());
            pstmt.setInt(4, userId);

            int rownum = pstmt.executeUpdate();
            updated = rownum!=0;
            pstmt.close();
        }catch(SQLException se){
            System.out.println(se+": User"+eSpaceStatic.daoEM_rtr);
        }
        return updated;
    }

    /**
    *Method to delete a User in the database.
    *@param userId This is the User Id.
    *@return Returns a boolean. If true, User is deleted. If false, User is not deleted.
    */
    public boolean delete(int userId) throws Exception {
        boolean deleted=false;
        try {
            PreparedStatement ps=con.prepareStatement("delete from User where userId= ?");
            ps.setInt(1,userId);
            ps.executeUpdate();

            deleted=true;
        }
        catch (SQLException se) {
            System.out.println(se+": User"+eSpaceStatic.daoEM_del);
        }
        return deleted;
    }

    /**
     *Method to close connection.
     */
    public void close() throws SQLException{
        con.close();
    }
}

eSpaceStatic类

public class eSpaceStatic {
    public static String daoDS_name="java:comp/env/jdbc/myDB";
    public static String daoEM_cp="Could not look up connection pool.";
    public static String daoEM_rtr=" could not be retrieved.";
    public static String daoEM_add=" could not be added.";
    public static String daoEM_del=" could not be deleted.";
    public static String daoEM_cnf=" could not be found.";
}

Answer 1

编辑：我应该从一开始就问这个：

当我尝试使用登录 jsp 登录时，它不会检查 mysql 数据库

你怎么知道你的代码“不检查mysql数据库”

有什么建议吗？

是的。

• 将登录和注销分离到两个 servlet 中。它将使您的代码更易于理解和测试，
• 而不是将所有用户读入 ArrayList (UserDAO.retrieve())，而是向 UserDAO 添加一个方法，该方法获取登录名和密码并根据数据库检查它们。这样，如果您无法登录，您将确切地知道在哪里寻找问题
• 不要以纯文本形式存储密码。只是不要那样做。
• 在 JSP 中使用 JSTL。 action="/web/login.do" 可以替换为 .您的上下文的名称可能会更改，JSTL 会处理这个问题。

Answer 2

您的数据源上下文不应该是 java:comp/env/jdbc/myDB 而不是 eSpaceStatic.daoDS_name。在进行 JNDI 查找时也要捕获 SQL 异常。