3:56 a.m.
Criteria Query with joined-subclass generates incorrect SQL
-----------------------------------------------------------
Key: HHH-4829
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4829
Project: Hibernate Core
Issue Type: Bug
Components: query-criteria
Affects Versions: 3.3.2
Environment: Hibernate 3.3.2
MySQL 5.0
Reporter: Rajender Aggarwal
We have a scenario where joined-sublcass is used for Inheritance. There is a property
called 'messageBody' that is present in two Child classes. When the query Criteria
created on Parent class and the criteria contains 'messageBody' - the where clause
contains like for only one table (the one that appears first in joined-subclass).
I am attaching trimmed version of HBM file, Method that prepares Criteria query, Current
SQL and expected SQL.
Please let me know if any further information is required. rajender.aggarwal(a)gmail.com
---------------------------------------------- HBM File
------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-cascade="none">
<class name="com.myproject.core.model.Ad" abstract="true"
table="Ad">
<id name="id" type="long" column="ad_id"
unsaved-value="null">
<generator class="native" />
</id>
<property name="subAccount" type="string">
<column name="subaccount" sql-type="text" />
</property>
<many-to-one name="shortCode" column="shortcode_id"
class="com.myproject.core.model.ShortCode"></many-to-one>
<many-to-one name="account" column="account_id"
class="com.myproject.core.model.Account"
lazy="false"></many-to-one>
<joined-subclass name="com.myproject.core.model.D2PAd"
extends="com.myproject.core.model.Ad"
table="d2pad" batch-size="50">
<key column="d2pad_id" />
<property name="createdOn" type="timestamp"
column="createdOn"
not-null="true" />
<property name="modifiedOn" column="modifiedOn"
type="timestamp" />
<property name="activationDate" column="activation_date"
type="timestamp" />
<property name="active" column="active" type="boolean"
/>
<property name="ageVerfReq" column="ageverfreq"
type="boolean" />
<property name="deactivationDate" column="deactivation_date"
type="timestamp" />
<property name="title" type="string">
<column name="title" sql-type="text" />
</property>
<property name="url" type="string">
<column name="url" sql-type="text" />
</property>
<property name="deleted" column="deleted"
type="boolean" />
<list name="templates" cascade="all,delete-orphan">
<key column="adId" />
<index column="sequence" />
<one-to-many class="com.myproject.d2p.model.D2PAdTemplate" />
</list>
<bag name="webPages" cascade="all,delete-orphan">
<key column="d2pAd" />
<one-to-many class="com.myproject.d2p.webpage.model.D2PAdWebPage" />
</bag>
</joined-subclass>
<joined-subclass name="com.myproject.cmm.model.CMMAd"
table="cmmad" abstract="true"
extends="com.myproject.core.model.Ad" batch-size="50">
<key column="cmmad_id" />
<property name="title" type="string">
<column name="title" sql-type="text" />
</property>
<property name="dateTime" column="scheduled_date_time"
type="timestamp" />
<many-to-one name="optInD2PAd" column="optin_d2pad"
class="com.myproject.core.model.D2PAd"></many-to-one>
<many-to-one name="destinationGroup" column="contactGroup_Id"
class="com.myproject.contactgroup.model.ContactGroup"></many-to-one>
<property name="deleted" column="deleted"
type="boolean" />
<joined-subclass name="com.myproject.cmm.model.GeneralCMMAd"
table="generalcmmad" extends="com.myproject.cmm.model.CMMAd"
batch-size="50">
<key column="cmmad_id" />
<property name="messageBody" type="string">
<column name="messagebody" sql-type="text" />
</property>
<property name="optInText" type="string">
<column name="optin_text" sql-type="text" />
</property>
</joined-subclass>
<joined-subclass name="com.myproject.cmm.model.D2PCMMAd"
table="d2pcmmad" extends="com.myproject.cmm.model.CMMAd"
batch-size="50">
<key column="cmmad_id" />
<many-to-one name="d2pAd" column="d2pad_id"
class="com.myproject.core.model.D2PAd"></many-to-one>
<property name="optInText" type="string">
<column name="optin_text" sql-type="text" />
</property>
</joined-subclass>
<joined-subclass name="com.myproject.cmm.model.OptInCMMAd"
table="optincmmad" extends="com.myproject.cmm.model.CMMAd"
batch-size="50">
<key column="cmmad_id" />
<property name="messageBody" type="string">
<column name="messagebody" sql-type="text" />
</property>
</joined-subclass>
<joined-subclass name="com.myproject.cmm.model.AppointmentCMMAd"
table="appointmentcmmad" extends="com.myproject.cmm.model.CMMAd"
batch-size="50">
<key column="cmmad_id" />
<property name="appointmentDateTime"
column="appointment_date_time" type="timestamp" />
<list name="messageCMMAds" cascade="all,delete-orphan"
lazy="false">
<key column="appointmentCMM" />
<index column="sequence" />
<one-to-many class="com.myproject.cmm.model.AppointmentOption" />
</list>
</joined-subclass>
</joined-subclass>
</class>
</hibernate-mapping>
---------------------------------------------- Criteria Query Method
------------------------------------------------
Input Parameters:
pageNo = 1
accountId = 1
filterMap = [{"messageBody", "test"}]
adClass = com.converdia.cmm.model.CMMAd
rowCount = 10
protected <T extends Ad> Criteria createCriteriaForAd(Integer pageNo, Long
accountId,
Map<FilterKey, Object> filterMap, Class<T> adClass, boolean
rowCount) {
Criteria crt = this.getSession().createCriteria(adClass);
if (accountId != null) {
crt.add(Restrictions.eq("account.id", accountId));
}
crt.add(Restrictions.ne("deleted", Boolean.TRUE));
for (FilterKey key : filterMap.keySet()) {
switch (key.getFilterType()) {
case FilterKey.CONDITION_EQUALS_TYPE:
crt.add(Restrictions.eq(key.getFieldKey(), filterMap.get(key)));
break;
case FilterKey.CONDITION_LIKE_TYPE:
String srch = ((String) filterMap.get(key)).replace('*',
'%');
crt.add(Restrictions.like(key.getFieldKey(), '%' + srch +
'%'));
break;
case FilterKey.CONDITION_GREATER:
crt.add(Restrictions.ge(key.getFieldKey(), filterMap.get(key)));
break;
case FilterKey.CONDITION_LESSER:
crt.add(Restrictions.le(key.getFieldKey(), filterMap.get(key)));
break;
case FilterKey.CONDITION_BETWEEN:
Object[] values = (Object[]) filterMap.get(key);
crt.add(Restrictions.between(key.getFieldKey(), values[0],
values[1]));
break;
default:
break;
}
}
if (rowCount) {
crt.setProjection(Projections.projectionList().add(Projections.rowCount()));
} else {
crt.setFirstResult((pageNo - 1) * 10).setMaxResults(10);
}
return crt;
}
---------------------------------------------- Filter Key Class
------------------------------------------------
package com.myproject.core.helper;
public class FilterKey {
public static final int CONDITION_LIKE_TYPE = 1;
public static final int CONDITION_EQUALS_TYPE = 2;
public static final int CONDITION_GREATER = 3;
public static final int CONDITION_LESSER = 4;
public static final int CONDITION_BETWEEN = 5;
public FilterKey() {
}
public FilterKey(String fieldKey, Integer filterType) {
this.fieldKey = fieldKey;
this.filterType = filterType;
}
private String fieldKey;
private Integer filterType;
public String getFieldKey() {
return this.fieldKey;
}
public void setFieldKey(String fieldKey) {
this.fieldKey = fieldKey;
}
public Integer getFilterType() {
return this.filterType;
}
public void setFilterType(Integer filterType) {
this.filterType = filterType;
}
}
---------------------------------------------- Generated Query
------------------------------------------------
select
count(*) as y0_
from
cmmad this_
inner join
Ad this_1_
on this_.cmmad_id=this_1_.ad_id
left outer join
generalcmmad this_2_
on this_.cmmad_id=this_2_.cmmad_id
left outer join
d2pcmmad this_3_
on this_.cmmad_id=this_3_.cmmad_id
left outer join
optincmmad this_4_
on this_.cmmad_id=this_4_.cmmad_id
left outer join
appointmentcmmad this_5_
on this_.cmmad_id=this_5_.cmmad_id
where
this_.deleted<>?
and this_2_.messagebody like ?
---------------------------------------------- Expected Query
------------------------------------------------
select
count(*) as y0_
from
cmmad this_
inner join
Ad this_1_
on this_.cmmad_id=this_1_.ad_id
left outer join
generalcmmad this_2_
on this_.cmmad_id=this_2_.cmmad_id
left outer join
d2pcmmad this_3_
on this_.cmmad_id=this_3_.cmmad_id
left outer join
optincmmad this_4_
on this_.cmmad_id=this_4_.cmmad_id
left outer join
appointmentcmmad this_5_
on this_.cmmad_id=this_5_.cmmad_id
where
this_.deleted<>?
and (this_2_.messagebody like ? or this_4_.messagebody like ?)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira