Agfdhyk

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.

I ran into the same problem. Left join between two not linked entities (person left join address in your case) through another entities (employee).

I used JPQL. These entities could be linked directly by IDs, but eclipselink (2.6.2) added implicit join after the left join. And reffered (in left join) to ID from implicit join. It broke the query. I got the exception "ORA-00904: "T3"."ID": invalid identifier".

I solved my problem by removing left join and adding exists and subquery. I did not need to receive entity from left join.

You can try add explicit join on employee before the left join on addres. It did not help me. My eclipselink changing join on implicit join. But maybe you are lucky.

Or you can add left join on employee before the left join on addres. It helps. But maybe this is not what you need.