如何在java中调用包含用户定义类型的oracle存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3626061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call oracle stored procedure which include user-defined type in java?
提问by xmurobi
In Oracle DB:
在 Oracle 数据库中：
I have the following stored procedure:
我有以下存储过程：
procedure getInfo ( p_ids IN IDS_TABLE, p_details OUT cursor )
Type IDS_TABLEis:
类型IDS_TABLE是：
create or replace type IDS_TABLE as table of IDS
create or replace type IDS as object ( id1 NUMBER, id2 NUMBER, id3 NUMBER )
How can I call getInfo in Java?
如何在 Java 中调用 getInfo？
采纳答案by Vincent Malgrat
Setting up a link between Oracle SQL objects and java objects manually is not a trivial task. In particular, arrays (or nested tables) of user-defined objects are more complex to pass from java to Oracle than arrays of standard datatypes. In other words, it is easier to call a procedure with signature:
手动设置 Oracle SQL 对象和 java 对象之间的链接不是一项简单的任务。特别是，用户定义对象的数组（或嵌套表）从 java 传递到 Oracle 比标准数据类型的数组更复杂。换句话说，调用带有签名的过程更容易：
(TABLE OF NUMBER, TABLE OF NUMBER, TABLE OF NUMBER)`
than a procedure whose signature is:
而不是签名为：
(TABLE OF (NUMBER, NUMBER, NUMBER)) <- your case
You can write a wrapper around your procedure to transform the second case into the first case.
您可以围绕您的过程编写一个包装器，将第二种情况转换为第一种情况。
That being said, it is by far not impossible to map your procedure. The following example is largely inspired by a post by Tom Kyte. Tom describes how to map a TABLE OF NUMBERusing oracle.sql.ARRAY. In your case we will also have to use oracle.sql.STRUCTto map the IDSSQL object.
话虽如此，但到目前为止，绘制您的程序并非不可能。以下示例很大程度上受到 Tom Kyte 的帖子的启发。Tom 描述了如何TABLE OF NUMBER使用oracle.sql.ARRAY. 在您的情况下，我们还必须使用oracle.sql.STRUCT来映射IDSSQL 对象。
You may also want to browse the Oracle JDBC doc, in particular the chapter Working with Oracle Object Types.
您可能还想浏览 Oracle JDBC 文档，特别是使用 Oracle 对象类型一章。
First is a setup similar to yours:
首先是类似于您的设置：
SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER );
2 /
Type created
SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS;
2 /
Type created
SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
2 BEGIN
3 FOR i IN 1 .. p_ids.COUNT LOOP
4 dbms_output.put_line(p_ids(i).id1
5 || ',' || p_ids(i).id2
6 || ',' || p_ids(i).id3);
7 END LOOP;
8 END getInfo;
9 /
Procedure created
This is the java procedure:
这是java程序：
SQL> CREATE OR REPLACE
2 AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import oracle.sql.*;
7 import oracle.jdbc.driver.*;
8
9 public class ArrayDemo {
10
11 public static void passArray() throws SQLException {
12
13 Connection conn =
14 new OracleDriver().defaultConnection();
15
16
17 StructDescriptor itemDescriptor =
18 StructDescriptor.createDescriptor("IDS",conn);
19
20 Object[] itemAtributes = new Object[] {new Integer(1),
21 new Integer(2),
22 new Integer(3)};
23 STRUCT itemObject1 = new STRUCT(itemDescriptor,conn,itemAtributes);
24
25 itemAtributes = new Object[] {new Integer(4),
26 new Integer(5),
27 new Integer(6)};
28 STRUCT itemObject2 = new STRUCT(itemDescriptor,conn,itemAtributes);
29
30 STRUCT[] idsArray = {itemObject1,itemObject2};
31
32 ArrayDescriptor descriptor =
33 ArrayDescriptor.createDescriptor( "IDS_TABLE", conn );
34
35 ARRAY array_to_pass =
36 new ARRAY( descriptor, conn, idsArray );
37
38 OraclePreparedStatement ps =
39 (OraclePreparedStatement)conn.prepareStatement
40 ( "begin getInfo(:x); end;" );
41
42 ps.setARRAY( 1, array_to_pass );
43 ps.execute();
44
45 }
46 }
47 /
Java created
Let's call it:
让我们称之为：
SQL> CREATE OR REPLACE
2 PROCEDURE show_java_calling_plsql
3 AS LANGUAGE JAVA
4 NAME 'ArrayDemo.passArray()';
5 /
Procedure created
SQL> exec show_java_calling_plsql ;
1,2,3
4,5,6
PL/SQL procedure successfully completed
回答by Ravi Petchimuthu
This is a pretty good example. if you see java.sql.SQLException: invalid name pattern:still. Check the scope of the type that you declared in Oracle. I am using Oracle 11g and had to declare both Object of String Array and Table of Objects of my type in schema level. Spent some 3 hours and found that.
这是一个很好的例子。如果您看到java.sql.SQLException: invalid name pattern:still。检查您在 Oracle 中声明的类型的范围。我正在使用 Oracle 11g，并且必须在模式级别声明我的类型的字符串数组对象和对象表。花了大约3个小时，发现了这一点。
oracle.sql.StructDescriptor docObjDescriptor = StructDescriptor.createDescriptor("SSIADM.DOCUMENT_OBJECT",conn);
String[] strArray = new String[] {"doc1","file1"};
oracle.sql.STRUCT DocObject1 = new STRUCT(docObjDescriptor,conn,strArray);
strArray = new String[] {"doc2","file2"};
oracle.sql.STRUCT DocObject2 = new STRUCT(docObjDescriptor,conn,strArray);
oracle.sql.STRUCT[] docObjArray = {DocObject1,DocObject2};
arrDesc = ArrayDescriptor.createDescriptor("DOCUMENT_TABLE", conn);
oracle.sql.ARRAY array = new ARRAY(arrDesc, conn, docObjArray);
回答by ScrappyDev
The solution I used lets Spring parse the object instead of having to manually create the STRUCT arrays. Unfortunately, it still isn't environment independent.
我使用的解决方案让 Spring 解析对象，而不必手动创建 STRUCT 数组。不幸的是，它仍然不是环境独立的。
Stored Proc DAO:
存储过程 DAO：
package ****.dao.storedProcedures;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.lang3.Validate;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlTypeValue;
import org.springframework.jdbc.object.StoredProcedure;
import ****.persistent.ComplexTypeObj;
import ****.persistent.InnerType;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class SaveStoredProc extends StoredProcedure implements InitializingBean {
public static final String IT_COMPLEX_TYPE = "it_complex_type";
public SaveStoredProc() {
}
@Override
public void afterPropertiesSet() {
Validate.notNull(getJdbcTemplate());
super.setFunction(true);
super.declareParameter(new SqlOutParameter(RESULT, Types.NUMERIC));
super.declareParameter(new SqlParameter(IT_COMPLEX_TYPE, Types.OTHER, ComplexTypeObj.ORACLE_OBJECT_NAME));
compile();
}
public long execute(final ComplexTypeObj complexTypeObj) {
Map<String, Object> inParameters = new HashMap<String, Object>();
inParameters.put(IT_COMPLEX_TYPE, new ComplexSqlTypeValue(complexTypeObj));
@SuppressWarnings("unchecked")
Map<String, Object> resp = super.execute(inParameters);
return ((Number)resp.get(RESULT)).longValue();
}
private static final class ComplexSqlTypeValue implements SqlTypeValue {
private final Log logger = LogFactory.getLog(getClass());
private final ComplexTypeObj complexTypeObj;
public ComplexSqlTypeValue(ComplexTypeObj complexTypeObj) {
this.complexTypeObj = complexTypeObj;
}
@Override
public void setTypeValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName) throws SQLException {
Connection conn = ps.getConnection();
try {
conn = conn.unwrap(oracle.jdbc.OracleConnection.class);
} catch (Exception e) {
logger.debug("Could not unrap connection");
}
Map<String, Class<?>> typeMap = conn.getTypeMap();
typeMap.put(typeName, ComplexTypeObj.class); //The name of the outer object type.
typeMap.put(InnerType.ORACLE_OBJECT_NAME, InnerType.class); //The name of the inner object type.
ArrayDescriptor des = ArrayDescriptor.createDescriptor(InnerType.ORACLE_LIST_NAME, conn); //The name of the inner list type.
Array objArray = new ARRAY(des, conn, complexTypeObj.getInnerList().toArray());
complexTypeObj.setInnerArray(objArray);
ps.setObject(paramIndex, complexTypeObj);
}
}
}
Outer Type:
外型：
import java.sql.*;
import java.util.*;
public class OuterType extends BaseSQLData implements SQLData {
public static final String ORACLE_OBJECT_NAME = "T_OUTER_TYPE";
private List<InnerType> innerList;
private Array innerArray;
public OuterType() {
this.innerList = new ArrayList<InnerType>();
}
public String getSQLTypeName() throws SQLException {
return ORACLE_OBJECT_NAME;
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeArray(innerArray);
}
Inner Type:
内部类型：
public final class InnerType extends BaseSQLData {
public static final String ORACLE_OBJECT_NAME = "T_INNER_TYPE";
public static final String ORACLE_LIST_NAME = "T_INNER_TYPE_LIST";
private String valueA;
private Long valueB = 0;
public String getSQLTypeName() throws SQLException {
return ORACLE_OBJECT_NAME;
}
@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
throw new UnsupportedOperationException("This class doesn't support read opperations.");
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(valueA);
stream.writeBigDecimal(valueB == null ? null : new BigDecimal(valueB.toString()));
}
回答by Arlo
If you're using Spring, you may want to look at Spring Data JDBC Extensions, which provides a SqlArrayValuetype.
如果您使用的是Spring，您可能需要查看Spring Data JDBC Extensions，它提供了一个SqlArrayValue类型。
Chapter 7.2.1 Setting ARRAY values using SqlArrayValue for an IN parameterexplains how to call procedures with array parameters.
第7.2.1章使用 SqlArrayValue 为 IN 参数设置 ARRAY 值解释了如何使用数组参数调用过程。
