Java調用SQL Server預存程序

標籤：

1、調用普通預存程序
（1）建立預存程序
CREATE Procedure [dbo].[GetContactListByName]  /*根據連絡人姓名擷取連絡人資訊*/
@Name nvarchar(50)
As
begin
    select Contact.Id,Contact.Name,Phone,Email,QQ,GroupName from Contact,ContactGroup
 where Contact.GroupId=ContactGroup.Id and Name like ‘%‘[email protected]+‘%‘  order by Contact.Id desc
end
（2）Java代碼
final String DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
final String DATABASE_URL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=AddressList";
final String DATABASE_USRE = "sa";
final String DATABASE_PASSWORD = "1234";
try {
    Class.forName(DRIVER_CLASS);
    Connection connection=DriverManager.getConnection(DATABASE_URL,DATABASE_USRE,DATABASE_PASSWORD);
    CallableStatement callableStatement=connection.prepareCall("{call GetContactListByName(?)}");
    callableStatement.setString(1, name);
    ResultSet resultSet=callableStatement.executeQuery();
    while(resultSet.next()){
        int id=resultSet.getInt(1);
        String string=resultSet.getString(2);
        System.out.println(id+","+string);
    }
} catch (Exception e) {
    // TODO: handle exception
    e.printStackTrace();
}
注意：如果預存程序無參數，則不需要寫括弧，如
CallableStatement callableStatement=connection.prepareCall("{call GetAllContactGroup}");
        
2、調用包含傳回值及輸出參數的預存程序
（1）建立預存程序
USE [AddressList]
GO
CREATE PROCEDURE [dbo].[GetGroupById]  /*根據分組編號擷取分組資訊*/
 @GroupName nvarchar(50) OUTPUT,   /*輸出參數*/
 @Memo nvarchar(200) OUTPUT,      /*輸出參數*/
 @id int
AS
BEGIN
 select @GroupName=GroupName,@Memo=Memo from ContactGroup where [email protected]
 if @@Error<>0
  RETURN -1   /*傳回值*/
 else
  RETURN 0   /*傳回值*/
END
（2）Java代碼
    CallableStatement callableStatement=connection.prepareCall("{?=call GetGroupById(?,?,?)}");
    //傳回值
    callableStatement.registerOutParameter(1, Types.INTEGER);
    //輸出參數
    callableStatement.registerOutParameter(2, Types.VARCHAR);
    //輸出參數
    callableStatement.registerOutParameter(3, Types.VARCHAR);
    //輸入參數
    callableStatement.setInt(4, 2);
    callableStatement.execute();
    //獲得傳回值
    int returnValue=callableStatement.getInt(1);
    //獲得輸出參數
    String groupName=callableStatement.getString(2);
    //獲得輸出參數
    String memo=callableStatement.getString(3);
    System.out.println(returnValue);
    System.out.println(groupName);
    System.out.println(memo);
} catch (Exception e) {
    // TODO: handle exception
    e.printStackTrace();
}
3、調用包含輸入輸出參數的預存程序。
（1）建立預存程序
USE [AddressList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[test]
 @GroupName nvarchar(50) output
AS
BEGIN
 select @GroupName=GroupName from ContactGroup where GroupName like ‘%‘[email protected]+‘%‘
END
（2）Java代碼
CallableStatement callableStatement=connection.prepareCall("{call test(?)}");
callableStatement.setString(1, name);
callableStatement.registerOutParameter(1, Types.VARCHAR);
callableStatement.execute();
String string=callableStatement.getString(1);
System.out.println(string);

Java調用SQL Server預存程序