如何在 JDBC 中处理 VARRAY(Oracle 9i)?

发布于 2024-08-13 15:25:42 字数 8097 浏览 8 评论 0原文

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

以下是 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 的方式有问题。请帮助我...

谢谢! :-)

I am trying to write a small program which has to store and retrieve values from database. The database is object relational.

Here're the DDL statements :

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)

And this is my Java code :

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();
    }
}

The code compiles fine. But when I try to insert values in database, it gives the following error

F:\adb>java SimpleJdbcApplication
jdbc:oracle:thin:@127.0.0.1:1521:ralphdb
Enter your choice.
1. Insert
2. Delete
2. Update
4. Display
5. Enter a direct query
6. Exit 1 Enter the name, address and date of birth. rahul mumbai 1989-12-22
Enter a brief description. happy Enter
names of known languages. (At most
10.) Type 'done' when you're done. c++ c java done Exception in thread "main"
java.sql.SQLException: invalid name
pattern: SYSTEM.K nownLanguages
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:146)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:463
)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:362)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1756)
at oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:272)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:196)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:165)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:150)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115)
at SimpleJdbcApplication.insertCoder(SimpleJdbcApplication.java:143)
at SimpleJdbcApplication.main(SimpleJdbcApplication.java:62)
F:\adb>

I think there's something wrong with the way I have handled the VARRAY. Please help me...

Thank you! :-)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

笑忘罢 2024-08-20 15:25:42

这似乎是抛出异常的行:

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

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

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

It appears to be this line throwing the exception:

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

I think the issue is that it is not resolving the type name in the database. I believe I found in the past that the type lookup is case-sensitive, and by default in Oracle the type name is all upper-case. So try using "KNOWNLANGUAGES" in the above line.

Also, it appears to be looking in the SYSTEM schema, although I don't know why. If the type is not owned by SYSTEM, you might need to explicitly specify that in the the descriptor call as well (e.g. "SCOTT.KNOWNLANGUAGES").

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文