Access to MySQL using Java Servlet?

Question

The Solution:

I added this code

Class.forName("com.mysql.jdbc.Driver");

brfore

Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");

Thank you all for reply my question

====================

I have problem, I try to insert data into mysql db using servlet, but I couldn'y access to MySQL

• Database name: test
• Table name: test
• I already added jdbc connector to the project library
• I'm using JDK 1.7, NetBeans 7.3, MySQL 5.6, Tomcat 7.0, Connector/J 5.1.24

1- this is "form action" in sign_up.jsp page:

<form action="RegisterUser" method="post">
    <td><input type="submit" value="Submit"></td>
</form>

2- this is RegisterUser.java servlet:

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

@WebServlet(urlPatterns = {"/RegisterUser"})
public class RegisterUser extends HttpServlet {
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {

    try{
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");
        Statement s = (Statement) con.createStatement();

        String name = "Hassan3";
        int phone = 123456;

        String insert = "INSERT INTO test VALUES ('\" + name + \"', \" + phone + \")";

        s.executeUpdate(insert);

        s.close();
        con.close();

    }catch(Exception e){
        throw new SecurityException("Class not found " + e.toString());
    }
}

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    try {
        processRequest(request, response);
    } catch (SQLException ex) {
        Logger.getLogger(RegisterUser.class.getName()).log(Level.SEVERE, null, ex);
    }
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    try {
        processRequest(request, response);
    } catch (SQLException ex) {
        Logger.getLogger(RegisterUser.class.getName()).log(Level.SEVERE, null, ex);
    }
}

@Override
public String getServletInfo() {
    return "Short description";
}// </editor-fold>

}

3- the exception result:

HTTP Status 500 - Class not found java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost/test

type Exception report

message Class not found java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost/test

description The server encountered an internal error that prevented it from fulfilling this request.

exception
java.lang.SecurityException: Class not found java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost/test
    RegisterUser.processRequest(RegisterUser.java:66)
    RegisterUser.doPost(RegisterUser.java:173)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

note The full stack trace of the root cause is available in the Apache Tomcat/7.0.39 logs.

4- But when I use same code but in java file "without servlet or web app" it's working correctly:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Test {
    public static void main(String[] args){
    try{
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");
        Statement s = (Statement) con.createStatement();

        String name = "Hassan4";
        int phone = 8985895;

        String insert = "INSERT INTO test VALUES ('" + name + "', " + phone + ")";

        s.executeUpdate(insert);

        s.close();
        con.close();
        System.out.println("done");
    }catch(Exception e){
        throw new SecurityException("Class not found " + e.toString());
    }
}

}

so what is problem with servlet? Why the code works with java app. but it doesn't work with web app.?

Answer 1

You are getting Class not found java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost/test

It means When you are running it from Web Application, JRE could not find Class in the Classpath.

If this code works in your Standalone it means you need to have a JAR file somewhere containing com.mysql.jdbc.Driver class (so called JDBC driver). This JAR needs to be visible in Tomcat. So, I would suggest placing mysql-jdbc.jar at a physical location to /WEB-INF/lib directory of your project.

Alternatively, you can add Third party libraries like JDBC driver here using

Right Click Project Name--> Properties

from your NetBeans IDE

Then restarting Tomcat should work.

Second, you don't need

import com.mysql.jdbc.Driver;

in your Servlet.

Answer 2

David is right and i want to add, you can also install the driver by pasting the jar file in the the installation folder of java.

\Program Files\Java\jre7\lib\ext  

Well i dont like mysql very much and always use Mssql with a windows server 2008. This is the code i use for that, i might be your answer since mysql connection works pretty much the same as sql.

 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
 conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName="+database+";user="+user+";password="+password);

Answer 3

First, you should put that into a persistance layer.

1) Ensure that your JDBC driver is in place. Copy it into your classpath, e.g. /WEB-INF/lib directory. Link: MySQL JDBC Driver Download Page

2) Check your connect string: jdbc:mysql://<server>:<port>/<database>, looks like the port is missing. Try jdbc:mysql://127.0.0.1:3306/test