jpa - shardingsphere 无法查询自引用实体

Question

当某些实体具有自引用关系时，我遇到了分片问题。

用户.java：

@Entity
@Table("t_user")
@Data
@Builder
class User {

    @Id
    private Long id;

    private String name;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "t_parent_children",
        joinColumns = @JoinColumn(name = "parent", foreighKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT)),
        inverseJoinColumns = @JoinColumn(name = "children", foreignKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT)))
    @Builder.default
    private List<User> children = new ArrayList<>();

    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "children")
    @org.hibernate.annotations.ForeignKey(name="none")
    @Builder.default
    private List<User> parents = new ArrayList<>();
}

像这样的分片属性：

spring:
  ...
  shardingsphere:
    datasource:
      ...
    sharding:
      tables:
        t_user:
          actual-data-nodes: dbname.t_user_$->{0..9}
          key-generator:
            type: SNOWFLAKE
            column: id
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: t_user_$->{id % 10}
    props:
      sql:
        show: true
  ...

测试数据：

User father = User.builder().id(1).name("father").build();
userRepository.save(father);

User son = User.builder().id(2).name("son").build();
son.getParents().add(father);
father.getChildren().add(son);
userRepository.save(son);

User daughter = User.builder().id(3).name("daughter").build();
daughter.getParents().add(father);
father.getChildren().add(daughter);
userRepository.save(daughter);

用户存储库.java：

@Repository
public interface UserRepository extends JpaRepository<User,Long> {
    
    @Query("select c from User p join p.children c where p.id = ?1")
    public List<User> findByParent(Long id);
}

测试代码：

@Test
public void test() {
    List<User> children = userRepository.findByParent(1L);
    assertEquals(2, children.size()); // expect 2, but is 0
}

分片实际 SQL 仅从表 t_user_1 中选择，因此无法找到实际在 t_user_2/t_user_3 中的儿子和女儿。

Actual SQL: slave ::: select user1.id as user1_id,user1.name as user1_name from t_user_1 user0 
inner join t_parent_children parent_children1 on parent_chidlren.parent = user0.id 
inner join t_user_1 user1 on parent_children1.children = user1.id 
where user0.id=? ::: [1]

我该如何解决？

标签： jpasharding