JDBC认证

Question

我有一个表单，它向Servlet提交posts请求。我创建了一个类DbConnection，它管理针对MySQL数据库的身份验证，当我用静态main方法测试它时，它运行得非常好。当我在Servlet中使用这个类时，它总是给我这个错误。

我的密码怎么了？

这是DbConnection类。

package com.cloud.db;

public class DbConnection {

    private Connection conn;
    private String user = NAME;
    private String passwd = SOME_PASSWORD;
    private Statement stmt;
    private ResultSet result;
    private PreparedStatement auth;

    public DbConnection() {
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cloudbase", user, passwd);
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean authenticate(String usrname, String pwd) throws SQLException {
        PreparedStatement stm = conn.prepareStatement("select * from users where email=? and password = ?");
        stm.setString(1, usrname);
        stm.setString(2, pwd);
        ResultSet result = stm.executeQuery();

        if (result.next()) {
            return true;
        } else {
            return false;
        }
    }
}

这是我的servlet

package com.cloud.db;

import com.cloud.*;
import java.sql.*;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/auth")
public class Auth extends HttpServlet {

    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public Auth() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
     * response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
     * response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String email = request.getParameter("email");
        String passwd = request.getParameter("pwd");
        DbConnection connect = new DbConnection();

        try {
            new DbConnection().authenticate("example@cloudserver.com", "password");
        } catch (SQLException e) {
            System.out.println("Error :" + e.getMessage());
        }
    }
}

Answer 1

你也可以这样做

public class ConnectionProvider {
    public static Connection getConnection() throws SQLException {
      Connection connection = null;
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/cloudbase"
                                                                        ,user,passwd);
      return connection;
    }

   public void authenticate(Connection connection,String usrname,String pwd) 
       throws SQLException {
    String query = "select firstName from users where email=? and password = ?";
    try {
        PreparedStatement stm = connection.prepareStatement(query);
        stm.setString(1, usrname);
        stm.setString(2, pwd);
        ResultSet result = stm.executeQuery();
        if (resultSet.next()) {
            do {
               System.out.println("Welcome, "+resultSet.getString("firstName"));
            } while(resultSet.next());
        }
       else{
           System.out.println("Username or id doesnot exist");
     }
    } catch (SQLException e) {
        System.out.println("Error :" + e.getMessage());   
    }
    finally{
       connection.close();
}

}

然后在Servlet中

protected void doPost(...) throws ServletException, IOException {
    String email = request.getParameter("email");
    String passwd = request.getParameter("pwd");
    Connection connection = ConnectionProvider.getConnection();
    new ConnectionProvider().authenticate(connection,email ,passwd);    

}

Answer 2

根据你发布的信息，我认为问题在于：

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cloudbase",user,passwd);
stmt = conn.createStatement();

DriverManager.getConnection回来了，null，null.create.是在扔NPE。

如何验证这一点:在conn = ...之后放置sysout并查看连接是否为空，如何修复: 1)确保驱动程序类在类路径中并已加载。2)在使用对象之前，请确保已初始化对象。

Answer 3

在本地运行代码和在tomcat中运行代码的最大区别是JDBC驱动程序的位置，因此最可能的问题是Tomcat找不到JDBC驱动程序。Tomcat希望在$CATALINA_HOME/lib中找到一个司机。有关于设置MySQL数据源这里的说明。

目前，您的异常处理掩盖了正在发生的事情。最初的问题发生在DBConnection构造函数中，但是由于异常被捕获，所以身份验证方法将继续以空连接运行，从而产生一个NullPointerException。

Tomcat应该将在DBConnection构造函数中生成的异常写入控制台，这样您就可以在那里查找原始问题。要修复异常处理，可以将DbConnection代码更改为如下所示：

public boolean authenticate(String usrname,String pwd) throws SQLException {
    Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/cloudbase",user,passwd);
    try {
        PreparedStatement stm = conn.prepareStatement(
        "select * from users where email=? and password = ?");
        stm.setString(1, usrname);
        stm.setString(2, pwd);
        ResultSet result = stm.executeQuery();
        return result.next();
    } finally {
        try {
            conn.close();
        } catch (SQLException e) {
            // use a logger instead of stdout
            log.info("close caused exception", e); 
        }
    }
}

这确保抛出的异常是导致原始问题的异常。