存储过程仅返回多行中的一行 [英] stored procedure returns just one row of multiple rows

问题描述

我将从为您提供我的餐桌内容开始

I will start by giving you my table content

这是我的存储过程:

ALTER PROCEDURE dbo.getFoodsForOrder
    (
    @orderID INT,
    @ID INT OUTPUT,
    @food_restaurantID INT OUTPUT,
    @count INT OUTPUT,
    @description VARCHAR(200) OUTPUT
    )
AS
SET NOCOUNT ON
BEGIN
    SELECT [ID],
    [food_restaurantID],
    [count],
    [description]
    FROM Order_Food
    WHERE orderID = @orderID
END

我的问题

当我像这样从JDBC调用存储过程时

My Problem

when I call the stored procedure from JDBC like this

Connection con = Database.getConnection();
        CallableStatement callableStatement = null;
        try {
            callableStatement = con
                    .prepareCall("{call getFoodsForOrder(?,?,?,?,?)}");
            callableStatement.setInt(1, getID());
            callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(3, java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(4, java.sql.Types.INTEGER);
            callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
            System.out.println("ID   = " + getID());
            boolean haveResult = callableStatement.execute();
            while (haveResult) {
                System.out.println("here I am");
                haveResult = callableStatement.getMoreResults();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (callableStatement != null)
                    callableStatement.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

它只打印一次here I am，一次，即使我有15行以上(如图片所示).

it just print here I am once, just once, even If (like the picture said) I have more than 15 rows are ture.

您的问题会问我是否确定getID()方法，是的，即使在将其替换为显式10时，结果也不会改变.

you problem will ask me if I am sure about the getID() method, Yes I am sure and even when I replace it with explicit 10 , the result doesn't change.

当我从Visual Studio调用该存储过程时，我得到了17个这样的结果:

when I call that stored procedure from my Visual studio , I got 17 results like this:

推荐答案

callableStatement.execute()表示如果可用的结果集.

The callableStatement.execute() indicates if a result set is available.

getMoreResults()仅指示是否存在另一个 ResultSet ，而不是如果最后一个"结果集具有更多行(这-我认为-很清楚从JavaDocs).如果您的过程中有多个select语句，则getMoreResults()将返回true.

getMoreResults() only indicates if there are is another ResultSet available, not if the "last" result set has more rows (which is - I think - pretty clear from the JavaDocs). getMoreResults() would return true if you had more than one select statement in your procedure.

如果execute()指示结果集可用可用，则需要使用callableStatement.getResultSet()获取它，然后遍历由此返回的行.

If execute() indicates a ResultSet is available you need to obtain it using callableStatement.getResultSet() and then iterate over the rows returned by that.

类似:

boolean haveResult = callableStatement.execute();
if (haveResult) {
   ResultSet rs = callableStatement.getResultSet();
   while (rs.next()) {
      System.out.println(rs.getInt(1));
   }
} else {
  System.out.println("Nothing returned");
}

但是知道后，它会返回结果集，您也可以立即调用getResultSet()，而不必担心execute()调用的返回值.

But as you know it returns a result set you can also call getResultSet() right away without even bothering about the return value of the execute() call.

这篇关于存储过程仅返回多行中的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！