使用 CallableStatement 从 Oracle DB 的自定义对象类型的关联数组中检索数据 [英] Retrieving data from associative array of custom object type from Oracle DB using CallableStatement
问题描述
我正在从 Spring-boot 应用程序调用 Oracle 12c 数据库中的存储过程 (SP).我正在使用 CallableStatement 来做到这一点.SP 的 OUT 参数之一是具有自定义对象类型元素的关联数组.我无法以正确的方法检索数据.
I am calling a stored procedure(SP) in an Oracle 12c database from a Spring-boot application. I am using CallableStatement to do this. One of the OUT parameters of the SP is an associative array with elements of a custom object type. I am having trouble retrieving the data in a proper method.
TYPE trans_list IS TABLE OF T_RPT_TXN_DTLS_OBJ INDEX BY PLS_INTEGER;
TYPE T_RPT_TXN_DTLS_OBJ AS OBJECT( id VARCHAR2(20), amount NUMBER(10,2), desc VARCHAR2(100))
Connection connection = jdbcTemplate.getDataSource().getConnection();
OracleCallableStatement callableStatement = connection.prepareCall("{call SAMPLE_PROC(?,?)}").unwrap(OracleCallableStatement.class);
callableStatement.setString(1, ID);
--Here I don't know what to do--
callableStatement.registerIndexTableOutParameter(2, 500, OracleTypes.OTHER, 0);
--------------------------------
我尝试过的事情
我使用谷歌浏览了不同的网站.也通过了stackoverflow.上面的代码是我被困的地方.由于第三个参数是数组元素的数据类型,我输入了 OracleTypes.OTHER
但这会引发 SQLException: invalid column type 1111
.
我在官方文档中读到,对于自定义对象类型,我们可以将它们映射到 Java 对象.我也不知道该怎么做.请参阅用户定义类型>https://docs.oracle.com/cd/B19306_01/java.102/b14355/datacc.htm#BHCGCBJC
Things I have tried
I looked through different websites using google. Went through stackoverflow as well. The above code is where I am stuck at. Since the 3rd argument is the datatype of the array elements, I put in OracleTypes.OTHER
but that throws an SQLException: invalid column type 1111
.
I read in the official docs that for custom object types, we can map them to Java objects. I have no idea how to do that either. Please refer to User_defined types in https://docs.oracle.com/cd/B19306_01/java.102/b14355/datacc.htm#BHCGCBJC
任何示例代码、详细文档、适合我的案例的在线示例链接.我特别需要知道 out 参数注册的 arg[2],以及如何从 callableStatement
映射到自定义 Java 对象(也许使用 .getObject(int)代码>,但我不知道映射).
Any sample code, detailed docmentation, links to online examples that fits my case. I specifically need to know arg[2] of the out parameter registration, and how I might go about mapping to a custom Java object from the callableStatement
(perhaps using .getObject(int)
but I don't know about the mapping).
推荐答案
参见这个答案.
你不能.
- 您需要在 SQL 范围内定义数据类型(使用
CREATE TYPE
),而不是在 PL/SQL 范围内,因为 JDBC 只能使用 SQL 定义的数据类型. - 第 1 点的结果是 JDBC 不支持关联数组,因为它们是仅 PL/SQL 的数据类型,并且您需要使用集合(与 C# 不同,C# 只支持关联数组而不支持集合).所以你需要从类型中删除
INDEX BY
子句. - 您需要能够从数组中的类型映射到 Java 数据结构;一种方法是使用
SQLData
接口.这个答案中有一个例子.
- You need to define the data type in the SQL scope (using
CREATE TYPE
) rather than in the PL/SQL scope, in a package as JDBC can only work with SQL defined data types. - A consequence of point 1 is that JDBC does not support associative arrays, as they are a PL/SQL only data type, and you need to use collections (unlike C#, which only supports associative arrays and does not support collections). So you need to remove the
INDEX BY
clause from the type. - You need to be able to map from the type in the array to a Java data structure; one way of doing this is to use the
SQLData
interface. An example is in this answer.
这篇关于使用 CallableStatement 从 Oracle DB 的自定义对象类型的关联数组中检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!