使用实体管理器如何使用多进入和退出参数调用存储过程 [英] how to call stored procedure with multiple in and out parameters using entity manager

问题描述

从应用程序调用存储过程时，我们面临一个问题。
数据库是 oracle 10g
这个proc有2个输入参数和2个输出参数。

<输入1： - DB列表
输入2： - 字符串

输出1： - 增加一个DB列表
输出2：号码

当我们试图使用

 查询q =会话.createSQLQuery（{call proc_name（？，？，？，？）}）; 
  

我们无法区分参数中的 code> out 参数。
那么我们应该如何使用这个来处理它。

另外，
我们试图使用callable语句如下：

 会话session =（Session）getEntityManager（）。getDelegate（）; 
 SessionImpl sessionImpl =（（SessionImpl）getEntityManager（）。getDelegate（））; 
连接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）; 
 //执行查询
 callableStatement.execute（）; 
  

我们收到以下错误：

  javax.ejb.EJBException：java.lang.ClassCastException：
 $ Proxy50不能转换为oracle.jdbc.OracleConnection 
  

可以提供一些建议。

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

  public abstract class GenericDAO< T，ID extends Serializable>实现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; 
} 
} 
  

这是Persistance.xml中的条目， p>

 <？xml version =1.0encoding =UTF-8？> 
< persistence version =2.0xmlns =http://java.sun.com/xml/ns/persistencexmlns：xsi =http://www.w3.org/2001/XMLSchema-实例
 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> 
<验证模式> AUTO< / validation-mode> 
<属性> 
< property name =hibernate.dialectvalue =org.hibernate.dialect.Oracle10gDialect/> 
< property name =hibernate.connection.driver_classvalue =oracle.jdbc.driver.OracleDriver/> 
< property name =hibernate.show_sqlvalue =true/> 
< property name =hibernate.format_sqlvalue =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> 
<验证模式> AUTO< / validation-mode> 
<属性> 
< property name =hibernate.dialectvalue =org.hibernate.dialect.Oracle10gDialect/> 
< property name =hibernate.connection.driver_classvalue =oracle.jdbc.driver.OracleDriver/> 
< property name =hibernate.show_sqlvalue =true/> 
< property name =hibernate.format_sqlvalue =true/> 
< / properties> 
< / persistence-unit> 
< / persistence> 
  

解决方案

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

  @NamedStoredProcedureQuery（
 name =PROC_NAME，
 procedureName = proc_name，
 returnsResultSet = true / false，
 parameters = {
 @StoredProcedureParameter（queryParameter =param1，name =p1，direction = Direction.IN，type = Integer。类），
 @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）
} 
）
  

并使用em.createNativeQuery（）方法

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

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

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

When we are trying to use

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

We cannot distinguish between in parameters and out parameters. So how should we handle it by using this.

Also, We tried to use callable statement as follows:

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

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

解决方案

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

@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

这篇关于使用实体管理器如何使用多进入和退出参数调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！