用Hibernate执行Oracle存储过程(Executing Oracle Stored Procedure with Hibernate)

 我一直在尝试使用Hibernate来执行Oracle存储过程。 这不是为了生产 - 而是为了我正在追求的Java源代码解析项目。 简而言之，我无法从Oracle存储过程中返回一个值。 
 
 我已经搜索并阅读了SO，Hibernate社区/文档（Native SQL章节）链接中的所有相关链接并尝试了这些建议，但无法让它们工作。 以下是我的来源 - 我只包括相关部分。 
 
 我的实体类。 我已经为PL / SQL OUT参数保留了第一个参数。 
 
 Login.java 
 
@NamedNativeQuery(
        name = "getLoginDet", 
        query = "call GET_LOGIN_DET(?,:userId)", 
        resultClass = Login.class)
@Entity
@Table(name = "T_LOGIN_DET")
public class Login {
 
 Oracle存储过程： GET_LOGIN_DET.sql 。 第一个参数是按照Hibernate Spec的OUT REFCURSOR 
 
create or replace PROCEDURE GET_LOGIN_DET(listLogin OUT SYS_REFCURSOR,userId IN VARCHAR2)
AS 
BEGIN
OPEN listLogin FOR 
        SELECT *
        FROM T_LOGIN_DET 
        WHERE USER_ID = userId;
  END GET_LOGIN_DET;
 
 我的DAO类：我只绑定了命名参数，忽略了第一个？ 在指定的查询中。 
 
Session session = sessionFactory.openSession();
List results = session.getNamedQuery("getLoginDet").setParameter("userId", u.getUserId()).list();
 
 我的休眠配置 
 
<bean id="mysessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>
    <property name="configurationClass">
        <value>org.hibernate.cfg.AnnotationConfiguration</value>
    </property>
    <property name="annotatedClasses">
        <list>
            <value>com.cogn.gto.sea.employee.entity.Employee</value>
            <value>com.cogn.gto.sea.employee.entity.Department</value>
            <value>com.cogn.gto.sea.login.entity.User</value>
            <value>com.cogn.gto.sea.login.entity.Login</value>  
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
            <!-- <prop key="hibernate.hbm2ddl.auto">update</prop> -->
            <prop key="hibernate.show_sql">true</prop>
        </props>
    </property>
</bean>
 
 但是，我总是以Hibernate QueryException / Java SQLException结束 
 
Expected positional parameter count: 1, actual parameters: [] [{call GET_LOGIN_DET(?,:userId)}]
 
 我尝试过{? = call GET_LOGIN_DET(:userId)} {? = call GET_LOGIN_DET(:userId)} ， call GET_LOGIN_DET(?,:userId)无效。 我的要求是调用我已列出的过程，并将结果返回到DAO类。 有人能让我知道我错过了什么吗？ 

I have been trying to execute an Oracle Stored Procedure using Hibernate. This is not for production - but for a Java source parsing project, that I'm pursuing. To put it in simple terms, I'm not able to return a value from an Oracle Stored Proc. 
 
I have searched and read all relevant links from SO, Hibernate community/documentation(Native SQL chapter) links and tried out the suggestions, but somehow couldn't get them to work. Below are my sources - I'm including only the relevant parts. 
 
My Entity Class. I have reserved the first parameter for a PL/SQL OUT parameter.
 
Login.java
 
@NamedNativeQuery(
        name = "getLoginDet", 
        query = "call GET_LOGIN_DET(?,:userId)", 
        resultClass = Login.class)
@Entity
@Table(name = "T_LOGIN_DET")
public class Login {
 
Oracle Stored Proc : GET_LOGIN_DET.sql. The first parameter is OUT REFCURSOR as per Hibernate Spec
 
create or replace PROCEDURE GET_LOGIN_DET(listLogin OUT SYS_REFCURSOR,userId IN VARCHAR2)
AS 
BEGIN
OPEN listLogin FOR 
        SELECT *
        FROM T_LOGIN_DET 
        WHERE USER_ID = userId;
  END GET_LOGIN_DET;
 
My DAO Class : I'm binding only the named parameter, ignoring the first ? in the named Query.
 
Session session = sessionFactory.openSession();
List results = session.getNamedQuery("getLoginDet").setParameter("userId", u.getUserId()).list();
 
My Hibernate Config 
 
<bean id="mysessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>
    <property name="configurationClass">
        <value>org.hibernate.cfg.AnnotationConfiguration</value>
    </property>
    <property name="annotatedClasses">
        <list>
            <value>com.cogn.gto.sea.employee.entity.Employee</value>
            <value>com.cogn.gto.sea.employee.entity.Department</value>
            <value>com.cogn.gto.sea.login.entity.User</value>
            <value>com.cogn.gto.sea.login.entity.Login</value>  
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
            <!-- <prop key="hibernate.hbm2ddl.auto">update</prop> -->
            <prop key="hibernate.show_sql">true</prop>
        </props>
    </property>
</bean>
 
However, I always end up with Hibernate QueryException / Java SQLException
 
Expected positional parameter count: 1, actual parameters: [] [{call GET_LOGIN_DET(?,:userId)}]
 
I have tried variations of {? = call GET_LOGIN_DET(:userId)} , call GET_LOGIN_DET(?,:userId) to no avail. My requirement is to call the procedure that I have listed and get the result back in the DAO class. Can someone lead me to what exactly I'm missing here ?

原文：https://stackoverflow.com/questions/41166999