java.sql.SQLException：过程或函数...期望参数...，未提供

While executing daoMethod() I am getting the following exception:

执行daoMethod()时,我收到以下异常:

java.sql.SQLException: Procedure or function 'Get_Books' expects parameter '@totalRowsReturned', which was not supplied.

值java.sql.SQLException:过程或函数“Get_Books”预计参数“@totalRowsReturned”,但未提供。

Why? I did define @totalRowsReturned as OUTPUT. And I do not understand why I am required to supply @totalRowsReturned - it is an output parameter, not input.

为什么?我确实将@totalRowsReturned定义为OUTPUT。我不明白为什么我需要提供@totalRowsReturned - 它是一个输出参数,而不是输入。

Dao Class:

public class BookDao {

    @Autowired
    DataSource dataSource;

    public void daoMethod() {

        Integer programIdLocal = null;

        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("bookId", 1);

        MyStoredProcedure storedProcedure = new MyStoredProcedure(dataSource);      

        //Exception!!!!
        Map<String, Object> results = storedProcedure.execute(parameters);

    }

    private class MyStoredProcedure extends StoredProcedure {

        private static final String SQL = "dbo.Get_Books";

        public MyStoredProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(true);
            setSql(SQL);

            declareParameter(new SqlReturnResultSet("rs", new BookMapper()));

            declareParameter(new SqlOutParameter("totalRowsReturned", Types.INTEGER));

            declareParameter(new SqlParameter("bookId", Types.INTEGER));

            setFunction(true);

            compile();
        }

    }   
}

Stored Procedure:

CREATE PROCEDURE [dbo].[Get_Books]

    @bookId int,
    @totalRowsReturned int OUTPUT

AS

BEGIN

  SET NOCOUNT ON;
  DECLARE @SelectQuery NVARCHAR(2000)

  DECLARE @first_id int
  DECLARE @totalRows int

  SET @SelectQuery = 'FROM books b WHERE b.book_id >= @bookId'

  Set @SelectQuery = 'SELECT @first_id = b.book_id , @totalRows=Count(*) OVER() ' + @SelectQuery + ' ORDER BY b.book_id'
  Execute sp_Executesql @SelectQuery, N'@first_id int, @bookId int, @totalRows int OUTPUT', @first_id, @bookId, @totalRows=@totalRowsReturned OUTPUT

END

1 个解决方案

#1

There's a important caveat in the Javadoc for StoredProcedure#declareParameter() that you must declare the parameters in the order they are declared in the stored procedure, presumably because the same restriction exists for the underlying CallableStatement class. That means you should be declaring @bookId before @totalRowsReturned.

在Javadoc for StoredProcedure#declareParameter()中有一个重要的警告,你必须按照它们在存储过程中声明的顺序声明参数,大概是因为底层CallableStatement类存在相同的限制。这意味着你应该在@totalRowsReturned之前声明@bookId。

Also, I'm not all that knowledgable about JdbcTemplate but, based on this example, I don't think you need to declare a result set parameter.

另外,我并不是所有关于JdbcTemplate的知识,但基于这个例子,我认为你不需要声明一个结果集参数。