0 Replies Latest reply on Aug 29, 2008 7:23 AM by Victor Denisov

    Bad SQL created from EJB QL for simple join - please help!

    Victor Denisov Newbie

      I'm stuck for the last couple of hours trying to understand what could be causing the following problem on JBoss 4.0.5 GA w/ EJB3.

      I have two entities:

      package com.airgonomix.entities;
      
      import javax.persistence.*;
      import java.util.Date;
      import java.util.Collection;
      import java.io.Serializable;
      
      @Entity
      @Table(name="holidays")
      public class Holiday implements Serializable {
      
       private int holidayId;
       private Date holidayStart;
       private Date holidayEnd;
      
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       @Column(name="hol_int_holiday_id")
       public int getHolidayId() {
       return holidayId;
       }
      
       public void setHolidayId(int holidayId) {
       this.holidayId = holidayId;
       }
      
       @Column(name="hol_dt_holiday_start")
       @Temporal(TemporalType.DATE)
       public Date getHolidayStart() {
       return holidayStart;
       }
      
       public void setHolidayStart(Date holidayStart) {
       this.holidayStart = holidayStart;
       }
      
       @Column(name="hol_dt_holiday_end")
       @Temporal(TemporalType.DATE)
       public Date getHolidayEnd() {
       return holidayEnd;
       }
      
       public void setHolidayEnd(Date holidayEnd) {
       this.holidayEnd = holidayEnd;
       }
      }
      


      and

      package com.airgonomix.entities;
      
      import javax.persistence.*;
      import java.io.Serializable;
      
      @Entity
      @Table(name="holidays_by_tenant")
      @IdClass(HolidayByTenantKey.class)
      public class HolidayByTenant implements Serializable {
       private int holidayId;
       private int tenantId;
       private Tenant tenant;
       private Holiday holiday;
      
       @Id
       @Column(name="hol_int_holiday_id")
       public int getHolidayId() {
       return holidayId;
       }
      
       public void setHolidayId(int holidayId) {
       this.holidayId = holidayId;
       }
      
       @Id
       @Column(name="tnt_int_tenant_id")
       public int getTenantId() {
       return tenantId;
       }
      
       public void setTenantId(int tenantId) {
       this.tenantId = tenantId;
       }
      
       @ManyToOne
       @PrimaryKeyJoinColumn(name="tnt_int_tenant_id", referencedColumnName="tnt_int_tenant_id")
       public Tenant getTenant() {
       return tenant;
       }
      
       public void setTenant(Tenant tenant) {
       this.tenant = tenant;
       }
      
       @ManyToOne
       @PrimaryKeyJoinColumn(name="hol_int_holiday_id", referencedColumnName = "hol_int_holiday_id")
       public Holiday getHoliday() {
       return holiday;
       }
      
       public void setHoliday(Holiday holiday) {
       this.holiday = holiday;
       }
      }
      


      The last one references another entity, Tenant, but that's not important right now. All tables and columns are explicitly defined, but I can add a screenshot of the database structure, if it'll help.

      Now, I'm trying to execute a very simple query - to fetch all Holidays for a given tenantId:

      select hbt.holiday from HolidayByTenant hbt where hbt.tenantId = 1


      Hibernate generates the following SQL:

      select
       holiday1_.hol_int_holiday_id as hol1_39_,
       holiday1_.hol_dt_holiday_start as hol2_39_,
       holiday1_.hol_dt_holiday_end as hol3_39_
       from
       holidays_by_tenant holidaybyt0_
       inner join
       holidays holiday1_
       on holidaybyt0_.holiday_hol_int_holiday_id=holiday1_.hol_int_holiday_id
       where
       holidaybyt0_.tnt_int_tenant_id=1
      


      Look on what column it's trying to join! holiday_hol_int_holiday_id]!Since I don't have such a column in holidays_by_tenant table in my database, the query fails:

      ...
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
       at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:647)
       at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
       at com.airgonomix.ejb.utils.UtilsBean.joinTest(UtilsBean.java:224)
      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'holidaybyt0_.holiday_hol_int_holiday_id' in 'on clause'
      


      What could be the reason for Hibernate to create this field mapping? Any help will be greatly appreciated.