Java JDBC Connection with MySQL Driver in VS Code + Troubleshooting

It's 2022 and not much has changed with the way we configure JDBC Connection with MySQL Driver in Java apart from the driver class has changed path from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver, if you use the old one you will get a warning message in the console,

Loading class `com.mysql.jdbc.Driver'. 
This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. 
The driver is automatically registered via the SPI and manual loading
of the driver class is generally unnecessary.

In this Tutorial, we have made use of VS Studio Code to write a simple MySQL JDBC Example.

Table:

mysql> desc student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int          | NO   | PRI | NULL    |       |
| student_name | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table student;
CREATE TABLE `student` (
  `student_id` int NOT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> insert into student values(1,"Sam");
Query OK, 1 row affected (0.02 sec)

mysql> insert into student values(2,"Luke");
Query OK, 1 row affected (0.02 sec)

mysql> insert into student values(3,"Andy");
Query OK, 1 row affected (0.02 sec)

Downloading the MySQL Driver/ConnectorJ jar file

We would need to get the MySQL Driver jar file in order to successfully execute our program, we can download the Platform Independent (mysql-connector-java-8.0.30.zip) file from the below link,

https://dev.mysql.com/downloads/connector/j/8.0.html

Adding MySQL Connector jar file to ClassPath in VS Code

Open the Pallete (Shift + Command + P on macOS, or, Shift + Control + P on Windows) and Type: Java: Configure Classpath and add the jar file we just downloaded as Reference Library,

Code Example:

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

public class JavaJDBCExample {

    private static String MYSQL_JDBC_DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    private static String MYSQL_DB_URL = "jdbc:mysql://localhost:3306/my_uat";
    private static String MYSQL_DB_USER = "root";
    private static String MYSQL_DB_USER_PASSWORD = "root123";
    private static String SQL_QUERY = "Select * from student";


    public static void main(String[] args) {
            
        try(Connection connection = DriverManager.getConnection(MYSQL_DB_URL,MYSQL_DB_USER,MYSQL_DB_USER_PASSWORD)) {

            Class.forName(MYSQL_JDBC_DRIVER_CLASS); 
            Statement statement =connection.createStatement();  
            ResultSet resultSet = statement.executeQuery(SQL_QUERY); 

            while(resultSet.next())  {
                System.out.println(resultSet.getInt(1)+"  "+resultSet.getString(2));
            }     
                
        } catch (ClassNotFoundException e) {
            System.out.println("MySQL Driver class not found!");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Error occured while executing query: " + SQL_QUERY);
            e.printStackTrace();
        } 
    }

}

Output:

1 Sam
2 Luke
3 Andy

Troubleshooting MySQL JDBC Example Exceptions:

Connector jar not added to classpath

Note if the jar file is not properly imported you will get java.lang.ClassNotFoundException exception.

MySQL Driver class not found!
java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Drive
        at java.net.URLClassLoader.findClass(URLClassLoader.java:387)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:352)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:264)
        at JavaJDBCExample.main(JavaJDBCExample.java:20)

Invalid Database name:

If you provide a wrong database name, you will get a java.sql.SQLSyntaxErrorException: Unknown database error,

java.sql.SQLSyntaxErrorException: Unknown database 'my_ua'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at JavaJDBCExample.main(JavaJDBCExample.java:18)

Invalid Database URL or Database Down:

If you provide an invalid DB URL or the Database is down you will get CommunicationsException

Error occured while executing query: Select * from student

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
        ....
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at JavaJDBCExample.main(JavaJDBCExample.java:18)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        ....
        at com.mysql.cj.NativeSession.connect(NativeSession.java:120)
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:948)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:818)
        ... 6 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:607)
        at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:153)
        at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:63)
        ... 9 more

Invalid Username or Password:

You will get access denied if you provide an invalid username or password,

Error occured while executing query: Select * from student
java.sql.SQLException: Access denied for user 'roo'@'172.19.0.1' (using password: YES)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at JavaJDBCExample.main(JavaJDBCExample.java:18)

Invalid SQL Query:

If the SQL query is incorrect you will get java.sql.SQLSyntaxErrorException

Error occured while executing query: Select * rom student
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rom student' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
        at JavaJDBCExample.main(JavaJDBCExample.java:22)