JDBC CRUD Example Tutorial

Author:
In this tutorial, we will create a simple JDBC application to perform CRUD (Create, Read, Update, Delete) operations on a MySQL database. We will use the latest JDBC features and best practices, including using try-with-resources for resource management.

Table of Contents

  1. Introduction
  2. Technologies Used
  3. Database Setup
  4. Project Setup
  5. Creating the JDBC Utility Class
  6. CRUD Operations
    • Create
    • Read
    • Update
    • Delete
  7. Testing CRUD Operations
  8. Conclusion

Check out the complete JDBC tutorial: JDBC Tutorial.

1. Introduction

Java Database Connectivity (JDBC) is an API that provides industry-standard and database-independent connectivity between Java applications and relational databases. JDBC allows Java applications to perform database operations like querying, updating, and retrieving data from relational databases, spreadsheets, and flat files.

To keep it simple, JDBC allows a Java application to connect to a relational database. Major databases such as Oracle, Microsoft SQL Server, DB2, and many others are supported.

JDBC Database Connection

In this tutorial, we will demonstrate how to use JDBC to perform CRUD operations on a MySQL database.

2. Technologies Used

  • JDK 21
  • MySQL 8.0
  • JDBC API 4.2
  • IDE (Eclipse, IntelliJ IDEA, etc.)

3. Database Setup

First, ensure you have MySQL installed and running. Create a database and a table for this tutorial.

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,
    country VARCHAR(50) NOT NULL,
    salary DOUBLE NOT NULL
);

4. Project Setup

Set up your Java project and add the MySQL JDBC driver to your project's build path. You can download the JDBC driver from MySQL Connector/J.

5. Creating the JDBC Utility Class

Create a utility class to handle the database connection.

package com.example.jdbc;

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

public class JDBCUtils {

    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 printSQLException(SQLException ex) {
        for (Throwable e : ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

6. CRUD Operations

Create

This operation inserts a new record into the database.

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, country, salary) VALUES (?, ?, ?, ?);";

    public void insertRecord(String name, String email, String country, double salary) throws SQLException {
        // Establishing a Connection
        try (Connection connection = JDBCUtils.getConnection();
             // Creating a statement using connection object
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEES_SQL)) {
            // Setting parameters
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, email);
            preparedStatement.setString(3, country);
            preparedStatement.setDouble(4, salary);
            // Executing the query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            JDBCUtils.printSQLException(e);
        }
    }
}

Read

This operation reads and retrieves records from the database.

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 void selectAllRecords() throws SQLException {
        // Establishing a Connection
        try (Connection connection = JDBCUtils.getConnection();
             // Creating a statement using connection object
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_EMPLOYEES)) {
            // Executing the query and getting the result set
            ResultSet rs = preparedStatement.executeQuery();
            // Processing the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String country = rs.getString("country");
                double salary = rs.getDouble("salary");
                System.out.println(id + ", " + name + ", " + email + ", " + country + ", " + salary);
            }
        } catch (SQLException e) {
            JDBCUtils.printSQLException(e);
        }
    }
}

Update

This operation updates existing records in the database.

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 = ?, country = ?, salary = ? WHERE id = ?;";

    public void updateRecord(int id, String name, String email, String country, double salary) throws SQLException {
        // Establishing a Connection
        try (Connection connection = JDBCUtils.getConnection();
             // Creating a statement using connection object
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_EMPLOYEES_SQL)) {
            // Setting parameters
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, email);
            preparedStatement.setString(3, country);
            preparedStatement.setDouble(4, salary);
            preparedStatement.setInt(5, id); // Updating record with the provided ID
            // Executing the query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            JDBCUtils.printSQLException(e);
        }
    }
}

Delete

This operation deletes a record from the database.

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 void deleteRecord(int id) throws SQLException {
        // Establishing a Connection
        try (Connection connection = JDBCUtils.getConnection();
             // Creating a statement using connection object
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_EMPLOYEES_SQL)) {
            // Setting parameter
            preparedStatement.setInt(1, id); // Deleting record with the provided ID
            // Executing the query
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            JDBCUtils.printSQLException(e);
        }
    }
}

7. Testing CRUD Operations

Now, let's create a main class to test all the CRUD operations.

package com.example.jdbc;

import java.sql.SQLException;

public class TestCRUDOperations {

    public static void main(String[] args) {
        InsertEmployee insertEmployee = new InsertEmployee();
        SelectEmployees selectEmployees = new SelectEmployees();
        UpdateEmployee updateEmployee = new UpdateEmployee();
        DeleteEmployee deleteEmployee = new DeleteEmployee();

        try {
            // Insert records
            insertEmployee.insertRecord("Ravi Kumar", "ravi.kumar@example.com", "India", 50000);
            insertEmployee.insertRecord("Sita Sharma", "sita.sharma@example.com", "India", 60000);
            insertEmployee.insertRecord("Rahul Jain", "rahul.jain@example.com", "India", 55000);

            // Select records
            System.out.println("After inserting records:");
            selectEmployees.selectAllRecords();

            // Update record
            updateEmployee.updateRecord(1, "Ravi Kumar Singh", "ravi.kumar@example.com", "India", 55000);

            // Select records
            System.out.println("After updating record with ID 1:");
            selectEmployees.selectAllRecords();

            // Delete record
            deleteEmployee.deleteRecord(3);

            // Select records
            System.out.println("After deleting record with ID 3:");
            selectEmployees.selectAllRecords();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

8. Conclusion

In this tutorial, we demonstrated how to use JDBC to perform CRUD operations on a MySQL database. JDBC allows you to interact with any relational database from your Java applications. This guide covered establishing a connection, creating a table, and performing insert, read, update, and delete operations using the JDBC API. Proper exception handling and resource management using try-with-resources were also emphasized to ensure the application's robustness.

Comments

Post a Comment

Leave Comment