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 '%'+@Name+'%' 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 id=@id
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 '%'+@GroupName+'%'
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存储过程

最新文章

热门文章