Java調用SQL Server的預存程序詳解

標籤：

轉載自Microsoft的官方文檔 http://msdn2.microsoft.com/zh-cn/library/ms378995.aspx
收錄於 www.enjoyjava.net/f25

本文較長，包含了如下幾部分

1. 使用不帶參數的預存程序
2. 使用帶有輸入參數的預存程序
3. 使用帶有輸出參數的預存程序
4. 使用帶有返回狀態的預存程序
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 應用程式中使用具名引數。請注意，這些參數名稱對應於預存程序的定義中的參數名稱：CREATE 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();
      }
   }

    

Java調用SQL Server的預存程序詳解