JDBC CRUD Operations

Create, Read, Update, Delete operations with JDBC

📊 CRUD Operations

CRUD operations are fundamental database operations: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE). JDBC provides methods to execute these SQL operations efficiently. 


// Basic CRUD example
Statement stmt = connection.createStatement();
stmt.executeUpdate("INSERT INTO users (name) VALUES ('John')");
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

CRUD Operations

➕

Create (INSERT)

Add new records to database 

stmt.executeUpdate("INSERT INTO users VALUES (1, 'John')");
👁️

Read (SELECT)

Retrieve data from database 

ResultSet rs = stmt.executeQuery("SELECT * FROM users");
✏️

Update (UPDATE)

Modify existing records 

stmt.executeUpdate("UPDATE users SET name='Jane' WHERE id=1");
🗑️

Delete (DELETE)

Remove records from database 

stmt.executeUpdate("DELETE FROM users WHERE id=1");

🔹 Create Operation (INSERT)

Adding new records to the database: 

import java.sql.*;

public class CreateOperation {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            // Create table first
            String createTable = "CREATE TABLE IF NOT EXISTS users (" +
                               "id INT PRIMARY KEY AUTO_INCREMENT, " +
                               "name VARCHAR(50), " +
                               "email VARCHAR(100))";
            
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(createTable);
            
            // Insert single record
            String insertSQL = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";
            int rowsAffected = stmt.executeUpdate(insertSQL);
            
            System.out.println("Rows inserted: " + rowsAffected);
            
            // Insert multiple records
            stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('Jane Smith', '[email protected]')");
            stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('Bob Johnson', '[email protected]')");
            
            System.out.println("Multiple users inserted successfully!");
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Rows inserted: 1

Multiple users inserted successfully!

🔹 Read Operation (SELECT)

Retrieving data from the database: 

import java.sql.*;

public class ReadOperation {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            Statement stmt = conn.createStatement();
            
            // Select all records
            String selectSQL = "SELECT id, name, email FROM users";
            ResultSet rs = stmt.executeQuery(selectSQL);
            
            System.out.println("All Users:");
            System.out.println("ID\tName\t\tEmail");
            System.out.println("--------------------------------");
            
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                
                System.out.println(id + "\t" + name + "\t" + email);
            }
            
            // Select specific record
            String specificSQL = "SELECT * FROM users WHERE name = 'John Doe'";
            ResultSet specificRS = stmt.executeQuery(specificSQL);
            
            System.out.println("\nSpecific User:");
            if (specificRS.next()) {
                System.out.println("Found: " + specificRS.getString("name"));
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

All Users:

ID Name Email

--------------------------------

1 John Doe [email protected]

2 Jane Smith [email protected]

3 Bob Johnson [email protected]


Specific User:

Found: John Doe

🔹 Update Operation (UPDATE)

Modifying existing records in the database: 

import java.sql.*;

public class UpdateOperation {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            Statement stmt = conn.createStatement();
            
            // Update single record
            String updateSQL = "UPDATE users SET email = '[email protected]' WHERE name = 'John Doe'";
            int rowsUpdated = stmt.executeUpdate(updateSQL);
            
            System.out.println("Rows updated: " + rowsUpdated);
            
            // Update multiple records
            String updateMultiple = "UPDATE users SET email = CONCAT(LOWER(REPLACE(name, ' ', '.')), '@company.com')";
            int multipleUpdated = stmt.executeUpdate(updateMultiple);
            
            System.out.println("Multiple rows updated: " + multipleUpdated);
            
            // Verify updates
            ResultSet rs = stmt.executeQuery("SELECT name, email FROM users");
            System.out.println("\nUpdated Records:");
            while (rs.next()) {
                System.out.println(rs.getString("name") + " - " + rs.getString("email"));
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Rows updated: 1

Multiple rows updated: 3


Updated Records:

John Doe - [email protected]

Jane Smith - [email protected]

Bob Johnson - [email protected]

🔹 Delete Operation (DELETE)

Removing records from the database: 

import java.sql.*;

public class DeleteOperation {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            Statement stmt = conn.createStatement();
            
            // Show records before deletion
            System.out.println("Before deletion:");
            ResultSet beforeRS = stmt.executeQuery("SELECT COUNT(*) as count FROM users");
            if (beforeRS.next()) {
                System.out.println("Total users: " + beforeRS.getInt("count"));
            }
            
            // Delete specific record
            String deleteSQL = "DELETE FROM users WHERE name = 'Bob Johnson'";
            int rowsDeleted = stmt.executeUpdate(deleteSQL);
            
            System.out.println("Rows deleted: " + rowsDeleted);
            
            // Delete with condition
            String conditionalDelete = "DELETE FROM users WHERE id > 2";
            int conditionalDeleted = stmt.executeUpdate(conditionalDelete);
            
            System.out.println("Conditional delete affected: " + conditionalDeleted + " rows");
            
            // Show remaining records
            System.out.println("\nRemaining users:");
            ResultSet remainingRS = stmt.executeQuery("SELECT name FROM users");
            while (remainingRS.next()) {
                System.out.println("- " + remainingRS.getString("name"));
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Before deletion:

Total users: 3

Rows deleted: 1

Conditional delete affected: 0 rows


Remaining users:

- John Doe

- Jane Smith

🔹 Complete CRUD Example

All CRUD operations in one class: 

import java.sql.*;

public class CompleteCRUD {
    private static final String URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "password";
    
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            
            CompleteCRUD crud = new CompleteCRUD();
            
            // CREATE
            crud.createUser(conn, "Alice Brown", "[email protected]");
            
            // READ
            crud.readAllUsers(conn);
            
            // UPDATE
            crud.updateUser(conn, "Alice Brown", "[email protected]");
            
            // DELETE
            crud.deleteUser(conn, "Alice Brown");
            
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
    
    public void createUser(Connection conn, String name, String email) throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.executeUpdate();
            System.out.println("✓ User created: " + name);
        }
    }
    
    public void readAllUsers(Connection conn) throws SQLException {
        String sql = "SELECT * FROM users";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("📋 All Users:");
            while (rs.next()) {
                System.out.println("  " + rs.getString("name") + " - " + rs.getString("email"));
            }
        }
    }
    
    public void updateUser(Connection conn, String name, String newEmail) throws SQLException {
        String sql = "UPDATE users SET email = ? WHERE name = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newEmail);
            pstmt.setString(2, name);
            int updated = pstmt.executeUpdate();
            System.out.println("✓ Updated " + updated + " user(s)");
        }
    }
    
    public void deleteUser(Connection conn, String name) throws SQLException {
        String sql = "DELETE FROM users WHERE name = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            int deleted = pstmt.executeUpdate();
            System.out.println("✓ Deleted " + deleted + " user(s)");
        }
    }
}

Output:

✓ User created: Alice Brown

📋 All Users:

John Doe - [email protected]

Jane Smith - [email protected]

Alice Brown - [email protected]

✓ Updated 1 user(s)

✓ Deleted 1 user(s)

🧠 Test Your Knowledge

Which method is used to execute SELECT queries?