@OneToMany关联下JPA查询结果倒置问题排查

问题背景 #

John（id=111）向Alice（id=222）发起关联Paperwork（Baz id=999）的求助请求，数据库中bar表数据符合预期，但查询Foo实体时，关联集合的结果出现倒置。

1. JPA实体定义 #

Foo实体 #

@Entity
@Table(name = "foo")
public class Foo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, unique = true)
    private Long id;
    
    // ... 其他字段

    @OneToMany(mappedBy = "fooer", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Bar> barInfo = new ArrayList<>();

    @OneToMany(mappedBy = "fooing", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Bar> baringInfo = new ArrayList<>();
}

Bar实体 #

@Entity
@Table(name = "bar")
public class Bar {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, unique = true)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fooer_id", referencedColumnName = "id", nullable = false,
                foreignKey = @ForeignKey(name = "bar_fooer_id_fkey"))
    private Foo fooer;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fooing_id", referencedColumnName = "id", nullable = false,
                foreignKey = @ForeignKey(name = "bar_fooing_id_fkey"))
    private Foo fooing;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "baz_id", referencedColumnName = "id", nullable = false,
                foreignKey = @ForeignKey(name = "bar_baz_id_fkey"))
    private Baz baz;
}

Baz实体 #

@Entity
@Table(name = "baz")
public class Baz {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, unique = true)
    private Long id;
    
    // ... 其他字段
}

2. 数据库DDL配置 #

CREATE TABLE some_schema.bar
(
    id        int8 GENERATED BY DEFAULT AS IDENTITY ( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
    fooer_id  int8 NOT NULL,
    fooing_id int8 NOT NULL,
    baz_id    int8 NOT NULL,
    CONSTRAINT bar_id_key UNIQUE (id),
    CONSTRAINT bar_fooer_id_fkey FOREIGN KEY (fooer_id) REFERENCES some_schema.foo (id),
    CONSTRAINT bar_fooing_id_fkey FOREIGN KEY (fooing_id) REFERENCES some_schema.foo (id),
    CONSTRAINT bar_baz_id_fkey FOREIGN KEY (baz_id) REFERENCES some_schema.baz (id)
);

3. 问题详情 #

业务场景与数据插入 #

John（id=111）作为请求发起方（fooing），Alice（id=222）作为接收方（fooer），插入关联请求的代码如下：

final var john = getById(contextId); // fooing
final var alice = getById(id);       // fooer
final var paperwork = bazService.getBazById(bazId);

john.getBarInfo()
    .add(Bar.builder()
            .fooer(alice)
            .fooing(john)
            .baz(paperwork)
            .build());

fooRepository.save(john);

数据库bar表数据符合预期：

预期与实际查询结果 #

预期结果：

{
  "John": {
    "barInfo": {
      "fooers": [
        {
          "foo_id": 222,
          "baz_id": 999
        }
      ],
      "fooing": []
    }
  },
  "Alice": {
    "barInfo": {
      "fooers": [],
      "fooing": [
        {
          "foo_id": 111,
          "baz_id": 999
        }
      ]
    }
  }
}

实际结果：

{
  "John": {
    "barInfo": {
      "fooers": [],
      "fooing": [
        {
          "foo_id": 111,
          "baz_id": 999
        }
      ]
    }
  },
  "Alice": {
    "barInfo": {
      "fooers": [
        {
          "foo_id": 222,
          "baz_id": 999
        }
      ],
      "fooing": []
    }
  }
}

问题原因 #

Foo实体中两个@OneToMany的mappedBy属性与业务含义完全倒置：

• barInfo配置mappedBy = "fooer"，表示该集合存储**当前Foo作为被请求方（fooer）**的Bar记录
• baringInfo配置mappedBy = "fooing"，表示该集合存储**当前Foo作为请求发起方（fooing）**的Bar记录

但业务逻辑中，你期望barInfo存储当前Foo发起的请求（作为fooing的Bar记录），baringInfo存储当前Foo接收的请求（作为fooer的Bar记录），映射关系与需求不匹配。

解决方案 #

调整Foo实体中两个@OneToMany的mappedBy属性，使其匹配业务含义：

@Entity
@Table(name = "foo")
public class Foo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, unique = true)
    private Long id;
    
    // ... 其他字段

    // 存储当前Foo作为请求发起方（fooing）的Bar记录
    @OneToMany(mappedBy = "fooing", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Bar> barInfo = new ArrayList<>();

    // 存储当前Foo作为请求接收方（fooer）的Bar记录
    @OneToMany(mappedBy = "fooer", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Bar> baringInfo = new ArrayList<>();
}

另外建议给字段起更具语义化的名称（比如initiatedRequests、receivedRequests），避免后续混淆映射关系。

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