在JPA中，使用实体图查询具有自定义中间表的多对多实体

2024年7月4日 · 阅读需 9 分钟

可爱小猫咪一枚呀

大家好哇！不知道大家在使用JPA的时候有没有试用过实体图(EntityGraph)进行查询呢？它的功能很强大，比如可以用来规避大家经常遇到的 n+1 的问题。那么你了不了解如何在"自定义中间表"的情况下去使用实体图查询呢？如果不太了解，那么就来跟着我看一下吧！

为了方便演示，后续的项目环境默认视为在 spring-boot-starter-data-jpa 中喔~

实体定义

首先，我们先来看看普通的多对多实体是如何定义、查询的。假设：我们有一个账户(account)和权限(permission)表，它们之间是多对多关系，中间表为 account_permission 。

那么，先来定义它们的实体类：

// Account.java

@Entity
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @ManyToMany
    @JoinTable(name = "account_permission")
    private Set<Permission> permissions = new HashSet<>();
}

// Permission.java

@Entity
public class Permission {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    @ManyToMany(mappedBy = "permissions")
    private Set<Account> accounts = new HashSet<>();
}

为了演示省事儿，省略掉 getter、setter 之类的东西咯。

然后，在 Account 中添加实体图描述。

@Entity
@NamedEntityGraph(
        name = Account.ALL_GRAPH,
        attributeNodes = {
                @NamedAttributeNode("permissions")
        }
)
public class Account {
    public static final String ALL_GRAPH = "ACCOUNT.ALL";

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @ManyToMany
    @JoinTable(name = "account_permission")
    private Set<Permission> permissions = new HashSet<>();
}

接下来，定义一个 Repository 并使用图查询，来看看效果：

public interface AccountRepository extends JpaRepository<Account, Integer> {
    // 使用具有命名的图查询
    @EntityGraph(Account.ALL_GRAPH)
    List<Account> findAllBy();
}

@SpringBootTest(classes = {GraphTests.class, AccountRepository.class})
@ActiveProfiles("test")
@SpringBootApplication
public class GraphTests {
    @Test
    public void graphTest(
            @Autowired AccountRepository accountRepository
    ) {
        accountRepository.findAllBy();
    }
}

当执行测试之后，我们便可以在日志中看到本次查询所生成的SQL：

select
    a1_0.id,
    a1_0.name,
    p1_0.accounts_id,
    p1_1.id,
    p1_1.name
from
    account a1_0
left join
    (account_permission p1_0
join
    permission p1_1
        on p1_1.id=p1_0.permissions_id)
    on a1_0.id=p1_0.accounts_id

可以看到，它帮我们自动进行了关联查询，避免了n+1的情况出现。

自定义中间表

有些时候，我们希望自定义中间表的内容，比如增加 created_date 来记录关联双方的创建日期。这时候就需要修改一下我们的几个实体类，并添加一个 AccountPermission ：

// Account.java

@Entity
@NamedEntityGraph(
        name = Account.ALL_GRAPH,
        attributeNodes = {
                @NamedAttributeNode("permissions")
        }
)
public class Account {
    public static final String ALL_GRAPH = "ACCOUNT.ALL";

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @OneToMany(mappedBy = "id.account")
    private Set<AccountPermission> permissions = new HashSet<>();
}

// Permission.java

@Entity
public class Permission {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    @OneToMany(mappedBy = "id.permission")
    private Set<AccountPermission> accounts = new HashSet<>();
}

// AccountPermission.java

@Entity
@Table(name = "account_permission")
public class AccountPermission {
    @EmbeddedId
    private PrimaryKey id = new PrimaryKey();

    @CreatedDate
    private Instant createdDate;

    @Embeddable
    public static class PrimaryKey {
        @ManyToOne
        private Account account;
        @ManyToOne
        private Permission permission;
    }
}

可是这时候再去执行 findAllBy 的时候，你会发现生成的SQL出现了一些变化。

@SpringBootTest(classes = {GraphTests.class, AccountRepository.class})
@ActiveProfiles("test")
@SpringBootApplication
@EnableJpaAuditing
public class GraphTests {
    @Autowired
    EntityManager entityManager;
    @Autowired
    AccountRepository accountRepository;
    @Autowired
    PermissionRepository permissionRepository;
    @Autowired
    AccountPermissionRepository accountPermissionRepository;

    @Test
    @Transactional
    public void graphTest() {
        initData(); // 初始化2个 Account、4个 Permission

        final var all = accountRepository.findAllBy();
        for (var account : all) {
            System.out.println("account: " + account.getName());
            for (var permission : account.getPermissions()) {
                System.out.println("Account.permission: " + permission.id.getPermission().getName());
            }
            System.out.println();
        }
    }

    private void initData() {
        // ... 生成2个 Account、4个 Permission，
        // 并使它们之间存在关联：每一个account关联两个permission。
    }
}

而控制台产生的日志为：

Hibernate:
    select
        a1_0.id,
        a1_0.name,
        p1_0.account_id,
        p1_0.permission_id,
        p1_0.created_date
    from
        account a1_0
    left join
        account_permission p1_0
            on a1_0.id=p1_0.account_id
account: Account1
Hibernate:
    select
        p1_0.id,
        p1_0.name
    from
        permission p1_0
    where
        p1_0.id=?
Account.permission: Permission2
Hibernate:
    select
        p1_0.id,
        p1_0.name
    from
        permission p1_0
    where
        p1_0.id=?
Account.permission: Permission1

account: Account1
Hibernate:
    select
        p1_0.id,
        p1_0.name
    from
        permission p1_0
    where
        p1_0.id=?
Account.permission: Permission3
Hibernate:
    select
        p1_0.id,
        p1_0.name
    from
        permission p1_0
    where
        p1_0.id=?
Account.permission: Permission4

这下糟糕了！你心里想着。因为很明显，n+1的问题又出现了！

一些尝试

究其原因，我们在 Account 和 Permission 中的关联由 ManyToMany 变为了针对中间表的 OneToMany，因此 Account 上的 @NamedEntityGraph 便只会抓取中间表，而不是中间表对应的另一个关联表。

那么我们应该如何修改图定义呢？如果改成下面这样...

@NamedEntityGraph(
        name = Account.ALL_GRAPH,
        attributeNodes = {
                @NamedAttributeNode(value = "permissions", subgraph = "sub")
        },
        subgraphs = {
                @NamedSubgraph(
                        name = "sub",
                        attributeNodes = @NamedAttributeNode(value = "id")
                )
        }
)

我们再添加一个子图，用来抓取中间表的 id ，如何？但是很遗憾，结果并不会有什么区别。

那如果是：

@NamedEntityGraph(
        name = Account.ALL_GRAPH,
        attributeNodes = {
                @NamedAttributeNode(value = "permissions", subgraph = "sub")
        },
        subgraphs = {
                @NamedSubgraph(
                        name = "sub",
                        attributeNodes = @NamedAttributeNode(value = "id", subgraph = "id-permission")
                ),
                @NamedSubgraph(
                        name = "id-permission",
                        attributeNodes = @NamedAttributeNode(value = "permission")
                ),
        }
)

再加一层，抓取id中明确的 permission 属性呢？可是 Permission.PrimaryKey 类型本身其实不算是实体类，这样的结构其实是无效的。

那么如此又如何：

@NamedEntityGraph(
        name = Account.ALL_GRAPH,
        attributeNodes = {
                @NamedAttributeNode(value = "permissions", subgraph = "sub")
        },
        subgraphs = {
                @NamedSubgraph(
                        name = "sub",
                        attributeNodes = @NamedAttributeNode(value = "id.permission")
                )
        }
)

但是很抱歉，这种路径结构的方式是并不受支持的。

再次改进

那么该怎么做才能真正关联到中间表对应的另一侧的关联实体呢？首先，我们需要先修改一下中间表的定义。

@Entity
@Table(name = "account_permission")
public class AccountPermission {
    @EmbeddedId
    PrimaryKey id = new PrimaryKey();

    @ManyToOne
    @MapsId("accountId")
    private Account account;

    @ManyToOne
    @MapsId("permissionId")
    private Permission permission;

    @CreatedDate
    Instant createdDate;

    @Embeddable
    public static class PrimaryKey {
        private Integer accountId;
        private Integer permissionId;
    }
}

可以看到，PrimaryKey 中我们只留下了两个ID类型，而真正具有关联性的实体对象则挪到了外层，并使用 @MapsId 进行了标记。此时，我们再来修改 Account 上的图定义：

@NamedEntityGraph(
        name = Account.ALL_GRAPH,
        attributeNodes = {
                @NamedAttributeNode(value = "permissions", subgraph = "sub")
        },
        subgraphs = {
                @NamedSubgraph(
                        name = "sub",
                        attributeNodes = @NamedAttributeNode(value = "permission")
                )
        }
)

调整两个实体中的 OneToMany 注解上的 mappedBy：

// Account.java
public class Account {
    public static final String ALL_GRAPH = "ACCOUNT.ALL";

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @OneToMany(mappedBy = "account")
    private Set<AccountPermission> permissions = new HashSet<>();
}

// Permission.java
@Entity
public class Permission {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    @OneToMany(mappedBy = "permission")
    private Set<AccountPermission> accounts = new HashSet<>();
}

然后，运行我们的测试方法：

@Test
@Transactional
public void graphTest() {
    initData(); // 初始化2个 Account、4个 Permission

    final var all = accountRepository.findAllBy();
    for (var account : all) {
        System.out.println("account: " + account.getName());
        for (var permission : account.getPermissions()) {
            System.out.println("Account.permission: " + permission.getPermission().getName());
        }
        System.out.println();
    }
}

此时观察日志：

Hibernate:
    select
        a1_0.id,
        a1_0.name,
        p1_0.account_id,
        p1_0.permission_id,
        p1_0.created_date,
        p2_0.id,
        p2_0.name
    from
        account a1_0
    left join
        account_permission p1_0
            on a1_0.id=p1_0.account_id
    left join
        permission p2_0
            on p2_0.id=p1_0.permission_id
account: Account1
Account.permission: Permission1
Account.permission: Permission2

account: Account1
Account.permission: Permission4
Account.permission: Permission3

你会发现，生成的SQL中该有的表关联都回来了，n+1的问题也不见了，可喜可贺，可喜可贺~

结尾

如此，本篇文章就接近尾声了。不知道有没有对你起到帮助呢？如果有，点赞收藏或在评论区表扬一下我吧~

实体定义​

自定义中间表​

一些尝试​

再次改进​

结尾​

实体定义

自定义中间表

一些尝试

再次改进

结尾