如何在JDBC中处理VARRAYs（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 输入姓名、地址和出生日期。rahul孟买1989-12-22 输入简要描述。快乐的 已知语言名称。(最多 10。)完成后键入“完成”。c ++ c java完成主要线程中的意外异常“ 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中) 在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的方式有问题。请帮我看一下...

谢谢！ :-)

- missingfaktor

Answer 1

看起来是这一行引发了异常：

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

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

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

- Dave Costa