C#转java的最好利器easy-query就是efcore4j sqlsugar4j freesql4j
<h1 id="c转java的最好利器easy-query就是efcore4j-sqlsugar4j-freesql4j">C#转java的最好利器easy-query就是efcore4j sqlsugar4j freesql4j</h1><h2 id="背景">背景</h2>
<p>随着信创或者其他一系列面向领导编程导致部分使用C#的公司转型java，java下<code>EfCore</code>、<code>SqlSugar</code> 、<code>FreeSql</code>的竞品或者替代品就是<code>easy-query</code></p>
<p>本章节我将介绍隐式分区分组在实际中如何使用</p>
<p>并且带来秒杀全世界ORM的独特功能隐式Group又叫GroupJoin可以无缝合并多个相同子查询</p>
<p>转java没有一个好用的orm怎么办，我之前用sqlsugar的现在有没有sqlsugar-java，我之前用efcore的现在是否有efcore-java，我之前是freesql的粉丝转java后有没有freesql-java？这一切的一切都随着easy-query的迭代而变得轻松</p>
<h2 id="介绍">介绍</h2>
<p>文档地址 https://www.easy-query.com/easy-query-doc/</p>
<p>GITHUB地址 https://github.com/dromara/easy-query</p>
<p>GITEE地址 https://gitee.com/dromara/easy-query</p>
<h2 id="数据库模型">数据库模型</h2>
<p><img src="https://img2024.cnblogs.com/blog/1346660/202510/1346660-20251019222149746-567396819.png"></p>
<details>
<summary>点击查看实体代码</summary>
<pre><code>
@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;
}
</code></pre>
</details>
<h2 id="案例">案例</h2>
<h3 id="查询用户信息和最早开户的银行卡信息">查询用户信息和最早开户的银行卡信息</h3>
<p>通过模型我们可以清晰的看到SysUser和BankCard是一对多的关系，eq如何实现这种一对多的数据返回呢？</p>
<ul>
<li>使用临时对象返回</li>
</ul>
<pre><code class="language-java">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();
</code></pre>
<p>生成的sql</p>
<pre><code class="language-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`
</code></pre>
<h3 id="查询用户至少有5张储蓄卡且没有信用卡的用户信息和对应的第4张储蓄卡信息">查询用户至少有5张储蓄卡且没有信用卡的用户信息和对应的第4张储蓄卡信息</h3>
<p>分解条件</p>
<ul>
<li>要先找出用户至少有5张储蓄卡</li>
<li>要找出用户没有信用卡</li>
<li>要返回用户信息</li>
<li>额外返回用户的第4张储蓄卡信息</li>
</ul>
<pre><code class="language-java">//新创建一个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();
</code></pre>
<p>最终生成的sql</p>
<pre><code class="language-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
)
</code></pre>
<p>什么你看不懂sql？没关系直接丢给ai让他帮我们看看</p>
<p><img src="https://img2024.cnblogs.com/blog/1346660/202510/1346660-20251019224332508-501473174.png"></p>
<p>看来ai还是很懂sql的嘛</p>
<p>聪明的肯定又发现了盲点，你这边生成了两个子查询sql，导致整体sql性能偏弱是否有好的解决方案呢</p>
<p>隐式group，eq提供了子查询合并我们又叫他groupJoin或者隐式group，那么应该怎么做呢，基本上什么代码都不需要动，只需要加一行配置即可</p>
<pre><code class="language-java">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();
</code></pre>
<p>再来看看我们生成的sql</p>
<pre><code class="language-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
</code></pre>
<p>好的我们再把sql交给ai重新开启一个会话看看他会怎么回答<br>
<img src="https://img2024.cnblogs.com/blog/1346660/202510/1346660-20251019224921524-139799832.png"></p>
<p>很好ai再次证明了easy-query的准确性，且easy-query再次证明了他在OLAP中秒杀所有其他ORM</p>
<h2 id="最后的最后我非常感谢您能看到这边我相信eq绝对是你不二的orm选择">最后的最后我非常感谢您能看到这边我相信eq绝对是你不二的orm选择</h2>
<p>给c#转java的所有用户一个最好的抉择</p>
<p>框架地址 https://github.com/dromara/easy-query<br>
文档地址 https://www.easy-query.com/easy-query-doc/</p><br><br>
来源：https://www.cnblogs.com/xuejiaming/p/19151573
頁:
[1]