nested table是Oracle所特有的一种实现,作为被Oracle收购了的OR-Mapping工具Toplink,自然也应该对其提供支持。但是在寻找这个的过程中,又一次深深体会到了Toplink文档的稀少(OTN,也只有OTN)以及语焉不详(我就不信有人看着官方文档能调出来的)。为以后起见,这里记录一下吧。
假设有以下的nested table:
CREATE OR REPLACE TYPE STUDENT_TYPE AS OBJECT
(
STUDENT_ID NUMBER(4),
STUDENT_NAME VARCHAR2(10)
);
CREATE OR REPLACE TYPE STUDENT_LIST IS TABLE OF STUDENT_TYPE;
CREATE TABLE SCHOOL(SCHOOL_ID NUMBER(2) primary key, SCHOOL_NAME VARCHAR2(14),
STUDENTS STUDENT_LIST) NESTED TABLE STUDENTS store AS STUDENTS_TAB;
ALTER TABLE STUDENTS_TAB add CONSTRAINT UK_STUDENTS_TAB UNIQUE(STUDENT_ID);
和以下的JO
public class School {
private int id;
private String name;
private List<Student> students;
//skip getter, setter
}
public class Student {
private int id;
private String name;
// skip getter,setter
}
那么相应的Descriptor应该这么写
public ClassDescriptor buildSchoolDescriptor() {
ObjectRelationalDescriptor descriptor = new ObjectRelationalDescriptor();
descriptor.setJavaClass(School.class);
descriptor.addTableName("SCHOOL");
descriptor.addPrimaryKeyFieldName("SCHOOL.SCHOOL_ID");
DirectToFieldMapping idMapping = new DirectToFieldMapping();
idMapping.setAttributeName("id");
idMapping.setFieldName("SCHOOL_ID");
descriptor.addMapping(idMapping);
DirectToFieldMapping nameMapping = new DirectToFieldMapping();
nameMapping.setAttributeName("name");
nameMapping.setFieldName("SCHOOL.SCHOOL_NAME");
descriptor.addMapping(nameMapping);
ObjectArrayMapping studentsMapping = new ObjectArrayMapping();
studentsMapping.setReferenceClass(Student.class);
studentsMapping.setAttributeName("students");
studentsMapping.setFieldName("STUDENTS");
studentsMapping.setStructureName("STUDENT_LIST");
descriptor.addMapping(studentsMapping);
return descriptor;
}
public ClassDescriptor buildStudentDescriptor() {
ObjectRelationalDescriptor descriptor = new ObjectRelationalDescriptor();
descriptor.setJavaClass(Student.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("STUDENT_TYPE");
descriptor.addPrimaryKeyFieldName("id");
descriptor.addFieldOrdering("id");
descriptor.addFieldOrdering("name");
descriptor.addDirectMapping("id", "id");
descriptor.addDirectMapping("name", "name");
return descriptor;
}
相应的测试代码如下
Server server = project.createServerSession();
server.login();
UnitOfWork uow = server.acquireUnitOfWork();
ClientSession cs = server.acquireClientSession();
server.shouldLogMessages();
uow.shouldLogMessages();
uow.setLogLevel(1);
School school = new School();
school.setId(1);
school=(School)uow.readObject(school);
System.out.println(school.getStudents().get(0).getId());
school.getStudents().get(0).setName("NN");
uow.commit();
在这个问题上我花了一整天时间,主要是在官方文档这里 完全没有把和核心讲出来。
In TopLink if the nested table is of refs, then you use the NestedTableMapping (target class is not aggregate), if it is a nested table or varray of object types, then you use the ObjectArrayMapping (target class is aggregate).
也就是说,所谓的NestedTableMapping,居然只对ref nested table有效,普通的nested table应该用ObjectArrayMapping, 这是何等跳跃的思维啊!
2 在Student Descriptor中,一定要有以下两句,不然会报ora-17049 "Inconsistent java and sql object types"
descriptor.addFieldOrdering("id");
descriptor.addFieldOrdering("name");
3.还是在Student Descriptor中,添加DirectMapping时一定要写成以下方式,
descriptor.addDirectMapping("id", "id");
descriptor.addDirectMapping("name", "name");
切忌写成以下这样
descriptor.addDirectMapping("id", "STUDENT_ID");
这里我没有去看源码,既然addDirectMapping添加的是一个哑元(不要求指定数据库Column name) 又要求指定fieldOrdering,估计在组装对象时是按顺序来的,也就是把STUDENT_TYPE中出现的属性依照addFieldOrdering依次塞给Student上的属性。为了验证这个猜想,把addFieldOrdering的顺序颠倒一下变成
descriptor.addFieldOrdering("name");
descriptor.addFieldOrdering("id");
果然报了java.lang.NumberFormatException: For input string: "NN",验证了我的猜想