诚心请教一HQL的写法或映射方法.急...

目前我有一个HQL语句运行正常,但数据不对要进行调整
标准SQL是这样:

select a.BRANDID,a.PICURL ,a.BRAND_CH ,a.SORT ,count(b.PRODUCTID) as counts
  from BRAND a  left outer join PRODUCT b on a.BRANDID =b.BRANDID and  b.Auditstatus='1'
 where a.STATUSFLAG = 0  group by a.BRANDID, a.PICURL, a.BRAND_CH, a.SORT
 order by a.SORT

调整前的代码:

   StringBuffer sql = new StringBuffer();
   sql.append("select new com.kmi.terminal.memory.model.view.ViewBrand(a.brandid,");
   sql.append("a.picurl,a.brandCh,a.sort,count(b.productid)) from ").append(TABLE_NAME);
   sql.append(" a left join a.products b ");
   sql.append(" where a.statusflag=0 ");
   sql.append(" group by a.brandid,a.picurl,a.brandCh,a.sort order by a.sort");
 

调整后的代码:

StringBuffer sql = new StringBuffer();
sql.append("select new com.kmi.terminal.memory.model.view.ViewBrand(a.brandid,");
sql.append("a.picurl,a.brandCh,a.sort,count(b.productid)) from ").append(TABLE_NAME);
sql.append(" a left join a.products b ");
sql.append(" and b.auditstatus='1' where a.statusflag=0 ");
sql.append(" group by a.brandid,a.picurl,a.brandCh,a.sort order by a.sort");

具体变化在这一行:

sql.append(" and b.auditstatus='1' where a.statusflag=0 ");

我本来是想Hibernate解析后自动就加上where前的and 条件,结果确不是往想象的方向发展

我想实现和标准SQL相同效果的一个HQL要如何写,或在*.hbm.xml中要如何修改.烦请达人告知

评论

2 楼 ssy8110 2007-12-21  

red008 写道

不要用and 用with

left join cat.kittens as kitten
        with kitten.bodyWeight > 10.0

谢谢,再感谢,完全正解....

1 楼 red008 2007-12-21  

不要用and 用with

left join cat.kittens as kitten
        with kitten.bodyWeight > 10.0