java - 如何使用 Hibernate 4 调用具有多个输出参数的 MS SQL 存储过程？

Question

同事们，您能帮我使用 Hibernate 4 调用具有两个输入和两个输出参数的 MS SQL 2008 存储过程吗？存储过程如下所示:

PROCEDURE Product_Create(
        @Return  int  output,
        @ProdID numeric(18,0)  output,

        @PassID numeric(18,0),
        @Amount numeric(18,2))

我正在尝试使用 session.createSQLQuery 方法来调用过程:

Query qr = session.createSQLQuery("{? = Product_Create (:PassID, :Amount")});
qr.setParameter("PassID", new BigInteger("999999999"));
qr.setParameter("Amount", new BigDecimal("87384738"));

但是有一些不清楚的时刻:

1. 如何注册多个输出参数？
2. session.createSQLQuery 方法是否适合调用具有多个输出参数的存储过程？

更新

我有一个 MS SQL 过程

PROCEDURE Product_Create(
        @Return  int  output,
        @ProdID numeric(18,0)  output,

        @IN$PassID numeric(18,0),
        @IN$Amount numeric(18,2))

并有映射文件:

<hibernate-mapping>
    <sql-query name="Product_Create" callable="true">
        <return-scalar column = "Return" type="int"/>
        <return-scalar column = "ProdID" type="long"/>
        <![CDATA[CALL IProduct_Create (:@IN$PassID, :@IN$Amount)]]>
  </sql-query>
</hibernate-mapping>

当我运行应用程序时，我收到异常:

WARN : [Tue-06 10:11:39,374] jdbc.spi.SqlExceptionHelper - SQL Error: 102, SQLState: S0001 ERROR: [Tue-06 10:11:39,374] jdbc.spi.SqlExceptionHelper - Incorrect syntax near '@P0'. Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:130) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:76) at org.hibernate.loader.Loader.getResultSet(Loader.java:2066) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839) at org.hibernate.loader.Loader.doQuery(Loader.java:910) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355) at org.hibernate.loader.Loader.doList(Loader.java:2554) at org.hibernate.loader.Loader.doList(Loader.java:2540) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370) at org.hibernate.loader.Loader.list(Loader.java:2365) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332) at org.hibernate.dialect.AbstractTransactSQLDialect.getResultSet(AbstractTransactSQLDialect.java:226) at org.hibernate.dialect.SQLServerDialect.getResultSet(SQLServerDialect.java:42) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:121) ... 16 more

我尝试做一些改变

  <![CDATA[CALL IProduct_Create (:@IN$PassID, :@IN$Amount)]]>

但没有成功。

我的映射出了什么问题？

更新1

添加了括号 {} 来调用过程。当调用看起来像

时它会起作用

 <![CDATA[{CALL Product_Create (:@IN$PassID, :@IN$Amount)}]]>

Answer 1

使用getNamedQuery()调用它。

Query query = session.getNamedQuery("product_create")
    .setParameter("PassID", new BigInteger("999999999"))
    .setParameter("Amount", new BigDecimal("87384738"));

List result = query.list();  // here you have DTO List i.e. List<Product>