java 如何使用实体管理器调用具有多个输入和输出参数的存储过程

how to call stored procedure with multiple in and out parameters using entity manager

提问by Hardik Dhanuka

We are facing an issue while calling the stored procedure from the application. The database is oracle 10gThis proc has 2 input parameters and 2 output parameters.

我们在从应用程序调用存储过程时遇到问题。数据库是oracle 10gThis proc 有 2 个输入参数和 2 个输出参数。

Input 1:- DB-List Input 2:- String

输入 1:- DB-List 输入 2:- 字符串

Output 1:-Again a DB-List Output 2:- Number

输出 1：- 又是一个 DB-List 输出 2：- 数字

When we are trying to use

当我们尝试使用

 Query q = session.createSQLQuery("{call proc_name(?,?,?,?)}");

We cannot distinguish between inparameters and outparameters. So how should we handle it by using this.

我们无法区分in参数和out参数。那么我们应该如何通过使用它来处理它。

Also, We tried to use callable statement as follows:

此外，我们尝试使用 callable 语句如下：

Session session = (Session) getEntityManager().getDelegate();
SessionImpl sessionImpl = ((SessionImpl) getEntityManager().getDelegate());
Connection cc = sessionImpl.connection();
CallableStatement callableStatement = null;

callableStatement = cc.prepareCall("{call proc_name(?,?,?,?)}");
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("DB_LIST",callableStatement.getConnection());
ARRAY paramArray = new ARRAY(descriptor, callableStatement.getConnection(), array);
callableStatement.setArray(1, paramArray);
callableStatement.setString(2, "N");
callableStatement.registerOutParameter(3, OracleTypes.ARRAY, "DB_RETURN_LIST");
callableStatement.registerOutParameter(4, Types.INTEGER);
// executing the query
callableStatement.execute();

We get the following error:

我们收到以下错误：

javax.ejb.EJBException: java.lang.ClassCastException: 
$Proxy50 cannot be cast to oracle.jdbc.OracleConnection

Can you please provide some suggestions.

能否请您提供一些建议。

This is the Entity Manager that we are using

这是我们正在使用的实体管理器

public abstract class GenericDAO<T, ID extends Serializable> implements IGenericDAO<T, ID> {
private final Class<T> persistentClass;
@PersistenceContext(unitName = "firstPersistenceUnit")
@Produces
private EntityManager entityManager;

public void setEntityManager(final EntityManager entityManager) throws DataAccessException {
this.entityManager = entityManager;
}

public EntityManager getEntityManager() throws DataAccessException {
return entityManager;
}
}

Here is the entry in the Persistance.xml

这是 Persistance.xml 中的条目

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="firstPersistenceUnit">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/firstDataSource</jta-data-source>
<class>com.domain.Branch</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode>
<validation-mode>AUTO</validation-mode>
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" />
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
    </properties>
</persistence-unit>

<persistence-unit name="secondPersistenceUnit">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:jboss/secondDataSource</jta-data-source>
<class>com.domain.PocJeeCounty</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode>
<validation-mode>AUTO</validation-mode>
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" />
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
    </properties>
</persistence-unit>
</persistence>

回答by Meiyappan Kannappa

Dont use second way, instead create and define query and query parameters using @NamedStoredProcedureQuery and @StoredProcedureParameter. Following is the code for your requirement

不要使用第二种方式，而是使用@NamedStoredProcedureQuery 和@StoredProcedureParameter 创建和定义查询和查询参数。以下是您的要求的代码

@NamedStoredProcedureQuery(  
    name="PROC_NAME",  
    procedureName="proc_name",  
    returnsResultSet=true/false,  
    parameters={  
        @StoredProcedureParameter(queryParameter="param1",name="p1",direction=Direction.IN,type=Integer.class),  
        @StoredProcedureParameter(queryParameter="param2",name="p2",direction=Direction.IN,type=Timestamp.class),  
        @StoredProcedureParameter(queryParameter="param3",name="p3",direction=Direction.OUT,type=String.class),  
        @StoredProcedureParameter(queryParameter="param4",name="p4",direction=Direction.OUT,type=Integer.class)  
    }  
    )

and use em.createNativeQuery() method

并使用 em.createNativeQuery() 方法