1 Reply Latest reply on Aug 3, 2003 5:02 PM by John M. Nordgaard

    Incorrect SQL query generated

    John M. Nordgaard Newbie

      (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>

        • 1. Re: Incorrect SQL query generated
          John M. Nordgaard Newbie

          Additionally: EmploymentPK implements Serializable, and overrides hashCode() and equals(Object) correctly. However, these methods are implemented so that they'll throw a NullPointerException if either company or employee is null. Since it all fails on an error message from MySQL regarding the erroneous SQL statement neither of these methods are called.