企业官网建设流程全解析

数据库模型

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();

生成的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让他帮我们看看

image

看来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重新开启一个会话看看他会怎么回答

image

很好ai再次证明了easy-query的准确性，且easy-query再次证明了他在OLAP中秒杀所有其他ORM

最后的最后我非常感谢您能看到这边我相信eq绝对是你不二的orm选择