跳到主要内容

直接查询中间表

被对象模型隐藏的中间表​

让我们回顾一下这段实体接口定义代码

@Entity
public interface Book {

    @ManyToMany
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING", 
        joinColumnName = "BOOK_ID", 
        inverseJoinColumnName = "AUTHOR_ID"
    )
    List<Author> authors();

    ...omit other code...
}

上述代码中，BOOK_AUTHOR_MAPPING表作为中间表被使用。

  • 数据库的BOOK表，Java代码有与之对应的实体接口Book。

  • 数据库的AUTHOR表，Java代码有与之对应的实体接口Author。

  • 但是，数据库中的BOOK_AUTHOR_MAPPING表，在Java代码中没有对应的实体接口。

即，中间表被对象模型隐藏了。

直接查询中间表​

Jimmer提供了一个有趣的功能，即便中间表被隐藏没有对应实体，也可以对其直接查询。

AssociationTable<Book, BookTableEx, Author, AuthorTableEx> association =
    AssociationTable.of(BookTableEx.class, BookTableEx::authors);
    
List<Association<Book, Author>> associations =
    sqlClient
        .createAssociationQuery(association)
        .where(association.source().id().eq(3L))
        .select(association)
        .execute();
associations.forEach(System.out::println);

这里，Java的createAssociationQuery或Kotlin的queries.forList表示基于中间表创建查询，而非基于实体表。

提示

这里的Java代码示范为了兼容Java8，第一行的变量association的类型比较复杂。建议提高Java的版本，采用var关键字。

生成的SQL如下

select 
    tb_1_.BOOK_ID, 
    tb_1_.AUTHOR_ID 
/* highlight-next-line */
from BOOK_AUTHOR_MAPPING as tb_1_
where tb_1_.BOOK_ID = ? /* 3 */

果然，这是一个基于中间表的查询。

最终打印结果如下（原输出是紧凑的，为了方便阅读，这里进行了格式化）:

Association{
    source={
        "id":3
    }, target={
        "id":1
    }
}
Association{
    source={
        "id":3
    }, 
    target={
        "id":2
    }
}

返回数据是一系列Association对象

package org.babyfish.jimmer.sql.association;

public class Association<S, T> {

    public Association(S source, T target) {
        this.source = source;
        this.target = target;
    }

    public S source() {
        return source;
    }

    public T target() {
        return target;
    }
}

Association<S, T>表示从S类型指向T类型关联的中间表实体。中间表实体是伪实体，没有id。它只有两个属性:

  • source: 中间表指向己方的外键所对应的对象(在这个例子中，就是Book对象)。
  • target: 中间表指向对方的外键所对应的对象(在这个例子中，就是Author对象)。
备注

  1. 在这个例子中，并未使用对象抓取器定义Association的对象格式（事实上Association也不支持对象抓取器），因此对象的source和target关联属性仅包含对象id。

  2. Author也有一个从动的多对多关联Author.books, 它是Book.authors的镜像。

    @Entity
    public interface Author {

        @ManyToMany(mappedBy = "authors")
        List<Book> books();

        ...
    }

    基于Author.books也可以创建中间表查询，但是source代表Author，而target代表Book。和当前例子相反。

这个例子中，我们只查询了中间表本身。所以，source和target对象中只有id。

要获取完整的source和target对象，可以表连接，然后利用元组进行返回。

代码如下

AssociationTable<Book, BookTableEx, Author, AuthorTableEx> association =
    AssociationTable.of(BookTableEx.class, BookTableEx::authors);

List<Tuple2<Book, Author>> tuples =
    sqlClient
        .createAssociationQuery(association)
        .where(association.source().id().eq(3L))
        .select(
            association.source(),
            association.target()
        )
        .execute();
tuples.forEach(System.out::println);

生成的SQL如下:

select 

    /* source() */
    tb_1_.BOOK_ID, 
    tb_2_.NAME, 
    tb_2_.EDITION, 
    tb_2_.PRICE, 
    tb_2_.STORE_ID, 

    /* target() */
    tb_1_.AUTHOR_ID, 
    tb_3_.FIRST_NAME, 
    tb_3_.LAST_NAME, 
    tb_3_.GENDER

from BOOK_AUTHOR_MAPPING as tb_1_ 
inner join BOOK as tb_2_ 
    on tb_1_.BOOK_ID = tb_2_.ID 
inner join AUTHOR as tb_3_ 
    on tb_1_.AUTHOR_ID = tb_3_.ID 
where tb_1_.BOOK_ID = ? /* 3 */

最终打印结果如下（原输出是紧凑的，为了方便阅读，这里进行了格式化）:

Tuple2{
    _1={
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51.00,
        "store":{
            "id":1
        }
    }, 
    _2={
        "id":1,
        "firstName":"Alex",
        "lastName":"Banks",
        "gender":"MALE"
    }
}
Tuple2{
    _1={
        "id":3,
        "name":"Learning GraphQL",
        "edition":3,
        "price":51.00,
        "store":{
            "id":1
        }
    }, 
    _2={
        "id":2,
        "firstName":"Eve",
        "lastName":"Procello",
        "gender":"MALE"
    }
}
警告

关联对象Association<S, T>很简单也很特殊，不支持也不需要对象抓取器。

注意，这里仅指Association<S, T>对象本身不支持，其关联属性source和target仍然支持对象抓取器，如：

select(
    table
        .source
        .fetchBy {
            allScalarFields()
            store { 
                allScalarFields()
            }
        },
    table.target
)

和非中间表查询的对比​

读者可能会认为，基于中间表查询的查询存在的价值，是为了让开发人员写出性能更高的查询。

但其实不是这样的。由于幻连接和半连接这两个优化手段的存在，无论是否使用基于中间表的查询，都能达到很好的性能。是否选择使用基于中间表的查询，完全看用户自己喜好。

1. 基于中间表子查询实现一个功能​

之前的代码，我们演示基于中间表的顶级查询；而这个例子演示基于中间表的子查询。

BookTable table = Tables.BOOK_TABLE;
AssociationTable<Book, BookTableEx, Author, AuthorTableEx> association =
    AssociationTable.of(BookTableEx.class, BookTableEx::authors);

List<Book> books = sqlClient
    .createQuery(table)
    .where(
        table.id().in(
            sqlClient
                .createAssociationSubQuery(association)
                .where(
                    association
                        .target() ❶
                        .firstName().eq("Alex")
                )
                .select(
                    association
                        .source() ❷
                        .id()
                )
        )
    )
    .select(table)
    .execute();

其中

  • Java的createAssociationSubQuery和Kotlin的subQueries.forList用于创建一个基于中间表的子查询。该查询用户寻找所有包含firstName为"Alex"的作者的书籍。

  • ❶处association.target是真正的表连接，会生成SQL JOIN连接AUTHOR表进行条件判断。

  • ❷处association.source是由于幻连接，并不会生成SQL join。

最终生成的SQL如下:

select 
    tb_1_.ID, 
    tb_1_.NAME, 
    tb_1_.EDITION, 
    tb_1_.PRICE, 
    tb_1_.STORE_ID 
from BOOK as tb_1_ 
where 
    tb_1_.ID in (
        /* highlight-next-line */
        select 
            tb_2_.BOOK_ID 
        from BOOK_AUTHOR_MAPPING as tb_2_ 
        inner join AUTHOR as tb_3_ 
            on tb_2_.AUTHOR_ID = tb_3_.ID 
        where tb_3_.FIRST_NAME = ?
    )

2. 不基于中间表子查询实现同样的功能​

BookTable book = Tables.BOOK_TABLE;
AuthorTableEx author = TableExes.AUTHOR_TABLE_EX;

List<Book> books = sqlClient
    .createQuery(book)
    .where(
        book.id().in(sqlClient
            .createSubQuery(author)
            .where(author.firstName().eq("Alex"))
            .select(
                author.books().id() ❶
            )
        )
    )
    .select(book)
    .execute();

❶处author.books是半连接，所以仅仅生成从表AUTHOR到中间表BOOK_AUTHOR_MAPPING的SQL JOIN，而不会进一步SQL JOIN到BOOK表。

最终生成的SQL如下:

select 

    tb_1_.ID, 
    tb_1_.NAME, 
    tb_1_.EDITION, 
    tb_1_.PRICE, 
    tb_1_.STORE_ID 

from BOOK as tb_1_ 
where 
    tb_1_.ID in (
        /* highlight-next-line */
        select 
            tb_3_.BOOK_ID 
        from AUTHOR as tb_2_ 
        inner join BOOK_AUTHOR_MAPPING as tb_3_ 
            on tb_2_.ID = tb_3_.AUTHOR_ID 
        where tb_2_.FIRST_NAME = ?
    )

对比这两个SQL，不难发现，它们功能一样，性能一样。

信息

基于中间表的查询，只是为开发人员多提供一种代码书写风格，并不具备不可取代性，用其他手段也可以实现功能和性能形同的查询。