JDBC (Java Database Connectivity) with MySQL: Step-by-Step Tutorial

Author:
In this guide, we will walk you through the steps required to connect a Java application to a MySQL database using JDBC (Java Database Connectivity). JDBC is an API that allows Java applications to interact with relational databases.

Table of Contents

  1. Introduction to JDBC
  2. Setting Up the MySQL Database
  3. JDBC Driver and Dependencies
  4. Establishing a Connection
  5. Creating a Table
  6. Inserting Data
  7. Reading Data
  8. Updating Data
  9. Deleting Data
  10. Closing the Connection
  11. Conclusion

1. Introduction to JDBC

JDBC (Java Database Connectivity) is a standard Java API that allows Java programs to access database management systems (DBMS). It provides methods to query and update data in a database.

Key Concepts

  • DriverManager: Manages a list of database drivers.
  • Connection: Interface with all methods for contacting a database.
  • Statement: Interface used to execute SQL queries.
  • ResultSet: Interface representing the result set of a query.
  • SQLException: Exception for handling errors in database access.

2. Setting Up the MySQL Database

First, make sure you have MySQL installed and running. Create a new database and table for this example.

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL
);

3. JDBC Driver and Dependencies

To connect to MySQL from Java, you need the MySQL JDBC driver. You can download it from the MySQL website.

Add the downloaded JAR file to your project's build path. If you are using Maven, add the following dependency to your pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

4. Establishing a Connection

Example Code

package com.example.jdbc;

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

public class JDBCConnection {

    private static final String URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            } else {
                System.out.println("Failed to connect to the database.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

  • URL: Database URL to connect to.
  • USER: Database username.
  • PASSWORD: Database password.
  • DriverManager.getConnection(): Establishes a connection to the database.

5. Creating a Table

Example Code

package com.example.jdbc;

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

public class CreateTableExample {

    private static final String CREATE_TABLE_SQL = "CREATE TABLE employees (" +
            "id INT AUTO_INCREMENT PRIMARY KEY," +
            "name VARCHAR(50) NOT NULL," +
            "email VARCHAR(50) NOT NULL," +
            "department VARCHAR(50) NOT NULL" +
            ");";

    public static void main(String[] args) {
        try (Connection connection = JDBCConnection.getConnection();
             Statement statement = connection.createStatement()) {
            statement.execute(CREATE_TABLE_SQL);
            System.out.println("Table created successfully!");
        } catch (SQLException e) {
            JDBCConnection.printSQLException(e);
        }
    }
}

Explanation

  • Statement: Used to execute static SQL statements.
  • execute(): Executes the given SQL statement.

6. Inserting Data

Example Code

package com.example.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertEmployee {

    private static final String INSERT_EMPLOYEES_SQL = "INSERT INTO employees (name, email, department) VALUES (?, ?, ?);";

    public static void main(String[] args) {
        try (Connection connection = JDBCConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEES_SQL)) {
            preparedStatement.setString(1, "Ravi Kumar");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.setString(3, "IT");

            preparedStatement.executeUpdate();
            System.out.println("Record inserted successfully!");
        } catch (SQLException e) {
            JDBCConnection.printSQLException(e);
        }
    }
}

Explanation

  • PreparedStatement: Used to execute parameterized SQL queries.
  • executeUpdate(): Executes the SQL statement and returns the number of affected rows.

7. Reading Data

Example Code

package com.example.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SelectEmployees {

    private static final String SELECT_ALL_EMPLOYEES = "SELECT * FROM employees;";

    public static void main(String[] args) {
        try (Connection connection = JDBCConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_EMPLOYEES)) {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String department = rs.getString("department");
                System.out.println(id + ", " + name + ", " + email + ", " + department);
            }
        } catch (SQLException e) {
            JDBCConnection.printSQLException(e);
        }
    }
}

Explanation

  • ResultSet: Represents the result set of a query.
  • executeQuery(): Executes the given SQL statement and returns a single ResultSet object.

8. Updating Data

Example Code

package com.example.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateEmployee {

    private static final String UPDATE_EMPLOYEES_SQL = "UPDATE employees SET name = ?, email = ?, department = ? WHERE id = ?;";

    public static void main(String[] args) {
        try (Connection connection = JDBCConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_EMPLOYEES_SQL)) {
            preparedStatement.setString(1, "Ravi Kumar Singh");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.setString(3, "IT");
            preparedStatement.setInt(4, 1);

            preparedStatement.executeUpdate();
            System.out.println("Record updated successfully!");
        } catch (SQLException e) {
            JDBCConnection.printSQLException(e);
        }
    }
}

Explanation

  • executeUpdate(): Executes the SQL statement and returns the number of affected rows.

9. Deleting Data

Example Code

package com.example.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeleteEmployee {

    private static final String DELETE_EMPLOYEES_SQL = "DELETE FROM employees WHERE id = ?;";

    public static void main(String[] args) {
        try (Connection connection = JDBCConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_EMPLOYEES_SQL)) {
            preparedStatement.setInt(1, 1);

            preparedStatement.executeUpdate();
            System.out.println("Record deleted successfully!");
        } catch (SQLException e) {
            JDBCConnection.printSQLException(e);
        }
    }
}

Explanation

  • executeUpdate(): Executes the SQL statement and returns the number of affected rows.

10. Closing the Connection

In all the above examples, we used the try-with-resources statement to automatically close JDBC resources, including the Connection, Statement, and ResultSet objects. This ensures that resources are properly closed and avoids potential memory leaks.

11. Conclusion

In this guide, we covered the basics of JDBC and demonstrated how to perform CRUD operations using JDBC with a MySQL database. By following these steps, you can connect a Java application to a MySQL database and execute SQL statements to create, read, update, and delete records.

Comments

Post a Comment

Leave Comment