How to Retrieve the Number of Rows Affected by an SQL UPDATE Statement in Java

How can I return the number of rows affected by an SQL UPDATE statement in Java?

int rowsAffected = statement.executeUpdate(sqlQuery);

In Java, when executing an SQL UPDATE statement using JDBC, you can retrieve the number of rows affected by that operation. This is helpful for validating whether your database changes were successful.

Connection conn = DriverManager.getConnection(dbUrl, username, password);
String sqlQuery = "UPDATE employees SET salary = salary * 1.10 WHERE department = 'Sales'";
try (Statement statement = conn.createStatement()) {
    int rowsAffected = statement.executeUpdate(sqlQuery);
    System.out.println("Rows affected: " + rowsAffected);
} catch (SQLException e) {
    e.printStackTrace();
}

Causes

• The statement was executed correctly against the database.
• Permissions allow updating the intended rows.

Solutions

• Utilize the `executeUpdate` method of the `Statement` or `PreparedStatement` object.
• Assign the return value of `executeUpdate` to an integer to capture the number of affected rows.

Mistake: Not using `PreparedStatement` for dynamically built queries, leading to SQL injection risks.

Solution: Always prefer using `PreparedStatement` to safeguard against SQL injection.

Mistake: Assuming rows will be affected without proper conditions in the WHERE clause.

Solution: Double-check your SQL query conditions to ensure they are correct.