将数组从 Java 传递到 Oracle:java.sql.SQLException:无法转换为内部表示:错误 [英] Pass array from Java to Oracle: java.sql.SQLException: Fail to convert to internal representation:error
问题描述
我在 DAO 中有以下内容,当我执行时,我得到 p><块引用>
java.sql.SQLException:无法转换为内部表示:test.Project@843
DAO 代码
List projectList = new LinkedList();public void saveRecord(List project)抛出数据库异常,SQLException {对于(项目项目:项目){插入记录(项目);}}private void insertRecord(Project project) 抛出 SQLException {projectList.add(project);尝试{ArrayDescriptor desc =ArrayDescriptor.createDescriptor("MY_ARRAY", dbConn);//在这一行执行ARRAY arr = new ARRAY(desc, dbConn, (Object[])projectList.toArray());
我该如何解决这个问题?
编辑 1
CREATE OR REPLACE TYPE project_type as object(proj_id varchar2 (10),proj_title varchar2 (10));创建或替换类型 my_array 作为 project_type 的表;
不幸的是,这比人们想象的要复杂.您必须使用STRUCT
对象、描述符,最后使用ARRAY
.下面是一个工作示例.
-- 数据库代码--创建表 project_types (proj_id VARCHAR2(10),proj_title VARCHAR2(10));/创建或替换类型 project_type AS OBJECT (proj_id VARCHAR2(10),proj_title VARCHAR2(10));/创建或替换类型 my_array 作为 project_type 表;/创建或替换程序 add_projects(p_projects_array IN my_array)作为开始如果 p_projects_array 不是 NULL THENFOR v_i IN 1..p_projects_array.LAST环形INSERT INTO project_types值 (p_projects_array(v_i).proj_id,p_projects_array(v_i).proj_title);结束循环;万一;结尾;/
//Java 代码——主类导入 java.sql.Connection;导入 java.sql.DriverManager;导入 oracle.jdbc.OracleCallableStatement;导入 oracle.jdbc.OracleConnection;导入 oracle.sql.ARRAY;导入 oracle.sql.ArrayDescriptor;导入 oracle.sql.STRUCT;导入 oracle.sql.StructDescriptor;公共类 ArrayExampleMain {public static void main(String[] args) 抛出异常 {OracleConnection conn = getOracleConnection().unwrap(OracleConnection.class);System.out.println("连接成功.");OracleCallableStatement callStmt = null;尝试 {callStmt = (OracleCallableStatement)conn.prepareCall("{call add_projects(?)}");//为 ProjectType 对象的属性创建保存值的数组Object[] project1 = new Object[] {"1", "Title 1"};Object[] project2 = new Object[] {"2", "Title 2"};//数据库中定义的 OBJECT 类型的描述符StructDescriptor projectTypeDesc = StructDescriptor.createDescriptor("PROJECT_TYPE", conn);//每个结构体都是一个 ProjectType 对象STRUCT structProject1 = new STRUCT(projectTypeDesc, conn, project1);STRUCT structProject2 = new STRUCT(projectTypeDesc, conn, project2);结构[] structArrayOfProjects = {structProject1, structProject2};//数据库中定义的 TABLE 类型的描述符ArrayDescriptor projectTypeArrayDesc = ArrayDescriptor.createDescriptor("MY_ARRAY", conn);//包含两个 ProjectType 对象的数组ARRAY arrayOfProjects = new ARRAY(projectTypeArrayDesc, conn, structArrayOfProjects);callStmt.setARRAY(1, arrayOfProjects);callStmt.execute();conn.commit();System.out.println("已提交.");} 捕获(异常 e){if (conn != null) try { conn.rollback();} catch (Exception ex) { System.out.println("回滚失败.");}扔e;} 最后 {callStmt.close();连接.关闭();}}公共静态连接 getOracleConnection() 抛出异常 {String driver = "oracle.jdbc.driver.OracleDriver";String url = "jdbc:oracle:thin:@YOUR_HOST:orcl";字符串用户名 = "hr";String password = "密码";Class.forName(驱动程序);//加载 Oracle 驱动Connection conn = DriverManager.getConnection(url, username, password);返回连接;}}
主类执行后检查project_types
表的内容:
SELECT * FROM project_types;
输出:
PROJ_ID PROJ_TITLE---------- ----------1 标题 12 标题 2
I have the following in DAO and when I execute, I am getting
java.sql.SQLException: Fail to convert to internal representation: test.Project@843
DAO Code
List projectList = new LinkedList();
public void saveRecord(List<Project> project)
throws DatabaseException,SQLException {
for (Project items: project) {
insertRecord(items);
}
}
private void insertRecord(Project project) throws SQLException {
projectList.add(project);
try{
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("MY_ARRAY", dbConn);
// execpetion in this line
ARRAY arr = new ARRAY(desc, dbConn, (Object[])projectList.toArray());
How can I resolve this issue?
Edit 1
CREATE OR REPLACE TYPE project_type as object(
proj_id varchar2 (10),
proj_title varchar2 (10));
create or replace type my_array as Table of project_type;
Unfortunately, this is more complicated than one might expect. You have to use STRUCT
objects, descriptors and, finally, ARRAY
. Below is a working example.
-- Database code --
CREATE TABLE project_types (
proj_id VARCHAR2(10),
proj_title VARCHAR2(10)
);
/
CREATE OR REPLACE TYPE project_type AS OBJECT (
proj_id VARCHAR2(10),
proj_title VARCHAR2(10)
);
/
CREATE OR REPLACE TYPE my_array AS TABLE OF project_type;
/
CREATE OR REPLACE PROCEDURE add_projects(p_projects_array IN my_array)
AS
BEGIN
IF p_projects_array IS NOT NULL THEN
FOR v_i IN 1..p_projects_array.LAST
LOOP
INSERT INTO project_types
VALUES (p_projects_array(v_i).proj_id,
p_projects_array(v_i).proj_title);
END LOOP;
END IF;
END;
/
// Java code - main class
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class ArrayExampleMain {
public static void main(String[] args) throws Exception {
OracleConnection conn = getOracleConnection().unwrap(OracleConnection.class);
System.out.println("Got Connection.");
OracleCallableStatement callStmt = null;
try {
callStmt = (OracleCallableStatement)conn.prepareCall("{call add_projects(?)}");
// create array holding values for ProjectType object's properties
Object[] project1 = new Object[] {"1", "Title 1"};
Object[] project2 = new Object[] {"2", "Title 2"};
// descriptor for OBJECT type defined in database
StructDescriptor projectTypeDesc = StructDescriptor.createDescriptor("PROJECT_TYPE", conn);
// each struct is one ProjectType object
STRUCT structProject1 = new STRUCT(projectTypeDesc, conn, project1);
STRUCT structProject2 = new STRUCT(projectTypeDesc, conn, project2);
STRUCT[] structArrayOfProjects = {structProject1, structProject2};
// descriptor of TABLE type defined in database
ArrayDescriptor projectTypeArrayDesc = ArrayDescriptor.createDescriptor("MY_ARRAY", conn);
// array holding two ProjectType objects
ARRAY arrayOfProjects = new ARRAY(projectTypeArrayDesc, conn, structArrayOfProjects);
callStmt.setARRAY(1, arrayOfProjects);
callStmt.execute();
conn.commit();
System.out.println("Committed.");
} catch (Exception e) {
if (conn != null) try { conn.rollback(); } catch (Exception ex) { System.out.println("Rollback failed."); }
throw e;
} finally {
callStmt.close();
conn.close();
}
}
public static Connection getOracleConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@YOUR_HOST:orcl";
String username = "hr";
String password = "password";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
Checking content of the project_types
table after execution of main class:
SELECT * FROM project_types;
Output:
PROJ_ID PROJ_TITLE ---------- ---------- 1 Title 1 2 Title 2
这篇关于将数组从 Java 传递到 Oracle:java.sql.SQLException:无法转换为内部表示:错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!