露阶辟较C#转java的最好利器easy-query就是efcore4j sqlsugar4j freesql4j背景随着信创或者其他一系列面向领导编程导致部分使用C#的公司转型javajava下EfCore、SqlSugar 、FreeSql的竞品或者替代品就是easy-query本章节我将介绍隐式分区分组在实际中如何使用并且带来秒杀全世界ORM的独特功能隐式Group又叫GroupJoin可以无缝合并多个相同子查询转java没有一个好用的orm怎么办我之前用sqlsugar的现在有没有sqlsugar-java我之前用efcore的现在是否有efcore-java我之前是freesql的粉丝转java后有没有freesql-java这一切的一切都随着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();生成的sqlSELECT t.id AS value1, t.name AS value2, t3.code AS value3, t3.type AS value4, t3.open_time AS value5, t4.name AS value6FROM t_sys_user tLEFT 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) t3ON t3.uid t.idAND t3.__row__ 1INNER JOIN t_bank t4 ON t4.id t3.bank_id查询用户至少有5张储蓄卡且没有信用卡的用户信息和对应的第4张储蓄卡信息分解条件要先找出用户至少有5张储蓄卡要找出用户没有信用卡要返回用户信息额外返回用户的第4张储蓄卡信息//新创建一个dto用来接收而不是使用临时对象DataEntityProxypublic 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();最终生成的sqlSELECT 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_nameFROM t_sys_user tLEFT 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) t5ON t5.uid t.idAND t5.__row__ 4INNER JOIN t_bank t6 ON t6.id t5.bank_idWHERE (SELECT COUNT(*)FROM t_bank_card t1WHERE t1.uid t.idAND t1.type 储蓄卡) 4AND NOT EXISTS (SELECT 1FROM t_bank_card t2WHERE t2.uid t.idAND t2.type 信用卡LIMIT 1)什么你看不懂sql没关系直接丢给ai让他帮我们看看image看来ai还是很懂sql的嘛聪明的肯定又发现了盲点你这边生成了两个子查询sql导致整体sql性能偏弱是否有好的解决方案呢隐式groupeq提供了子查询合并我们又叫他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();再来看看我们生成的sqlSELECT 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_nameFROM t_sys_user tLEFT JOIN (SELECT t1.uid AS uid, COUNT(CASEWHEN t1.type 储蓄卡 THEN 1ELSE NULLEND) AS __count2__, COUNT(CASEWHEN t1.type 信用卡 THEN 1ELSE NULLEND) 0 AS __none3__FROM t_bank_card t1GROUP BY t1.uid) t2ON t2.uid t.idLEFT 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) t5ON t5.uid t.idAND t5.__row__ 4INNER JOIN t_bank t6 ON t6.id t5.bank_idWHERE IFNULL(t2.__count2__, 0) 4AND IFNULL(t2.__none3__, true) true好的我们再把sql交给ai重新开启一个会话看看他会怎么回答image很好ai再次证明了easy-query的准确性且easy-query再次证明了他在OLAP中秒杀所有其他ORM最后的最后我非常感谢您能看到这边我相信eq绝对是你不二的orm选择