Доброго дня господа !
Имею задачу из Kuassi Mensah (Oracle Database Programming Using Java and Web Services)
Java-источник 1–ый объект
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
create or replace and resolve java source named AddressObj as
import java.sql.*;
import java.io.*;
import java.util.Date;
import oracle.sql.*;
public class AddressObj implements SQLData
{
String sql_type ="address_t";
public String street1;
public String street2;
public String city;
public int zip;
public AddressObj()
{
}
public AddressObj(String sql_type, String street1, String street2, String city, int zip)
{
this.sql_type = sql_type;
this.street1= street1;
this.street2= street2;
this.city= city;
this.zip= zip;
}
public String getSQLTypeName() throws SQLException
{
return sql_type;
}
public void readSQL(SQLInput stream, String typeName)
throws SQLException
{
sql_type = typeName;
SQLInput istream = (SQLInput) stream;
street1 = istream.readString();
street2 = istream.readString();
city = istream.readString();
zip = istream.readInt();
}
public void writeSQL(SQLOutput stream)
throws SQLException
{
SQLOutput ostream = (SQLOutput) stream;
ostream.writeString (street1);
ostream.writeString (street2);
ostream.writeString(city);
ostream.writeInt(zip);
}
}
Java-источник 2–ый объект
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
create or replace and resolve java source named PersonObj as
import java.sql.*;
import java.io.*;
import java.util.Date;
import oracle.sql.*;
public class PersonObj implements SQLData
{
String sql_type = "person_t";
public String name;
public int age;
public AddressObj addrObj;
public PersonObj()
{
}
public PersonObj(String sql_type, String name, int age, AddressObj addrObj)
{
this.sql_type = sql_type;
this.name = name;
this.age = age;
this.addrObj = addrObj;
}
public String getSQLTypeName() throws SQLException
{
return sql_type;
}
public void readSQL(SQLInput stream, String typeName)
throws SQLException
{
sql_type = typeName;
SQLInput istream = (SQLInput) stream;
name = istream.readString();
age = istream.readInt();
addrObj = (AddressObj) istream.readObject();
}
public void writeSQL(SQLOutput stream)
throws SQLException
{
SQLOutput ostream = (SQLOutput) stream;
ostream.writeString (name);
ostream.writeInt (age);
ostream.writeObject(addrObj);
}
}
Java – источник иниц. запуск объект
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
create or replace and resolve java source named SQLJ_Objetc as
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.math.*;
public class SQLJ_Object
{
public static PersonObj getPersonObj(int id)
throws SQLException
{
OracleConnection oconn =(OracleConnection)DriverManager.getConnection("jdbc:oracle:kprb:");
java.util.Dictionary map =(java.util.Dictionary) (oconn.getTypeMap());
Statement stmt = oconn.createStatement();
try
{
map.put("person_t" ,Class.forName("PersonObj" ));
map.put("address_t",Class.forName("AddressObj"));
oconn.setTypeMap((Map)map);
oconn.commit();
}catch(Exception e)
{
throw new SQLException(e.getMessage());
}
PersonObj pobj = null;
PreparedStatement ps =oconn.prepareStatement("SELECT adtcol1 from PersonObjTab where id = ?");
ps.setInt(1,id);
OracleResultSet ors =(OracleResultSet)
ps.executeQuery();
java.util.Hashtable ht = new java.util.Hashtable();
try
{
ht.put("person_t" ,Class.forName("PersonObj" ));
ht.put("address_t",Class.forName("AddressObj"));
}
catch(Exception e)
{
throw new SQLException(e.getMessage());
}
while(ors.next())
{
oconn.commit();
pobj =(PersonObj)ors.getObject(1,(Map)ht);
}
ps.close();
oconn.close();
stmt.close();
PersonObj pobj1 = null;
pobj1 = new PersonObj("person_t","Name",25,new AddressObj("address_t","City","street1","street2",10));
// return pobj1
return pobj;
}
public static void insPersonObj(int id, PersonObj personin, PersonObj[] personout)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:oracle:kprb:");
OracleConnection orconn = (OracleConnection)conn;
PreparedStatement ps = conn.prepareStatement("insert into PersonObjTab values(?,?)");
java.util.Dictionary map =(java.util.Dictionary)(orconn.getTypeMap());
try
{
map.put("person_t" ,Class.forName("PersonObj" ));
map.put("address_t",Class.forName("AddressObj"));
orconn.setTypeMap((Map)map);
}
catch(Exception e)
{
throw new SQLException(e.getMessage());
}
ps.setInt(1,id);
ps.setObject(2,personin);
ps.executeUpdate();
ps.close();
PreparedStatement ps1 = conn.prepareStatement("select adtcol1 from PersonObjTab where id = ?");
ps1.setInt(1,id);
OracleResultSet ors1 = (OracleResultSet) ps1.executeQuery();
while(ors1.next())
{
personout[0] = (PersonObj) ors1.getObject(1);
}
ors1.close();
conn.close();
}
}
PersonObj pobj1 = null;
pobj1 = new PersonObj("person_t","Name",25,new AddressObj("address_t","City","street1","street2",10));
// return pobj1;
Строки кода – ПОКА не содержат логического смысла
Попытаюсь вернуть переменную pobj1 вместо pobj по окончанию задачи
Имею создание PL/SQL типов и таблицы в качестве примера
1.
2.
3.
4.
5.
6.
7.
8.
CREATE OR REPLACE TYPE address_t AS OBJECT
EXTERNAL NAME 'AddressObj'
LANGUAGE JAVA USING SQLDATA
(street1 varchar2(25) external name 'street1',
street2 varchar2(25) external name 'street2',
city varchar2(25) external name 'city',
zip number external name 'zip'
);
1.
2.
3.
4.
5.
6.
7.
CREATE OR REPLACE TYPE person_t AS OBJECT
EXTERNAL NAME 'PersonObj'
LANGUAGE JAVA USING SQLDATA
(name varchar2(25) external name 'name',
age number external name 'age',
addrObj address_t external name 'addrObj'
);
1.
2.
3.
4.
create table PersonObjTab (id number,adtcol1 person_t);
insert into PersonObjTab values(10,person_t('Jack',25,Address_t('10 Embarcadero','Ferry Plazza','San Francisco',93126)));
insert into PersonObjTab values(11,person_t('Bob' ,26,Address_t('12 JrMLK' ,'Alley3','Chicago',1090)));
insert into PersonObjTab values(12,person_t('Doug',27,Address_t('10 Alley1','Alley2','Denvers',1091)));
Создание функции с ссылкой на Java-класс
1.
2.
3.
4.
5.
create or replace function getPersonObj (id IN number) return person_t
as language java name 'SQLJ_Object.getPersonObj(int) returns PersonObj';
create or replace procedure insPersonObj(id IN number,personin IN person_t,personout IN OUT person_t)
as language java name 'SQLJ_Object.insPersonObj(int, PersonObj, PersonObj [])';
Запускающий код
1.
2.
3.
4.
5.
6.
declare
m4 person_t;
begin
m4 := getPersonObj(11);
dbms_output.put_line(' *name is '|| m4.name ||'*age is'||m4.age ||' *street1 is '|| m4.addrObj.street1 ||' *street2 is '||m4.addrObj.street2 || ' *city is '||m4.addrObj.city ||' *zip is '|| m4.addrObj.zip);
end;
Делаю замену вывода переменных в функции getPersonObj pobj=pobj1
Деремирую/ремирую строки
return pobj1;
//return pobj;
Получаю ошибку - скрин приложен в файле
Переменны идентичны по типу, поля объектов заполнены
В чем проблема подскажите пожалуйста.
Спасибо.
