背景
随着信创或者其他一系列面向领导编程导致部分使用C#的公司转型java，java下EfCore、SqlSugar 、FreeSql的竞品或者替代品就是easy-query
本章节我将介绍隐式分区分组在实际中如何使用
并且带来秒杀全世界ORM的独特功能隐式Group又叫GroupJoin可以无缝合并多个相同子查询
转java没有一个好用的orm怎么办，我之前用sqlsugar的现在有没有sqlsugar-java，我之前用efcore的现在是否有efcore-java，我之前是freesql的粉丝转java后有没有freesql-java？这一切的一切都随着easy-query的迭代而变得轻松
介绍
文档地址 https://www.easy-query.com/easy-query-doc/
GITHUB地址 https://github.com/dromara/easy-query
GITEE地址 https://gitee.com/dromara/easy-query
数据库模型
![image]()
![]()
查询用户信息和最早开户的银行卡信息
通过模型我们可以清晰的看到SysUser和BankCard是一对多的关系，eq如何实现这种一对多的数据返回呢？
var list = easyEntityQuery.queryable(SysUser.class)
.select(user -> {
//定义最早开户的银行卡
SysBankCardProxy firstBankCard = user.bankCards().orderBy(bankCard -> bankCard.openTime().asc()).first();
return Select.DRAFT.of(
user.id(),
user.name(),
firstBankCard.code(),
firstBankCard.type(),
firstBankCard.openTime(),
firstBankCard.bank().name()
);
}).toList();
生成的sql
SELECT t.`id` AS `value1`, t.`name` AS `value2`, t3.`code` AS `value3`, t3.`type` AS `value4`, t3.`open_time` AS `value5`
, t4.`name` AS `value6`
FROM `t_sys_user` t
LEFT JOIN (
SELECT t1.`id`, t1.`uid`, t1.`code`, t1.`type`, t1.`bank_id`
, t1.`open_time`, ROW_NUMBER() OVER (PARTITION BY t1.`uid` ORDER BY t1.`open_time` ASC) AS `__row__`
FROM `t_bank_card` t1
) t3
ON t3.`uid` = t.`id`
AND t3.`__row__` = 1
INNER JOIN `t_bank` t4 ON t4.`id` = t3.`bank_id`
查询用户至少有5张储蓄卡且没有信用卡的用户信息和对应的第4张储蓄卡信息
分解条件
- 要先找出用户至少有5张储蓄卡
- 要找出用户没有信用卡
- 要返回用户信息
- 额外返回用户的第4张储蓄卡信息
//新创建一个dto用来接收而不是使用临时对象
@Data
@EntityProxy
public class UserDTO2 {
private String id;
private String name;
private String thirdCardType;
private String thirdCardCode;
private String thirdCardBankName;
}
List list = easyEntityQuery.queryable(SysUser.class)
.where(user -> {
//用户至少有三张储蓄卡
user.bankCards().where(c -> c.type().eq("储蓄卡")).count().gt(4L);
//用户没有信用卡
user.bankCards().where(c -> c.type().eq("信用卡")).none();
})
.select(user -> {
SysBankCardProxy thirdCard = user.bankCards().orderBy(bankCard -> bankCard.openTime().asc()).element(3);
return new UserDTO2Proxy()
.id().set(user.id())
.name().set(user.name())
.thirdCardType().set(thirdCard.type())
.thirdCardCode().set(thirdCard.code())
.thirdCardBankName().set(thirdCard.bank().name());
}).toList();
最终生成的sql
SELECT t.`id` AS `id`, t.`name` AS `name`, t5.`type` AS `third_card_type`, t5.`code` AS `third_card_code`, t6.`name` AS `third_card_bank_name`
FROM `t_sys_user` t
LEFT JOIN (
SELECT t3.`id`, t3.`uid`, t3.`code`, t3.`type`, t3.`bank_id`
, t3.`open_time`, ROW_NUMBER() OVER (PARTITION BY t3.`uid` ORDER BY t3.`open_time` ASC) AS `__row__`
FROM `t_bank_card` t3
) t5
ON t5.`uid` = t.`id`
AND t5.`__row__` = 4
INNER JOIN `t_bank` t6 ON t6.`id` = t5.`bank_id`
WHERE (
SELECT COUNT(*)
FROM `t_bank_card` t1
WHERE t1.`uid` = t.`id`
AND t1.`type` = '储蓄卡'
) > 4
AND NOT EXISTS (
SELECT 1
FROM `t_bank_card` t2
WHERE t2.`uid` = t.`id`
AND t2.`type` = '信用卡'
LIMIT 1
)
什么你看不懂sql？没关系直接丢给ai让他帮我们看看
![]()
看来ai还是很懂sql的嘛
聪明的肯定又发现了盲点，你这边生成了两个子查询sql，导致整体sql性能偏弱是否有好的解决方案呢
隐式group，eq提供了子查询合并我们又叫他groupJoin或者隐式group，那么应该怎么做呢，基本上什么代码都不需要动，只需要加一行配置即可
List list = easyEntityQuery.queryable(SysUser.class)
//增加这行配置
.configure(s -> s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN))
.where(user -> {
//用户至少有三张储蓄卡
user.bankCards().where(c -> c.type().eq("储蓄卡")).count().gt(4L);
//用户没有信用卡
user.bankCards().where(c -> c.type().eq("信用卡")).none();
})
.select(user -> {
SysBankCardProxy thirdCard = user.bankCards().orderBy(bankCard -> bankCard.openTime().asc()).element(3);
return new UserDTO2Proxy()
.id().set(user.id())
.name().set(user.name())
.thirdCardType().set(thirdCard.type())
.thirdCardCode().set(thirdCard.code())
.thirdCardBankName().set(thirdCard.bank().name());
}).toList();
再来看看我们生成的sql
SELECT t.`id` AS `id`, t.`name` AS `name`, t5.`type` AS `third_card_type`, t5.`code` AS `third_card_code`, t6.`name` AS `third_card_bank_name`
FROM `t_sys_user` t
LEFT JOIN (
SELECT t1.`uid` AS `uid`
, COUNT(CASE
WHEN t1.`type` = '储蓄卡' THEN 1
ELSE NULL
END) AS `__count2__`
, COUNT(CASE
WHEN t1.`type` = '信用卡' THEN 1
ELSE NULL
END) <= 0 AS `__none3__`
FROM `t_bank_card` t1
GROUP BY t1.`uid`
) t2
ON t2.`uid` = t.`id`
LEFT JOIN (
SELECT t3.`id`, t3.`uid`, t3.`code`, t3.`type`, t3.`bank_id`
, t3.`open_time`, ROW_NUMBER() OVER (PARTITION BY t3.`uid` ORDER BY t3.`open_time` ASC) AS `__row__`
FROM `t_bank_card` t3
) t5
ON t5.`uid` = t.`id`
AND t5.`__row__` = 4
INNER JOIN `t_bank` t6 ON t6.`id` = t5.`bank_id`
WHERE IFNULL(t2.`__count2__`, 0) > 4
AND IFNULL(t2.`__none3__`, true) = true
好的我们再把sql交给ai重新开启一个会话看看他会怎么回答
![]()
很好ai再次证明了easy-query的准确性，且easy-query再次证明了他在OLAP中秒杀所有其他ORM
最后的最后我非常感谢您能看到这边我相信eq绝对是你不二的orm选择
给c#转java的所有用户一个最好的抉择
框架地址 https://github.com/dromara/easy-query 文档地址 https://www.easy-query.com/easy-query-doc/
