To delete rows from a database using JDBC, you use the executeUpdate() method. This method is used for SQL statements that modify data (like DELETE, INSERT, or UPDATE) and returns an integer representing the number of rows affected.

While you can use a simple Statement, it is highly recommended to use a PreparedStatement to prevent SQL injection and handle parameters safely.

Example: Deleting a Row with PreparedStatement

Here is a typical implementation using the try-with-resources pattern to ensure database connections are closed properly:

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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

        // SQL query with a placeholder (?) for the ID
        String sql = "DELETE FROM users WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Set the value for the placeholder
            int idToDelete = 101;
            pstmt.setInt(1, idToDelete);

            // Execute the delete operation
            int rowsDeleted = pstmt.executeUpdate();

            if (rowsDeleted > 0) {
                System.out.println("Successfuly deleted " + rowsDeleted + " row(s).");
            } else {
                System.out.println("No record found with the specified ID.");
            }

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

Key Steps:

1. Prepare the SQL: Use the DELETE syntax. Use ? as placeholders for dynamic values.
2. Create a PreparedStatement: Call connection.prepareStatement(sql).
3. Bind Parameters: Use setter methods like setInt(), setString(), or setLong() to provide values for the placeholders (indices start at 1).
4. Execute Update: Call executeUpdate(). It returns the count of deleted rows.
5. Handle Exceptions: Wrap the code in a try-catch block to handle SQLException.

Which method should I use?

• executeUpdate(): Use this for DELETE statements. It returns the number of rows removed.
• Statement: Use only for static SQL with no user input.
• PreparedStatement: Always preferred for security and performance when using variables in your WHERE clause.

To insert rows into a database using JDBC, you typically use the executeUpdate(String sql) method of a Statement or PreparedStatement object.

Here are the two primary ways to do it:

1. Using PreparedStatement (Recommended)

This is the standard approach because it prevents SQL Injection and is more efficient for repeated inserts.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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

        String sql = "INSERT INTO users (username, email) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Set the values for the placeholders (?)
            pstmt.setString(1, "john_doe");
            pstmt.setString(2, "[email protected]");

            // executeUpdate returns the number of rows affected
            int rowsInserted = pstmt.executeUpdate();
            if (rowsInserted > 0) {
                System.out.println("A new user was inserted successfully!");
            }

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

2. Using Statement

Use this only for simple, static SQL queries where no user input is involved.

// ... existing code ...
    try (Connection conn = DriverManager.getConnection(url, user, password);
         Statement stmt = conn.createStatement()) {

        String sql = "INSERT INTO users (username, email) VALUES ('jane_doe', '[email protected]')";
        int rows = stmt.executeUpdate(sql);

        System.out.println("Rows affected: " + rows);
    } catch (SQLException e) {
        e.printStackTrace();
    }
// ... existing code ...

Key Takeaways:

• executeUpdate(): Unlike executeQuery() (which returns a ResultSet), executeUpdate() returns an int representing how many rows were added, changed, or deleted.
• Try-with-resources: Always wrap your Connection, Statement, or PreparedStatement in a try-with-resources block to ensure they are closed automatically, even if an error occurs.
• Placeholders: In a PreparedStatement, indices for ? parameters start at 1.