[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Bad SQL created from EJB QL for simple join - please help!

[Plukh]

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.

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4173296#4173296

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4173296