跳到主要内容

超级QBE查询

Super QBE是一个非常强大的功能，利用DTO语言生成复杂查询的参数类型，并自动实现查询逻辑。

创建文件​

在任何可以访问实体类型的项目中，创建src/main/dto目录，在这个目录下创建文件Book.dto。

对于Java项目而言，如果当前项目并非定义实体类型的项目，需要在为当前项目中任何一个类添加@EnableDtoGeneration注解。

在文件头部添加如下代码

export com.yourcompany.yourproject.model.Book
    -> package com.yourcompany.yourproject.dto

以上这些步骤，以及如何编译DTO文件，在DTO语言#2. 创建文件中有详细的讨论，本文不作重复性阐述。

定义Specification类型​

...省略export语句...

specification BookSpecification {

}

编译后的代码​

编译后，将会生成这样的Java/Kotlin代码

BookSpecification.java
package com.yourcompany.yourproject.dto;

import org.babyfish.jimmer.sql.ast.query.specification.JSpecification;
import org.babyfish.jimmer.sql.ast.query.specification.SpecificationArgs;
...省略其他import语句...

public class BookSpecification implements JSpecification<Book, BookTable> {

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {}
}
信息

applyTo是specification编译后生成的代码中的特有方法，按照当前对象的信息动态地为Jimmer查询添加where条件。

此方法无需用户调用 (被Jimmer内部行为调用) ，用户也无需关心其内部代码实现。这里只需要知道此方法有什么用即可。

在后续的讨论中，我们会陆续为DTO文件中的BookSpecification添加属性。

相应地，对于自动生成的BookSpecification类而言，一方面属性会同步增加，另外一方面，applyTo方法中的代码也会变多。

用法​

  • 在查询中使用

    public List<Book> find(
        Specification<Book> specification ❶
    ) {
        
        BookTable table = Tables.BOOK_TABLE;
        
        return sqlClient
            .createQuery(table)
            .where(specification) ❷
            .select(table)
            .execute();
    }

    • ❶ org.babyfish.jimmer.Specification<Book>类型的参数，用于生产各种动态SQL条件。

    • ❷ 无论specification格式简单还是复杂，只需一个简单的where语句就可以使用。

  • 在Spring Data Repository中使用

    Jimmer整合了Spring Data，所以能定义Spring Data Repository，请参考SpringData风格以了解更多。

    Jimmer的Spring Data Repository有两种查询风格，抽象方法和default方法。default方法使用specification的代码和上面雷同， 所以，我们看看抽象查询方法使用specification的例子

    public interface BookRepository : JRepository<Book, Long> {

        public List<Book> find(
            Specification<Book> specification
        )
    }

属性映射​

映射属性​

...省略export语句...

specification BookSpecification {
    name
}

这样，就将实体属性映射到了DTO中，生成的代码为

BookSpecification.java
public class BookSpecification implements JSpecification<Book, BookTable> {

    @Nullable
    private String name;

    ...省略getters和setters...

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {
        ...略...
    }
}

可空性​

我们发现，在生成的代码中name字段时可以为null的，这是specification的特殊性所在。

提示

specification用作查询参数，为了支持动态查询，默认情况下，所有属性都是可以为null的。除非显式地使用!规定属性不能为null*(请参见DTO语言#7. 可空性)*。

用法​

  • 让BookSpecification的name字段为null

    BookSpecification specification = new BookSpecification();
    List<Book> books = bookRepository.find(specification);

    由于specification.name为null，生成的SQL不会带任何where条件

    生成的SQL如下

    select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
    from BOOK tb_1_
    信息

    这种不指定specification相关属性的查询，必然导致查询没有任何条件。

    这种例子本文仅仅示范一次。

  • 让BookSpecification的name字段不为null

    BookSpecification specification = new BookSpecification();
    specification.setName("GraphQL in Action");
    List<Book> books = bookRepository.find(specification);

    生成的SQL如下

    select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
    from BOOK tb_1_
    /* highlight-next-line */
    where tb_1_.NAME = ? /* GraphQL in Action */

QBE函数​

初识QBE函数​

上面的代码中，当指定了specification.name后，生成的where条件是等于。

等于不一定是我们需要的，可以为映射属性施加QBE函数，改变运算符，以like为例

...省略export语句...

specification BookSpecification {
    like(name)
}

like函数不会影响生成的BookSpecification类的属性，但是会影响其applyTo方法，该方法的内部实现是where条件的添加逻辑，无需用户关心。

执行

BookSpecification specification = new BookSpecification();
specification.setName("GraphQL");
List<Book> books = bookRepository.find(specification);

生成的SQL如下

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_
/* highlight-next-line */
where tb_1_.NAME like ? /* %GraphQL% */

like函数的选项​

like是所有QBE函数中比较特殊的一个，支持3个选项

  • i: 大小写不敏感

  • ^: 精确匹配开头 (Jimmer不会自动在参数值前面加%)

  • $: 精确匹配结尾 (Jimmer不会自动在参数值后面加%)

如果需要选项，可以在like后面加/，再加上需要的选项。比如：like/i、like/^、like/$、like/i^、like/i$和like/i^$。

警告

虽然i、^和$都是可选的，但是彼此先后顺序是固定的。

让我们来试试大小写不敏感的like，修改DTO代码如下

...省略export语句...

specification BookSpecification {
    like/i(name)
}

再次执行上面的代码，执行如下SQL

  • 不支持ilike的数据库

    select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
    from BOOK tb_1_
    /* highlight-next-line */
    where lower(tb_1_.NAME) like ? /* %graphql% */

  • 支持ilike的数据库

    select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
    from BOOK tb_1_
    /* highlight-next-line */
    where tb_1_.NAME ilike ? /* %graphql% */

所有QBE函数​

事实上，除了like外，speciation支持大量的QBE函数，涵盖了常见的SQL判断，如下表

QBE函数原实体属性类型 (或要求)生成的DTO类属性的类型备注
eq任何非关联属性原类型等于，和不使用任何QBE函数等价
ne任何非关联属性原类型不等于
gt任何非关联属性原类型大于
ge任何非关联属性原类型大于或等于
lt任何非关联属性原类型小于
le任何非关联属性原类型小于或等于
likeStringString模糊匹配
notLikeStringString模糊不匹配
null任何属性boolean如果DTO属性为true，is null判断
notNull任何属性boolean如果DTO属性为true，is not null判断
valueIn任何非关联属性List<原类型>in(...)
valueNotIn任何非关联属性List<原类型>not in(...)
associatedIdEq任何关联属性关联实体的id属性的类型关联id = ?。注意，和id(DTO语言固有函数) 等价
associatedIdNe任何关联属性关联实体的id属性的类型关联id <> ?
associatedIdIn任何关联属性List<关联实体的id属性的类型>关联id in(...)
associatedIdNotIn任何关联属性List<关联实体的id属性的类型>关联id not in(...)

综合示范​

修改DTO代码

...省略export语句...

specification BookSpecification {
    like/i(name)
    ge(price)
    le(price)
}

ge(price)会指定默认的别名minPrice，le(price)会指定默认的别名maxPrice，因此，上述代码也可以写为

...省略export语句...

specification BookSpecification {
    like/i(name)
    ge(price) as minPrice
    le(price) as maxPrice
}

由此可见，某些QBE函数自带默认alias的功能。

假设原属性名称为Prop，所有QBE函数的默认alias行为如下

QBE函数默认alias
eq (或不指定QBE函数)prop
ne不支持，不指定alias会导致编译错
gtminPropExclusive
geminProp
ltmaxPropExclusive
lemaxProp
likeprop
notLike不支持，不指定alias会导致编译错
valueIn不支持，不指定alias会导致编译错
valueNotIn不支持，不指定alias会导致编译错
associatedIdEq或id如果关联是引用 (非集合)，propId；否则，编译报错
associatedIdNe如果关联是引用 (非集合)，excludedPropId；否则，编译报错
associatedIdIn如果关联是引用 (非集合)，propIds；否则，编译报错
associatedIdNotNull如果关联是引用 (非集合)，excludedPropIds；否则，编译报错

编译后生成的代码为

BookSpecification.java
public class BookSpecification implements JSpecification<Book, BookTable> {

    @Nullable
    private String name;

    @Nullable
    private BigDecimal minPrice;

    @Nullable
    private BigDecimal maxPrice;

    ...省略getters和setters...

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {
        ...略...
    }
}

执行

BookSpecification specification = new BookSpecification();
specification.setName("GraphQL");
specification.setMinPrice(new BigDecimal(40));
specification.setMaxPrice(new BigDecimal(40));
List<Book> books = bookRepository.find(specification);

生成的SQL如下

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_
where
        lower(tb_1_.NAME) like ? /* %graphql% */
    and
        tb_1_.PRICE >= ? /* 40 */
    and
        tb_1_.PRICE <= ? /* 60 */
order by
    tb_1_.NAME asc,
    tb_1_.EDITION desc

关联对象​

之前例子，所有过滤规则都是针对当前表字段的。现在，让我们对关联对象进行过滤。修改DTO文件如下

...省略export语句...

specification BookSpecification {
    like/i(name)
    ge(price)
    le(price)
    flat(store) { ❶ 
        like/i(name) as storeName
    }
    flat(authors) { ❷ 
        like/i(firstName) as authorName
    }
}

我们发现，上面的代码使用了flat函数。flat函数在DTO语言#10.4-flat函数中有详细讨论，本文不作重复性阐述。

信息

由于specification作为复杂查询的参数，很有可能HTTP GET参数。采用flat函数消除关联，生成平坦的DTO对象，更有利于基于Spring MVC开发HTTP GET API。

  • ❶ Book.store是引用关联

  • ❷ Book.authors是集合关联

编译后生成的代码为

BookSpecification.java
public class BookSpecification implements JSpecification<Book, BookTable> {

    @Nullable
    private String name;

    @Nullable
    private BigDecimal minPrice;

    @Nullable
    private BigDecimal maxPrice;

    @Nullable
    private String storeName;

    @Nullable
    private String authorName;

    ...省略getters和setters...

    @Override
    public void applyTo(SpecificationArgs<Book, BookTable> args) {
        ...略...
    }
}

执行

BookSpecification specification = new BookSpecification();
specification.setStoreName("MANNING");
specification.setAuthorName("a");
List<Book> books = bookRepository.find(specification);

生成的SQL如下

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_
/* highlight-next-line */
inner join BOOK_STORE tb_2_ /* ❶ */
    on tb_1_.STORE_ID = tb_2_.ID
where
        tb_2_.NAME ilike ? /* %manning% */
    and
        /* highlight-next-line */
        exists( /* ❷ */
            select
                1
            from AUTHOR tb_3_
            inner join BOOK_AUTHOR_MAPPING tb_4_
                on tb_3_.ID = tb_4_.AUTHOR_ID
            where
                    tb_1_.ID = tb_4_.BOOK_ID
                and
                    tb_3_.FIRST_NAME ilike ? /* %a% */
                    
        )

  • ❶ Book.store是引用关联，不会破坏分页安全性。

    因此，仅使用动态JOIN即可应用DTO对象storeName属性所表示的过滤条件。

  • ❷ Book.authors是集合关联，会破坏分页安全性。

    因此，不能使用动态JOIN，必须使用子查询才能应用DTO对象authorName属性所表示的过滤条件。

逻辑或​

之前的例子中，Jimmer会按照DTO对象的每个属性来生成若干个where条件，这些条件之间的关系是逻辑与，但能否支持逻辑或呢？

另外，实体类型Author除了firstName属性外还有lastName，上一个例子中的authorName只映射了firstName显得不太合理，有没有更好的办法？

以上两个问题是同一个问题。为解决这个问题，Super QBE支持逻辑或，也称呼多属性映射。

修改DTO代码，如下

...省略export语句...

specification BookSpecification {
    like/i(name)
    ge(price)
    le(price)
    flat(store) {
        like/i(name) as storeName
    }
    flat(authors) {
        like/i(firstName, lastName) as authorName
    }
}

这里，DTO属性authorName映射了两个属性，firstName和lastName，这就叫做多属性映射。

  • 多属性映射只能在specification中使用，无法在DTO语言所描述的其他类型中使用。

  • 被QBE函数映射的多个属性的类型必须完全一致 (但可空性允许不同)。例如，这里的firstName和lastName都是String类型`。

  • 多属性映射的DTO属性必须通过as指定别名，否则会导致编译错。

  • 并非所有QBE函数都支持多属性映射，支持多属性映射的QBE函数如下：

    • eq
    • ne
    • null
    • notNull
    • valueIn
    • associatedIdEq
    • associatedIdIn

    盲目地让所有QBE函数都支持多属性映射会带来理解歧义。所以，才会有此限制。

重复执行上个例子中的查询，生成的SQL如下

select tb_1_.ID, tb_1_.NAME, tb_1_.EDITION, tb_1_.PRICE, tb_1_.STORE_ID
from BOOK tb_1_
inner join BOOK_STORE tb_2_
    on tb_1_.STORE_ID = tb_2_.ID
where
        tb_2_.NAME ilike ? /* %manning% */
    and
        exists(
            select
                1
            from AUTHOR tb_3_
            inner join BOOK_AUTHOR_MAPPING tb_4_
                on tb_3_.ID = tb_4_.AUTHOR_ID
            where
                    tb_1_.ID = tb_4_.BOOK_ID
                and
                    (
                        tb_3_.FIRST_NAME ilike ? /* %a% */
                    /* highlight-next-line */
                    or
                        tb_3_.LAST_NAME ilike ? /* %a% */
                    )
        )

不难发现，多属性映射就是逻辑或。