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

This error comes when you are trying to connect to MySQL database from Java program using JDBC but either the JDBC driver for MySQL is not available in the classpath or it is not registered prior to calling the DriverManager.getConnection() method. In order to get the connection to the database, you must first register the driver using Class.forName() method. You should call this method with the correct the name of the JDBC driver "com.mysql.jdbc.Driver" and this will both load and register the driver with JDBC. The type 4 JDBC driver for MySQL is bundled into MySQL connector JAR e.g. mysql-connector-java-5.1.18-bin.jar depending upon which version of MySQL database you are connecting. Make sure this JAR is available in classpath before running your Java program, otherwise Class.forName() will not be able to find and load the class and throw java.lang.ClassNotFoundException: com.mysql.jdbc.Driver, another dreaded JDBC error, which we have seen in the earlier post.

Recently I have seen a common pattern of this error where a Java developer running his program Java SE 6 expects that JDBC driver's JAR will be automatically loaded by JVM because of autoloading of JDBC driver feature of JDBC 4.0 released in JDK 6 but misses the trick that the JDBC driver should also be JDBC 4.0 compliant e.g. mysql-connector-java-5.1.18-bin.jar will be automatically loaded but older version may not, even if you run on Java 6. See Core Java Volume 2 - Advanced Features to learn more about JDBC 4.0 features.


java.sql.SQLException: No suitable driver found for 'jdbc:mysql://localhost:3306/mysql


So, make sure you have both JDK 6 and a JDBC 4.0 compliant driver to leverage the auto-loading feature of JDBC 4.0 specification. 




How to reproduce "No suitable driver found for 'jdbc:mysql://localhost:3306/"

In order to better understand this error, let's first reproduce this error by executing following Java program. I expect this program to throw the "No suitable driver found for 'jdbc:mysql://localhost:3306/" error because I don't have JDBC driver in the classpath.


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

/*
 * Java Program to to reproduce
 * java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306
 * error which occurs if MySQL JDBC Driver JAR is missing
 * or you not registering the JDBC driver before calling 
 * DriverManager.getConnection() method in JDBC.
 */

public class MySQLTest {

    public static void main(String[] args) throws ClassNotFoundException {

        Connection con = null;

        try {
            String url = "jdbc:mysql://localhost:3306/mysql";
            String username = "root";
            String password = "root";

            // Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, username, password);

            if (con != 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/mysql
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:215)

You can see that we got the "No suitable driver found" error in JDBC. The reason was that JDBC API couldn't find any Driver corresponding to "jdbc:mysql://" URL because we have not added the MySQL connector JAR which contains the JDBC driver required to connect to MySQL database.

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



How to solve "No suitable driver found for jdbc:mysql://localhost:3306/mysql"

You can solve this problem first by adding MySQL connector JAR, which includes JDBC driver for MySQL into classpath e.g. mysql-connector-java-5.1.18-bin.jar. It's very easy, just download the JAR from MySQL website and drop it into your classpath. For Example, if you are running in Eclipse then you can drop it on the root of your project folder. Same is true for Netbeans, but if you are running your Java program from the command prompt then either use -cp option or set the CLASSPATH as described here.

I prefer -cp option because it's simple and easy and you can see what is included in classpath right in the command line itself, no need to worry about whether JAR is included in CLASSPATH environment variable or not.

java -cp mysql-connector-java-5.1.18-bin.jar:. MySQLTest

The error should go away just by adding JDBC driver in the classpath if you are running on Java 6, which supports JDBC 4.0 and the driver is also JDBC 4.0 compliant e.g. mysql-connector-java-5.1.36-bin.jar. From JDBC 4.0, Java has introduced auto loading of JDBC driver, hence you don't need to load or register it manually using Class.forName() method.

If you are not running on Java SE 6 or your JDBC driver version doesn't support JDBC 4.0 then just add the following line before calling DriverManager.getConnection() to load and register the MySQL JDBC driver. This will solve the problem (uncomment the line in above program):

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

This throws checked java.lang.ClassNotFoundException so makes sure you catch it. I have not caught it to keep the code clutter free by introducing try and catch statement.


So, in short:

1) Just add the MySQL JDBC JAR into classpath if you are running on Java SE 6 and driver is JDBC 4.0 compliant e.g. mysql-connector-java-5.1.36-bin.jar.

2) Alternatively, add the MySQL JDBC driver to classpath e.g. mysql-connector-java-5.1.18-bin.jar and call the Class.forName("com.mysql.jdbc.Driver"); to load and register the driver before calling DriverManager.getConnection() method.

You can also check out JDBC API Tutorial and Reference (3rd Edition) to learn more about new features introduced in JDBC 3.0 and JDBC 4.0 specification and it is also one of the best books to learn JDBC API in Java.

How to solve No suitable driver found for 'jdbc:mysql://localhost:3306/mysql



That's all about how to fix "No suitable driver found for jdbc:mysql://localhost:3306/mysql" error in Java. You can get this error from Eclipse or NetBeans IDE while connecting to local MySQL instance listening on default port 3306, don't afraid, just follow the same approach. Drop the MySQL JDBC driver and call the Class.forName() method with the name of the class with implements Driver interface from JDBC API.

Related JDBC Tutorials for Java Programmers
  1. How to connect to MySQL database from Java Program (Guide)
  2. How to connect to Oracle database from Java (Guide)
  3. How to connect to Microsoft SQL Server from Java (Guide)
  4. How to setup JDBC connection Pool in Spring + Tomcat (Guide)
  5. 10 JDBC Best Practices Java Programmer Should Follow (see here)
  6. 6 JDBC Performance Tips for Java Applications (see here)



No comments:

Post a Comment