Hibernate：如何调用一个返回varchar的存储函数？ [英] Hibernate: how to call a stored function returning a varchar?

问题描述

我试图使用Hibernate从Java调用Oracle9i数据库中的遗留存储函数。这个函数是这样声明的：

pre $ $ $ $ $ $ $ $ $ $ $创建或替换FUNCTION TransferLocation_Fix（mnemonic_code IN VARCHAR2）
RETURN VARCHAR2

经过多次失败尝试和广泛的搜索后，我发现关于Hibernate论坛的这个线程，它提示了如下映射：

 < sql-query name =TransferLocationFixcallable =true> 
< return-scalar column =retValtype =string/> 
从dual 
中选择Transferlocation_Fix（：助记符）作为retVal< / sql-query> 
  

执行它的代码是

 查询查询= session.getNamedQuery（TransferLocationFix）; 
 query.setParameter（mnemonic，FC3）; 
 String result =（String）query.uniqueResult（）; 
  

以及生成的日志为

<$ p $ $> $ DEBUG（org.hibernate.jdbc.AbstractBatcher：366） - - 打开PreparedStatement（打开PreparedStatements：0，全局：0）
DEBUG（org.hibernate.SQL：401） - - 从
中选择Transferlocation_Fix（？）作为retVal TRACE（org.hibernate.jdbc.AbstractBatcher：484） - - 准备语句
TRACE（org.hibernate.type.StringType：133） - - binding 'FC3'参数：2
TRACE（org.hibernate.type.StringType：133） - - 将'FC3'绑定到参数：2

java.lang.NullPointerException
在oracle.jdbc.ttc7.TTCAdapter.newTTCType（TTCAdapter.java:300）
在oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray（TTCAdapter.java:270）
在oracle.jdbc.ttc7.TTCAdapter .createNonPlsqlTTCDataSet（TTCAdapter.java:231）
在oracle.jdbc.ttc7.TTC7Protocol.doOall7（TTC7Protocol.java:1924）
在oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe（TTC7Protocol.java:850 ）在oracle.jdb上
c.driver.OracleStatement.doExecuteQuery（OracleStatement.java:2599）
在oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout（OracleStatement.java:2963）
在oracle.jdbc.driver.OraclePreparedStatement.executeUpdate（ OraclePreparedStatement.java:658）在oracle.jdbc.driver.OraclePreparedStatement.execute（OraclePreparedStatement.java:736
）
在com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute（NewProxyCallableStatement.java：在org.hibernate.dialect.Oracle8iDialect.getResultSet（Oracle8iDialect.java:379 3044）
）
在org.hibernate.jdbc.AbstractBatcher.getResultSet（AbstractBatcher.java:193）
。在组织.hibernate.loader.Loader.getResultSet（Loader.java:1784）
在org.hibernate.loader.Loader.doQuery（Loader.java:674）
在org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections （Loader.java:236）
在org.hibernate.loader.Loader.doList（Loader.java:2220）
在org.hibernate.loader.Loader.listIgnoreQueryCache（Loader.java:2104）
在org.hibernate.l oader.Loader.list（Loader.java:2099）
在org.hibernate.loader.custom.CustomLoader.list（CustomLoader.java:289）
在org.hibernate.impl.SessionImpl.listCustomQuery（ SessionImpl.java:1695）在org.hibernate.impl.AbstractSessionImpl.list（AbstractSessionImpl.java:142
）
在org.hibernate.impl.SQLQueryImpl.list（SQLQueryImpl.java:152）
at org.hibernate.impl.AbstractQueryImpl.uniqueResult（AbstractQueryImpl.java:811）
at com.my.project.SomeClass.method（SomeClass.java:202）
...

任何线索我做错了什么？或者任何更好的方法来调用这个存储函数？

---

更新：尝试@ axtavt的建议，我得到以下错误：

  ORA-14551：无法在查询中执行DML操作
  

该函数确实进行了大量的插入/更新操作，所以我猜想运行它的唯一方法是使用存储过程语法。

 < sql-query name =TransferLocationFixcallable =true我只是不知道如何映射返回值> 
< return-scalar column =???类型= 字符串/> 
 {？ = call Transferlocation_Fix（：助记符）} 
< / sql-query> 
  

列应该是什么？我会尝试一个空值...

---

Update2：一个SQL语法异常...所以我尝试了Pascal建议的JDBC方式，它似乎工作！我在下面的答案中添加了代码。

解决方案

我不是100％肯定的，我没有测试，但根据Hibernate的文档：

16.2.2。使用存储过程进行查询

Hibernate3通过存储过程和
函数提供对
查询的支持。以下大部分
文档对于两者都是等效的。
存储过程/函数必须返回结果集作为第一个
out参数才能使用
Hibernate 。在Oracle 9和更高版本的
中，这样一个
存储函数的例子如下：

  CREATE OR REPLACE FUNCTION selectAllEmployments 
 RETURN SYS_REFCURSOR 
 AS 
 st_cursor SYS_REFCURSOR; 
开始为
选择雇员，雇主，
 STARTDATE，ENDDATE，
 REGIONCODE，EID，价值，货币
起价就业
打开st_cursor; 
 RETURN st_cursor; 
 END; 
  

要在Hibernate中使用这个查询，
需要通过命名查询映射它。 / p>

 < sql-query name =selectAllEmployees_SPcallable =true> 
< return alias =empclass =Employment> 
< return-property name =employeecolumn =EMPLOYEE/> 
< return-property name =employercolumn =EMPLOYER/> 
< return-property name =startDatecolumn =STARTDATE/> 
< return-property name =endDatecolumn =ENDDATE/> 
< return-property name =regionCodecolumn =REGIONCODE/> 
< return-property name =idcolumn =EID/> 
< return-property name =salary> 
< return-column name =VALUE/> 
< return-column name =CURRENCY/> 
< / return-property> 
< / return> 
 {？ =调用selectAllEmployments（）} 
< / sql-query> 
  

存储过程目前只有
返回标量和实体。不支持
< return-join> 和
< load-collection> 。

16.2.2.1。使用存储过程的规则/限制

除非遵循一些
过程/函数规则，否则不能将存储过程与
Hibernate一起使用。 如果他们做
不遵循这些规则，他们不是
可用于Hibernate。如果你还有
想要使用这些程序，你可以通过
session.connection（） 执行
。对于每个数据库，这些规则是
不同的，因为
数据库供应商具有不同的存储的
过程语义/语法。

存储过程查询不能（
setFirstResult（）/ setMaxResults（）。

建议的呼叫表单是标准
SQL92： {？ = call
functionName（< parameters>）} 或 {？
= call procedureName（< parameters>} 。不支持本地调用语法。

• 函数必须返回结果集。
  过程的第一个参数必须是一个OUT，它返回
  a结果集，这是通过在Oracle 9中使用
  SYS_REFCURSOR类型或
  10来完成的。在Oracle中，您需要定义REF CURSOR类型。请参阅Oracle文献
  以获取更多信息。
  
  
  $ p
  $ /
  
  

  正如我所说，我不确定，但我的理解是，您必须在此处使用 session.getConnection（）。

  
  

  I am trying to call a legacy stored function in an Oracle9i DB from Java using Hibernate. The function is declared like this:

  create or replace FUNCTION Transferlocation_Fix (mnemonic_code IN VARCHAR2)
     RETURN VARCHAR2
  

  After several failed tries and extensive googling, I found this thread on the Hibernate forums which suggested a mapping like this:

  <sql-query name="TransferLocationFix" callable="true">
      <return-scalar column="retVal" type="string"/>
      select Transferlocation_Fix(:mnemonic) as retVal from dual
  </sql-query>
  

  My code to execute it is

      Query query = session.getNamedQuery("TransferLocationFix");
      query.setParameter("mnemonic", "FC3");
      String result = (String) query.uniqueResult();
  

  and the resulting log is

  DEBUG (org.hibernate.jdbc.AbstractBatcher:366) -  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
  DEBUG (org.hibernate.SQL:401) -  - select Transferlocation_Fix(?) as retVal from dual
  TRACE (org.hibernate.jdbc.AbstractBatcher:484) -  - preparing statement
  TRACE (org.hibernate.type.StringType:133) -  - binding 'FC3' to parameter: 2
  TRACE (org.hibernate.type.StringType:133) -  - binding 'FC3' to parameter: 2
  
  java.lang.NullPointerException
  at oracle.jdbc.ttc7.TTCAdapter.newTTCType(TTCAdapter.java:300)
  at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray(TTCAdapter.java:270)
  at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCDataSet(TTCAdapter.java:231)
  at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1924)
  at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
  at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
  at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:736)
  at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:3044)
  at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:379)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:193)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1784)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
  at com.my.project.SomeClass.method(SomeClass.java:202)
  ...
  

  Any clues what am I doing wrong? Or any better ways to call this stored function?

  ---

  Update: upon trying @axtavt's suggestion, I get the following error:

  ORA-14551: cannot perform a DML operation inside a query
  

  The function indeed does extensive inserts/updates, so I guess the only way to run it would be using the stored procedure syntax. I just have no clue how to map the return value:

  <sql-query name="TransferLocationFix" callable="true">
      <return-scalar column="???" type="string"/>
      { ? = call Transferlocation_Fix(:mnemonic) }
  </sql-query>
  

  What should be the column? I will try an empty value...

  ---

  Update2: that failed as well, with an SQL Grammar Exception... So I tried the JDBC way as suggested by Pascal, and it seems to work! I added the code in an answer below.

  解决方案

  I am not 100% sure and I didn't test it but according to Hibernate's documentation:

  16.2.2. Using stored procedures for querying

  Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

  CREATE OR REPLACE FUNCTION selectAllEmployments
      RETURN SYS_REFCURSOR
  AS
      st_cursor SYS_REFCURSOR;
  BEGIN
      OPEN st_cursor FOR
   SELECT EMPLOYEE, EMPLOYER,
   STARTDATE, ENDDATE,
   REGIONCODE, EID, VALUE, CURRENCY
   FROM EMPLOYMENT;
        RETURN  st_cursor;
   END;
  

  To use this query in Hibernate you need to map it via a named query.

  <sql-query name="selectAllEmployees_SP" callable="true">
      <return alias="emp" class="Employment">
          <return-property name="employee" column="EMPLOYEE"/>
          <return-property name="employer" column="EMPLOYER"/>
          <return-property name="startDate" column="STARTDATE"/>
          <return-property name="endDate" column="ENDDATE"/>
          <return-property name="regionCode" column="REGIONCODE"/>
          <return-property name="id" column="EID"/>
          <return-property name="salary">
              <return-column name="VALUE"/>
              <return-column name="CURRENCY"/>
          </return-property>
      </return>
      { ? = call selectAllEmployments() }
  </sql-query>
  

  Stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

  16.2.2.1. Rules/limitations for using stored procedures

  You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

  Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

  The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

  For Oracle the following rules apply:

  • A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

  ...

  As I said, I'm not sure but my understanding is that you'll have to use session.getConnection() here.

  这篇关于Hibernate：如何调用一个返回varchar的存储函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！