Friday, July 23, 2021

How to Fix java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test [Solved]

The error "java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test" occurs when you try to connect MySQL database running on your localhost, listening on port 3306 port from Java program but either you don't have MySQL JDBC driver in your classpath or driver is not registered before calling the getConnection() method. Since JDBC API is part of JDK itself, when you write a Java program to connect any database like MySQL, SQL Server, or Oracle, everything compiles fine, as you only use classes from JDK but at runtime, when the JDBC driver which is required to connect to the database is not available, JDBC API either throws this error or "java.lang.ClassNotFoundException: com.mysql.jdbc.Driver".

The most common reason for this error is missing MySQL JDBC Driver JAR e.g. mysql-connector-java-5.0.8.jar not available in the classpath. Another common reason is you are not registering the driver before calling the getConnection() and you are running on Java version lower than 6 and not using a JDBC 4.0 compliant driver. We'll see these reasons in more detail in this article.

Btw, if you are new to JDBC and looking for a comprehensive online course to learn JDBC in-depth then I also suggest you check out these Complete JDBC Programming course on Udemy. It's a great course of direct classroom lectures and covers JDBC in depth



JAR not available in Classpath

If mysql-connector-java-5.0.8.jar is not available in classpath then you cannot connect to MySQL database from Java. Your program like below will compile fine but as soon as you will run it you will get the error "java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test" because of the JDBC URL format "jdbc:mysql" is not matching with any registered JDBC driver.



Here is our Java program to demonstrate this error. This program reproduces this error by first leaving out the required JDBC JAR from the classpath and also not explicitly registering the driver before use by not calling the Class.forName() method.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/*
 * Java Program to to connect to MySQL database and
 * fix java.sql.SQLException: No suitable driver found 
 * for jdbc:mysql://localhost:3306
 * error which occur if JAR is missing or you fail to register driver.
 */

public class Main {

  public static void main(String[] args) {

    Connection dbConnection = null;

    try {
      String url = "jdbc:mysql://localhost:3306/test";
      Properties info = new Properties();
      info.put("user", "root");
      info.put("password", "test");

      dbConnection = DriverManager.getConnection(url, info);

      if (dbConnection != null) {
        System.out.println("Successfully connected to MySQL database test");
      }

    } catch (SQLException ex) {
      System.out.println("An error occurred while connecting MySQL databse");
      ex.printStackTrace();
    }

  }

}

Output
An error occurred while connecting MySQL databse
java.sql.SQLException: No suitable driver found 
for jdbc:mysql://localhost:3306/test
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:187) 
at Main.main(Main.java:24))

How to solve java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/



You need to do two things in order to solve this problem:

1) Add mysql-connector-java-5.0.8.jar or any other MySQL JAR corresponding to the MySQL database you are connecting. If you don't have MySQL JDBC driver, you can download from here http://dev.mysql.com/downloads/connector/j/3.1.html

2) Add following line of code just before the call to Connection.getConnection(url, props) method

// load and register JDBC driver for MySQL
Class.forName("com.mysql.jdbc.Driver"); 

This will load the class, the JDBC driver to connect MySQL, com.mysql.jdbc.Driver from mysql-connector-java-5.0.8.jar and register it with JDBC API. Once you do that "java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test" will go away.


Also, it's worth noting that JDBC 4.0 released with Java SE 6 has now introduced auto-loading of JDBC driver class, which means you don't need Class.forName("com.mysql.jdbc.Driver"); any more, but only when you are running on at least Java 6 and your driver JAR is also JDBC 4.0 compliant

For example, the driver used in this program "mysql-connector-java-5.0.8.jar" is not JDBC 4.0 compliant, so even if you run this program in Java 6, 7 or Java 8, it will not work, but if you use mysql-connector-java-5.1.36.jar then even without adding "Class.forName("com.mysql.jdbc.Driver");", your program will work fine. Why? because JDBC will automatically load and register the driver, provided you have mysql-connector-java-5.1.36.jar file in your classpath. . See Core Java Volume 2 - Advanced features to learn more about new features introduces in JDBC 3.0 and JDBC 4.0 releases.

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test [Solution]


That's all about how to solve "java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test" error in Java program. This error occurs if JDBC is not able to find a suitable driver for the URL format passed to the getConnection() method e.g. "jdbc:mysql://" in our case. 

In order to solve this error, you need the MySQL JDBC driver like  mysql-connector-java-5.1.36.jar in your classpath. If you use a driver which is not JDBC 4.0 compliant then you also need to call the Class.forName("com.mysql.jdbc.Driver") method to load and register the driver.


Other JDBC troubleshooting guides for Java Programmers
  • How to connect to MySQL database from Java Program [steps]
  • How to connect Microsoft SQL Server from Java Program [guide]
  • How to solve java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver in Java? [solution]
  • How to connect Oracle database from Java and Eclipse? [guide]
  • How to fix java.lang.ClassNotFoundException: org.postgresql.Driver error in Java? [solution]
  • SQLServerException: The index 58 is out of range - JDBC [solution]
  • How to fix java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver in Java? [solution]
  • java.sql.SQLException: No suitable driver found for 'jdbc:mysql://localhost:3306/mysql [Solution]
  • java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [Solution]



10 comments :

Unknown said...

I was also getting the same error but could not find a single working solution and wasted my whole day after it. Finally, I myself was experimenting with every possibility taking some assistance from different websites such as this one, stack overflow, etc. After a gruesome 10 hrs, I was able to run my program. Here's a complete working solution to tackle the problem once and for all.

I am using jdk version 1.8 also known as java se 8. I would recommend using this and not jdk 11(latest till the date this is being written) because jdk 9 onwards do not include jre explicitly(Read more here: https://stackoverflow.com/questions/1906445/what-is-the-difference-between-jdk-and-jre). But jdk 8 takes care of it.

error which I was getting :
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/ebookshop?useSSL=false
at java.sql.DriverManager.getConnection(DriverManager.java:689)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at JdbcSelectTest.main(JdbcSelectTest.java:10)

The error is due to the reason that although you have downloaded mysql jdbc driver from here(http://dev.mysql.com/downloads/connector/j/3.1.html), if you have ,and unzipped it, you have not placed the executable jar file contained in the driver in its right place. So, while running the compiled java file, jre is not able to find the mysql jdbc driver.

Steps to resolve:
1. Download the mysql jdbc driver and unzip it (If you have not).
2. Open the unzipped folder named mysql-connector-java-8.0.13(version as on today's date) and copy the executable jar file named mysql-connector-java-8.0.13.
3. Now if you are doing something with webapps(Using Tomcat server, eclipse, java ee), then go to the location where tomcat files are placed and paste the jar file(mentioned in step 2) in the tomcat\bin\lib, for example in my case it is C:\Users\ujjwa\Desktop\Practice\Winter\apache-tomcat-9.0.14\lib
If not working with webapps, then ignore this step.
4. Go to the locations where jdk and jre are installed and paste the jar file in the ext directory, for example in my case they are
C:\Program Files\Java\jre1.8.0_191\lib\ext
C:\Program Files\Java\jdk1.8.0_191\jre\lib\ext
5. Then recompile the code using the command javac .java
6. Then run the file. It should work fine as in my case it was displaying desired output.

For starting up with new softwares and their installation, you can follow the links below.
1. http://www.ntu.edu.sg/home/ehchua/programming/howto/tomcat_howto.html
2. http://www.ntu.edu.sg/home/ehchua/programming/java/JDBC_Basic.html#MySQLJDBCDriverInstall
3. This link if for common errors during executing and installing the above mentioned sofwares. http://www.ntu.edu.sg/home/ehchua/programming/howto/ErrorMessages.html#JDBCErrors
4. For starting with eclipse http://www.ntu.edu.sg/home/ehchua/programming/howto/EclipseJava_HowTo.html#eclipse-java-install

Unknown said...

Also I am using Java version 1.8 and my java file is working with or without writing the line Class.forName("com.mysql.jdbc.Driver"); before DriverManager.getConnection(url,post) line

Anonymous said...

The way I fixed this error was I added the dependency in my POM file, because I use Maven for my Web Projects, added the same MySQL Connector to the library and clicked on download all dependencies for the project, over the Dependencies tab. All this in Netbeans.

Anonymous said...

thanks dude

Anonymous said...

Anyone know how to install JDBC on java 11?

javin paul said...

how to install JDBC? can you clarify more?

Anonymous said...

can you exactly tell me how and where to put dependecies code in pom.xml I have tried that method but it made the pom.xml error. I dont know where it supposed to be placed or added

sujila said...

Thank u so much..u saved my day..

This worked for me!!!
then go to the location where tomcat files are placed and paste the jar file(mentioned in step 2) in the tomcat\lib,

Read more: https://javarevisited.blogspot.com/2016/09/javasqlsqlexception-no-suitable-driver-mysql-jdbc-localhost.html#ixzz7LKDpH6dK

Anonymous said...

I am working with JSP and have a problem adding JDBC driver to my current project. I tried to build a path to jdbc library as well as copy and pasting JDBC jar directly into "lib" folder, but nothing works, i keep getting "No suitable driver found for jdbc:mysql://localhost:3306/mydb" error. Am i doing something wrong? is there other way to add jdbc?

javin paul said...

Which version of JAR are you adding? Are you putting inside tomcat/lib or your WEB-INF lib? also check if your MySQL server is running on localhost and port 3306 or not?

Post a Comment