How Do You Find the Number of Rows Affected by a SQL Query? in JDBC

Suppose you execute a query to delete some rows and you want to know how many rows were deleted. In general, the JDBC API provides two methods (available in the java.sql.Statement interface) to find the number of rows affected by a SQL query: execute() and executeUpdate().

Get the Number of Rows Affected Using the executeUpdate() Method

Using the executeUpdate() method, we can get the number of rows affected:

Connection conn = ... get a java.sql.Connection object ...
String sqlQuery = "delete from employees where employee_status = 'inactive'";
Statement stmt = conn.createStatement();
int rowsAffected = stmt.executeUpdate(sqlQuery);
System.out.println("number of rows affected = "+ rowsAffected);

Also, we may write this as a method:

/**
* Get the number of rows affected for a given SQL query.
* @param conn the connection object.
* @param sqlQuery the SQL query to be executed.
* @return the number of rows affected by the execution of the SQL query.
* @exception Failed to execute the SQL query.
*
*/
public static int getNumberOfRowsAffected(Connection conn,
String sqlQuery)
throws Exception {
Statement stmt = null
try {
stmt = conn.createStatement();
int rowsAffected = stmt.executeUpdate(sqlQuery);
System.out.println("number of rows affected = "+ rowsAffected);
return rowsAffected;
}
catch(Exception e) {
throw new Exception(e.toString+
"could not get the number of rows affected");
}
finally {
DatabaseUtil.close(stmt);
}
}

Get the Number of Rows Affected Using the execute() Method

The execute() method executes the given SQL query, which may return multiple results. This method returns a boolean (true/false). The execute() method returns true if the first result is a ResultSet object; it returns false if it is an update count or there are no results.

Connection conn = ... get a java.sql.Connection object ...
String sqlQuery = "delete from employees where employeeStatus = 'inactive'";
Statement stmt = conn.createStatement();
if (!stmt.execute(sqlQuery)) {
//
// then there is no result set
// get the number of rows affected
//
int rowsAffected = stmt.getUpdateCount();
System.out.println("number of rows affected = "+ rowsAffected);
}
Also, we may write this as a method:
/**
* Get the number of rows affected for a given SQL query.
* @param conn the connection object.
* @param sqlQuery the SQL query to be executed.
* @return the number of rows affected by the execution of the SQL query.
* @exception Failed to execute the SQL query.
*
*/
public static int getNumberOfRowsAffected(Connection conn,
String sqlQuery)
throws Exception {
Statement stmt = null
try {
stmt = conn.createStatement();
if (!stmt.execute(sqlQuery)) {
//
// then there is no result set
// get the number of rows affected
//
int rowsAffected = stmt.getUpdateCount();
System.out.println("number of rows affected = "+ rowsAffected);
return rowsAffected;
}
else {
return 0;
}
}
catch(Exception e) {
throw new Exception(e.toString+
"could not get the number of rows affected");
}
finally {
DatabaseUtil.close(stmt);
}
}


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics