I'm using a MySQL database and accessing it through Java.
PreparedStatement prep1 = this.connection.prepareStatement(
"UPDATE user_table
SET Level = 'Super'
WHERE Username = ?");
prep1.setString(1, username);
The update statement above works fine however I'd like to get the number of rows affected with this statement. Is this possible please?
Statement.executeUpdate() or execute() followed by getUpdateCount() will return the number of rows matched, not updated, according to the JDBC spec. If you want the updated count, you can specify useAffectedRows=true as a non-standard URL option. More information is available here.
Calling executeUpdate() on your PreparedStatement should return an int, the number of updated records.
First of all, prepare the 'PreparedStatement' object using below constructor:
PreparedStatement pStmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//here variable 'sql' is your query ("UPDATE user_table SET Level = 'Super' WHERE Username = ?")
Then, set your argument to 'pStmt'. In this case:
prep1.setString(1, username);
Finally, executeUpdate and get affected rows as an integer
int affectedRows = pStmt.executeUpdate();
Looking at this just now for another similar situation, where I only want to do additional work if something really changed, I think the most platform neutral way to do it would be to alter the query to exclude the case where the set fields match:
UPDATE user_table SET Level = 'Super' WHERE Username = ? AND Level <> 'Super'
The number of rows affected by SQL Update can be returned using SQL%ROWCOUNT (For ORACLE) or @@ROWCOUNT(FOR SQL SERVER)
Note: In order to return the number of rows updated, deleted, etc.. we have to use OUT Parameter in Stored Procedure which will store the number of rows updated,deleted etc..
To get the number of rows updated,deleted etc.. we have to use registerOutParameter method in Java
To store the number of rows updated or deleted etc.. into one of the OUT parameter in stored procedure we have to set the type of that parameter in our script before executing the command. (In case of Update or delete it will be NUMERIC)
Once the command is executed, store the value of updated or deleted rows into the variable (It can be new variable or variables available in class etc..) by calling the index of that parameter (for ex: A=cs.getInt(3) if the OUT parameter in stored procedure is 2nd parameter)
Now, the variable has the value of Updated or deleted rows (i.e.A=10)
Example for Stored porcedure
Function demo( A varchar2(10), B OUT NUMBER)RETURN NUMBER IS EXIST_LP NUMBER;
BEGIN
UPDATE demo_temp SET name=A where name="ABC";
B:=SQL%ROWCOUNT -- total number of rows updated
RETRUN EXIST_LP;
END demo;
Example for java script
public void update(demo demo){
int rowCount = 0;
Connection conn = null;
CallableStatement cs = null;
try{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("your data source path");
conn = ds.getConnection();
cs = conn.prepareCall("BEGIN ? :=demo_dbp.demo(?,?) ); END;"); // stored proc
cs.registerOutParameter(1, Types.INTEGER);
cs.setString(2, "XYZ");
cs.registerOutParameter(3, Types.NUMERIC);
rowCount=cs.execcuteUpdate();
demo.setUpdateCount(cs.getInt(3));
} catch (SQLException exc) {
throw new DaoException("An SQL Exception has occurred.", exc);
} catch (NamingException ne) {
throw new DaoException("A Naming Exception has occurred.", ne);
} catch (Exception e) {
throw new DaoException("An Exception has occurred", e);
} finally {
try {
if (cs != null) {
cs.close();
}
} catch (SQLException ex1) {
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
}
}
}
Note: executeUpdate() doesn't return the number of rows updated or deleted. It just returns 0 or 1.
That number is returned when you run the query:
int rows = prep1.executeUpdate();
System.out.printf("%d row(s) updated!", rows);
If it is necessary to know how many rows will be affected without executing it, you will have to run a SELECT statement first.
