CRUD Operations in JDBC: A Step-by-Step Guide

Learn to Create, Read, Update, and Delete Data with Java Database Connectivity

CRUD (Create, Read, Update, Delete) forms the foundation of database interactions. In JDBC, these operations are performed using SQL queries executed through Statement or PreparedStatement. Let’s implement each operation with practical examples.

1. Setting Up the Database

Create a sample table to work with:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Create (INSERT)

Add new records to the database using PreparedStatement:

public void createEmployee(String name, String position, double salary) {
    String sql = "INSERT INTO employees (name, position, salary) VALUES (?, ?, ?)";
    
    try (Connection conn = DriverManager.getConnection(url, user, password);
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        pstmt.setString(1, name);
        pstmt.setString(2, position);
        pstmt.setDouble(3, salary);
        pstmt.executeUpdate();
        
        System.out.println("Employee added!");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Usage:

createEmployee("Alice", "Software Engineer", 85000.00);

3. Read (SELECT)

Retrieve data from the database and process the ResultSet:

public void readEmployees() {
    String sql = "SELECT * FROM employees";
    
    try (Connection conn = DriverManager.getConnection(url, user, password);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String position = rs.getString("position");
            double salary = rs.getDouble("salary");
            
            System.out.printf("ID: %d, Name: %s, Position: %s, Salary: $%.2f%n", 
                id, name, position, salary);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

4. Update (UPDATE)

Modify existing records using parameterized queries:

public void updateEmployeePosition(int id, String newPosition) {
    String sql = "UPDATE employees SET position = ? WHERE id = ?";
    
    try (Connection conn = DriverManager.getConnection(url, user, password);
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        pstmt.setString(1, newPosition);
        pstmt.setInt(2, id);
        int rowsAffected = pstmt.executeUpdate();
        
        System.out.println(rowsAffected + " row(s) updated.");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Usage:

updateEmployeePosition(1, "Senior Software Engineer");

5. Delete (DELETE)

Remove records from the database:

public void deleteEmployee(int id) {
    String sql = "DELETE FROM employees WHERE id = ?";
    
    try (Connection conn = DriverManager.getConnection(url, user, password);
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        pstmt.setInt(1, id);
        int rowsAffected = pstmt.executeUpdate();
        
        System.out.println(rowsAffected + " row(s) deleted.");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Usage:

deleteEmployee(1);

6. Full Example

Putting it all together:

public class JdbcCrudExample {
    private static final String url = "jdbc:mysql://localhost:3306/company_db";
    private static final String user = "root";
    private static final String password = "root123";

    public static void main(String[] args) {
        // Create
        createEmployee("Bob", "Data Analyst", 75000.00);
        
        // Read
        System.out.println("All Employees:");
        readEmployees();
        
        // Update
        updateEmployeePosition(2, "Lead Data Analyst");
        
        // Delete
        deleteEmployee(2);
    }

    // Include the methods createEmployee(), readEmployees(), etc. here
}

7. Best Practices

1. Use PreparedStatement: Prevents SQL injection and improves performance.
2. Close Resources: Always use try-with-resources for Connection, Statement, and ResultSet.
3. Handle Exceptions: Log errors instead of printing stack traces in production.
4. Validate Inputs: Ensure data types and lengths match database schema.

Summary

• Create: Insert data with INSERT and PreparedStatement.
• Read: Retrieve data using SELECT and process ResultSet.
• Update: Modify records with UPDATE.
• Delete: Remove records using DELETE.

Learn MySQL Commands for Developers

Next Up: Transactions in JDBC – Learn to group operations for data consistency!