跳到主要内容

中间表过滤器

两种场景​

Jimmer支持中间表过滤器，有两种场景

1. 双边实体类型相同的多个关联​

@Entity
public interface Shop {

    @Id
    long id();

    String name();

    @ManyToMany
    @JoinTable(
        name = "SHOP_CUSTOMER_MAPPING",
        joinColumnName = "SHOP_ID",
        inverseJoinColumnName = "CUSTOMER_ID",
        readonly = true
    )
    List<Customer> customers();

    @ManyToMany
    @JoinTable(
        name = "SHOP_CUSTOMER_MAPPING",
        joinColumnName = "SHOP_ID",
        inverseJoinColumnName = "CUSTOMER_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "VIP"
        )
    )
    List<Customer> vipCustomers();

    @ManyToMany
    @JoinTable(
        name = "SHOP_CUSTOMER_MAPPING",
        joinColumnName = "SHOP_ID",
        inverseJoinColumnName = "CUSTOMER_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "ORDINARY"
        )
    )
    List<Customer> ordinaryCustomers();
}

这个例子中，Shop.customers，Shop.vipCustomers和Shop.ordinaryCustomers共享一张中间表

create table SHOP_CUSTOMER_MAPPING(
    SHOP_ID bigint not null,
    CUSTOMER_ID bigint not null,
    MAPPING_TYPE varchar(8) not null
);

// 中间表主键是否包含过滤字段，需要具体问题具体分析。
// 在这个例子中，一个`Shop`和一个`Customer`之间的关联
// 不能既是"VIP"又是"ORDINARY"，
// 所以，主键约束不包含`MAPPING_TYPE`
alter table SHOP_CUSTOMER_MAPPING
    add constraint pk_SHOP_CUSTOMER_MAPPING
        primary key(SHOP_ID, CUSTOMER_ID);

alter table SHOP_CUSTOMER_MAPPING
    add constraint fk_SHOP_CUSTOMER_MAPPING__SHOP
        foreign key(SHOP_ID)
            references SHOP(ID);

alter table SHOP_CUSTOMER_MAPPING
    add constraint fk_SHOP_CUSTOMER_MAPPING__CUSTOMER
        foreign key(CUSTOMER_ID)
            references CUSTOMER(ID);

alter table SHOP_CUSTOMER_MAPPING
    add constraint fk_SHOP_CUSTOMER_MAPPING__MAPPING_TYPE
        check(MAPPING_TYPE in ('VIP', 'ORDINARY'));

该表以如下方式存储数据

SHOP_IDCUSTOMER_IDMAPPING_TYPE
213176VIP
211087ORDINARY
219155ORDINARY
408347VIP
404365ORDINARY
403478ORDINARY

其中，Shop.vipCustomers对应两条数据，Shop.ordinaryCustomers对应4条数据，而Shop.customers可以看到所有数据，即可。

Shop.customers = Shop.vipCustomers + Shop.ordinaryCustomers

可见，Shop.customers并非原始关联，而是其他多个关联的并集。我们不妨称其为并集关联。

警告

并集关联不能被直接修改，因此必须声明readonly = true。

其实，并集关联Shop.customers还有另外一种等价的写法

@ManyToMany
@JoinTable(
    ...省略其他参数...,
    filter = @JoinTable.JoinTableFilter(
        columnName = "MAPPING_TYPE",
        type = String.class,
        values = {"VIP", "ORDINARY"}
    )
)
List<Customer> customers();

最后，让我们看看这三个关联的JOIN行为的差异

  • Shop.customers

    ShopTable table = Tables.SHOP_TABLE;

    List<Long> shopIds = sqlClient
        .create(table)
        .where(
            table
                .asTableEx()
                .customers()
                .name().eq("Smith")
        )
        .select(table.id())
        .distinct()
        .execute();

    最终生成的SQL为

    select distinct tb_1_.ID, tb_1_.NAME
    from SHOP tb_1_
    inner join SHOP_CUSTOMER_MAPPING tb_2_ 
        /* highlight-next-line */
        on tb_1_.ID = tb_2_.SHOP_ID
    inner join CUSTOMER tb_3_ 
        on tb_2_.CUSTOMER_ID = tb_3_.ID
    where tb_3_.NAME = ? /* Smith */

  • Shop.vipCustomers

    ShopTable table = Tables.SHOP_TABLE;

    List<Long> shopIds = sqlClient
        .create(table)
        .where(
            table
                .asTableEx()
                .vipCustomers()
                .name().eq("Smith")
        )
        .select(table.id())
        .distinct()
        .execute();

    最终生成的SQL为

    select distinct tb_1_.ID, tb_1_.NAME
    from SHOP tb_1_
    inner join SHOP_CUSTOMER_MAPPING tb_2_ 
        /* highlight-next-line */
        on tb_1_.ID = tb_2_.SHOP_ID
        /* highlight-next-line */
        and tb_2_.MAPPING_TYPE = ? /* VIP */
    and
        tb_2_.type = ?
    inner join CUSTOMER tb_3_ 
        on tb_2_.CUSTOMER_ID = tb_3_.ID
    where tb_3_.NAME = ? /* Smith */

  • Shop.ordinaryCustomers

    ShopTable table = Tables.SHOP_TABLE;

    List<Long> shopIds = sqlClient
        .create(table)
        .where(
            table
                .asTableEx()
                .ordinaryCustomers()
                .name().eq("Smith")
        )
        .select(table.id())
        .distinct()
        .execute();

    最终生成的SQL为

    select distinct tb_1_.ID, tb_1_.NAME
    from SHOP tb_1_
    inner join SHOP_CUSTOMER_MAPPING tb_2_ 
        /* highlight-next-line */
        on tb_1_.ID = tb_2_.SHOP_ID
        /* highlight-next-line */
        and tb_2_.MAPPING_TYPE = ? /* ORDINARY */
    and
        tb_2_.type = ?
    inner join CUSTOMER tb_3_ 
        on tb_2_.CUSTOMER_ID = tb_3_.ID
    where tb_3_.NAME = ? /* Smith */

2. 双边实体类型不同的多个关联​

有一种常见的案例，项目中有很多种不同的数据，每一种数据都可以标记多个Tag。

  • Tag

    @Entity
    public interface Tag {

        @Id
        long id();

        String name();

        ...省略其他属性...
    }

  • BookStore

    @Entity
    public interface BookStore {

        @ManyToMany
        @JoinTable(
            name = "SOURCE_TAG_MAPPING",
            joinColumns = @joinColumn(
                name = "SOURCE_ID",
                foreignKeyType = ForeignKeyType.FAKE
            ),
            inverseJoinColumnName = "TAG_ID",
            filter = @JoinTable.JoinTableFilter(
                columnName = "SOURCE_TYPE",
                type = String.class,
                values = "BOOK_STORE",
            )
        )
        List<Tag> tags();
        
        ...省略其他属性...
    }

  • Book

    @Entity
    public interface Book {

        @ManyToMany
        @JoinTable(
            name = "SOURCE_TAG_MAPPING",
            joinColumns = @joinColumn(
                name = "SOURCE_ID",
                foreignKeyType = ForeignKeyType.FAKE
            ),
            inverseJoinColumnName = "TAG_ID",
            filter = @JoinTable.JoinTableFilter(
                columnName = "SOURCE_TYPE",
                type = String.class,
                values = "BOOK",
            )
        )
        List<Tag> tags();
        
        ...省略其他属性...
    }

  • Author

    @Entity
    public interface Author {

        @ManyToMany
        @JoinTable(
            name = "SOURCE_TAG_MAPPING",
            joinColumns = @joinColumn(
                name = "SOURCE_ID",
                foreignKeyType = ForeignKeyType.FAKE
            ),
            inverseJoinColumnName = "TAG_ID",
            filter = JoinTable.JoinTableFilter(
                columnName = "SOURCE_TYPE",
                type = String.class,
                values = "AUTHOR",
            )
        )
        List<Tag> tags();
        
        ...省略其他属性...
    }

对于中间表SOURCE_TAG_MAPPING而言，虽然其外键TAG_ID指向TAG表，但是另外一个外键SOURCE_ID却没有固定的目标，BOOK_STORE、BOOK和AUTHOR都有可能是其目标表。

因此，我们无法为SOURCE_ID添加外键约束。

create table SOURCE_TAG_MAPPING(
    SOURCE_ID bigint not null,
    TAG_ID bigint not null,
    SOURCE_TYPE varchar(10) not null
);

// 中间表主键是否包含过滤字段，需要具体问题具体分析。
// 在这个例子中，不能类型的`SOURCE_ID`可能相等，
// 所以，主键约束需要包含`SOURCE_TYPE`
alter table SOURCE_TAG_MAPPING
    add constraint pk_SHOP_CUSTOMER_MAPPING
        primary key(SOURCE_ID, TAG_ID, SOURCE_TYPE);

// highlight-next-line
// `SOURCE_ID`可能引用`BOOK_STORE`、`BOOK`和`AUTHOR`表，
// highlight-next-line
// 由于没有固定的行为，故而无法为建立外键约束

alter table SOURCE_TAG_MAPPING
    add constraint fk_SOURCE_TAG_MAPPING__TAG
        primary key(TAG_ID)
            references TAG(ID);

alter table SOURCE_TAG_MAPPING
    add constraint ck_SOURCE_TAG_MAPPING__SOURCE_TYPE
        check(SOURCE_TYPE in ('BOOK_STORE', 'BOOK', 'AUTHOR'));

SOURCE_ID无法建立外键约束，所以，这是一个伪外键，所以，即，上述代码中的

@JoinTable(
    joinColumns = @joinColumn(
        name = "SOURCE_ID",
        foreignKeyType = ForeignKeyType.FAKE
    ),
    ...省略其他参数...
)

该表数据看起来如夏

SOURCE_IDTARGET_IDSOURCE_TYPE
92BOOK_STORE
94BOOK_STORE
863BOOK
864BOOK
7811AUTHOR
7813AUTHOR

混合和多对一和多对多​

多对一和多对多都可以给予中间表映射，所以，可以利用这个技巧把二者的中间表合并成一张表。但是，请注意

警告

强烈建议仅在数据库支持条件索引时，才使用此用法。

即，不要在以MySQL为代表的不支持条件索引的数据库中使用此用法。

定义关联​

@Entity
public interface Book {

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

    @Nullable
    @ManyToOne
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING",
        joinColumnName = "BOOK_ID",
        inverseJoinColumnName = "AUTHOR_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "PRIMARY"
        )
    )
    Author primaryAuthor();

    @ManyToMany
    @JoinTable(
        name = "BOOK_AUTHOR_MAPPING",
        joinColumnName = "BOOK_ID",
        inverseJoinColumnName = "AUTHOR_ID",
        filter = @JoinTable.JoinTableFilter(
            columnName = "MAPPING_TYPE",
            type = String.class,
            values = "SECONDARY"
        )
    )
    List<Author> secondaryAuthors();

    ...省略其他属性...
}

很明显

authors (many-to-many) = primaryAuthor (many-to-one) + secondaryAuthors (many-to-many)

条件索引​

create table BOOK_AUTHOR_MAPPING(
    BOOK_ID bigint not null,
    AUTHOR_ID bigint not null,
    MAPPING_TYPE varchar(9) not null
);

// 中间表主键是否包含过滤字段，需要具体问题具体分析。
// 在这个例子中，一个`Author`和一个`Book`之间的关联
// 不能既是"PRIMARY"又是"SECONDARY"，
// 所以，主键约束不包含`MAPPING_TYPE`
alter table BOOK_AUTHOR_MAPPING
    add constraint pk_BOOK_AUTHOR_MAPPING
        primary key(BOOK_ID, AUTHOR_ID);

alter table BOOK_AUTHOR_MAPPING
    add constraint fk_BOOK_AUTHOR_MAPPING__BOOK
        foreign key(BOOK_ID)
            references BOOK(ID);

alter table BOOK_AUTHOR_MAPPING
    add constraint fk_BOOK_AUTHOR_MAPPING__BOOK
        foreign key(AUTHOR_ID)
            references AUTHOR(ID);

alter table BOOK_AUTHOR_MAPPING
    add constraint ck_BOOK_AUTHOR_MAPPING__MAPPING_TYPE
        check(MAPPING_TYPE in ('PRIMARY', 'SECONDARY'));

// highlight-start
// 不同数据库的条件索引的语法可能有少量差异，
// 这里以Postgres为例
create unique index BOOK_AUTHOR_MAPPING__PRIMARY_INDEX 
    on BOOK_AUTHOR_MAPPING(AUTHOR_ID)
    when MAPPING_TYPE = 'PRIMARY';
// highlight-end

其中when语句非常重要，表示该索引时条件索引。

不同数据库的条件索引的语法可能有少量差异，这里以Postgres为例。更多详情请参考https://www.postgresql.org/docs/current/indexes-partial.html

  • 当MAPPING_TYPE为"PRIMARY"时，为AUTHOR_ID外键添加一个唯一索引，保证每个Book最多只能有一个primary Author。 即，保证Book.primaryAuthor为多对一关联。

  • 否则，没有唯一性约束，每个Book可以有任意数量的secondary Author。 即，Book.secondaryAuthors仍然为多对多关联。