MySQL - Java Syntax

Modern MySQL and Java Integration

To connect a Java application with a MySQL database, we use the Java Database Connectivity (JDBC) API. The official JDBC driver for MySQL is called MySQL Connector/J. This guide covers the modern approach to using JDBC, emphasizing best practices like dependency management, secure coding, and proper resource handling.

Project Setup with a Build Tool (Maven/Gradle)

Manually downloading and managing JAR files is an outdated practice. Modern Java projects use build automation tools like Maven or Gradle to handle dependencies. This ensures consistency and simplifies project setup.

Using Maven

Add the following dependency to your pom.xml file:

<dependencies>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.0.33</version> <!-- Or the latest version -->
    </dependency>
</dependencies>

Using Gradle

Add the following to your build.gradle file’s dependencies block:

dependencies {
    implementation 'com.mysql:mysql-connector-j:8.0.33' // Or the latest version
}

With modern JDBC drivers (version 4.0 and newer), you no longer need to manually load the driver with Class.forName(). The driver is automatically registered when it’s on the classpath.

Establishing a Database Connection

A connection is established using the DriverManager.getConnection() method. It’s crucial to manage database resources (Connection, Statement, ResultSet) correctly to prevent resource leaks. The try-with-resources statement, introduced in Java 7, is the standard way to do this, as it guarantees that each resource is closed at the end of the statement.

String url = "jdbc:mysql://localhost:3306/your_database?serverTimezone=UTC";
String user = "your_username";
String password = "your_password";

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    // Use the connection here
} catch (SQLException e) {
    e.printStackTrace();
}

Executing Queries with PreparedStatement

To prevent SQL injection attacks, you must always use PreparedStatement for queries that include user-supplied data. It pre-compiles the SQL query and safely substitutes parameters, making it both more secure and often more performant than a plain Statement.

String sql = "SELECT id, name, email FROM users WHERE id = ?";

try (Connection conn = ...; // establish connection
     PreparedStatement pstmt = conn.prepareStatement(sql)) {

    pstmt.setInt(1, userId); // Safely set the user ID parameter

    try (ResultSet rs = pstmt.executeQuery()) {
        // Process results
    }
}

Processing Results from a ResultSet

The executeQuery() method returns a ResultSet object, which acts as a pointer to the rows returned by your query. You iterate through it using a while (rs.next()) loop.

while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    String email = rs.getString("email");

    System.out.printf("ID: %d, Name: %s, Email: %s%n", id, name, email);
}

Handling Transactions

When you need to execute multiple statements as a single atomic unit, you must manage transactions manually. This involves disabling auto-commit, executing your statements, and then either commit() on success or rollback() on failure.

conn.setAutoCommit(false); // Start transaction
try {
    // ... execute multiple update statements ...
    conn.commit(); // Finalize transaction
} catch (SQLException e) {
    conn.rollback(); // Revert changes on error
}

Complete, Runnable Example

This example demonstrates creating a table, inserting data securely with PreparedStatement, and then querying that data. It uses try-with-resources for all JDBC objects.

import java.sql.*;

public class ModernMySQLExample {

    // Replace with your database details
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS?serverTimezone=UTC&createDatabaseIfNotExist=true";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            System.out.println("Database connection established.");

            setupTable(conn);
            insertUser(conn, "Alice", "alice@example.com");
            insertUser(conn, "Bob", "bob@example.com");
            queryUsers(conn);

        } catch (SQLException e) {
            System.err.println("Database Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void setupTable(Connection conn) throws SQLException {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                                "id INT AUTO_INCREMENT PRIMARY KEY," +
                                "name VARCHAR(100) NOT NULL," +
                                "email VARCHAR(100) NOT NULL UNIQUE" +
                                ");";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSQL);
            System.out.println("Table 'users' is ready.");
        }
    }

    private static void insertUser(Connection conn, String name, String email) throws SQLException {
        String insertSQL = "INSERT INTO users(name, email) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.printf("Successfully inserted user: %s%n", name);
            }
        }
    }

    private static void queryUsers(Connection conn) throws SQLException {
        String selectSQL = "SELECT id, name, email FROM users";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(selectSQL)) {

            System.out.println("\n--- Current Users ---");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.printf("ID: %-3d | Name: %-10s | Email: %s%n", id, name, email);
            }
            System.out.println("---------------------");
        }
    }
}