将Oracle存储过程结果映射到自定义Java类型(类) [英] Mapping an Oracle stored procedure result to a custom Java type (class)

问题描述

我必须在Oracle(11g)中调用一个使用单个IN OUT参数的存储过程.此参数是定义为

I have to call a stored procedure in Oracle (11g) that uses a single IN OUT parameter. This parameter is an Oracle custom type defined as

 CREATE OR REPLACE TYPE "SEPADD"."T_NAPRAVI_NALOG_TEST" IS OBJECT
 (
    I_INICIJALI              varchar2(3)  ,          
    I_STATUS                 number(1)        
 )

实际类型更复杂，但是为了简化可读性，我在这里对其进行了简化.使用这种类型的Oracle过程(再次简化)定义为

The actual type is more complex but I simplified it here for better readability. The Oracle procedure using this type (again simplified ) is defined as

  CREATE OR REPLACE PROCEDURE "SEPADD"."GETNALOGTESTPROC"(nalog in out T_NAPRAVI_NALOG_TEST )
IS   
      BEGIN       
  nalog.I_INICIJALI := 'PC';         
      nalog.I_STATUS := nalog.I_STATUS + 3; 
END;

Oracle自定义类型映射到实现SQLData接口的Java类. (请参见 https://docs.oracle.com/javase/tutorial /jdbc/basics/sqlcustommapping.html#implementing_sqldata )

The Oracle custom type is mapped to a Java class that implements the SQLData interface. (see https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlcustommapping.html#implementing_sqldata)

 public class TNapraviNalog implements SQLData{

private int I_STATUS;
private String I_INICIJALI;
private String sql_type = "T_NAPRAVI_NALOG_TEST";
public String getSQLTypeName() {
        return sql_type;
    }
    public int getIStatus(){
     return this.I_STATUS;
}
    public String getIInicijali(){
        return this.I_INICIJALI;
    }
    public void setIInicijali(String in){
        I_INICIJALI = in;
    }
    public void setIStatus(int st){
        I_STATUS = st;
    }
    public void readSQL(SQLInput stream, String type)
        throws SQLException {
        sql_type = type;
        I_INICIJALI = stream.readString();
        I_STATUS = stream.readInt();

    }

    public void writeSQL(SQLOutput stream)
        throws SQLException {
        stream.writeString(I_INICIJALI);
        stream.writeInt(I_STATUS);          
    }
}

现在，从我的JDBC代码中，我通过以下方式调用Oracle存储过程

Now, from my JDBC code I call the Oracle stored procedure in the following way

    Object obj=null;
    ResultSet rs=null;
    CallableStatement stmt=null;
    TNapraviNalog n = null;
    try{

          sqlQuery = "{call getnalogtestproc(?)}";

          Map m = conn.getTypeMap();
          m.put("sepadd.T_NAPRAVI_NALOG_TEST", Class.forName("ib.easyorm.db.TNapraviNalog"));//this maps the Java class to the Oracle custom type
          conn.setTypeMap(m);

          stmt=conn.prepareCall(sqlQuery);
          stmt.registerOutParameter(1, Types.STRUCT, "T_NAPRAVI_NALOG_TEST");
          stmt.setObject(1, paramValues.get(0) ); //paramValues.get(0) returns an instance of TNapraviNalog class

          stmt.execute();    

          obj = stmt.getObject(1, m);

          //obj = stmt.getObject(1,TNapraviNalog.class); this method is not implemented in the JDBC driver

    }catch(Exception e){
          throw new EasyORMException(e);
    }finally{
          closeResources(rs,stmt);
    }
    return obj;

现在，问题是，尽管我可以得到存储过程返回的结果，但是结果没有转换为Java类(TNapraviNalog)，因此我必须手动执行.我可以成功 使用TNapraviNalog(stmt.setObject(1，paramValues.get(0));)实例调用Oracle过程，但是我无法将结果转换为TNapraviNalog.我真的很想 能够拥有类似的东西

Now, the problem is that, while I can get the result returned by the stored procedure, the result is not converted to a Java class (TNapraviNalog) so I have to do that manually. I can successfully call the Oracle procedure with an instance of TNapraviNalog ( stmt.setObject(1, paramValues.get(0) ); ) but I can't get the result converted to TNapraviNalog. I really would like to be able to have something like

 TNapraviNalog nalog = stmt.getObject(1, m); 

但是，此行将导致异常( java.lang.ClassCastException:oracle.sql.STRUCT无法转换为ib.easyorm.db.TNapraviNalog ). 我猜测JDBC驱动程序不知道stmt.getObject(1，m)返回的实际类型，因此无法进行转换.

However, this line will cause an exception ( java.lang.ClassCastException: oracle.sql.STRUCT cannot be cast to ib.easyorm.db.TNapraviNalog). I'm guessing that the JDBC driver isn't aware of the actual type returned by the stmt.getObject(1,m) and thus can't make the conversion.

有人知道这可以使用纯JDBC或Hibernate完成吗?

Does anybody know if this can be done using either plain JDBC or Hibernate?

Oracle页面中的相关代码(链接为cihan 7的答案)

The relevant code from an Oracle page (link given in cihan seven's answer)

从可调用语句OUT参数中检索SQLData对象

请考虑您有一个OracleCallableStatement实例ocs，该实例调用PL/SQL函数GETEMPLOYEE.该程序将员工编号传递给该功能.该函数返回相应的Employee对象.要检索此对象，请执行以下操作:

Consider you have an OracleCallableStatement instance, ocs, that calls a PL/SQL function GETEMPLOYEE. The program passes an employee number to the function. The function returns the corresponding Employee object. To retrieve this object you do the following:

1.准备一个OracleCallableStatement来调用GETEMPLOYEE函数，如下所示:

1.Prepare an OracleCallableStatement to call the GETEMPLOYEE function, as follows:

 OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{ ? = call GETEMPLOYEE(?) }");

2.声明empnumber作为GETEMPLOYEE的输入参数.使用类型代码OracleTypes.STRUCT将SQLData对象注册为OUT参数.然后，运行该语句.可以按照以下步骤进行操作:

2.Declare the empnumber as the input parameter to GETEMPLOYEE. Register the SQLData object as the OUT parameter, with the type code OracleTypes.STRUCT. Then, run the statement. This can be done as follows:

 ocs.setInt(2, empnumber); 
 ocs.registerOutParameter(1, OracleTypes.STRUCT, "EMP_OBJECT"); 
ocs.execute(); 

3.使用getObject方法检索员工对象.以下代码假定存在一个类型映射项，用于将Oracle对象映射到Java类型Employee:

3.Use the getObject method to retrieve the employee object. The following code assumes that there is a type map entry to map the Oracle object to the Java type Employee:

 Employee emp = (Employee)ocs.getObject(1); //my comment-->this doesn't seem to work

如果没有类型映射项，则getObject将返回oracle.sql.STRUCT对象.将输出强制转换为STRUCT类型，因为getObject方法返回通用java.lang.Object类的实例.这样做如下:

If there is no type map entry, then getObject would return an oracle.sql.STRUCT object. Cast the output to the STRUCT type, because the getObject method returns an instance of the generic java.lang.Object class. This is done as follows:

STRUCT emp = (STRUCT)ocs.getObject(1); 

谢谢

推荐答案

该行中似乎出现了错误:

The error appears to be in this line:

      m.put("sepadd.T_NAPRAVI_NALOG_TEST", Class.forName("ib.easyorm.db.TNapraviNalog"));//this maps the Java class to the Oracle custom type

这是唯一要让架构所有者限定T_NAPRAVI_NALOG_TEST类型的行.您可以在另外两个没有模式名称的地方引用它.

This is the only line in which you are qualifying your T_NAPRAVI_NALOG_TEST type with the schema owner. You refer to it in two other places without the schema name.

如果您以SEPADD用户(似乎是)的身份连接到数据库，则可以从此行中删除模式所有者前缀sepadd..或者，尝试将上一行的架构所有者更改为大写.

If you're connecting to your database as the SEPADD user (it seems you are), you can remove the schema owner prefix sepadd. from this line. Alternatively, try changing the schema owner in the above line to upper-case.

这篇关于将Oracle存储过程结果映射到自定义Java类型(类)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！