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

MariaDB is a popular open-source relational database management system (RDBMS) and a MySQL-compatible database. In this tutorial, we will walk you through the process of performing CRUD (Create, Read, Update, Delete) operations in MariaDB 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.
MariaDB: You need to have a MariaDB server installed and running. You can download and install MariaDB from the official MariaDB website or use a package manager if you’re on a Linux system.
MariaDB JDBC Driver: You’ll need the MariaDB JDBC driver to connect to the MariaDB database from your Java application. Download the MariaDB JDBC driver JAR file and include it in your project’s classpath.

Step 1: Create a MariaDB Database

Let’s start by creating a MariaDB database where we will perform CRUD operations. Open your MariaDB client or use the command-line tool and execute the following SQL command:

CREATE DATABASE mydb;

This command 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 MariaDB JDBC driver JAR file to your project’s classpath.

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 MariaDBCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mariadb://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 MariaDBCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mariadb://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 MariaDBCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mariadb://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 MariaDBCRUDExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mariadb://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 MariaDB using Java. You created a MariaDB database, set up a Java project, and wrote code for Create, Read, Update, and Delete operations with detailed explanations. These fundamental database operations are essential for building data-driven applications. You can further enhance and extend this knowledge as you work on more complex database applications. Happy coding!