Hibernate/JPA：如何关联三个表并实现嵌套结构的CRUD查询

现有三个JPA实体及一张三元关联表a_b_c，实体定义如下：

Entity A #

@Table(name = "A")
public class A {
    @Id
    private Long id;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "a_b",
            joinColumns = @JoinColumn(name = "a_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "b_id", referencedColumnName = "id")
    )
    private Set<B> b;
    // getter、setter省略
}

Entity B #

@Table(name = "B")
public class B {
    @Id
    private Long id;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "b_c",
            joinColumns = @JoinColumn(name = "b_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "c_id", referencedColumnName = "id")
    )
    private Set<C> c;
    // getter、setter省略
}

Entity C #

@Table(name = "C")
public class C {
    @Id
    private Long id;
    // getter、setter省略
}

额外存在一张三元关联表a_b_c，结构如下：

ID
idA
idB
idC

需求是查询得到嵌套结构的结果：

{
  "id": 1,
  "b": [
    {
      "id": 1,
      "c": [{"id":1}, {"id":2}]
    },
    {
      "id": 2,
      "c": [{"id":1}, {"id":3}]
    }
  ]
}

当前实体仅定义了A-B、B-C的多对多关系，无法直接获取与同一A、B同时关联的C集合，需解决该关联查询问题。

方法1：映射三元关联实体（推荐） #

直接将a_b_c表映射为JPA实体，通过它精准关联A、B、C三者的关系，后续CRUD操作都能直接复用该关联。

步骤1：创建ABCRelation实体 #

@Entity
@Table(name = "a_b_c")
public class ABCRelation {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "idA")
    private A a;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "idB")
    private B b;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "idC")
    private C c;

    // getter、setter、equals、hashCode方法
}

步骤2：在A实体中添加关联并分组 #

修改A实体，新增到ABCRelation的集合关联，并添加方法直接返回需求的嵌套结构：

@Table(name = "A")
public class A {
    // 原有字段...

    @OneToMany(mappedBy = "a", fetch = FetchType.LAZY)
    private Set<ABCRelation> abcRelations;

    // 自定义方法：返回按B分组的C集合
    public Map<B, Set<C>> getGroupedBC() {
        return abcRelations.stream()
                .collect(Collectors.groupingBy(ABCRelation::getB,
                        Collectors.mapping(ABCRelation::getC, Collectors.toSet())));
    }
}

查询A后，调用getGroupedBC()即可直接得到符合需求的B嵌套C的结构，完全匹配a_b_c表的关联规则。

方法2：JPQL构造查询（无需修改实体） #

如果不想调整实体结构，可直接编写JPQL查询，通过关联a_b_c表筛选出与A、B同时关联的C。

示例JPQL查询及结果组装 #

String jpql = "SELECT a, b, c FROM A a " +
              "JOIN a.b b " +
              "JOIN ABCRelation abc ON abc.a = a AND abc.b = b " +
              "JOIN abc.c c " +
              "WHERE a.id = :aId";

List<Object[]> results = entityManager.createQuery(jpql)
        .setParameter("aId", 1L)
        .getResultList();

// 手动组装目标结构
Map<A, Map<B, Set<C>>> resultMap = new HashMap<>();
for (Object[] row : results) {
    A a = (A) row[0];
    B b = (B) row[1];
    C c = (C) row[2];

    resultMap.computeIfAbsent(a, k -> new HashMap<>())
             .computeIfAbsent(b, k -> new HashSet<>())
             .add(c);
}

方法3：原生SQL+DTO映射（灵活适配复杂场景） #

若JPQL无法满足需求，可使用原生SQL查询，再映射到自定义DTO结构。

步骤1：定义DTO类 #

public class ADTO {
    private Long id;
    private List<BDTO> bList;

    // 构造器、getter、setter

    public static class BDTO {
        private Long id;
        private List<CDTO> cList;

        // 构造器、getter、setter
    }

    public static class CDTO {
        private Long id;

        // 构造器、getter、setter
    }
}

步骤2：原生SQL查询并组装DTO #

String sql = "SELECT a.id AS a_id, b.id AS b_id, c.id AS c_id " +
             "FROM A a " +
             "JOIN a_b ab ON a.id = ab.a_id " +
             "JOIN B b ON ab.b_id = b.id " +
             "JOIN a_b_c abc ON abc.idA = a.id AND abc.idB = b.id " +
             "JOIN C c ON abc.idC = c.id " +
             "WHERE a.id = ?";

List<Object[]> rows = entityManager.createNativeQuery(sql)
        .setParameter(1, 1L)
        .getResultList();

// 组装成目标DTO结构
ADTO aDto = new ADTO();
aDto.setId(1L);
Map<Long, ADTO.BDTO> bMap = new HashMap<>();

for (Object[] row : rows) {
    Long bId = (Long) row[1];
    Long cId = (Long) row[2];

    ADTO.BDTO bDto = bMap.computeIfAbsent(bId, k -> {
        ADTO.BDTO dto = new ADTO.BDTO();
        dto.setId(k);
        dto.setcList(new ArrayList<>());
        return dto;
    });

    ADTO.CDTO cDto = new ADTO.CDTO();
    cDto.setId(cId);
    bDto.getcList().add(cDto);
}

aDto.setbList(new ArrayList<>(bMap.values()));

内容的提问来源于stack exchange，提问作者Bruno Andrade