java 调用MSSQL的存储过程

java调用存储过程

语句写法：

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
   {call <procedure-name>[(<arg1>,<arg2>, ...)]}

一。获取存储过程的返回值

存储过程

create procedure GetComputer
as
    select * from t_computer
    if @@error <> 0 return @@error
    return 0

java代码

	/**
	 * 得到存储过程返回的结果集
	 */
	@Test
	public void getProcedureResultSet(){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			conn= DBUtils.getConn();
			cs = conn.prepareCall("{call GetComputer}");
			rs = cs.executeQuery();
			while (rs.next()) {
				System.out.println("分机号:" + rs.getString("extention"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, cs, conn);
		}
	}

二，得到存储过程的多个返回值

存储过程

create procedure [dbo].[GetComputerAndAccount]
as
 select * from t_computer
 select * from tx_account

java代码

	/**
	 * 得到存储过程返回的多结果集
	 */
	@Test
	public void getProcedureMoreResultSet(){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			conn= DBUtils.getConn();
			cs = conn.prepareCall("{call GetComputerAndAccount}");
			rs = cs.executeQuery();
			while (rs.next()) {
				System.out.println("分机号:" + rs.getString("extention"));
			}
			if (cs.getMoreResults()) {
				rs = cs.getResultSet();
				while (rs.next()) {
					System.out.println("帐号:" + rs.getString("account_name"));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, cs, conn);
		}
	}

三，得到存储过程的output参数

存储过程

create procedure [dbo].[GetComputerCount]
    @computer varchar(16),
    @count int output
as
 select @count=count(*) from t_computer where computer=@computer

java代码

	/**
	 * 得到存储过程的output参数的值
	 */
	@Test
	public void getProcedureOutputValue(){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			conn= DBUtils.getConn();
			cs = conn.prepareCall("{call GetComputerCount(?,?)}");
			cs.setString(1, "127.0.0.1");
			cs.registerOutParameter(2, Types.INTEGER);
			cs.execute();
			System.out.println(cs.getInt(2));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, cs, conn);
		}
	}

四，得到存储过程的返回值

存储过程

create procedure [dbo].[IsExistComputer]
 @extension varchar(16)
as
 select computer from t_computer where extention=@extension
 if @@rowcount > 0 
   return 1
 else
   return 0

java代码

	/**
	 * 得到存储过程的返回值
	 */
	@Test
	public void getProcedureReturnValue(){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			conn= DBUtils.getConn();
			cs = conn.prepareCall("{? = call IsExistComputer(?)}");
			cs.registerOutParameter(1, Types.INTEGER);
			cs.setString(2, "3001");
			cs.execute();
			System.out.println(cs.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, cs, conn);
		}
	}

五。得到存储过程的数据集和output参数

存储过程

create procedure [dbo].[GetComputer2]
	@computer varchar(16),
    @count int output
as
 select @count=count(*) from t_computer where computer=@computer
 select * from t_computer where computer=@computer

java代码

	/**
	 * 得到存储过程返回的结果集和output参数
	 * 注：同时存在结果集与output值或者Result值，应该先获取结果集，
	 *     对结果集操作后在去获取其它值，不然结果集会关闭
	 */
	@Test
	public void getProcedureResultSetAndOutputValue(){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			conn= DBUtils.getConn();
			cs = conn.prepareCall("{call GetComputer2(?,?)}");
			cs.setString(1, "127.0.0.1");
			cs.registerOutParameter(2, Types.INTEGER);
			//cs.execute();
			rs = cs.executeQuery();
			//先获取output参数值，后取结果集的话，则得不到结果集
			//com.microsoft.sqlserver.jdbc.SQLServerException: 结果集已关闭。
			//System.out.println(cs.getInt(2));
			//rs = cs.getResultSet(); 
			if (rs != null){
				while (rs.next()) {
					System.out.println(rs.getString("extention"));
				}
			}
			System.out.println(cs.getInt(2));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, cs, conn);
		}
	}