java - 如何将字符串数组插入到 VARRAY 列中？

Question

我正在尝试使用 jdbc 插入具有两个 VARRAY 列的表；一个是 int 的 VARRAY，另一个是 varchar2 的 VARRAY。第一列正在完美插入。但是，第二个将所有值插入为 null。

这是表格:

CREATE OR REPLACE TYPE ARRAY_DTBN_STRING AS VARRAY(10) OF VARCHAR2(100);
CREATE OR REPLACE TYPE ARRAY_DTBN_INTEGER AS VARRAY(10) OF int;
CREATE TABLE DTBN_DETAILS(
    ID VARCHAR2(100) PRIMARY KEY ,
    VALS ARRAY_DTBN_STRING,
    SIZES ARRAY_DTBN_INTEGER
);

这是使用 JDBC 进行插入的 Java 代码:

DtbnDetails det = new DtbnDetails();
det.setId("ABC");
det.setValues(new String[] {"room","house"});
det.setSizes(new int[] {3,5});

String query = "INSERT INTO DTBN_DETAILS "+ 
    "(ID, VALS, SIZES) VALUES (?, ?, ?)";

Connection conAux = Conexion.getConnection("main");                     
OracleConnection con = conAux.unwrap(OracleConnection.class);

PreparedStatement statement = con.prepareStatement(query);

int i=1;

String id = details.getId();
sentencia.setString(i++,id);

String[] values = details.getValues()==null ? new String[0] : details.getValues();
Array valuesArray = con.createOracleArray("ARRAY_DTBN_STRING", values);
statement.setArray(i++, valuesArray);

int[] sizes = details.getSizes()==null ? new int[0] : details.getSizes();
Array sizesArray = con.createOracleArray("ARRAY_DTBN_INTEGER", sizes);
statement.setArray(i++, sizesArray);

statement.execute();        
con.commit();

执行此代码后，“VALS”列有两个值，但均为空:

SELECT * FROM DTBN_DETAILS;

ABC
ARRAY_DTBN_STRING(NULL, NULL)
ARRAY_DTBN_INTEGER(3, 5)

Answer 1

我也遇到了和你一样的问题。解决方案是将 orai18n.jar 添加到类路径中。

根据this .

If the clients and the server use different character sets, then the driver provides the support to perform the conversions between the database character set and the client character set.

The basic ojdbc JAR file contains all the necessary classes to provide complete globalization support for:

• Oracle character sets for CHAR, VARCHAR. LONGVARCHAR, or CLOB data that is not being retrieved or inserted as a data member of an Oracle 8 Object or Collection type.

• CHAR or VARCHAR data members of Object and Collection for the character sets US7ASCII, WE8DEC, WE8ISO8859P1 and UTF8.

To use any other character sets in CHAR or VARCHAR data members of Objects or Collections, you must include orai18n.jar in your application's CLASSPATH.