Java呼叫SQL Server的儲存過程詳解

本文作者介紹了通過Java如何去呼叫SQL Server的儲存過程，詳解了5種不同的儲存。詳細請看下文

1、使用不帶引數的儲存過程

使用 JDBC 驅動程式呼叫不帶引數的儲存過程時，必須使用 call SQL 轉義序列。不帶引數的 call 轉義序列的語法如下所示：

{call procedure-name}

作為例項，在 SQL Server 2005 AdventureWorks 示例資料庫中建立以下儲存過程：

CREATE PROCEDURE GetContactFormalNames  
AS 
BEGIN 
 SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName  
 FROM Person.Contact  
END

此儲存過程返回單個結果集，其中包含一列資料(由 Person.Contact 表中前十個聯絡人的稱呼、名稱和姓氏組成)。

在下面的例項中，將向此函式傳遞 AdventureWorks 示例資料庫的開啟連線，然後使用 executeQuery 方法呼叫 GetContactFormalNames 儲存過程。

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、使用帶有輸入引數的儲存過程

使用 JDBC 驅動程式呼叫帶引數的儲存過程時，必須結合 SQLServerConnection 類的 prepareCall 方法使用 call SQL 轉義序列。帶有 IN 引數的 call 轉義序列的語法如下所示：

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

構造 call 轉義序列時，請使用 ?(問號)字元來指定 IN 引數。此字元充當要傳遞給該儲存過程的引數值的佔位符。可以使用 SQLServerPreparedStatement 類的 setter 方法之一為引數指定值。可使用的 setter 方法由 IN 引數的資料型別決定。

向 setter 方法傳遞值時，不僅需要指定要在引數中使用的實際值，還必須指定引數在儲存過程中的序數位置。例如，如果儲存過程包含單個 IN 引數，則其序數值為 1。如果儲存過程包含兩個引數，則第一個序數值為 1，第二個序數值為 2。

作為如何呼叫包含 IN 引數的儲存過程的例項，使用 SQL Server 2005 AdventureWorks 示例資料庫中的 uspGetEmployeeManagers 儲存過程。此儲存過程接受名為 EmployeeID 的單個輸入引數(它是一個整數值)，然後基於指定的 EmployeeID 返回僱員及其經理的遞迴列表。下面是呼叫此儲存過程的 Java 程式碼：

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、使用帶有輸出引數的儲存過程

使用 JDBC 驅動程式呼叫此類儲存過程時，必須結合 SQLServerConnection 類的 prepareCall 方法使用 call SQL 轉義序列。帶有 OUT 引數的 call 轉義序列的語法如下所示：

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

構造 call 轉義序列時，請使用 ?(問號)字元來指定 OUT 引數。此字元充當要從該儲存過程返回的引數值的佔位符。要為 OUT 引數指定值，必須在執行儲存過程前使用 SQLServerCallableStatement 類的 registerOutParameter 方法指定各引數的資料型別。

使用 registerOutParameter 方法為 OUT 引數指定的值必須是 java.sql.Types 所包含的 JDBC 資料型別之一，而它又被對映成本地 SQL Server 資料型別之一。有關 JDBC 和 SQL Server 資料型別的詳細資訊，請參閱瞭解 JDBC 驅動程式資料型別。

當您對於 OUT 引數向 registerOutParameter 方法傳遞一個值時，不僅必須指定要用於此引數的資料型別，而且必須在儲存過程中指定此引數的序號位置或此引數的名稱。例如，如果儲存過程包含單個 OUT 引數，則其序數值為 1;如果儲存過程包含兩個引數，則第一個序數值為 1，第二個序數值為 2。

作為例項，在 SQL Server 2005 AdventureWorks 示例資料庫中建立以下儲存過程： 根據指定的整數 IN 引數 (employeeID)，該儲存過程也返回單個整數 OUT 引數 (managerID)。根據 HumanResources.Employee 表中包含的 EmployeeID，OUT 引數中返回的值為 ManagerID。

在下面的例項中，將向此函式傳遞 AdventureWorks 示例資料庫的開啟連線，然後使用 execute 方法呼叫 GetImmediateManager 儲存過程：

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();  
 }  
}

本示例使用序號位置來標識引數。或者，也可以使用引數的名稱(而非其序號位置)來標識此引數。下面的程式碼示例修改了上一個示例，以說明如何在 Java 應用程式中使用命名引數。請注意，這些引數名稱對應於儲存過程的定義中的引數名稱： 11x16CREATE PROCEDURE GetImmediateManager

@employeeID INT,  
 @managerID INT OUTPUT 
AS 
BEGIN 
 SELECT @managerID = ManagerID  
 FROM HumanResources.Employee  
 WHERE EmployeeID = @employeeID  
END

儲存過程可能返回更新計數和多個結果集。Microsoft SQL Server 2005 JDBC Driver 遵循 JDBC 3.0 規範，此規範規定在檢索 OUT 引數之前應檢索多個結果集和更新計數。也就是說，應用程式應先檢索所有 ResultSet 物件和更新計數，然後使用 CallableStatement.getter 方法檢索 OUT 引數。否則，當檢索 OUT 引數時，尚未檢索的 ResultSet 物件和更新計數將丟失。

4、使用帶有返回狀態的儲存過程

使用 JDBC 驅動程式呼叫這種儲存過程時，必須結合 SQLServerConnection 類的 prepareCall 方法使用 call SQL 轉義序列。返回狀態引數的 call 轉義序列的語法如下所示：

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

構造 call 轉義序列時，請使用 ?(問號)字元來指定返回狀態引數。此字元充當要從該儲存過程返回的引數值的佔位符。要為返回狀態引數指定值，必須在執行儲存過程前使用 SQLServerCallableStatement 類的 registerOutParameter 方法指定引數的資料型別。

此外，向 registerOutParameter 方法傳遞返回狀態引數值時，不僅需要指定要使用的引數的資料型別，還必須指定引數在儲存過程中的序數位置。對於返回狀態引數，其序數位置始終為 1，這是因為它始終是呼叫儲存過程時的第一個引數。儘管 SQLServerCallableStatement 類支援使用引數的名稱來指示特定引數，但您只能對返回狀態引數使用引數的序號位置編號。

作為例項，在 SQL Server 2005 AdventureWorks 示例資料庫中建立以下儲存過程：

CREATE PROCEDURE CheckContactCity  
 (@cityName CHAR(50))  
AS 
BEGIN 
 IF ((SELECT COUNT(*)  
 FROM Person.Address  
 WHERE City = @cityName) > 1)  
 RETURN 1  
ELSE 
 RETURN 0  
END

該儲存過程返回狀態值 1 或 0，這取決於是否能在表 Person.Address 中找到 cityName 引數指定的城市。

在下面的例項中，將向此函式傳遞 AdventureWorks 示例資料庫的開啟連線，然後使用 execute 方法呼叫 CheckContactCity 儲存過程：

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、使用帶有更新計數的儲存過程

使用 SQLServerCallableStatement 類構建對儲存過程的呼叫之後，可以使用 execute 或 executeUpdate 方法中的任意一個來呼叫此儲存過程。executeUpdate 方法將返回一個 int 值，該值包含受此儲存過程影響的行數，但 execute 方法不返回此值。如果使用 execute 方法，並且希望獲得受影響的行數計數，則可以在執行儲存過程後呼叫 getUpdateCount 方法。

作為例項，在 SQL Server 2005 AdventureWorks 示例資料庫中建立以下表和儲存過程：

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;

在下面的例項中，將向此函式傳遞 AdventureWorks 示例資料庫的開啟連線，並使用 execute 方法呼叫 UpdateTestTable 儲存過程，然後使用 getUpdateCount 方法返回受儲存過程影響的行計數。

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();