JDBC Statement - CRUD operations on a PostgreSQL database.

 Here’s an example of how to use JDBC Statement for performing CRUD (Create, Read, Update, Delete) operations on a PostgreSQL database.

Prerequisites

Ensure you have:

• A PostgreSQL database running.
• The PostgreSQL JDBC driver added to your project.
• A table in the database for performing CRUD operations.

Example SQL for creating a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

JDBC CRUD Operations

1. Create – Insert a record into the table.
2. Read – Retrieve records from the table.
3. Update – Modify an existing record.
4. Delete – Remove a record from the table.

---

Full Example of JDBC CRUD Operations Using Statement

import java.sql.*;

public class JDBCStatementCRUD {
    private static final String DB_URL = "jdbc:postgresql://localhost:5432/mydatabase";
    private static final String USER = "postgres";
    private static final String PASSWORD = "root";
    
    public static void main(String[] args) {
        // Establish the connection
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            Statement stmt = conn.createStatement();

            // 1. CREATE (Insert)
            String insertSQL = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
            stmt.executeUpdate(insertSQL);
            System.out.println("Record inserted successfully");

            // 2. READ (Select)
            String selectSQL = "SELECT * FROM users";
            ResultSet rs = stmt.executeQuery(selectSQL);
            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);
            }

            // 3. UPDATE (Modify)
            String updateSQL = "UPDATE users SET name = 'Jane Doe' WHERE id = 1";
            int rowsAffected = stmt.executeUpdate(updateSQL);
            System.out.println(rowsAffected + " record(s) updated.");

            // 4. DELETE (Remove)
            String deleteSQL = "DELETE FROM users WHERE id = 1";
            int rowsDeleted = stmt.executeUpdate(deleteSQL);
            System.out.println(rowsDeleted + " record(s) deleted.");

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

---

Explanation:

1. Creating a Connection:

   Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
   

   • Establishes a connection to the PostgreSQL database using the provided URL, username, and password.

2. Using Statement to Execute Queries:

   Statement stmt = conn.createStatement();
   

   • Creates a Statement object, which is used to execute SQL queries.

CRUD Operations

1. Create (Insert)

String insertSQL = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
stmt.executeUpdate(insertSQL);

• This query inserts a new record into the users table with the specified name and email.
• executeUpdate() is used for queries that modify the database (INSERT, UPDATE, DELETE).

2. Read (Select)

String selectSQL = "SELECT * FROM users";
ResultSet rs = stmt.executeQuery(selectSQL);

• The executeQuery() method is used to run a SELECT query, returning a ResultSet.
• The ResultSet is iterated to fetch data for each record in the table.

3. Update (Modify)

String updateSQL = "UPDATE users SET name = 'Jane Doe' WHERE id = 1";
int rowsAffected = stmt.executeUpdate(updateSQL);

• The UPDATE query modifies the name field for the user with id = 1.
• The executeUpdate() method returns the number of rows affected by the update.

4. Delete (Remove)

String deleteSQL = "DELETE FROM users WHERE id = 1";
int rowsDeleted = stmt.executeUpdate(deleteSQL);

• The DELETE query removes the user with id = 1 from the table.
• The executeUpdate() method returns the number of rows deleted.

Closing Resources:

In the above example, try-with-resources is used to automatically close the Connection and Statement objects after the execution of the operations.

Additional Notes:

• Exceptions Handling: Make sure to handle SQLException properly. In this example, it is printed using e.printStackTrace(), but you might want to log it or handle it in other ways.
• SQL Injection Prevention: For more complex SQL operations, consider using PreparedStatement instead of Statement to avoid SQL injection vulnerabilities.