Connect Servlet to MySQL database in eclipse

Question

I successfully connected database with simple java program using JDBC but when I am trying to connect database with Servlet, it gives me following errors and exceptions:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:8888/ebookshop
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at QueryServlet.doGet(QueryServlet.java:35)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:534)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1081)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)
    at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

All severs running well and also I already added servlet and connecter API's in my library.

For more Information these are my html and servlet files are:

html file

<html>
    <head><title>Yet Another Bookshop</title></head>

    <body>
        <h2>Yet Another Bookshop</h2>
        <form method="get" action="http://localhost:9999/Sixth/query">
            <b>Choose an author:</b>
            <input type="checkbox" name="author" value="Tan Ah Teck">Ah Teck
            <input type="checkbox" name="author" value="Mohammad Ali">Ali
            <input type="checkbox" name="author" value="Kumar">Kumar
            <input type="submit" value="Search">
        </form>
    </body>
</html>

My servlet:

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class QueryServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;

    // JDK 6 and above only
    // The doGet() runs once per HTTP GET request to this servlet.
    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // Set the MIME type for the response message
        response.setContentType("text/html");
        // Get a output writer to write the response message into the network socket
        PrintWriter out = response.getWriter();

        Connection conn = null;
        Statement stmt = null;

        try {
            // Step 1: Allocate a database Connection object
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:8888/ebookshop", "myuser", "xxxx"); // <== Check!
            // database-URL(hostname, port, default database), username, password

            // Step 2: Allocate a Statement object within the Connection
            stmt = conn.createStatement();

            // Step 3: Execute a SQL SELECT query
            String sqlStr = "select * from books where author = "
                    + "'" + request.getParameter("author") + "'"
                    + " and qty > 0 order by price desc";

            // Print an HTML page as the output of the query
            out.println("<html><head><title>Query Response</title></head><body>");
            out.println("<h3>Thank you for your query.</h3>");
            out.println("<p>You query is: " + sqlStr + "</p>"); // Echo for debugging
            ResultSet rset = stmt.executeQuery(sqlStr);  // Send the query to the server

            // Step 4: Process the query result set
            int count = 0;
            while (rset.next()) {
                // Print a paragraph <p>...</p> for each record
                out.println("<p>" + rset.getString("author")
                        + ", " + rset.getString("title")
                        + ", $" + rset.getDouble("price") + "</p>");
                count++;
            }
            out.println("<p>==== " + count + " records found =====</p>");
            out.println("</body></html>");
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

Do you have the msql driver in your class-path: `WEB-INF/lib/mysql.jar`? — Paul Vargas, Sep 08 '14 at 13:44
You need to add the MYSql driver to you classpath. Check this :http://stackoverflow.com/questions/13442747/set-the-classpath-in-order-to-connect-between-mysql-to-jdbc — AndreDuarte, Sep 08 '14 at 13:44
There's a good HOWTO at http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html — Gyro Gearless, Sep 08 '14 at 14:05

score 1 · Answer 1 · answered Sep 08 '14 at 13:45

1

You need to load your driver before you get a connection, something like that:

//Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");

Here is a nice example: http://www.tutorialspoint.com/jdbc/jdbc-sample-code.htm

answered Sep 08 '14 at 13:45

Bruno Franco

1,958
8
20

already used but not working. – Pushpendra Sep 08 '14 at 13:54
Is the drive lib in your project? – Bruno Franco Sep 08 '14 at 14:29

score 0 · Answer 2 · answered Sep 08 '14 at 13:44

you should add this line,

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

As it is the first step to establish connection with JDBC driver,

Connection conn = null;
      Statement stmt = null;
      try {
              Class.forName("com.mysql.jdbc.Driver");
         // Step 1: Allocate a database Connection object
         conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:8888/ebookshop", "myuser", "xxxx"); // <== Check!
            // database-URL(hostname, port, default database), username, password

         // Step 2: Allocate a Statement object within the Connection
         stmt = conn.createStatement();

Provide that you added the mysql connector jar in your buidpath

score 0 · Answer 3 · answered Sep 08 '14 at 15:06

As I see this jdbc:mysql://localhost:8888/ebookshop you have changed the default port of mysql from 3306 to 8888

If you did not change the port just use 3306 as the port for mysql

As the exception is saying No suitable driver found that obviously means you don't have the mysql-connector-[version].jar in your classpath If you are using eclipse just place the jar under WEB-INF/lib and if you are using standalone tomcat just place the driver jar in the lib folder of Tomcat

score 0 · Answer 4 · answered Sep 10 '14 at 09:54

try this

public static void connect() throws Exception {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String url = "jdbc:mysql://localhost:3306/database_name?autoReconnect=true";
    c = DriverManager.getConnection(url,"root","123");
}

thanx..

Connect Servlet to MySQL database in eclipse

4 Answers4