如何在 JDBC 中处理 VARRAY（Oracle 9i）？

Question

我正在尝试编写一个小程序，它必须从数据库存储和检索值。数据库是对象关系型的。

以下是 DDL 语句：

create or replace type Guy as object (name varchar(80), address varchar(80), dateOfBirth date)

create or replace type KnownLanguages as varray(10) of varchar(80)

create table Coders (person_ Guy, description clob, knownLanguages_ KnownLanguages)

这是我的 Java 代码：

package adbms;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import oracle.jdbc.OracleDriver;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class SimpleJdbcApplication {
    public static final String DRIVER_TYPE = "thin";
    public static final String HOST_NAME = "localhost";
    public static final int PORT = 1521;
    public static final String DATABASE_SID = "ralphdb";
    public static final String USER_NAME = "scott";
    public static final String PASSWORD = "tiger";
    public static final String URL = String.format("jdbc:oracle:%s:@%s:%d:%s", DRIVER_TYPE, HOST_NAME, PORT, DATABASE_SID);

    private static Connection connection;
    private static Scanner read;
    private static PreparedStatement insertStatement;
    private static PreparedStatement deleteStatement;
    private static PreparedStatement updateStatement;
    private static PreparedStatement displayStatement;

    static {
        try {
            System.out.println(URL);
            read = new Scanner(System.in);
            DriverManager.registerDriver(new OracleDriver());
            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            insertStatement = connection.prepareStatement("insert into Coders values( Guy(?,?,?) , ? , ?)");
            deleteStatement = connection.prepareStatement("delete from Coders where person_.name=?");
            updateStatement = connection.prepareStatement("update Coders set person_.name=? where person_.name=?");
            displayStatement = connection.prepareStatement("select * from Coders");
        } catch (Exception x) {
            x.printStackTrace();
        }
    }

    public static void main(String... args) throws SQLException {
        while (true) {
            System.out.println("Enter your choice.");
            System.out.println("1. Insert");
            System.out.println("2. Delete");
            System.out.println("2. Update");
            System.out.println("4. Display");
            System.out.println("5. Enter a direct query");
            System.out.println("6. Exit");
            switch (read.nextInt()) {
                case 1:
                    insertCoder();
                    break;
                case 2:
                    removeCoder();
                    break;
                case 3:
                    updateCoder();
                    break;
                case 4:
                    displayAllCoders();
                    break;
                case 5:
                    processDirectQuerry();
                    break;
                default:
                    System.exit(0);
            }
        }
    }

    private static void processDirectQuerry() throws SQLException {
        System.out.println("Enter the query.");
        String query = read.nextLine();
        connection.createStatement().execute(query);
    }

    private static void displayAllCoders() throws SQLException {
        ResultSet resultSet = displayStatement.executeQuery();
        System.out.println("Name\tAddress\tDate of birth\tDescription\tKnown Languages");
        while (resultSet.next()) {
            System.out.print(resultSet.getString("person_.name")+"\t");
            System.out.print(resultSet.getString("person_.address")+"\t");
            System.out.print(resultSet.getDate("person_.dateOfBirth")+"\t");
            Clob description = resultSet.getClob("description");
            System.out.print(description.getSubString(1L, (int) description.length()));
            ARRAY oraArray = ((OracleResultSet) resultSet).getARRAY("knownLanguages_");
            String[] languagesArray = (String[]) oraArray.getArray();
            for (String language : languagesArray) {
                System.out.print(language + " ");
            }
            System.out.println();
        }
    }

    private static void updateCoder() throws SQLException {
        System.out.println("Enter the name of the coder whose name is to be updated.");
        String originalName = read.nextLine();
        System.out.println("ENter the new name.");
        String newName = read.nextLine();
        updateStatement.setString(1, newName);
        updateStatement.setString(2, originalName);
        updateStatement.executeUpdate();
    }

    private static void removeCoder() throws SQLException {
        System.out.println("Enter the name of coder to be removed.");
        String name = read.nextLine();
        deleteStatement.setString(1, name);
        deleteStatement.executeUpdate();
    }

    private static void insertCoder() throws SQLException {
        read = new Scanner(System.in);
        System.out.println("Enter the name, address and date of birth.");
        String name = read.nextLine();
        String address = read.nextLine();
        String dateOfBirth = read.nextLine();
        System.out.println("Enter a brief description.");
        String description = read.nextLine();
        List<String> languagesList = new ArrayList<String>();
        System.out.println("Enter names of known languages. (At most 10.) Type 'done' when you're done.");
        String token = null;
        int i = 0;
        while (i < 10 && !(token = read.nextLine()).equalsIgnoreCase("done")) {
            languagesList.add(token);
                    i++;
        }
        insertStatement.setString(1, name);
        insertStatement.setString(2, address);
        insertStatement.setDate(3, Date.valueOf(dateOfBirth));
        insertStatement.setString(4, description);
        //String[] languagesArray = (String[]) languagesList.toArray();
        Object[] languagesArray = languagesList.toArray();
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("KnownLanguages", connection);
        ARRAY oraArray = new ARRAY(arrayDescriptor, connection, languagesArray);
        ((OraclePreparedStatement) insertStatement).setARRAY(5, oraArray);
        insertStatement.executeUpdate();
    }
}

该代码编译良好。但是当我尝试在数据库中插入值时，出现以下错误

F:\adb>java SimpleJdbcApplication jdbc:oracle:thin:@127.0.0.1:1521:ralphdb 输入您的选择。 1. 插入 2. 删除 2. 更新 4. 显示 5. 输入直接查询 6. 退出 1 输入姓名、地址和出生日期。拉胡尔 孟买 1989-12-22 输入简短说明。快乐进入 已知语言的名称。 （最多 10.) 完成后输入“完成”。时间：2019-03-17 标签：c++cjavadoneThread“main”中的异常 java.sql.SQLException：名称无效 模式：SYSTEM.K nownLanguages 在 oracle.jdbc.driver.DatabaseError.throwSqlException（DatabaseError.java ：112） 在 oracle.jdbc.driver.DatabaseError.throwSqlException（DatabaseError.java ：146） 在 oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:463 ） 在 oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:362) 在 oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1756) 在 oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:272) 在 oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:196) 在 oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:165) 在 oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:150) 在 oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115) 在 SimpleJdbcApplication.insertCoder（SimpleJdbcApplication.java:143） 在 SimpleJdbcApplication.main(SimpleJdbcApplication.java:62) F:\adb>

我认为我处理 VARRAY 的方式有问题。请帮助我...

谢谢！ :-)

Answer 1

这似乎是抛出异常的行：

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("KnownLanguages", connection);

我认为问题在于它没有解析数据库中的类型名称。我相信我过去发现类型查找是区分大小写的，默认情况下在 Oracle 中类型名称都是大写的。因此，请尝试在上面的行中使用“KNOWNLANGUAGES”。

另外，它似乎正在查找系统架构，尽管我不知道为什么。如果该类型不属于 SYSTEM，您可能还需要在描述符调用中显式指定该类型（例如“SCOTT.KNOWNLANGUAGES”）。