C#转java的最好利器easy-query就是efcore4j sqlsugar4j freesql4j

背景

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

数据库模型

点击查看实体代码

@Table("t_bank")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("bank")
public class SysBank implements ProxyEntityAvailable<SysBank, SysBankProxy> {
    @Column(primaryKey = true)
    private String id;
    /**
     * 银行名称
     */
    private String name;
    /**
     * 成立时间
     */
    private LocalDateTime createTime;

    /**
     * 拥有的银行卡
     */
    @Navigate(value = RelationTypeEnum.OneToMany,
            selfProperty = {"id"},
            targetProperty = {"bankId"})
    private List<SysBankCard> bankCards;


    @Navigate(value = RelationTypeEnum.ManyToMany,
            selfProperty = {SysBank.Fields.id},
            selfMappingProperty = {SysBankCard.Fields.bankId},
            mappingClass = SysBankCard.class, targetProperty = {SysUser.Fields.id}, targetMappingProperty = {SysBankCard.Fields.uid})
    private List<SysUser> users;
}


@Table("t_bank_card")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("bank_card")
public class SysBankCard implements ProxyEntityAvailable<SysBankCard, SysBankCardProxy>, Serializable {
    @Column(primaryKey = true)
    private String id;
    private String uid;
    /**
     * 银行卡号
     */
    private String code;
    /**
     * 银行卡类型借记卡 储蓄卡
     */
    private String type;
    /**
     * 所属银行
     */
    private String bankId;
    /**
     * 用户开户时间
     */
    private LocalDateTime openTime;

    /**
     * 所属银行
     */
    @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {"bankId"}, targetProperty = {"id"}, required = true)
    @ForeignKey
    private SysBank bank;

    /**
     * 所属用户
     */
    @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {"uid"}, targetProperty = {"id"})
    private SysUser user;
}


@Table("t_sys_user")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("user")
public class SysUser implements ProxyEntityAvailable<SysUser, SysUserProxy> {
    @Column(primaryKey = true)
    private String id;
    private String name;
    private String phone;
    private Integer age;
    private LocalDateTime createTime;
    /**
     * 用户拥有的银行卡数
     */
    @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {"id"}, targetProperty = {"uid"}, partitionOrder = PartitionOrderEnum.IGNORE)
    private List<SysBankCard> bankCards;


    @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {"id"}, targetProperty = {"uid"})
    private List<SysUserBook> userBooks;

}


@Table("t_sys_user_book")
@EntityProxy
@Data
@FieldNameConstants
@EasyAlias("user_book")
public class SysUserBook implements ProxyEntityAvailable<SysUserBook , SysUserBookProxy> {
    private String id;
    private String name;
    private String uid;
    private BigDecimal price;
}

案例

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

通过模型我们可以清晰的看到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<UserDTO2> 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<UserDTO2> 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/