Java Hibernate：如何调用返回 varchar 的存储函数？

Question

提问by Péter T?r?k

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

我正在尝试使用 Hibernate 从 Java 调用 Oracle9i DB 中的旧存储函数。函数声明如下：

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

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

经过几次失败的尝试和广泛的谷歌搜索后，我在 Hibernate 论坛上找到了这个帖子，它建议了这样的映射：

<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:

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

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...

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

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.

更新 2：那也失败了，出现了 SQL 语法异常......所以我按照 Pascal 的建议尝试了 JDBC 方式，它似乎有效！我在下面的答案中添加了代码。

Answer 1

采纳答案by Pascal Thivent

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

我不是 100% 确定，我没有测试它，但根据 Hibernate 的文档：

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.
...

16.2.2. 使用存储过程进行查询
Hibernate3 通过存储过程和函数提供对查询的支持。以下大多数文档对两者都是等效的。 存储过程/函数必须返回一个结果集作为第一个输出参数才能使用 Hibernate。Oracle 9 及更高版本中此类存储函数的示例如下：
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;
要在 Hibernate 中使用此查询，您需要通过命名查询来映射它。
<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>
存储过程目前只返回标量和实体。 <return-join>并且 <load-collection>不受支持。
16.2.2.1. 使用存储过程的规则/限制
除非您遵循某些过程/函数规则，否则您不能在 Hibernate 中使用存储过程。如果它们不遵循这些规则，它们就不能与 Hibernate 一起使用。如果您仍想使用这些过程，则必须通过 session.connection(). 每个数据库的规则都不同，因为数据库供应商有不同的存储过程语义/语法。
存储过程查询不能用分页 setFirstResult()/setMaxResults()。
推荐的调用形式是标准 SQL92:{ ? = call functionName(<parameters>) }或{ ? = call procedureName(<parameters>}. 不支持本机调用语法。
对于 Oracle，以下规则适用：
一个函数必须返回一个结果集。过程的第一个参数必须是返回结果集的 OUT。这是通过在 Oracle 9 或 10 中使用 SYS_REFCURSOR 类型来完成的。在 Oracle 中，您需要定义一个 REF CURSOR 类型。有关详细信息，请参阅 Oracle 文献。
...

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

正如我所说，我不确定，但我的理解是你必须session.getConnection()在这里使用。

Answer 2

回答by axtavt

callable = trueis for calling stored procedures with {? = call ...()}syntax. Oracle's select ... from dualsyntax is a normal query, so you don't need callable = true:

callable = true用于调用带有{? = call ...()}语法的存储过程。Oracle 的select ... from dual语法是普通查询，因此您不需要callable = true：

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

Answer 3

回答by Péter T?r?k

For further reference, here is my final solution:

为了进一步参考，这是我的最终解决方案：

CallableStatement statement = session.connection().prepareCall(
        "{ ? = call Transferlocation_Fix(?) }");
statement.registerOutParameter(1, Types.VARCHAR);
statement.setString(2, "FC3");
statement.execute();
String result = statement.getString(1);

Answer 4

回答by len

I've run into a similar question/problem and I've come to realize the changes should be done on the sql part as hibernate works only with cursor returns. I've described everything here: http://www.len.ro/2011/10/call-oracle-procedure-from-hibernate/

我遇到了类似的问题/问题，并且我已经意识到应该在 sql 部分进行更改，因为 hibernate 仅适用于游标返回。我在这里描述了一切：http: //www.len.ro/2011/10/call-oracle-procedure-from-hibernate/

Java Hibernate：如何调用返回 varchar 的存储函数？

提问by Péter T?r?k

采纳答案by Pascal Thivent

16.2.2. Using stored procedures for querying

16.2.2.1. Rules/limitations for using stored procedures

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

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

回答by axtavt

回答by Péter T?r?k

回答by len

相关推荐

最近更新

标签