您现在的位置是：首页 > 文章详情

🔥easy-query java 最强 ORM 3.1.41

日期：2025-10-20点击：7收藏

背景

随着信创或者其他一系列面向领导编程导致部分使用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

数据库模型

查询用户信息和最早开户的银行卡信息

通过模型我们可以清晰的看到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重新开启一个会话看看他会怎么回答