最新java持久层框架八 summer-data jql高级查询

官网地址: https://www.summer-data.com
代码库地址：https://gitee.com/hahan2020/summer-data

常量

Prod t1 = new Prod();

Jql jql = new Jql(){{
    select(t1)
    select(Value.use(100).alias("int_value"));
    select(Value.use("我是常量").alias("string_value"));
    from(t1);
}};

分组查询

Prod t1 = new Prod();
ProdDetail t2 = new ProdDetail();

Count cnt = Func.count(t2.res_id);
Jql jql = new Jql() {{
    select(t1.prod_name, cnt.alias("CNT"));
    from(t1);
    join(t2).on(t1.res_id.eq(t2.prod_res_id));
    group(t1.prod_name);
    having(cnt.eq(100));
}};

case when 语句

在标准sql中 case when 是关键字，不过考虑它的功能更像函数，这里我们它作为一个函数来介绍。

Prod t1 = new Prod();

CaseWhen caseWhen = Func.caseWhen(t1.res_id.eq(1), "1")
        .cashWhen(t1.res_id.eq(2), "2")
        .other("3");

Jql jql = new Jql(){{
    select(caseWhen.alias("xy"));
    from(t1);
    where(t1.res_id.eq(1));
}};

其它函数

concat, nvl, count, sum, lower, upper

Prod t1 = new Prod();

Concat concat = Func.concat(t1.prod_name);
Nvl nvl = Func.nvl(t1.prod_desc, t1.prod_name);
Count count = Func.count(t1.res_id);
Sum sum = Func.sum(t1.res_id);
Lower lower = Func.lower(t1.prod_name);
Upper upper = Func.upper(t1.prod_name);

Jql jql = new Jql(){{
    select(concat.alias("f1"));
    select(nvl.alias("f2"));
    select(count.alias("f3"));
    select(sum.alias("f4"));
    select(lower.alias("f5"));
    select(upper.alias("f6"));
    from(t1);
    where(t1.res_id.eq(1));
}};

嵌套查询

Prod t1 = new Prod();
ProdDetail t2 = new ProdDetail();

Jql jql1 = new Jql(){{
    select(t1.res_id);
    from(t1);
    where(t1.res_id.between(1, 100));
}};

Jql jql2 = new Jql(){{
    select(t2);
    from(t2);
    where(t2.prod_res_id.in(jql1));
    
}};

union、minix、interSect、except

Prod t1 = new Prod();
Jql jql1 = new Jql(){{
    select(t1);
    from(t1);
    where(t1.res_id.lt(100));
}};
Jql jql2 = new Jql(){{
    select(t1);
    from(t1);
    where(t1.res_id.gtEq(100));
}};

// 并集
jql1.union(jql2);
jql1.unionAll(jql2);

// 差集
jql1.minus(jql2);

// 交集
jql1.interSect(jql2);
// 除去
jql1.except(jql2);