Qfyilyi

There are three entities namely Employee, Person and Address. There is a parent-child relationship between Person and Employee (Employee IS-A Person).
There is a 1:1 relationship from Person to Address. (A Person is assumed to have one permanent address).

The key properties of Employee class are: 1. employeeId(pk) 2. personId(fk)

The key properties of Person class are: 1. pId(pk) 2. pCode

The key properties of Address class are: 1. addressId(pk) 2. employeeId(fk)

The following are the descriptor code snippets for Person, Employee and Address classes:

public RelationalDescriptor buildPersonDescriptor() {

RelationalDescriptor descriptor = new RelationalDescriptor();

descriptor.setJavaClass(Person.class);

descriptor.addTableName("PERSON");

descriptor.addPrimaryKeyFieldName("PERSON.PID");



// Inheritance properties.

descriptor.getInheritancePolicy().setClassIndicatorFieldName("PERSON.PCODE");

descriptor.getInheritancePolicy().addClassIndicator(Employee.class, "EMP");



// RelationalDescriptor properties.

descriptor.useSoftCacheWeakIdentityMap();

descriptor.setIdentityMapSize(100);

descriptor.useRemoteSoftCacheWeakIdentityMap();

descriptor.setRemoteIdentityMapSize(100);

descriptor.setSequenceNumberFieldName("PERSON.PID");

descriptor.setSequenceNumberName("PERSON_SEQ");

descriptor.setAlias("Person");



// Query manager.

descriptor.getDescriptorQueryManager().checkCacheForDoesExist();

//Named Queries





DirectToFieldMapping productIDMapping = new DirectToFieldMapping();

productIDMapping.setAttributeName("pId");

productIDMapping.setFieldName("PERSON.PID");

descriptor.addMapping(productIDMapping);



DirectToFieldMapping productIDMapping = new DirectToFieldMapping();

productIDMapping.setAttributeName("pCode");

productIDMapping.setFieldName("PERSON.PCODE");

descriptor.addMapping(productIDMapping);





return descriptor;

}

public RelationalDescriptor buildEmployeeDescriptor() {



RelationalDescriptor descriptor = new RelationalDescriptor();

descriptor.setJavaClass(Employee.class);

descriptor.addTableName("EMPLOYEE");



// Inheritance properties.

descriptor.getInheritancePolicy().setParentClass(Person.class);



// RelationalDescriptor properties.



descriptor.setAlias("Employee");



// Query manager.

descriptor.getDescriptorQueryManager().checkCacheForDoesExist();

//Named Queries



// Event manager.



// Mappings.

DirectToFieldMapping employeeIdMapping = new DirectToFieldMapping();

employeeIdMapping.setAttributeName("employeeId");

employeeIdMapping.setFieldName("EMPLOYEE.EID");

descriptor.addMapping(employeeIdMapping);



DirectToFieldMapping personIdMapping = new DirectToFieldMapping();

personIdMapping.setAttributeName("personId");

personIdMapping.setFieldName("EMPLOYEE.PID");

descriptor.addMapping(personIdMapping);



OneToOneMapping addressMapping = new OneToOneMapping();

addressMapping.setAttributeName("address");

addressMapping.setReferenceClass(Address.class);

addressMapping.dontUseIndirection();

addressMapping.addTargetForeignKeyFieldName("ADDRESS.EID", "EMPLOYEE.EID");

descriptor.addMapping(addressMapping);



return descriptor;

}

    public RelationalDescriptor buildAddressDescriptor() {



RelationalDescriptor descriptor = new RelationalDescriptor();

  descriptor.setJavaClass(com.tropics.application.products.domain.costingandpricing.SellingPriceAddOn.class);

  descriptor.addTableName("ADDRESS");

  descriptor.addPrimaryKeyFieldName("ADDRESS.AID");



  // Descriptor properties.

  descriptor.useSoftCacheWeakIdentityMap();

  descriptor.setIdentityMapSize(100);

  descriptor.useRemoteSoftCacheWeakIdentityMap();

  descriptor.setRemoteIdentityMapSize(100);

  descriptor.setSequenceNumberFieldName("ADDRESS.AID");

  descriptor.setSequenceNumberName("ADDRESS_SEQ");

  descriptor.setAlias("address");



  // Query manager.

  descriptor.getDescriptorQueryManager().checkCacheForDoesExist();



  //Mappings



  DirectToFieldMapping personIDMapping = new DirectToFieldMapping();

  personIDMapping.setAttributeName("employeeId");

  personIDMapping.setFieldName("ADDRESS.EID");

  descriptor.addMapping(personIDMapping);



  DirectToFieldMapping addressIDMapping = new DirectToFieldMapping();

  addressIDMapping.setAttributeName("addressId");

  addressIDMapping.setFieldName("ADDRESS.AID");

  descriptor.addMapping(addressIDMapping);  



}

Following is the code snippet for generating the dynamic query:

ExpressionBuilder employee = new ExpressionBuilder();

ReportQuery query = new ReportQuery(Employee.class,employee);

Expression address = employee.getAllowingNull("address");

query.addAttribute("pId");

query.addAttribute("pCode");

query.addAttribute("employeeId");

query.addAttribute("addressId",address.get("addressId"));

query.addNonFetchJoin(employee.leftJoin(address, 

 address.get("employeeId")));



resultCollection = (Vector) clientSessionHolder.eclipselinkClientSession().executeQuery(query);

On running this program, the query which is generated as per logs:

SELECT t0.PID, t0.PCODE, t1.EID, t2.AID FROM PERSON t0 LEFT OUTER JOIN ADDRESS t2 ON ((t2.EID = t1.EID),EMPLOYEE t1 WHERE ((t1.PID = t0.PID) AND (t0.PCODE = 'EMP'));

The expected query is:
SELECT t0.PID, t0.PCODE, t1.EID, t2.AID FROM PERSON t0,EMPLOYEE t1 LEFT OUTER JOIN ADDRESS t2 ON ((t2.EID = t1.EID) WHERE ((t1.PID = t0.PID) AND (t0.PCODE = 'EMP')));

The table t1 is not correctly applied in the join clause.

Could anyone help me with what's wrong with the expression?

Waiting for a positive reply.