Using a Stored Procedure with an Update Count
To modify data in a SQL Server database by using a stored procedure, the Microsoft SQL Server JDBC Driver provides the SQLServerCallableStatement class. By using the SQLServerCallableStatement class, you can call stored procedures that modify data that is contained in the database and return a count of the number of rows affected, also referred to as the update count.
After you have set up the call to the stored procedure by using the SQLServerCallableStatement class, you can then call the stored procedure by using either the execute or the executeUpdate method. The executeUpdate method will return an int value that contains the number of rows affected by the stored procedure, but the execute method does not. If you use the execute method and want to get the count of the number of rows affected, you can call the getUpdateCount method after you run the stored procedure.
Note
If you want the JDBC driver to return all update counts, including update counts returned by any triggers that may have fired, set the lastUpdateCount connection string property to "false". For more information about the lastUpdateCount property, see Setting the Connection Properties.
As an example, create the following table and stored procedure, and also insert sample data in the SQL Server 2005 AdventureWorks sample database:
CREATE TABLE TestTable
(Col1 int IDENTITY,
Col2 varchar(50),
Col3 int);
CREATE PROCEDURE UpdateTestTable
@Col2 varchar(50),
@Col3 int
AS
BEGIN
UPDATE TestTable
SET Col2 = @Col2, Col3 = @Col3
END;
INSERT INTO dbo.TestTable (Col2, Col3) VALUES ('b', 10);
In the following example, an open connection to the AdventureWorks sample database is passed in to the function, the execute method is used to call the UpdateTestTable stored procedure, and then the getUpdateCount method is used to return a count of the rows that are affected by the stored procedure.
public static void executeUpdateStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "A");
cstmt.setInt(2, 100);
cstmt.execute();
int count = cstmt.getUpdateCount();
cstmt.close();
System.out.println("ROWS AFFECTED: " + count);
}
catch (Exception e) {
e.printStackTrace();
}
}