Hibernate存储过程调用导致GenericJDBcException：无效的列索引

Question

为了使用Oracle存储过程学习Hibernate4.1，我尝试使用一个尽可能简单的示例：1。%2中没有传递参数。SYS_REFCURSOR作为一个返回参数

我得到的是一个异常：“org.hibernate.exception.GenericJDBcException：无效列索引”。在网上搜索几个小时来解决这个问题并没有什么帮助。

CREATE OR REPLACE PROCEDURE IFC_OWNER.JPA_APPLICATION_R_TEST 
(    o_result_set OUT SYS_REFCURSOR  )
AS
BEGIN
    OPEN o_result_set FOR
    SELECT APPLICATION_ID, APP_COMMON_NM, APP_DESC, APP_URL FROM APPLICATION_R;
END;
/

<sql-query name="oracleproccall" callable="true">
    <return alias="application_r" class="com.myco.entities.ApplicationR"/>
    <![CDATA[ call JPA_APPLICATION_R_TEST() ]]>
</sql-query>

public void run() {
EntityManager entityManager = entityManagerFactory.createEntityManager();
try {
    Query sqlQuery = entityManager.createNamedQuery("oracleproccall");
    List list = sqlQuery.getResultList();    
    ...snip...
} catch (PersistenceException ex) {
    ex.printStackTrace();
} finally {
    entityManager.close();
}    
}

    @Entity
    @Table(name = "APPLICATION_R", schema = "IFC_OWNER")
    public class ApplicationR implements Serializable {
        private int applicationId;
        private String appCommonNm;
        private String appDesc;
        private String appUrl;
        ...snip...
More available if needed. 

================================异常

## ApplicationsByStoredProc ##
Hibernate: 
    /* named native SQL query oracleproccall */ call JPA_APPLICATION_R_TEST()
Feb 19, 2013 2:58:07 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 17003, SQLState: 99999
Feb 19, 2013 2:58:07 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Invalid column index
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Invalid column index
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:266)
    at com.myco.jpa.tests.ApplicationsByStoredProc.run(ApplicationsByStoredProc.java:37)
    at com.dstoutput.jpa.JPAEntities_InFactDomain_Test.main(JPAEntities_InFactDomain_Test.java:56)
Caused by: org.hibernate.exception.GenericJDBCException: Invalid column index
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.CallableStatementProxyHandler.continueInvocation(CallableStatementProxyHandler.java:49)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy16.registerOutParameter(Unknown Source)
    at org.hibernate.dialect.Oracle8iDialect.registerResultSetOutParameter(Oracle8iDialect.java:507)

Answer 1

我找到问题了。我使用的是会话和查询类的javax.persistence版本，而不是org.hibernate版本；javax.persistence不支持Oracle存储过程调用，而Hibernate支持。如果没有下面提供的import语句，差异是微妙的。应该使用sqlquery.getResultList（）。这是一个JPA方法调用，在Hibernate版本的Query中不存在。

导入导入javax.persistence.query；

查询sqlQuery=EntityManager.createNamedQuery（“OracleProcCall”）；List List=sqlquery.getResultList（）；

import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.myco.entities.ApplicationR


private void anotherTest() {
    Configuration cfg = new Configuration();
    cfg.configure("hibernate.cfg.xml");
    SessionFactory sf = cfg.buildSessionFactory();
    Session s = sf.openSession();
    Transaction tx = s.beginTransaction();

    List<AppR> apps;
    Query namedQuery = s.getNamedQuery("oraclestoredproc");
    apps = namedQuery.list();
    apps = s.getNamedQuery("oraclestoredproc").list();
    Iterator<ApplicationR> iterator = apps.iterator();
    while (iterator.hasNext()) {
        ApplicationR app = (ApplicationR) iterator.next();
        System.out.print(app.getApplicationId() + " ");
        System.out.print(app.getAppCommonNm() + " ");
        System.out.print(app.getAppDesc() + " ");
        System.out.println(app.getAppUrl() + " ");
    }
    s.flush();
    tx.commit();
    s.close();
}

感谢吉图吉，也感谢这个来源