Connection to MySQL from Servlet not working

Question

I'm making a small application so I can post my notes from my classes to a server. I am using JDBC and MySQL for the database side and JSP/Servlets for the application side.

I wrote a stand-alone java application that is very similar to my servlet code and it worked fine; my servlet code is not working fine.

Attribute definitions:

static final String DB_URL = "jdbc:mysql://localhost/DATASET";
static final String USER = "user";
static final String PASS = "pass";
Connection con = null;
Statement s = null;

doPost Method:

    final String STATEMENT = "INSERT INTO FILE(upload_time, filename, class, data) VALUES(CURTIME(), '" + request.getParameter("file") + "', '" + request.getParameter("cls") + "', '" + request.getParameter("comm") + "')";
    final String STATEMENT2 = "INSERT INTO FILE(upload_time, filename, data) VALUES(CURTIME(), 'booga33.txt', 'ooga booga dooga22222222222222!')";

    try {
        con = DriverManager.getConnection(DB_URL, USER, PASS);
        s = con.createStatement();
        s.executeUpdate(STATEMENT2);
    }
    catch(SQLException i)
    {
        PrintWriter w = response.getWriter();
        w.println(i.getErrorCode());
    }
    catch(Exception e)
    {
        PrintWriter w = response.getWriter();
        w.println("error occured.");
    }

It is throwing an SQLException and giving me an error code of 0, which is an invalid login. The username/password is correct but I believe the URL is malformed (even though this is exactly the database-url I was using in my java application. Also, /DATASET is the correct name for my schema.

On a seperate note, I know there are problems with my implementation if it is used by many users; I just want to get it working with a single user and then I will worry about many.

Edit: I am using tomcat as a webserver for my JSP/Servlet pages, I also have the mysql-connector placed in the Referenced Libraries section of my project (Eclipse).

Any help on this is greatly appreciated!!!!

EDIT:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/DATASET
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at DoSubmit.doPost(DoSubmit.java:38)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:495)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:767)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1347)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
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)

SOLUTION: Place MySQL Connector JAR into WEB-INF->lib if using eclipse IDE. Also, Class.forName(driver); is needed prior to establishing connection.

When you get an exception, you need to print out the stacktrace with `e.printStackTrace();` (it's written to stdout, so it'll be somewhere in the tomcat logs). That will give you a better error message than "errorcode 0" or "error occurred". — Kayaman, Feb 02 '17 at 12:21
I may have found the problem. I got the message from the error and it states 'No suitable driver found for jdbc:mysql://localhost:3306/DATASET' — Feek, Feb 02 '17 at 12:28
@BalajiKrishnan That is an incredibly useless suggestion, the driver already defaults to 3306 if no port is included in the connection string. — Mark Rotteveel, Feb 02 '17 at 16:40

Youcef LAIDANI · Accepted Answer · 2017-09-10T18:24:39.507

5

No suitable driver found for jdbc:mysql://localhost:3306/DATASET

First make to put your JDBC Connector in your class path you can download it from here

If you are using maven use this :

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.13</version>
</dependency>

Second make sure that your information of connection is correct :

public class CreateConnection {
    String driver = "com.mysql.jdbc.Driver";
    String DB_username = "root";
    String DB_password = "pass";
    String DB_URL = "jdbc:mysql://localhost:3306/DATABASE";

    public Connection getConnection() {
        try {
            Class.forName(driver);
            java.sql.Connection con = DriverManager.getConnection(DB_URL, DB_username, DB_password);
            return con;

        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Exception " + e);
            return null;
        }
    }
}

I suggest to use Prepared Statement instead to your way, this can be a safe a practice way, you can learn about this here Prepared Statement

EDIT

Like @Gimby said : sure that your jar is in the right place, so if you are using Eclipse use How to place a file on classpath in eclipse? if Netbeans use How to setup classpath in Netbeans?

edited Sep 10 '17 at 18:24

answered Feb 02 '17 at 12:30

Youcef LAIDANI

55,661
15
90
140

I have placed mysql-connector-java-5.1.40-bin.jar into my class path. When I put in the line "Class.forName(...)" I am getting the starting error of "java.lang.ClassNotFoundException: com.mysql.jdbc.Driver" I'm not sure why. – Feek Feb 02 '17 at 12:38
try to use my class like is it @Feek – Youcef LAIDANI Feb 02 '17 at 12:42
I am using your class as is and I am not using maven. I am still getting a classnotfoundexception on com.mysql.jdbc.Driver... I feel this is going to be something very silly that I'm messing up on. – Feek Feb 02 '17 at 12:48
2

@feek it doesn't work because it is not actually in the classpath when running the application. There must be something wrong in how you build and deploy the application. To be more specific: the mysql jar should end up in WEB-INF/lib, but it isn't. – Gimby Feb 02 '17 at 12:48
Feek like @Gimby said you don't put your jar in the right place – Youcef LAIDANI Feb 02 '17 at 12:57
1

@Gimby that was the problem. I was watching a web-tutorial for stand-alone java applications and they had made their own lib folder in eclipse. When I placed it in there it works correctly. Thank you! – Feek Feb 02 '17 at 12:58

zillani · Answer 2 · 2017-02-02T13:02:55.737

0

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


public class Test {
    static final String DB_URL = "jdbc:mysql://localhost:3306/DATASET";
    static final String USER = "user";
    static final String PASS = "pass";
    static Connection con = null;
    static Statement s = null;
    public static void main(String[]args){
        final String query = "INSERT INTO FILE(upload_time, filename, data) VALUES(CURTIME(), 'booga33.txt', 'ooga booga dooga22222222222222!')";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(DB_URL, USER, PASS);
            s = con.createStatement();
            //s.executeUpdate(query);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

}

edited Feb 02 '17 at 13:02

answered Feb 02 '17 at 12:19

zillani

1,070
3
18
26

I tried this. It didn't work for me and my server is being run on 3306 as well. – Feek Feb 02 '17 at 12:27
Oh, Can you print the exception log ? using e.printStackTrace(); – zillani Feb 02 '17 at 12:28
Oh, got it, you need to put the appropriate jdbc driver https://mvnrepository.com/artifact/mysql/mysql-connector-java/5.1.6 Click on download jar and put this in you lib, after copying this to your lib right click on it and select add to build path. – zillani Feb 02 '17 at 12:39
I also see that you need to put this one Class.forName("com.mysql.jdbc.Driver"); above con = DriverManager.getConnection(DB_URL, USER, PASS); – zillani Feb 02 '17 at 12:43
@zillani no you don't. That's not been necessary for a very long time now. – Gimby Feb 02 '17 at 12:46
@Feek Did you right click on the jar and gave add to build path and then go to project > clean and restart the server ? – zillani Feb 02 '17 at 12:52
@Feek I have edited my answer, please check it, try to run this simple main class if you still get the exception that means the jar is not in the right place – zillani Feb 02 '17 at 13:04

score 0 · Answer 3 · edited Feb 02 '17 at 13:08

0

//Add MySQL jdbc jar file into project lib.

public static Connection con; 
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Database name", userid,password);

edited Feb 02 '17 at 13:08

Raymond

2,276
2
20
34

answered Feb 02 '17 at 12:39

Mr. Pawan kumar

1
2

1

Please elaborate a bit on your answer to help the question asker to understand how your answer helps. – Raymond Feb 02 '17 at 12:50
1

Welcome to stack overflow :-) Please look at [answer]. You should provide some information why your code solves the problem. Code-only answers aren't useful for the community. – JimHawkins Feb 02 '17 at 15:54
Welcome to Stack Overflow! While you may have solved this user's problem, code-only answers are not very helpful to users who come to this question in the future. Please edit your answer to explain why your code solves the original problem. – Joe C Feb 02 '17 at 21:03

Connection to MySQL from Servlet not working

3 Answers3

Linked