Hi everybody,
I try to use sqlRestrictions but have a problem using it with an alias table. In fact I use several aliases in my criterion. It seems to me that {alias} is replaced by the first alias table name that is found and I don't know how to give the name of my alias table.
Hibernate version: 3.1.3
Full stack trace of any exception that occurs:
Code:
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute query; bad SQL grammar [
select ...
from bo_DocumentContainer this_ inner join bo_Order this_1_ on this_.id=this_1_.documentContainer_ref inner join bo_CustomerOrder this_2_ on this_.id=this_2_.order_ref inner join set_OrderRole roles4_ on this_.id=roles4_.order_ref inner join bo_Role role1_ on roles4_.role_ref=role1_.id left outer join bo_AccountingRole role1_1_ on role1_.id=role1_1_.role_ref inner join bo_DocumentContainer customer2_ on role1_.associate_ref=customer2_.id left outer join bo_Associate customer2_1_ on customer2_.id=customer2_1_.documentContainer_ref left outer join bo_Party customer2_2_ on customer2_.id=customer2_2_.associate_ref left outer join bo_Person customer2_3_ on customer2_.id=customer2_3_.party_ref left outer join bo_System customer2_4_ on customer2_.id=customer2_4_.person_ref left outer join bo_Group customer2_5_ on customer2_.id=customer2_5_.party_ref left outer join bo_RemoteParty customer2_6_ on customer2_.id=customer2_6_.associate_ref left outer join bo_RemotePerson customer2_7_ on customer2_.id=customer2_7_.remoteParty_ref left outer join bo_RemoteGroup customer2_8_ on customer2_.id=customer2_8_.remoteParty_ref left outer join bo_PaymentMode paymentmod7_ on this_1_.paymentMode_ref=paymentmod7_.id left outer join bo_Task translatio8_ on this_2_.task_ref=translatio8_.id
where
((role1_.type=? and (lower(customer2_1_.name) like ? or lower(customer2_3_.firstName) like ? or 'Mustermann' SOUNDS LIKE role1_.name or 'Mustermann' SOUNDS LIKE role1_.firstName))) order by this_.id desc]; nested exception is java.sql.SQLException: Unknown column 'role1_.name' in 'where clause'
java.sql.SQLException: Unknown column 'role1_.name' in 'where clause'
Name and version of the database you are using: MySql 4.1
Code snippet Code:
roleCriteria.createAlias("associate", "customer");
if (customerId != null) {
conjunction.add(Restrictions.eq("customer.id", customerId));
}
if (StringUtils.hasText(customerName)) {
Disjunction nameCriterion = Restrictions.disjunction();
nameCriterion.add(Restrictions.ilike("customer.name",
customerName, MatchMode.ANYWHERE));
nameCriterion.add(Restrictions.ilike(
"customer.firstName", customerName,
MatchMode.ANYWHERE));
nameCriterion
.add(Restrictions.sqlRestriction("'"
+ customerName
+ "' SOUNDS LIKE {alias}.name"));
nameCriterion.add(Restrictions.sqlRestriction("'"
+ customerName
+ "' SOUNDS LIKE {alias}.firstName"));
conjunction.add(nameCriterion);
}