Unable to load suitable drive from java 18 on linux 24.02.1 wsl with mysql version 8.0.31

Question

I get the following error when trying to execute a Java program with SQL code:

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

I have installed the driver mysql-connector-j-8.0.31.jar in /usr/share/java

I have called the class with java -cp ./:/usr/share/java TestApplication and with CLASSPATH=./:/usr/share/java set.

My Java code is as follows:

import java.sql.*;

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/opiejbc1";
   static final String USER = "opiejbc1";
   static final String QUERY = "SELECT * FROM Test1";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, "");
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(QUERY);) {
         // Extract data from result set
         while (rs.next()) {
            // Retrieve by column name
            System.out.print("Name: " + rs.getInt("Name"));
            System.out.print(", Phone: " + rs.getInt("Phone"));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

I have tried inserting Class.forName("com.mysql.cj.jdbc.Driver"); immediately after the try statement, but then I get the following error:

Exception in thread "main" java.lang.Error: Unresolved compilation problem: 
        Syntax error on token(s), misplaced construct(s)

        at TestApplication.main(TestApplication.java:10)

What am I doing wrong?

I have tried all the recommended solutions as far as I know, but I still get those errors.

Answer 1

As noted by @g00se, the real problem is that the JAR file for the Connector/J driver is not on your runtime classpath. So the java runtime cannot find it.

Solution: put the JAR file on the classpath.

Notes:

1. If you use a -cp option, the CLASSPATH environment variable is ignored.
2. If you put a directory on the classpath, you are NOT telling the java to put JAR files (in that directory) on the classpath.
3. If you wanted to put all JAR files in (say) "/usr/share/java" on the classpath, you could use a wildcard entry; e.g. -cp .:/usr/share/java/*.jar. Note that the *.jar is not shell globbing. It needs to be processed by the java command. (In some cases you may need to escape it to prevent globbing.)
4. You should (IMO) take the time to read the Oracle documentation on how the classpath works.

---

Adding a Class.forName call is NOT recommended (except by people who don't understand the problem). If the drivers are on the classpath then DriverManager will find them. And if they are not on the classpath, then using Class.forName is going to fail.

But the reason that you got a compilation error was that you were inserting it at the wrong place:

 try ( // HERE is the wrong place
      Connection conn = DriverManager.getConnection(DB_URL, USER, "");
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(QUERY);) {

The try with resources syntax only allows variable declarations with initializers in that context. A Class.forName call is not such a thing.

I haven't checked, but I think that inserting

Class dummy = Class.forName("com.mysql.cj.jdbc.Driver");

at // HERE in the above would make the compiler happier. But DON'T. It is unnecessary. It won't help. See above for the correct solution.