java 如何创建oracle.sql.ARRAY对象?

pbpqsu0x  于 2023-02-14  发布在  Java
关注(0)|答案(6)|浏览(221)

这个问题与我最初的问题How to return an array from Java to PL/SQL ?有关,但更具体。
我一直在读Oracle Database JDBC Developer's Guide

但我仍然无法编写一个最小的代码来创建ARRAY

ARRAY array = oracle.jdbc.OracleConnection.createARRAY(sql_type_name, elements);

如创建ARRAY对象中所述。
我正在使用Oracle数据库JVM。
我尝试了以下方法:

    • 示例1**
create or replace type widgets_t is table of varchar2(32767);
/

create or replace and compile java source named "so20j1" as
public class so20j1 {
    public void f1() {
        String[] elements = new String[]{"foo", "bar", "zoo"};
        oracle.sql.ARRAY widgets =
            oracle.jdbc.OracleConnection.createARRAY("widgets_t", elements);
    }
};
/
show errors java source "so20j1"

失败原因:

Errors for JAVA SOURCE "so20j1":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0  so20j1:4: non-static method
     createARRAY(java.lang.String,java.lang.Object) cannot be
     referenced from a static context

0/0  1 error
0/0  ^
0/0  oracle.sql.ARRAY widgets =
     oracle.jdbc.OracleConnection.createARRAY("widgets_t", elements);
    • 示例2**
create or replace type widgets_t is table of varchar2(32767);
/

create or replace and compile java source named "so20j2" as

public class so20j2 {
    public void f1() {
        String[] elements = new String[]{"foo", "bar", "zoo"};
        oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver();
        java.sql.Connection conn = ora.defaultConnection();
        oracle.sql.ARRAY widgets = conn.createARRAY("widgets_t", elements);
    }
};
/
show errors java source "so20j2"

失败原因:

Errors for JAVA SOURCE "so20j2":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0  so20j2:6: cannot find symbol
0/0  symbol  : method createARRAY(java.lang.String,java.lang.String[])
0/0  1 error
0/0  oracle.sql.ARRAY widgets = conn.createARRAY("widgets_t",
     elements);

0/0  ^
0/0  location: interface java.sql.Connection

免责声明:我还不是一个Java程序员。

lo8azlld

lo8azlld1#

第二个方法是正确的,但是不能从java.sql.Connection类型的连接创建oracle Array,必须是OracleConnection才能使用这些方法。

oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver();
java.sql.Connection conn = ora.defaultConnection();
OracleConnection oraConn = conn.unwrap(OracleConnection.class);
oracle.sql.ARRAY widgets = oraConn.createARRAY("widgets_t", elements);
qxgroojn

qxgroojn2#

根据Affe和Chris Mazzola的回答,我成功地构建了两个在Oracle 11g R2数据库中编译的示例。

示例基于Affe的回答

create or replace type widgets_t is table of varchar2(32767);
/

create or replace and compile java source named "so20ja1" as
public class so20ja1 {
    public void f1() throws java.sql.SQLException {
        String[] elements = new String[]{"foo", "bar", "zoo"};
        oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver();
        java.sql.Connection conn = ora.defaultConnection();
        oracle.jdbc.OracleConnection oraConn = (oracle.jdbc.OracleConnection)conn;
        java.sql.Array widgets = oraConn.createARRAY("widgets_t", elements);
    }
};
/
show errors java source "so20ja1"

示例基于Chris Mazzola的回答

create or replace type widgets_t is table of varchar2(32767);
/

create or replace and compile java source named "so20ja2" as
public class so20ja2 {
    public void f1() throws java.sql.SQLException {
        String[] elements = new String[]{"foo", "bar", "zoo"};
        oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver();
        java.sql.Connection conn = ora.defaultConnection();
        oracle.sql.ArrayDescriptor desc = 
            oracle.sql.ArrayDescriptor.createDescriptor("widgets_t", conn);
        java.sql.Array widgets = new oracle.sql.ARRAY(desc, conn, elements);
    }
};
/
show errors java source "so20ja2"
mo49yndu

mo49yndu3#

// array sample (using a stored procedure to sum two or more numbers)
Connection connection = dataSource.getConnection(username,password);
ArrayDescriptor desc = 
    ArrayDescriptor.createDescriptor(schemaName + "." + arrayType, connection);

// first ? is the array, second ? is the result via out parameter
String sql = "call sum_numbers(?,?)";
CallableStatement cs = connection.prepareCall(sql);

String[] args = {"5","15","25","35"}; // what to sum
Array array = new oracle.sql.ARRAY(desc, connection, args);

cs.setArray(1, array);
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
int result = cs.getInt(2);
cs.close();
idv4meu8

idv4meu84#

在Java 1.6中,您有connection.createArrayOf(..),它是标准的。

h79rfbju

h79rfbju5#

Map〈字符串,对象〉params =新哈希Map〈〉();

params.put("input_to_sp", new ExampleArrayMapper (new String[] { "ABC" }));

    Map<String, Object> results = spObject.execute(params);

    final List<Object> output = (List<Object>) results.get("po_out_cur");

公共类ExampleArrayMapper扩展抽象SqlTypeValue {私有字符串[] customObject;

public ExampleArrayMapper (String[] customObject) {
        this.customObject= customObject;
    }

    public String getSQlTypeName() throws SQLException {
        return "NAME_OF_TYPE_IN_SQL";
    }

    @Override
    protected Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
        try {
            con = dataSource.getConnection().unwrap(OracleConnection.class);
            Array reportsArray = ((OracleConnection) con).createOracleArray(SQL_TYPE_NAME, customObject);
            return reportsArray;
        } finally {
            con.close();
        }
    }
}
kg7wmglp

kg7wmglp6#

Oracle 12.2版本:
数据库管理系统。字符串[] v_list ;
v_arr =((Oracle.jdbc. Oracle连接)驱动程序管理器.获取连接(“jdbc:默认:连接:“)).创建数组(“T_字符串_数组”,v_列表);

相关问题