Interacting with MySQL Using Java (JDBC)

This lab demonstrates how to connect to a MySQL database using Java and JDBC. Instead of Python and mysql-connector-python, we will use the official MySQL Connector/J driver.

Install MySQL Connector/J

If you are using Maven, include the following dependency:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

If using a standalone JAR, download it from the MySQL website and add it to your classpath.

Create a MySQL User

You should avoid connecting as root from your applications. Below is how to create a dedicated user with required permissions:

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

This user will have permissions to create, insert, update, and retrieve data across databases.

Connecting to MySQL Using Java

The following Java code connects to a local MySQL database named dbms:

import java.sql.*;

public class ConnectExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/dbms";
        String user = "sammy";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to database!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation: JDBC uses a URL-based system to determine which driver to load. The MySQL driver is automatically detected when the Connector/J library is in your classpath.

Running a SELECT Query

String query = "SELECT * FROM company";
try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(query)) {

    while (rs.next()) {
        System.out.println(rs.getString(1) + " " + rs.getInt(2) + " " + rs.getString(3));
    }
}

Explanation: JDBC's ResultSet allows you to iterate over each row returned from a query.

Insert Data Into the Database

String insert = "INSERT INTO company VALUES ('BMW', 10, 'Germany')";
try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    stmt.executeUpdate(INSERT);
    conn.COMMIT();

    System.out.println("INSERT committed.");
}

Important: Some JDBC configurations require conn.setAutoCommit(false) if you want manual control. If autocommit is enabled (default), inserts commit automatically.

Printing After Insert

If the insert is committed, reconnecting and selecting again will show the updated data.

String query = "SELECT * FROM company";
try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(query)) {

    while (rs.next()) {
        System.out.println(rs.getString(1) + " " + rs.getInt(2) + " " + rs.getString(3));
    }
}

Because changes were committed, they will persist even after reconnect.