分组查询

(1/5)添加分组字段:group by 子句

方法 等价HQL片段
groupBy(String fromClazzFieldName) select fromClazz.fromClazzFieldName
from...
group by fromClazz.fromClazzFieldName
groupByOther(String tableAlias, String fieldName, String alias) select tableAlias.fieldName as alias
from...
group by tableAlias.fieldName

(2/5)添加组函数及过滤结果:聚合函数、having 子句

A、组函数

无 tableAlias 参数的重载方法,tableAlias=HqlHelper.currTable 即:fromClazz 的别名

distinct 仅当调用 countDistinct 方法时候添加。

方法 等价HQL片段
avg,count,countDistinct,max,min,sum(String tableAlias, String fieldName, String avgAlias) select ..., sum,count,count,max,min,sum(distinct tableAlias.fieldName) as sumAlias
from ...
where ...
group by ...

B、having 子句

having_XXXX 系列方法需要紧随组函数使用,表示对该组函数的过滤规则

方法 等价HQL片段
having_eq,ne,ge,gt,lt,le(Object value) ...
group by ...
having {current group function} =,!=,>=,>,<,<= value
having_between,notBetween(Object value) ...
group by ...
having {current group function} between,not between value1 and value2
having_in,notIn(Object... values) ...
group by ...
having {current group function} in,not in values
having_like,notLike(String value, MatchType matchType) ...
group by ...
having {current group function} like,not like matchType:value
having_ilike,notiLike(String value, MatchType matchType) ...
group by ...
having upper({current group function}) like,not like upper(matchType:value)
having_lengthEq,Ne,Ge,Gt,Lt,Le(Integer length) ...
group by ...
having length({current group function}) =,!=,>=,>,<,<= length

(3/5)过滤条件分组:having 子句的条件分组

 .having_and().条件1.条件2.having_end()  等价HQL片段 : and (条件1 and 条件2) 
 .having_or().条件1.条件2.having_end()   等价HQL片段 : or (条件1 and 条件2)

(4/5)分组排序

group_orderBy 方法需要紧随组函数使用,表示对该组函数的结果排序

方法 等价HQL片段
group_orderBy(OrderType orderType) order by {current group function} orderType

(5/5)示例

    @Autowired
    private HqlHelperService helperService;

    @Test
    public void testHql() {
        HqlHelper helper = HqlHelper.queryFrom(City.class);
        // 示例一
        // 查询每个省份下的城市数量和最大的区号,返回城市数量 between 2 and 10 的数据
        helper.join(HqlHelper.currTable, "province", "p")
              .groupByOther("p", "name", "provinceName")
              // 组函数及其结果cityCount的过滤规则
              .count("id", "cityCount")
                  .having_between(2L, 10L)
              .max("areaCode", "maxAreaCode");
        Records provinceCityCount = helperService.getRecordsGroup(helper);
        System.err.println("provinceCityCount1 =" + provinceCityCount);

        // 示例二
        // 在示例一的基础上
        //(1)去掉`吉林`省的记录
        //(2)过滤分组结果,只读取最大区号08和09开头的数据
        //(3)按照最大区号升序排列
        //(4)读取3条记录
        helper.resetQueryFrom(City.class)
              .join(HqlHelper.currTable, "province", "p")
              //(1)去掉`吉林`省的记录
              .ne("p", "name", "吉林")
              .groupByOther("p", "name", "provinceName")
              // 组函数及其结果cityCount的过滤规则
              .count("id", "cityCount")
                  .having_between(2L, 10L)
              //(2)过滤分组结果,只读取最大区号08和09开头的数据
              .max("areaCode", "maxAreaCode")
                  .having_and()
                    .having_like("08", MatchType.START)
                    .having_or().having_like("09", MatchType.START).having_end()
                  .having_end()
                  //(3)按照最大区号升序排列
                  .group_orderBy(OrderType.asc)
              //(4)读取3条记录
              .setFirstResult(0).setMaxResults(3);
        provinceCityCount = helperService.getRecordsGroup(helper);
        System.err.println("provinceCityCount2 =" + provinceCityCount);
    }
//执行结果: 
    select
        province1_.name as col_0_0_,
        count(city0_.id) as col_1_0_,
        max(city0_.area_code) as col_2_0_ 
    from
        dodo_city city0_ 
    inner join
        dodo_province province1_ 
            on city0_.province_id=province1_.id 
    group by
        province1_.name 
    having
        count(city0_.id) between ? and ?

provinceCityCount1 =Records [rawData=[
    {maxAreaCode=0439, provinceName=吉林, cityCount=9}, 
    {maxAreaCode=0955, provinceName=宁夏, cityCount=5}, 
    {maxAreaCode=0898, provinceName=海南, cityCount=3}, 
    {maxAreaCode=0599, provinceName=福建, cityCount=9}, 
    {maxAreaCode=0897, provinceName=西藏, cityCount=7}, 
    {maxAreaCode=0859, provinceName=贵州, cityCount=9}, 
    {maxAreaCode=0919, provinceName=陕西, cityCount=10}, 
    {maxAreaCode=0979, provinceName=青海, cityCount=8}]]

    select
        province1_.name as col_0_0_,
        count(city0_.id) as col_1_0_,
        max(city0_.area_code) as col_2_0_ 
    from
        dodo_city city0_ 
    inner join
        dodo_province province1_ 
            on city0_.province_id=province1_.id 
    where
        province1_.name<>? 
    group by
        province1_.name 
    having
        (
            count(city0_.id) between ? and ?
        ) 
        and (
            max(city0_.area_code) like ? 
            or max(city0_.area_code) like ?
        ) 
    order by
        col_2_0_ asc limit ?

provinceCityCount2 =Records [rawData=[
    {maxAreaCode=0859, provinceName=贵州, cityCount=9}, 
    {maxAreaCode=0897, provinceName=西藏, cityCount=7}, 
    {maxAreaCode=0898, provinceName=海南, cityCount=3}]]

END

Copyright © DodoFramework 2020 all right reservedModify At: 2019-12-25 13:56:35