java代码使存储过程返回DAO层中的结果集 [英] java code make a stored procedure return a resultset in DAO layer

问题描述

我有一个在 MS SQL Server 上运行的存储过程.它需要一个参数作为输入.基本上，它返回多行.我正在使用 CallableStatement 从我的 Java 应用程序调用 SP.

I have a Stored Procedure that runs on MS SQL Server. It takes one parameter as input. Basically, it returns multiple rows. I am calling the SP from my java application by using CallableStatement.

我想知道是否可以在我的 DAO 层中以 ResultSet 的形式获取存储过程返回的行?[就像我们在执行 select * from EmployeeTable>] .如果是，我们该怎么做?

I would like to know if it possible to get the rows returned by the stored procedure in the form of ResultSet in my DAO layer?[like how we get a resultset when we do select * from EmployeeTable] . If yes, how do we do that?

P.S:我没有修改存储过程的权限.

P.S: I don’t have privilege to modify the stored procedure.

推荐答案

SQL Server 知道两种返回结果的过程:

SQL Server knows two types of procedures returning results:

过程如下:

CREATE PROCEDURE p_results(
  @p_result_sets INT
)
AS
BEGIN
  IF @p_result_sets = 1 BEGIN
    SELECT 1 a;
  END
  ELSE IF @p_result_sets = 2 BEGIN
    SELECT 1 a;
    SELECT 1 b UNION SELECT 2 b;
  END
END;

在这种情况下，您事先不知道结果集会是什么样子，以及您将获得多少个结果集.您必须使用 <运行该过程code>Statement.execute()如下:

In this case, you don't know in advance what the result sets will look like, and how many of them you'll get. You will have to run the procedure using Statement.execute() as follows:

try (CallableStatement stmt = con.prepareCall("...")) {
    boolean results = stmt.execute();

    for (;;) {
        if (results)
            try (ResultSet rs = stmt.getResultSet()) {
                // ... Fetch your results here
            }
        else if (stmt.getUpdateCount() != -1) {}
        else
            break;

        results = stmt.getMoreResults();
    }

    // After all results are fetched, you can also retrieve OUT parameters, if applicable
}

表值函数

函数看起来像这样:

Table-valued functions

The function looks something like this:

CREATE FUNCTION f_tables1 ()
RETURNS @out_table TABLE (
  column_value INTEGER
)
AS
BEGIN
  INSERT @out_table
  VALUES (1)
  RETURN
END

在这种情况下，您实际上并不需要 CallableStatement.一个普通的 SELECT 语句就可以:

In this case, you don't really need a CallableStatement. An ordinary SELECT statement will do:

try (PreparedStatement stmt = con.prepareStatement("SELECT * FROM f_tables1()");
     ResultSet rs = stmt.executeQuery()) {
    // ... Fetch your results here
}

这篇关于java代码使存储过程返回DAO层中的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！