Details of calling SQL Server stored procedures in Java

The author of this article introduced how to invoke SQL Server stored procedures through Java, and explained five different types of storage. Please refer to the following for details.

1. Use stored procedures with no parameters

When using the JDBC driver to call a stored procedure with no parameters, you must escape the sequence using call SQL. The syntax of the call escape sequence without parameters is as follows:

{call procedure-name}

As an example, create the following stored procedures in the SQL Server 2005 AdventureWorks sample database:

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 containing a column of data (made up of the first ten contacts in the person.contact table).

In the following example, an open connection to the AdventureWorks sample database is passed to this function, and the GetContactFormalNames stored procedure is invoked using the executeQuery method.

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 stored procedures with input parameters

When calling a parameterized stored procedure with a JDBC driver, you must use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax of the call escape sequence with the IN parameter is as follows:

{call procedure-name[([parameter][,[parameter]]...)]}

When constructing a call escape sequence, use ? The (question mark) character specifies the IN parameter. This character ACTS as a placeholder for the parameter value to be passed to the stored procedure. Can use one of setter methods of SQLServerPreparedStatement specify values for parameters. The available setter methods 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 in the stored procedure. For example, if the stored procedure contains a single IN parameter, its order value is 1. If the stored procedure contains two parameters, the first order value is 1 and the second order value is 2.

As an example of how to invoke a stored procedure with an IN parameter, use the uspGetEmployeeManagers stored procedure from the SQL Server 2005 AdventureWorks sample database. This stored procedure takes a single input parameter named EmployeeID, which is an integer value, and then returns a recursive list of employees and their managers based on the specified EmployeeID. Here is the Java code that invokes this stored procedure:

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 stored procedures with output parameters

When calling such stored procedures with a JDBC driver, you must use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax of the call escape sequence with the OUT parameter is as follows:
The same code at the page code block index 3
When constructing a call escape sequence, use ? The (question mark) character specifies the OUT parameter. This character ACTS as a placeholder for the parameter value to be returned from the stored procedure. To specify values for OUT parameters, must be used before the run the stored procedure SQLServerCallableStatement registerOutParameter method to specify the parameters of a class of data types.

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 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, you must specify not only the data type to be used for the parameter, but also the ordinal position of the parameter or the name of the parameter in the stored procedure. For example, if the stored procedure contains a single OUT parameter, its order value is 1; If the stored procedure contains two parameters, the first order value is 1 and the second order value is 2.

As an example, create the following stored procedure IN the SQL Server 2005 AdventureWorks sample database: the stored procedure also returns a single integer OUT parameter (managerID) based on the specified integer IN parameter (employeeID). According to the EmployeeID contained in the humanresources.employee table, the value returned in the OUT parameter is ManagerID.

In the following example, an open connection to the AdventureWorks sample database is passed to this function, and the GetImmediateManager stored procedure is invoked using the execute method:

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 positions to identify parameters. Alternatively, you can identify the parameter by its name rather than its ordinal position. The following code example modifies the previous example to show how named parameters can be used in a Java application. Note that these parameter names correspond to the parameter names in the definition of the stored PROCEDURE: 11x16CREATE PROCEDURE GetImmediateManager

@employeeID INT,  

 @managerID INT OUTPUT 

AS 

BEGIN 

 SELECT @managerID = ManagerID  

 FROM HumanResources.Employee  

 WHERE EmployeeID = @employeeID  

END

A stored procedure may return an update count and multiple result sets. Microsoft SQL Server 2005 JDBC Driver follows 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 the ResultSet objects and the update count, and then retrieve the OUT parameter using the callablestatement.getter method. Otherwise, when the OUT parameter is retrieved, the unretrieved ResultSet object and the update count are lost.

Use a stored procedure with a return state

When calling this stored procedure with a JDBC driver, you must use the call SQL escape sequence in conjunction with the prepareCall method of the SQLServerConnection class. The syntax of the call escape sequence that returns a status parameter is as follows:

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

When constructing a call escape sequence, use ? The (question mark) character specifies the return status parameter. This character ACTS as a placeholder for the parameter value to be returned from the stored procedure. To specify a value for the return status parameters, must execute the stored procedure before using SQLServerCallableStatement registerOutParameter method of the data type of the specified argument.

In addition, when passing the return state 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. For the return state parameter, its ordinal position is always 1, because it is always the first parameter when the stored procedure is called. Although SQLServerCallableStatement class supports the use of parameters to indicate the name of the specific, but you can only to return status parameters using the ordinal position number.

As an example, create the following stored procedures in the SQL Server 2005 AdventureWorks sample database:

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 table person.address.

In the following example, an open connection to the AdventureWorks sample database is passed to this function, and the CheckContactCity stored procedure is invoked using the execute method:

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

Use SQLServerCallableStatement class construction of stored procedure call, you can use any of the execute or executeUpdate methods to invoke the stored procedure. The executeUpdate method returns an int value that contains the number of rows affected by this stored procedure, but the execute method does not. If you use the execute method and want to get a count of the affected rows, 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:

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, an open connection to the AdventureWorks sample database is passed to this function, the UpdateTestTable stored procedure is invoked using the execute method, and the getUpdateCount method returns the count of rows 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();