java调用sql server存储过程

使用java调用带有输出参数的存储过程

本例存储过程的作用为：查询用户输入的ip地址所在的位置信息（国家，省份，城市）

USE [databaseName]
GO
/****** Object:  StoredProcedure [dbo].[procedureName]    Script Date: 12/27/2019 01:31:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[procedureName]
	@ip varchar(50),
	@country char(2) out,
	@province nvarchar(255) out,
	@city nvarchar(255) out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    select top 1 @country=country,@province=province,@city=city from tableName where ip_start<=@ip order by ip_start desc
END

先来看下在sql server 中可以怎么执行此存储过程

DECLARE	
		@country char(2),
		@province nvarchar(255),
		@city nvarchar(255)
		
exec p_dbip_city_sByIp  N'123.157.221.126',
		@country OUTPUT ,
		@province OUTPUT ,
		@city OUTPUT 
select @country,@province,@city

返回结果

与在百度上查询结果一致

然后看一下使用java语言如何调用

@Override
    public List<IpWarehouse> query(String url) {
        logger.info("call query url : " + url);
        //用户返回（其实本例中返回的只有一个对象，可以修改为返回对象而不是list）
        List<IpWarehouse> ipWarehouseList = Lists.newArrayList();
        try {
            //加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //调用存储过程
            String procName = "{Call p_dbip_city_sByIp(?,?,?,?)}";
            //获取连接 根据sql server的地址,用户名，密码
            java.sql.Connection conn = DriverManager.getConnection(procUrl, procUserName, procPassword);
            CallableStatement pstmt = conn.prepareCall(procName);
            //设置输入参数，注意位置下标
            pstmt.setString(1, url);
            // 注册输出参数 返回信息
            pstmt.registerOutParameter(2,java.sql.Types.VARCHAR);
            pstmt.registerOutParameter(3,java.sql.Types.VARCHAR);
            pstmt.registerOutParameter(4,java.sql.Types.VARCHAR);
            //执行
            pstmt.execute();
            //定义一个对象用户接收返回信息
            //返回的不是一个ResultSet，所以不能用ResultSet接收返回对象
            IpWarehouse ipWarehouse = new IpWarehouse();
            
            ipWarehouse.setIp(url);
            ipWarehouse.setCountry(pstmt.getString(2));
            ipWarehouse.setRegion(pstmt.getString(3));
            ipWarehouse.setCity(pstmt.getString(4));
            ipWarehouseList.add(ipWarehouse);
        } catch (Exception e) {
            e.printStackTrace();
        }


        return ipWarehouseList;
    }

调用此服务地址，返回结果为：