Incorrect SQL query generated
(This is a repost of an ongoing thread further down. My apologies, but we're on a tight schedule here, and if I can't solve this issue we have to ditch JBoss for another EJB server. Obviously not what I hope for...)
I'm trying to set up a bidirectional many-to-many CMR using three beans: Employee, Employment and Company, where an Employment associates an Employee with a Company. So in effect the m:n relation is split up into two 1:n relations. I can't use relation table mapping, since i need additional data in the Employment bean.
I have gotten to the point where JBoss generates almost correct SQL for the query:
SELECT FROM employment WHERE (company_pk=1)
It picks the correct table to select from, but no fields are marked for selection.
My code and config is basically as follows:
Database tables:
company:
- company_pk integer
employee:
- employee_pk integer
employment:
- company_pk integer
- employee_pk integer
- start_date date
Classes:
public abstract class EmployeeBean ... {
...
public abstract Collection getEmployments();
public abstract void setEmployments(Collection c);
...
}
public abstract class CompanyBean ... {
...
public abstract Collection getEmployments();
public abstract void setEmployments(Collection c);
...
}
public abstract class EmplomentBean ... {
...
public abstract Company getCompany();
public abstract void setCompany(Company c);
public abstract Employee getEmployee();
public abstract void setEmployee(Employee e);
public abstract Date getStartDate();
public abstract void setStartDate(Date date);
...
}
public class EmploymentPK {
...
public Employee employee;
public Company company;
...
}
Configuration:
(from ejb-jar.xml:)
<display-name>Company</display-name>
<ejb-name>Company</ejb-name>
<local-home>test.CompanyHome</local-home>
test.Company
<ejb-class>test.CompanyBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Company</abstract-schema-name>
<cmp-field>
<field-name>companyPK</field-name>
</cmp-field>
<primkey-field>companyPK</primkey-field>
<display-name>Employee</display-name>
<ejb-name>Employee</ejb-name>
<local-home>test.EmployeeHome</local-home>
test.Employee
<ejb-class>test.EmployeeBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Employee</abstract-schema-name>
<cmp-field>
<field-name>employeePK</field-name>
</cmp-field>
<primkey-field>employeePK</primkey-field>
<display-name>Employment</display-name>
<ejb-name>Employment</ejb-name>
<local-home>test.EmploymentHome</local-home>
test.Employment
<ejb-class>test.EmploymentBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>test.EmploymentPK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Employment</abstract-schema-name>
<cmp-field>
<field-name>startDate</field-name>
</cmp-field>
<ejb-relation>
<ejb-relation-name>Company-Employments</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Company</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>Company</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>employments</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Employments</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>Employment</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>company</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>Employee-Employments</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Employee</ejb-relationship-role-name>
One
<relationship-role-source>
<ejb-name>Employee</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>employments</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Employments</ejb-relationship-role-name>
Many
<relationship-role-source>
<ejb-name>Employment</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>employee</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
(from jbosscmp-jdbc.xml:)
<ejb-name>Company</ejb-name>
<table-name>company</table-name>
<cmp-field>
<field-name>companyPK</field-name>
<column-name>company_pk</column-name>
</cmp-field>
<ejb-name>Employee</ejb-name>
<table-name>employee</table-name>
<cmp-field>
<field-name>employeePK</field-name>
<column-name>employee_pk</column-name>
</cmp-field>
<ejb-name>Employment</ejb-name>
<table-name>employment</table-name>
<cmp-field>
<field-name>startDate</field-name>
<column-name>start_date</column-name>
</cmp-field>
<ejb-relation>
<ejb-relation-name>Company-Employments</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Company</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>companyPK</field-name>
<column-name>company_pk</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Employments</ejb-relationship-role-name>
<key-fields />
</ejb-relationship-role>
</ejb-relation>
<ejb-relation>
<ejb-relation-name>Employee-Employments</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Employee</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>employeePK</field-name>
<column-name>employee_pk</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Employments</ejb-relationship-role-name>
<key-fields />
</ejb-relationship-role>
</ejb-relation>