CRUD Operation

Database operations are fundamental in software development, and understanding CRUD operations (Create, Read, Update, Delete) is essential. Let's explore how to perform CRUD operations in SQL using JDBC (Java Database Connectivity), a standard Java API for interacting with databases.

Note: Before you start, ensure you have a SQL database (e.g., MySQL, PostgreSQL) installed and running.

Setting Up the Database

For this tutorial, let's consider a simple "users" table with columns for id, name, and email. Create this table in your database:

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

Connecting to the Database with JDBC

To interact with the database, you need to establish a connection using JDBC. Here's a simple Java class that connects to the database:

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

public class DatabaseConnector {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/your_database_name";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

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

Replace "your_database_name", "your_username", and "your_password" with your actual database name, username, and password.

1. Creating (Inserting) Data

Let's insert a new user into the "users" table:

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

public class CreateOperation {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnector.connect()) {
            String insertQuery = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(insertQuery)) {
                preparedStatement.setString(1, "John Doe");
                preparedStatement.setString(2, "[email protected]");
                int rowsAffected = preparedStatement.executeUpdate();
                System.out.println(rowsAffected + " row(s) inserted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. Reading (Selecting) Data

Let's retrieve and display all users from the "users" table:

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

public class ReadOperation {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnector.connect()) {
            String selectQuery = "SELECT * FROM users";
            try (PreparedStatement preparedStatement = connection.prepareStatement(selectQuery)) {
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String email = resultSet.getString("email");
                    System.out.println("User ID: " + id + ", Name: " + name + ", Email: " + email);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. Updating Data

Let's update the email of a user with a specific ID:

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

public class UpdateOperation {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnector.connect()) {
            String updateQuery = "UPDATE users SET email = ? WHERE id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(updateQuery)) {
                preparedStatement.setString(1, "[email protected]");
                preparedStatement.setInt(2, 1); // Update user with ID 1
                int rowsAffected = preparedStatement.executeUpdate();
                System.out.println(rowsAffected + " row(s) updated successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Deleting Data

Let's delete a user with a specific ID:

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

public class DeleteOperation {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnector.connect()) {
            String deleteQuery = "DELETE FROM users WHERE id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery)) {
                preparedStatement.setInt(1, 2); // Delete user with ID 2
                int rowsAffected = preparedStatement.executeUpdate();
                System.out.println(rowsAffected + " row(s) deleted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}