java.sql.SQLException: No suitable driver found (SQL Server Express)

Question

I'm going crazy and none of the pre-existing solutions seem to work for me. I'm old-school, so just coding in NotePad++/EditPlus and compiling via CLI. I've got a super generic DB.JAVA file and I can't get passed the attempt to load the SQL Driver Class. I'm at a loss and hoping others can help. Long term, this will become used in a JSP on Tomcat.

Java Version: Either 1.8.0_181 or 10.0.2 (I have both, x64 builds)

SQL Server: 2017 Express

JDBC JAR: mssql-jdbc-6.4.0.jre8.jar (presently in the same dir as DB.java)

My basic code:

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class DB {

    public static void main(String[] args) {

        // Create a variable for the connection string.
        String connectionURL = "jdbc:microsoft:sqlserver://HomeServer:1433;databaseName=MY_FIRST_DB";

        // Declare the JDBC objects.
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // I've read this isn't needed anymore as the DriverManager is smart enough
            // It will fail on this line, or the next if I comment it out with the same error
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionURL,"sa","xxxxxxxxxxxx");

            // Create and execute an SQL statement that returns a
            // set of data and then display it.
            String SQL = "SELECT * FROM v_Users";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                System.out.println(rs.getString("Username") + ":" + rs.getString("Email"));
            }
        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }

        finally {
            if (rs != null) try { rs.close(); } catch(Exception e) {}
            if (stmt != null) try { stmt.close(); } catch(Exception e) {}
            if (con != null) try { con.close(); } catch(Exception e) {}
        }
    }

}

Compiles: fine with "%JAVA_HOME%"/bin/javac.exe -cp ./* DB.java

Run: "%JAVA_HOME%"\bin\java.exe -cp ./ DB

Error when I run it, no matter what -cp I give it. If I comment the Class.forName then I get a generic "no suitable driver found":

java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
        at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:264)
        at DB.main(DB.java:19)

Answer 1

Correct way to compile code is:

javac -cp .;mssql-jdbc-6.4.0.jre8.jar DB.java

Correct way to run code is:

java -cp .;mssql-jdbc-6.4.0.jre8.jar DB

Both assumes that DB.java and mssql-jdbc-6.4.0.jre8.jar are in the current directory, and that you're using Java 8.

You will of course also need to fix the connection URL, i.e. remove microsoft:

String connectionURL = "jdbc:sqlserver://HomeServer:1433;databaseName=MY_FIRST_DB";

This is all documented here: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-2017