java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/db

Question

the page always forward to "custLogin" but the data is correct.

The code below:

Class Login:

private CustomerDB db;

public void init() {
    String dbUrl = "jdbc:mysql://localhost:3306/fyp";
    String dbUser = "root";
    String dbPassword = "";
    db = new CustomerDB(dbUrl, dbUser, dbPassword);
}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doPost(request, response);
}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String action = request.getParameter("action");

    if (!isAuthenticated(request) && !("authenticate".equals(action))) {
        doLogin(request, response);
        return;
    }
    if ("authenticate".equals(action)) {
        doAuthenticate(request, response);
    } else if ("logout".equals(action)) {
        doLogout(request, response);
    } else {
        response.sendError(HttpServletResponse.SC_NOT_IMPLEMENTED);
    }
}

private void doAuthenticate(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
    String username = request.getParameter("custID");
    String password = request.getParameter("custPW");
    String targetURL;
    init();
    boolean isValid = db.isValidUser("chan123", "123456");

    if (isValid) {
        targetURL = "/index.jsp";
    } else {
        targetURL = "/custLogin.jsp";
    }
    RequestDispatcher rd;
    rd = getServletContext().getRequestDispatcher("/" + targetURL);
    rd.forward(request, response);
}

public boolean isAuthenticated(HttpServletRequest request) {
    boolean result = false;
    HttpSession session = request.getSession();
    if (session.getAttribute("userInfo") != null) {
        result = true;
    }
    return result;
}

private void doLogin(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
    String targetURL = "login.jsp";
    RequestDispatcher rd;
    rd = getServletContext().getRequestDispatcher("/" + targetURL);
    rd.forward(request, response);
}

private void doLogout(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
    HttpSession session = request.getSession(false);
    if (session != null) {
        session.removeAttribute("userInfo");
        session.invalidate();
    }
    doLogin(request, response);
}

chan123 is the custID. 123456 is the custPW.

Class CustomerDB:

private String dbUrl;
private String dbUser;
private String dbPassword;

public CustomerDB() {
}

public CustomerDB(String dburl, String dbUser, String dbPassword) {
    this.dbUrl = dbUrl;
    this.dbUser = dbUser;
    this.dbPassword = dbPassword;
}

public Connection getConnection() throws SQLException, IOException {
    System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
    Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/fyp", "root", "");
    return conn;
}

public boolean isValidUser(String custID, String custPW) {
    boolean isValid = false;
    Connection cnnct = null;
    PreparedStatement pStmnt = null;

    try {
        cnnct = getConnection();
        String preQueryStatement = "SELECT * FROM customer WHERE custID=? and custPW=?";
        pStmnt = cnnct.prepareStatement(preQueryStatement);
        pStmnt.setString(1, custID);
        pStmnt.setString(2, custPW);
        ResultSet rs = null;
        rs = pStmnt.executeQuery();
        if (rs.next()) {
            isValid = true;
        }
        pStmnt.close();
        cnnct.close();
    } catch (SQLException ex) {
        while (ex != null) {
            ex.printStackTrace();
            ex = ex.getNextException();
        }
    } catch (IOException ex) {
        ex.printStackTrace();
    }
    return isValid;
}

I create the test java. It can load the correct result.

public static void main(String[] arg) {
    String dbUrl = "jdbc:mysql://localhost:3306/fyp";
    String dbUser = "root";
    String dbPassword = "";
    CustomerDB db = new CustomerDB(dbUrl, dbUser,dbPassword);
    boolean n = db.isValidUser("chan123", "123456");
    if (n) {
        System.out.println("TTTTTT");
    }else 
        System.out.println("f");
}

}

it shows "TTTTTT".

But the page forward to "custLogin". the output:

SEVERE:   java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/fyp
    at java.sql.DriverManager.getConnection(DriverManager.java:596)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at fyp.db.CustomerDB.getConnection(CustomerDB.java:29)
    at fyp.db.CustomerDB.isValidUser(CustomerDB.java:39)
    at fyp.servlet.Login.doAuthenticate(Login.java:52)
    at fyp.servlet.Login.doPost(Login.java:39)
...

Please help me solve this problem. Thank you!

Answer 1

You don't need to use Class.forName("com.mysql.jdbc.Driver"); from the DriverManager Javadoc Applications no longer need to explictly load JDBC drivers using Class.forName(). Existing programs which currently load JDBC drivers using Class.forName() will continue to work without modification.

You need to add the mysql connector/j jar file (for example, the one available here) to your server classpath.