JDBC PreparedStatement - CRUD operations on a PostgreSQL database.

Using PreparedStatement in JDBC for CRUD (Create, Read, Update, Delete) operations provides better security and performance compared to using a Statement, especially by protecting against SQL injection attacks. Below is an example of how to use PreparedStatement for CRUD operations in a PostgreSQL database.

Full Java Example of CRUD Operations Using

PreparedStatement

private static final String DB_URL = "jdbc:postgresql://localhost:5432/postgres";
    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)) {
            
            // 1. CREATE (Insert)
            String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(insertSQL)) {
                ps.setString(1, "John Doe");
                ps.setString(2, "john.doe@example.com");
                ps.executeUpdate();
                System.out.println("Record inserted successfully.");
            }
            
            // 2. READ (Select)
            String selectSQL = "SELECT * FROM users";
            try (PreparedStatement ps = conn.prepareStatement(selectSQL)) {
                ResultSet rs = ps.executeQuery();
                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 = ?, email = ? WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(updateSQL)) {
                ps.setString(1, "Jane Doe");
                ps.setString(2, "jane.doe@example.com");
                ps.setInt(3, 1);  // Update the user with id = 1
                int rowsAffected = ps.executeUpdate();
                System.out.println(rowsAffected + " record(s) updated.");
            }

            // 4. DELETE (Remove)
            String deleteSQL = "DELETE FROM users WHERE id = ?";
            try (PreparedStatement ps = conn.prepareStatement(deleteSQL)) {
                ps.setInt(1, 1);  // Delete the user with id = 1
                int rowsDeleted = ps.executeUpdate();
                System.out.println(rowsDeleted + " record(s) deleted.");
            }

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

---

Explanation of Each CRUD Operation:

1. Create (Insert)

String insertSQL = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(insertSQL)) {
    ps.setString(1, "John Doe");
    ps.setString(2, "john.doe@example.com");
    ps.executeUpdate();
}

• ? placeholders: The PreparedStatement uses ? as placeholders for the values you want to insert.
• setString(): Sets the value of the placeholder to a string.
• executeUpdate(): Executes the insert query.

2. Read (Select)

String selectSQL = "SELECT * FROM users";
try (PreparedStatement ps = conn.prepareStatement(selectSQL)) {
    ResultSet rs = ps.executeQuery();
    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);
    }
}

• executeQuery(): Used to run SELECT queries. It returns a ResultSet object.
• The ResultSet is iterated to extract the data.

3. Update (Modify)

String updateSQL = "UPDATE users SET name = ?, email = ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(updateSQL)) {
    ps.setString(1, "Jane Doe");
    ps.setString(2, "jane.doe@example.com");
    ps.setInt(3, 1);  // Assuming the user has id = 1
    int rowsAffected = ps.executeUpdate();
}

• setString(): Sets the updated name and email values.
• setInt(): Sets the id value to specify which record to update.
• executeUpdate(): Executes the update query and returns the number of rows affected.

4. Delete (Remove)

String deleteSQL = "DELETE FROM users WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(deleteSQL)) {
    ps.setInt(1, 1);  // Delete the user with id = 1
    int rowsDeleted = ps.executeUpdate();
}

• setInt(): Sets the id value to specify which record to delete.
• executeUpdate(): Executes the delete query and returns the number of rows deleted.

---

Important Notes:

• PreparedStatement is more secure because it prevents SQL injection by ensuring that input data is automatically escaped before being executed.
• Auto-closeable Resources: The try-with-resources statement is used to automatically close the PreparedStatement and Connection objects when they are no longer needed.
• Exception Handling: Always handle SQL exceptions to avoid crashes and to log meaningful error messages.

---

Summary of PreparedStatement Methods:

• setString(int parameterIndex, String value): Sets a string value for the specified parameter index.
• setInt(int parameterIndex, int value): Sets an integer value for the specified parameter index.
• executeUpdate(): Executes the SQL query (for INSERT, UPDATE, or DELETE) and returns the number of affected rows.
• executeQuery(): Executes a SELECT query and returns a ResultSet object containing the results.

This approach should be sufficient for performing secure and efficient CRUD operations in your database using JDBC and PreparedStatement.