随着信创或者其他一系列面向领导编程导致部分使用C#的公司转型javajava下EfCore、SqlSugar、FreeSql的竞品或者替代品就是easy-query本章节我将介绍隐式分区分组在实际中如何使用并且带来秒杀全世界ORM的独特功能隐式Group又叫GroupJoin可以无缝合并多个相同子查询转java没有一个好用的orm怎么办我之前用sqlsugar的现在有没有sqlsugar-java我之前用efcore的现在是否有efcore-java我之前是freesql的粉丝转java后有没有freesql-java这一切的一切都随着easy-query的迭代而变得轻松介绍文档地址 项目主页 | 文档演示GITHUB地址 GitHub - dromara/easy-query: java/kotlin high performance lightweight solution for jdbc query,support oltp and olap query,一款java下面支持强类型、轻量级、高性能的ORM,致力于解决jdbc查询,拥有对象模型筛选、隐式子查询、隐式join · GitHubGITEE地址 https://gitee.com/dromara/easy-query数据库模型点击查看实体代码案例查询用户信息和最早开户的银行卡信息通过模型我们可以清晰的看到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 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; } ListUserDTO2 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_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性能偏弱是否有好的解决方案呢隐式groupeq提供了子查询合并我们又叫他groupJoin或者隐式group那么应该怎么做呢基本上什么代码都不需要动只需要加一行配置即可ListUserDTO2 list easyEntityQuery.queryable(SysUser.class) //增加这行配置 .configure(s - s.getBehavior().add(EasyBehavi
