🔧 Full Guide: CRUD Operations in Java using JDBC API

📝 What is JDBC?

JDBC (Java Database Connectivity) is an API provided by Java to connect and execute queries with databases. JDBC allows Java programs to interact with a wide range of relational databases like MySQL, PostgreSQL, Oracle, etc.

📚 Prerequisites

Java Development Kit (JDK) installed.
MySQL database (or any RDBMS of your choice).
MySQL JDBC Driver (Connector/J).
IDE (like IntelliJ IDEA, Eclipse, or VSCode).

🗃️ Database Schema

Let’s assume we have a simple users table.

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

🔌 JDBC Setup

Download the JDBC driver (e.g., mysql-connector-java-x.x.x.jar) and add it to your classpath.

💻 Java Project Structure

src/
│
├── DBConnection.java      // Database utility
├── User.java              // Model class
├── UserDAO.java           // DAO (CRUD logic)
└── Main.java              // Main app to test

1️⃣ DBConnection.java

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

public class DBConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password";

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

2️⃣ User.java (Model)

public class User {
    private int id;
    private String name;
    private String email;

    public User() {}

    public User(int id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    public User(String name, String email) {
        this.name = name;
        this.email = email;
    }

    // Getters and Setters
    // ...
}

3️⃣ UserDAO.java (CRUD Operations)

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {

    // CREATE
    public void insertUser(User user) {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getName());
            stmt.setString(2, user.getEmail());
            stmt.executeUpdate();
            System.out.println("User added successfully.");

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

    // READ
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users";

        try (Connection conn = DBConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                users.add(new User(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("email")
                ));
            }

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

    // UPDATE
    public void updateUser(User user) {
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getName());
            stmt.setString(2, user.getEmail());
            stmt.setInt(3, user.getId());
            stmt.executeUpdate();
            System.out.println("User updated successfully.");

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

    // DELETE
    public void deleteUser(int id) {
        String sql = "DELETE FROM users WHERE id = ?";
        try (Connection conn = DBConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            stmt.executeUpdate();
            System.out.println("User deleted successfully.");

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

4️⃣ Main.java (Testing CRUD)

import java.util.List;

public class Main {
    public static void main(String[] args) {
        UserDAO dao = new UserDAO();

        // Insert user
        User newUser = new User("John Doe", "[email protected]");
        dao.insertUser(newUser);

        // Get all users
        List<User> users = dao.getAllUsers();
        users.forEach(u -> System.out.println(u.getId() + ": " + u.getName() + " - " + u.getEmail()));

        // Update user
        if (!users.isEmpty()) {
            User firstUser = users.get(0);
            firstUser.setName("Jane Doe");
            dao.updateUser(firstUser);
        }

        // Delete user
        if (!users.isEmpty()) {
            dao.deleteUser(users.get(0).getId());
        }
    }
}

✅ Output Example

User added successfully.
1: John Doe - [email protected]
User updated successfully.
User deleted successfully.

💡 Best Practices

Always close JDBC resources (Connection, Statement, ResultSet) using try-with-resources.
Use PreparedStatement to prevent SQL injection.
Move credentials to a config file or environment variables.
Handle exceptions more gracefully in production (logging, custom exceptions).
Use a connection pool like HikariCP in real-world apps.

📦 Optional: Using Maven for Dependency Management

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

DEV Community