跳至主要內容

where子查询

xuejmnet2025/4/19大约 6 分钟

where子查询区别于select子查询生成的必须是执行片段而不是类型片段,如果在where内部使用类型片段那么该片段将不会生效

提供支持子查询包括exists、not exists、in、not in,并且有手动和自动两种方式

以下是错误的演示❌


List<SysUser> cards = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {
            //必须添加any或者none相关的执行函数让其变成执行片段
            //或者使用count让其生成类型片段后进行比较操作
            user.bankCards().where(card -> card.type().eq("储蓄卡"));
        }).toList();
关系图

案例1

筛选用户条件是用户拥有至少一张储蓄卡

隐式子查询

List<SysUser> users = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {
            user.bankCards().any(card -> card.type().eq("储蓄卡"));
        }).toList();



    SELECT
        t.`id`,
        t.`name`,
        t.`phone`,
        t.`age`,
        t.`create_time` 
    FROM
        `t_sys_user` t 
    WHERE
        EXISTS (SELECT
            1 FROM `t_bank_card` t1 
        WHERE
            t1.`uid` = t.`id` 
            AND t1.`type` = '储蓄卡' 
        LIMIT
            1)

案例2

查询用户拥有两张储蓄卡的用户列表

隐式子查询

List<SysUser> users = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {

            user.bankCards().where(card->card.type().eq("储蓄卡")).count().eq(2L);
            
        }).toList();


    SELECT
        t.`id`,
        t.`name`,
        t.`phone`,
        t.`age`,
        t.`create_time` 
    FROM
        `t_sys_user` t 
    WHERE
        (
            SELECT
                COUNT(*) 
            FROM
                `t_bank_card` t1 
            WHERE
                t1.`uid` = t.`id` 
                AND t1.`type` = '储蓄卡'
        ) = 2
``

说明!!!

手动子查询的创建方式有两种一种是eq实例创建一个queryable一种是通过表达式内部的expression().subQeuryable()，在使用时我们应该尽可能的使用上下文来创建子查询表达式,区别就是eq实例创建的表达式必须子查询作为左侧的表而不是外部表作为左侧表而expression().subQeuryable()创建的子查询则不需要考虑这个问题

数据库对象模型

企业表
@Table("t_company")
@Data
@EntityProxy
@FieldNameConstants
@EasyAlias("com")
public class Company implements ProxyEntityAvailable<Company , CompanyProxy> {
    @Column(primaryKey = true)
    private String id;
    private String name;
    private LocalDateTime createTime;

    @Navigate(value = RelationTypeEnum.OneToMany,targetProperty = SysUser.Fields.companyId)
    private List<SysUser> users;
}

查询企业存在用户成年的

List<Company> list = easyEntityQuery.queryable(Company.class)
        .where(com -> {
                com.users().where(u -> {
                        u.age().gt(18);
                }).any();
        }).toList();
//当上述子查询有且只有一个条件比如age>18有且只有一个条件时,并且是用来断言当前条件的,那么可以使用flatElement来展开如下写法和上述写法一样

List<Company> list = easyEntityQuery.queryable(Company.class)
        .where(com -> {
                com.users().flatElement().age().gt(18);
        }).toList();

SELECT
    t.`id`,
    t.`name`,
    t.`create_time` 
FROM
    `t_company` t 
WHERE
    EXISTS (
        SELECT
            1 
        FROM
            `t_user` t1 
        WHERE
            t1.`company_id` = t.`id` 
            AND t1.`age` > 18 LIMIT 1
    )

查询企业存条件是企业所有用户平均年龄大于18

List<Company> list = easyEntityQuery.queryable(Company.class)
        .where(com -> {
                com.users().avg(u->u.age()).gt(BigDecimal.valueOf(18));
        }).toList();



SELECT
    t.`id`,
    t.`name`,
    t.`create_time` 
FROM
    `t_company` t 
WHERE
    IFNULL((SELECT
        AVG(t1.`age`) 
    FROM
        `t_user` t1 
    WHERE
        t1.`company_id` = t.`id`),0) > '18'

EXISTS

对象模式
//SELECT * FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
EntityQueryable<BlogEntityProxy, BlogEntity> subQueryable = easyEntityQuery.queryable(BlogEntity.class)
        .where(o -> o.id().eq("1" ));

List<Topic> list = easyEntityQuery.queryable(Topic.class)
        .where(o -> o.expression().exists(() -> {
                return subQueryable.where(q -> q.id().eq(o.id()));
        })).toList();


==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE EXISTS (SELECT 1 FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ? AND t1.`id` = t.`id`)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 4(ms)
<== Total: 1

NOT EXISTS

对象模式
//SELECT * FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
        EntityQueryable<BlogEntityProxy, BlogEntity> subQueryable = easyEntityQuery.queryable(BlogEntity.class)
                .where(o -> o.id().eq("1" ));

        List<Topic> list = easyEntityQuery.queryable(Topic.class)
                .where(o -> o.expression().notExists(() -> {
                    return subQueryable.where(q -> q.id().eq(o.id()));
                })).toList();

==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE NOT EXISTS (SELECT 1 FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ? AND t1.`id` = t.`id`)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 3(ms)
<== Total: 100

IN

对象模式
//SELECT t1.`id` FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
        EntityQueryable<StringProxy, String> idQueryable = easyEntityQuery.queryable(BlogEntity.class)
                .where(o -> o.id().eq("123" ))
                .selectColumn(o -> o.id());//如果子查询in string那么就需要select string，如果integer那么select要integer 两边需要一致

        List<Topic> list = easyEntityQuery.queryable(Topic.class)
                .where(o -> o.id().in(idQueryable)).toList();


==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE t.`id` IN (SELECT t1.`id` FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?)
==> Parameters: false(Boolean),123(String)
<== Time Elapsed: 4(ms)
<== Total: 0

NOT IN

对象模式
//SELECT t1.`id` FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
        EntityQueryable<StringProxy, String> idQueryable = easyEntityQuery.queryable(BlogEntity.class)
                .where(o -> o.id().eq("123" ))
                .selectColumn(o -> o.id());//如果子查询in string那么就需要select string，如果integer那么select要integer 两边需要一致

        List<Topic> list = easyEntityQuery.queryable(Topic.class)
                .where(o -> o.id().notIn(idQueryable)).toList();


==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE t.`id` NOT IN (SELECT t1.`id` FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 4(ms)
<== Total: 100

贡献者

xuejiaming
默认页脚
Copyright © 2025 xuejmnet