Saturday, September 3, 2016

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

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 database is not available, JDBC API either throws this error or "java.lang.ClassNotFoundException: com.mysql.jdbc.Driver".

Most common reason of this error is missing MySQL JDBC Driver JAR e.g. mysql-connector-java-5.0.8.jar not available in 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.


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 reproduce this error by first leaving out the required JDBC JAR from classpath and also not explicitly registering the driver before use by not calling 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 getConnection() method e.g. "jdbc:mysql://" in our case. In order to solve this error, you need the MySQL JDBC driver e.g. 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]


Further Learning
JSP, Servlets and JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 and 2
Java Platform: Working with Databases Using JDBC

3 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.

Post a Comment