MySQL CRUD Tutorials in Java: A Step-by-Step Guide

MySQL is one of the most popular open-source relational database management systems (RDBMS). In this tutorial, we will walk you through the process of performing CRUD (Create, Read, Update, Delete) operations in MySQL using Java. We’ll provide step-by-step instructions and practical examples with detailed explanations.

Prerequisites

Before you begin, make sure you have the following prerequisites in place:

Java Development Kit (JDK): Ensure you have Java installed on your system. You can download it from the official Oracle website or use an open-source alternative like OpenJDK.
Integrated Development Environment (IDE): Choose an IDE like Eclipse, IntelliJ IDEA, or NetBeans to write and manage your Java code.
MySQL: You need to have a MySQL server installed and running. You can download and install MySQL from the official MySQL website or use a package manager if you’re on a Linux system.
MySQL JDBC Driver: You’ll need the MySQL JDBC driver to connect to the MySQL database from your Java application. Download the MySQL JDBC driver JAR file and include it in your project’s classpath.

Step 1: Create a MySQL Database

Let’s start by creating a MySQL database where we will perform CRUD operations. You can use the MySQL command-line client or a graphical tool like phpMyAdmin. Here’s how to do it using the command-line client:

# Start the MySQL command-line client (you may need to provide your MySQL username and password)
mysql -u your_username -p

# Create a new database
CREATE DATABASE mydb;

This code will create a new database named mydb.

Step 2: Set Up a Java Project

Open your preferred IDE and create a new Java project.
Add the MySQL JDBC driver dependency to your project’s build file (e.g., Maven’s pom.xml or Gradle’s build.gradle).

For Maven, add the following dependency:

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

For Gradle, add the following dependency:

implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.26'

Step 3: Perform CRUD Operations

Create Operation (Insert)

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

public class MySQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String insertQuery = "INSERT INTO employees (first_name, last_name, age) VALUES (?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
            preparedStatement.setString(1, "John");
            preparedStatement.setString(2, "Doe");
            preparedStatement.setInt(3, 30);
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code inserts a new employee record into the employees table in the mydb database.

Read Operation (Select)

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

public class MySQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String selectQuery = "SELECT * FROM employees";
            PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                int age = resultSet.getInt("age");

                System.out.println("ID: " + id + ", Name: " + firstName + " " + lastName + ", Age: " + age);
            }

            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code retrieves and prints all employee records from the employees table.

Update Operation (Update)

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

public class MySQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String updateQuery = "UPDATE employees SET age = ? WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
            preparedStatement.setInt(1, 31);
            preparedStatement.setString(2, "John");
            preparedStatement.setString(3, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code updates the age of the employee named “John Doe” in the employees table.

Delete Operation (Delete)

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

public class MySQLCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydb";
        String username = "your_username_here";
        String password = "your_password_here";

        try {
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            String deleteQuery = "DELETE FROM employees WHERE first_name = ? AND last_name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery);
            preparedStatement.setString(1, "John");
            preparedStatement.setString(2, "Doe");
            preparedStatement.executeUpdate();

            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This code deletes the employee named “John Doe” from the employees table.

Conclusion

In this tutorial, you’ve learned how to perform CRUD operations in MySQL using Java. You created a MySQL database, set up a Java project, and wrote code for Create, Read, Update, and Delete operations with detailed explanations. MySQL is a versatile RDBMS used in various types of applications, and these fundamental database operations will serve as a solid foundation for building data-driven applications. As you continue your journey in Java development and MySQL, you can expand on these concepts and build more complex database-driven applications. Happy coding!