The author of this article introduces how to call SQL Server's stored procedures through Java, and explains in detail five different types of storage. Please see below for details
1. Use a stored procedure without parameters
When using a JDBC driver to call a stored procedure without parameters, you must use the call SQL escape sequence. The syntax for the call escape sequence without parameters is as follows:
Copy the code code as follows:
{call procedure-name}
As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:
Copy the code code as follows:
CREATE PROCEDURE GetContactFormalNames
AS
BEGIN
SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName
FROM Person.Contact
END
This stored procedure returns a single result set that contains a column of data consisting of the title, first name, and last name of the first ten contacts in the Person.Contact table.
In the following example, this function is passed an open connection to the AdventureWorks sample database, and then the GetContactFormalNames stored procedure is called using the executeQuery method.
Copy the code code as follows:
public static void executeSprocNoParams(Connection con) ...{
try...{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
while (rs.next()) ...{
System.out.println(rs.getString("FormalName"));
}
rs.close();
stmt.close();
}
catch (Exception e) ...{
e.printStackTrace();
}
}
2. Use a stored procedure with input parameters
When using a JDBC driver to call a stored procedure with parameters, you must use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax for a call escape sequence with an IN parameter is as follows:
Copy the code code as follows:
{call procedure-name[([parameter][,[parameter]]...)]}
When constructing a call escape sequence, use the ? (question mark) character to specify the IN parameter. This character serves as a placeholder for the parameter value to be passed to the stored procedure. You can use one of the setter methods of the SQLServerPreparedStatement class to specify values for parameters. The setter methods available are determined by the data type of the IN parameter.
When passing a value to a setter method, you must specify not only the actual value to be used in the parameter, but also the ordinal position of the parameter within the stored procedure. For example, if a stored procedure contains a single IN parameter, its ordinal value is 1. If the stored procedure contains two parameters, the first ordinal value is 1 and the second ordinal value is 2.
As an example of how to call a stored procedure that contains an IN parameter, use the uspGetEmployeeManagers stored procedure in the SQL Server 2005 AdventureWorks sample database. This stored procedure accepts a single input parameter named EmployeeID, which is an integer value, and returns a recursive list of employees and their managers based on the specified EmployeeID. Here is the Java code that calls this stored procedure:
Copy the code code as follows:
public static void executeSprocInParams(Connection con) ...{
try...{
PreparedStatement pstmt = con.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) ...{
System.out.println("EMPLOYEE:");
System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
System.out.println("MANAGER:");
System.out.println(rs.getString("ManagerLastName") + ", " + rs.getString("ManagerFirstName"));
System.out.println();
}
rs.close();
pstmt.close();
}
catch (Exception e) ...{
e.printStackTrace();
}
}
3. Use a stored procedure with output parameters
When calling such stored procedures using a JDBC driver, you must use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax for a call escape sequence with an OUT parameter is as follows:
Copy the code code as follows:
{call procedure-name[([parameter][,[parameter]]...)]}
When constructing a call escape sequence, use the ? (question mark) character to specify the OUT parameter. This character serves as a placeholder for the parameter value to be returned from this stored procedure. To specify values for OUT parameters, you must use the registerOutParameter method of the SQLServerCallableStatement class to specify the data type of each parameter before running the stored procedure.
The value specified for the OUT parameter using the registerOutParameter method must be one of the JDBC data types contained in java.sql.Types, which in turn is mapped to one of the native SQL Server data types. For more information about JDBC and SQL Server data types, see Understanding JDBC Driver Data Types.
When you pass a value to the registerOutParameter method for an OUT parameter, not only must you specify the data type to use for the parameter, but you must also specify the ordinal position of the parameter or the name of the parameter in the stored procedure. For example, if a stored procedure contains a single OUT parameter, its ordinal value is 1; if a stored procedure contains two parameters, the first ordinal value is 1 and the second ordinal value is 2.
As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database: Based on the specified integer IN parameter (employeeID), this stored procedure also returns a single integer OUT parameter (managerID). Based on the EmployeeID contained in the HumanResources.Employee table, the value returned in the OUT parameter is ManagerID.
In the following example, this function is passed an open connection to the AdventureWorks sample database, and then the GetImmediateManager stored procedure is called using the execute method:
Copy the code code as follows:
public static void executeStoredProcedure(Connection con) ...{
try...{
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
catch (Exception e) ...{
e.printStackTrace();
}
}
This example uses ordinal position to identify parameters. Alternatively, the parameter can be identified by its name rather than its ordinal position. The following code example modifies the previous example to illustrate how to use named parameters in a Java application. Note that these parameter names correspond to the parameter names in the definition of the stored procedure: 11x16CREATE PROCEDURE GetImmediateManager
Copy the code code as follows:
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
Stored procedures may return update counts and multiple result sets. The Microsoft SQL Server 2005 JDBC Driver complies with the JDBC 3.0 specification, which states that multiple result sets and update counts should be retrieved before retrieving OUT parameters. That is, the application should first retrieve all ResultSet objects and update the count, and then use the CallableStatement.getter method to retrieve the OUT parameters. Otherwise, when the OUT parameter is retrieved, the ResultSet object and update count that have not yet been retrieved will be lost.
4. Use stored procedures with return status
When calling such a stored procedure using a JDBC driver, you must use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax for a call escape sequence that returns a status parameter is as follows:
Copy the code code as follows:
{[?=]call procedure-name[([parameter][,[parameter]]...)]}
When constructing a call escape sequence, use the ? (question mark) character to specify the return status parameter. This character serves as a placeholder for the parameter value to be returned from this stored procedure. To specify a value for a return status parameter, you must specify the data type of the parameter using the registerOutParameter method of the SQLServerCallableStatement class before executing the stored procedure.
In addition, when passing the return status parameter value to the registerOutParameter method, you must specify not only the data type of the parameter to be used, but also the ordinal position of the parameter in the stored procedure. The ordinal position of the return status parameter is always 1 because it is always the first parameter when the stored procedure is called. Although the SQLServerCallableStatement class supports using the parameter's name to indicate a specific parameter, you can only use the parameter's ordinal position number for return status parameters.
As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:
Copy the code code as follows:
CREATE PROCEDURE CheckContactCity
(@cityName CHAR(50))
AS
BEGIN
IF ((SELECT COUNT(*)
FROM Person.Address
WHERE City = @cityName) > 1)
RETURN 1
ELSE
RETURN 0
END
The stored procedure returns a status value of 1 or 0, depending on whether the city specified by the cityName parameter can be found in the Person.Address table.
In the following example, this function is passed an open connection to the AdventureWorks sample database, and then the CheckContactCity stored procedure is called using the execute method:
Copy the code code as follows:
public static void executeStoredProcedure(Connection con) ...{
try...{
CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "Atlanta");
cstmt.execute();
System.out.println("RETURN STATUS: " + cstmt.getInt(1));
}
cstmt.close();
catch (Exception e) ...{
e.printStackTrace();
}
}
5. Use a stored procedure with an update count
After you use the SQLServerCallableStatement class to construct a call to a stored procedure, you can use either the execute or executeUpdate methods to call the stored procedure. The executeUpdate method returns an int value containing the number of rows affected by this stored procedure, but the execute method does not return this value. If you use the execute method and want to get a count of the number of rows affected, you can call the getUpdateCount method after running the stored procedure.
As an example, create the following tables and stored procedures in the SQL Server 2005 AdventureWorks sample database:
Copy the code code as follows:
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;
In the following example, this function is passed an open connection to the AdventureWorks sample database, uses the execute method to call the UpdateTestTable stored procedure, and then uses the getUpdateCount method to return the count of rows affected by the stored procedure.
Copy the code code as follows:
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();