CRUD Operations with JDBC

Table of Contents

1. Introduction to CRUD Operations
2. Setting Up the Database
3. Create Operation (INSERT)
4. Read Operation (SELECT)
5. Update Operation (UPDATE)
6. Delete Operation (DELETE)
7. PreparedStatement vs Statement
8. Best Practices in CRUD Operations
9. Conclusion

1. Introduction to CRUD Operations

CRUD stands for Create, Read, Update, and Delete—the four basic operations for managing data in a relational database. JDBC (Java Database Connectivity) provides a means for Java applications to execute these operations through SQL queries. Using JDBC, you can perform these operations on a database like MySQL or PostgreSQL.

In this module, we’ll explore how to use JDBC to perform CRUD operations, which are fundamental for managing and interacting with data in relational databases.

2. Setting Up the Database

Before diving into the CRUD operations, make sure your database is set up. This example will use MySQL for demonstration. You can use PostgreSQL or other relational databases in a similar way, but ensure the appropriate JDBC driver is added to your project.

Steps to Set Up MySQL Database:

1. Install MySQL: Download and install MySQL from MySQL Downloads.
2. Create a Database: CREATE DATABASE mydatabase; USE mydatabase;
3. Create a Table: CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) );

3. Create Operation (INSERT)

The CREATE operation involves inserting new records into the database. In JDBC, we use the INSERT SQL query to add new rows to a table.

Example of Insert Operation:

import java.sql.*;

public class JDBCInsertExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        try {
            // Establish connection
            Connection conn = DriverManager.getConnection(url, user, password);

            // Create INSERT SQL query
            String query = "INSERT INTO users (name, email) VALUES (?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setString(1, "John Doe");
            pstmt.setString(2, "[email protected]");

            // Execute the insert operation
            int rowsAffected = pstmt.executeUpdate();
            System.out.println("Inserted " + rowsAffected + " row(s).");

            // Close the resources
            pstmt.close();
            conn.close();

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

• Explanation: In the above code, we use PreparedStatement for better security and efficiency. The executeUpdate() method executes the SQL INSERT query and returns the number of rows affected.

4. Read Operation (SELECT)

The READ operation involves retrieving data from the database. In JDBC, we use the SELECT SQL query to fetch data.

Example of Select Operation:

import java.sql.*;

public class JDBCSelectExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        try {
            // Establish connection
            Connection conn = DriverManager.getConnection(url, user, password);

            // Create SELECT SQL query
            String query = "SELECT * FROM users";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            // Process the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

            // Close resources
            rs.close();
            stmt.close();
            conn.close();

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

• Explanation: In the above code, the executeQuery() method is used to execute the SELECT query. The result is stored in a ResultSet object, which is then processed to extract data.

5. Update Operation (UPDATE)

The UPDATE operation modifies existing records in the database. In JDBC, we use the UPDATE SQL query to modify rows in a table.

Example of Update Operation:

import java.sql.*;

public class JDBCUpdateExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        try {
            // Establish connection
            Connection conn = DriverManager.getConnection(url, user, password);

            // Create UPDATE SQL query
            String query = "UPDATE users SET email = ? WHERE id = ?";
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setString(1, "[email protected]");
            pstmt.setInt(2, 1);

            // Execute the update operation
            int rowsAffected = pstmt.executeUpdate();
            System.out.println("Updated " + rowsAffected + " row(s).");

            // Close resources
            pstmt.close();
            conn.close();

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

• Explanation: Here, the PreparedStatement is used for the UPDATE operation. The query updates the email of a user with a specific id. The executeUpdate() method returns the number of rows affected by the update.

6. Delete Operation (DELETE)

The DELETE operation removes records from the database. In JDBC, we use the DELETE SQL query to remove rows from a table.

Example of Delete Operation:

import java.sql.*;

public class JDBCDeleteExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "root";
        String password = "password";

        try {
            // Establish connection
            Connection conn = DriverManager.getConnection(url, user, password);

            // Create DELETE SQL query
            String query = "DELETE FROM users WHERE id = ?";
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, 1); // Delete the user with ID 1

            // Execute the delete operation
            int rowsAffected = pstmt.executeUpdate();
            System.out.println("Deleted " + rowsAffected + " row(s).");

            // Close resources
            pstmt.close();
            conn.close();

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

• Explanation: In this code, the PreparedStatement is used to execute the DELETE query. The executeUpdate() method returns the number of rows deleted.

7. PreparedStatement vs Statement

• Statement: The Statement object is used to execute simple SQL queries, but it is less efficient and prone to SQL injection vulnerabilities because it doesn’t support parameterized queries.
• PreparedStatement: The PreparedStatement object is preferred because it allows for parameterized queries. It also provides better performance when executing the same query multiple times and helps prevent SQL injection.

Example:

Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')");

PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users (name, email) VALUES (?, ?)");
pstmt.setString(1, "John Doe");
pstmt.setString(2, "[email protected]");
pstmt.executeUpdate();

8. Best Practices in CRUD Operations

• Use PreparedStatement: Always use PreparedStatement to avoid SQL injection attacks and improve performance.
• Handle Exceptions: Properly handle SQLExceptions by using try-catch blocks and providing meaningful error messages.
• Close Resources: Always close database resources (e.g., Connection, Statement, ResultSet) to avoid resource leaks.
• Transaction Management: For multiple updates, ensure you use transactions to maintain data consistency. Use commit() and rollback() appropriately.

9. Conclusion

CRUD operations are the backbone of any database-driven Java application. By using JDBC, Java developers can efficiently perform these operations on relational databases like MySQL or PostgreSQL. With the use of PreparedStatement, proper exception handling, and transaction management, you can create secure, efficient, and scalable database applications.

4o mini