HQL brackets question

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Hi all,

I have an issue with brackets not appearing in the SQL made from this HQL. =( This causes undesired results to be returned.

I have the following dynamic HQL generated:

Code:

select new uk.ltd.conntrol.presentation.web.sitemgmt.vo.AdvancedSearchSitesResultsVO(
  s.siteId,
  s.siteRef,
  s.siteName,
  s.siteAdd1,
  sg.sgDesc,
  tt.tenancyType
)
from
  uk.ltd.conntrol.domain.Site s
  inner join s.siteGroup sg
  inner join s.tenancyType tt
  left outer join s.siteSurveies as ss
  inner join ss.survey as su
where
  1=1
  and(
    1=2
    or(
      1=1
      and s.siteName like '%a%'
      and s.siteAdd1 like '%a%'
      and s.sitePostcode like '%a%'
      and s.siteRef like '%a%'
      and s.siteStatus = '3'
      and s.siteGroup.sgId = '5063'
      and s.siteArea like '%a%'
      and s.siteRegion = '5551'
      and s.tenancyType.tenancyId = '3'
      and s.siteDefective = 'false'
      and s.siteConstruction = '2'
      and s.siteType.id = '1'
      and su.id = '17'
      and ss.performedBy = '3'
    )
    or(
      1=1
      and s.siteName like '%a%'
      and s.siteAdd1 like '%a%'
      and s.sitePostcode like '%a%'
      and s.siteRef like '%a%'
      and s.siteStatus = '2'
      and s.siteGroup.sgId = '5061'
      and s.siteArea like '%a%'
      and s.siteRegion = '5589'
      and s.tenancyType.tenancyId = '19'
      and s.siteDefective = 'false'
      and s.siteConstruction = '2'
      and s.siteType.id = '4'
      and su.id = '27'
      and ss.performedBy = '200'
    )
    or(
      1=1
      and s.siteName like '%a%'
      and s.siteAdd1 like '%a%'
      and s.sitePostcode like '%a%'
      and s.siteRef like '%a%'
      and s.siteStatus = '2'
      and s.siteGroup.sgId = '5066'
      and s.siteArea like '%a%'
      and s.siteRegion = '5567'
      and s.tenancyType.tenancyId = '19'
      and s.siteDefective = 'true'
      and s.siteConstruction = '3'
      and s.siteType.id = '1'
      and su.id = '17'
      and ss.performedBy = '198'
    )
    or(
      1=1
      and s.siteName like '%a%'
      and s.siteAdd1 like '%a%'
      and s.sitePostcode like '%a%'
      and s.siteRef like '%a%'
      and s.siteStatus = '2'
      and s.siteGroup.sgId = '5061'
      and s.siteArea like '%a%'
      and s.siteRegion = '5664'
      and s.tenancyType.tenancyId = '1'
      and s.siteDefective = 'false'
      and s.siteConstruction = '2'
      and s.siteType.id = '3'
      and su.id = '23'
      and ss.performedBy = '194'
    )
  )
group by
  s.siteId,
  s.siteRef,
  s.siteName,
  s.siteAdd1,
  sg.sgDesc,
  tt.tenancyType

You can ignore the values as those arn't imporant. Basicaly this is a complex search page that allows you to search by different "sets" of site criteria. So I can search for a with with some details OR these other details OR these other details etc. The uses can add as many critera as they like. The HQL should work as indended however I get this SQL back:

Code:

select 
   site0_.site_id as col_0_0_, 
   site0_.site_ref as col_1_0_, 
   site0_.site_name as col_2_0_, 
   site0_.site_add1 as col_3_0_,
   sitegroup1_.sg_desc as col_4_0_, 
   tenancytyp2_.tenancy_type as col_5_0_ 
from 
   dbo.Site site0_ 
   inner join dbo.SiteGroup sitegroup1_ on site0_.site_group=sitegroup1_.sg_id 
   inner join dbo.TenancyType tenancytyp2_ on site0_.site_tenancytype=tenancytyp2_.tenancy_id 
   left outer join dbo.SiteSurvey sitesurvei3_ on site0_.site_id=sitesurvei3_.site_id 
   inner join dbo.Survey survey4_ on sitesurvei3_.survey_id=survey4_.id 
where 
   1=1 and (
      1=2 
      or 1=1 
      and (site0_.site_name like '%a%' )
      and (site0_.site_add1 like '%a%') 
      and (site0_.site_postcode like '%a%') 
      and (site0_.site_ref like '%a%') 
      and site0_.site_status='3' 
      and site0_.site_group='5063' 
      and (site0_.site_area like '%a%') 
      and site0_.site_region='5551' 
      and site0_.site_tenancytype='3' 
      and site0_.site_defective='false' 
      and site0_.site_construction='2' 
      and site0_.site_type_id='1' 
      and survey4_.id='17' 
      and sitesurvei3_.performed_by='3' 
      or 1=1 
      and (site0_.site_name like '%a%') 
      and (site0_.site_add1 like '%a%') 
      and (site0_.site_postcode like '%a%') 
      and (site0_.site_ref like '%a%') 
      and site0_.site_status='2' 
      and site0_.site_group='5061' 
      and (site0_.site_area like '%a%') 
      and site0_.site_region='5589' 
      and site0_.site_tenancytype='19' 
      and site0_.site_defective='false' 
      and site0_.site_construction='2' 
      and site0_.site_type_id='4' 
      and survey4_.id='27' 
      and sitesurvei3_.performed_by='200' 
      or 1=1 
      and (site0_.site_name like '%a%') 
      and (site0_.site_add1 like '%a%') 
      and (site0_.site_postcode like '%a%') 
      and (site0_.site_ref like '%a%') 
      and site0_.site_status='2' 
      and site0_.site_group='5066' 
      and (site0_.site_area like '%a%') 
      and site0_.site_region='5567' 
      and site0_.site_tenancytype='19' 
      and site0_.site_defective='true' 
      and site0_.site_construction='3' 
      and site0_.site_type_id='1' 
      and survey4_.id='17' 
      and sitesurvei3_.performed_by='198' 
      or 1=1 
      and (site0_.site_name like '%a%') 
      and (site0_.site_add1 like '%a%') 
      and (site0_.site_postcode like '%a%') 
      and (site0_.site_ref like '%a%') 
      and site0_.site_status='2' 
      and site0_.site_group='5061' 
      and (site0_.site_area like '%a%') 
      and site0_.site_region='5664' 
      and site0_.site_tenancytype='1' 
      and site0_.site_defective='false' 
      and site0_.site_construction='2' 
      and site0_.site_type_id='3' 
      and survey4_.id='23' 
      and sitesurvei3_.performed_by='194'
   )
group by 
   site0_.site_id , 
   site0_.site_ref , 
   site0_.site_name , 
   site0_.site_add1 , 
   sitegroup1_.sg_desc , 
   tenancytyp2_.tenancy_type

This runs without error but the brackets seem to be wrong. I would be expecting somthing more along the lines of:

Code:

select 
   site0_.site_id as col_0_0_, 
   site0_.site_ref as col_1_0_, 
   site0_.site_name as col_2_0_, 
   site0_.site_add1 as col_3_0_,
   sitegroup1_.sg_desc as col_4_0_, 
   tenancytyp2_.tenancy_type as col_5_0_ 
from 
   dbo.Site site0_ 
   inner join dbo.SiteGroup sitegroup1_ on site0_.site_group=sitegroup1_.sg_id 
   inner join dbo.TenancyType tenancytyp2_ on site0_.site_tenancytype=tenancytyp2_.tenancy_id 
   left outer join dbo.SiteSurvey sitesurvei3_ on site0_.site_id=sitesurvei3_.site_id 
   inner join dbo.Survey survey4_ on sitesurvei3_.survey_id=survey4_.id 
where 
   1=1 and (
      1=2 
      or (
         1=1 
         and (site0_.site_name like '%a%' )
         and (site0_.site_add1 like '%a%') 
         and (site0_.site_postcode like '%a%') 
         and (site0_.site_ref like '%a%') 
         and site0_.site_status='3' 
         and site0_.site_group='5063' 
         and (site0_.site_area like '%a%') 
         and site0_.site_region='5551' 
         and site0_.site_tenancytype='3' 
         and site0_.site_defective='false' 
         and site0_.site_construction='2' 
         and site0_.site_type_id='1' 
         and survey4_.id='17' 
         and sitesurvei3_.performed_by='3' 
      )
      or (
         1=1 
         and (site0_.site_name like '%a%') 
         and (site0_.site_add1 like '%a%') 
         and (site0_.site_postcode like '%a%') 
         and (site0_.site_ref like '%a%') 
         and site0_.site_status='2' 
         and site0_.site_group='5061' 
         and (site0_.site_area like '%a%') 
         and site0_.site_region='5589' 
         and site0_.site_tenancytype='19' 
         and site0_.site_defective='false' 
         and site0_.site_construction='2' 
         and site0_.site_type_id='4' 
         and survey4_.id='27' 
         and sitesurvei3_.performed_by='200' 
      )
      or (
         1=1 
         and (site0_.site_name like '%a%') 
         and (site0_.site_add1 like '%a%') 
         and (site0_.site_postcode like '%a%') 
         and (site0_.site_ref like '%a%') 
         and site0_.site_status='2' 
         and site0_.site_group='5066' 
         and (site0_.site_area like '%a%') 
         and site0_.site_region='5567' 
         and site0_.site_tenancytype='19' 
         and site0_.site_defective='true' 
         and site0_.site_construction='3' 
         and site0_.site_type_id='1' 
         and survey4_.id='17' 
         and sitesurvei3_.performed_by='198' 
      )
      or (
         1=1 
         and (site0_.site_name like '%a%') 
         and (site0_.site_add1 like '%a%') 
         and (site0_.site_postcode like '%a%') 
         and (site0_.site_ref like '%a%') 
         and site0_.site_status='2' 
         and site0_.site_group='5061' 
         and (site0_.site_area like '%a%') 
         and site0_.site_region='5664' 
         and site0_.site_tenancytype='1' 
         and site0_.site_defective='false' 
         and site0_.site_construction='2' 
         and site0_.site_type_id='3' 
         and survey4_.id='23' 
         and sitesurvei3_.performed_by='194'
      )
   )
group by 
   site0_.site_id , 
   site0_.site_ref , 
   site0_.site_name , 
   site0_.site_add1 , 
   sitegroup1_.sg_desc , 
   tenancytyp2_.tenancy_type

Here's my code to generate the dynamic HQL:

Code:

   public Collection<AdvancedSearchSitesResultsVO> findSitesBySiteAndAssetCriteria(AdvancedSearchSitesCriteria criteria){
      String hql = makeFindSitesBySiteAndAssetCriteriaHQL(criteria);
      return (Collection<AdvancedSearchSitesResultsVO>)getHibernateTemplate().find(hql);
   }

   private String makeFindSitesBySiteAndAssetCriteriaHQL(AdvancedSearchSitesCriteria criteria){
      boolean doSiteCriterias = doSiteCriterias(criteria);
      boolean doAssetCriterias = doAssetCriterias(criteria);
      String[] voFields = {
         "  s.siteId,\n", 
         "  s.siteRef,\n", 
         "  s.siteName,\n", 
         "  s.siteAdd1,\n", 
         "  sg.sgDesc,\n",
         "  tt.tenancyType\n"
      };
      StringBuilder hql = new StringBuilder();
      hql.append("select new ").append(AdvancedSearchSitesResultsVO.class.getName()).append("(\n");
      for(String field : voFields){
         hql.append(field);
      }
      hql.append(")\n");
      hql.append("from\n");
      hql.append("  ").append(Site.class.getName()).append(" s\n");
      hql.append("  inner join s.siteGroup sg\n");
      hql.append("  inner join s.tenancyType tt\n");
      if(doSiteCriterias){
         hql.append(makeSiteCriteriaJoins());
      }
      if(doAssetCriterias){
         hql.append(makeAssetCriteriaJoins(criteria.getAssetCriteriaGroupList()));
      }
      if(doSiteCriterias || doAssetCriterias){
         hql.append("where\n");
         hql.append("  1=1\n");
         if(doSiteCriterias){
            hql.append(makeSiteCriteraWheres(criteria.getSiteCriteriaGroupList()));
         }
         if(doAssetCriterias){
            hql.append(makeAssetCriteriaWheres(criteria.getAssetCriteriaGroupList()));
         }
      }
      hql.append("group by\n");
      for(String field : voFields){
         hql.append(field);
      }
      return hql.toString();
   }

   private String makeSiteCriteriaJoins(){
      StringBuilder joins = new StringBuilder();
      joins.append("  left outer join s.siteSurveies as ss\n");
      joins.append("  inner join ss.survey as su\n");
      return joins.toString();
   }

   private String makeSiteCriteraWheres(Collection<SiteCriteriaGroup> siteCriteriaGroups){
      StringBuilder wheres = new StringBuilder();
      wheres.append("  and(\n");
      wheres.append("    1=2\n");
      for(SiteCriteria siteCriteria : siteCriteriaGroups.iterator().next().getSiteCriteriaList()){
         wheres.append("    or(\n");
         wheres.append("      1=1\n");
         if(valid(siteCriteria.getSiteName())){
            wheres.append("      and s.siteName like '%");
            wheres.append(siteCriteria.getSiteName()).append("%'\n");
         }
         if(valid(siteCriteria.getAddr1())){
            wheres.append("      and s.siteAdd1 like '%");
            wheres.append(siteCriteria.getAddr1()).append("%'\n");
         }
         if(valid(siteCriteria.getPostCode())){
            wheres.append("      and s.sitePostcode like '%");
            wheres.append(siteCriteria.getPostCode()).append("%'\n");
         }
         if(valid(siteCriteria.getRef())){
            wheres.append("      and s.siteRef like '%");
            wheres.append(siteCriteria.getRef()).append("%'\n");
         }
         if(valid(siteCriteria.getStatus())){
            wheres.append("      and s.siteStatus = '");
            wheres.append(siteCriteria.getStatus()).append("'\n");
         }
         if(valid(siteCriteria.getLiveDate())){
            wheres.append("      and s.siteLivedate = '");
            wheres.append(SQL_DATE_FORMAT.format(siteCriteria.getLiveDate())).append("'\n");
         }
         if(valid(siteCriteria.getGroup())){
            wheres.append("      and s.siteGroup.sgId = '");
            wheres.append(siteCriteria.getGroup()).append("'\n");
         }
         if(valid(siteCriteria.getArea())){
            wheres.append("      and s.siteArea like '%");
            wheres.append(siteCriteria.getArea()).append("%'\n");
         }
         if(valid(siteCriteria.getRegion())){
            wheres.append("      and s.siteRegion = '");
            wheres.append(siteCriteria.getRegion()).append("'\n");
         }
         if(valid(siteCriteria.getUsageType())){
            wheres.append("      and s.tenancyType.tenancyId = '");
            wheres.append(siteCriteria.getUsageType()).append("'\n");
         }
         if(valid(siteCriteria.getUnderDefect())){
            wheres.append("      and s.siteDefective = '");
            wheres.append(siteCriteria.getUnderDefect()).append("'\n");
         }
         if(valid(siteCriteria.getConstrType())){
            wheres.append("      and s.siteConstruction = '");
            wheres.append(siteCriteria.getConstrType()).append("'\n");
         }
         if(valid(siteCriteria.getSiteType())){
            wheres.append("      and s.siteType.id = '");
            wheres.append(siteCriteria.getSiteType()).append("'\n");
         }
         if(valid(siteCriteria.getSurvey())){
            wheres.append("      and su.id = '");
            wheres.append(siteCriteria.getSurvey()).append("'\n");
         }
         if(valid(siteCriteria.getSurveyor())){
            wheres.append("      and ss.performedBy = '");
            wheres.append(siteCriteria.getSurveyor()).append("'\n");
         }
         wheres.append("    )\n");
      }
      wheres.append("  )\n");
      return wheres.toString();
   }

Any help would be much appriciated! Also I can't use the criteria API for this. So don't surgest it! :P

Adam

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Sorry pasted in some methods twice and missed some out, corrected now.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

I'm shamlessly bumping my own topic. >.>

piet.t · **Joined:** Fri May 12, 2006 4:05 am **Posts:** 106

Looks like you're missing the parentheses following the "or" that join the "and"-terms.
I'm pretty sure you don't need those, because usually "and" has precedence over "or" and hibernate seems to know that fact. I don't know if this is fixed in the SQL-standard, but most DBMS use it that way.
So the result should be identical - regardless if with or without the parentheses.

piet.t · **Joined:** Fri May 12, 2006 4:05 am **Posts:** 106

Looks like you're missing the parentheses following the "or" that join the "and"-terms.
I'm pretty sure you don't need those, because usually "and" has precedence over "or" and hibernate seems to know that fact. I don't know if this is fixed in the SQL-standard, but most DBMS use it that way.
So the result should be identical - regardless if with or without the parentheses.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

not missing any brackets, i've checked that many times lol. however i have run this in SQL myself with and without the brackets and you are right it doesnt actually matter if they are there or not... just looks a bit odd when you try and break it up yourself.
Thanks for the reply though.