如何高效实现关联双表的Upsert操作？

我基于Quarkus搭建Web服务，用Hibernate做数据库访问和实体映射，现在遇到批量插入的性能与正确性问题：

实体与表结构 #

Java实体类SanctionedEntity #

@Entity
@Table(name = "sanctioned_entities")
public class SanctionedEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name", nullable = false, unique = true)
    private String name;
    @ManyToMany
    @JoinTable(
        name = "sanctioned_entity_country_map", 
        joinColumns = { @JoinColumn(name = "sanctioned_entity_id") }, 
        inverseJoinColumns = { @JoinColumn(name = "country_id") }
    )
    private Set<Country> sanctioningCountries = new HashSet<>();
    //... get/set方法省略
}

底层SQL表结构 #

主表sanctioned_entities：

CREATE TABLE sanctioned_entities (
    id bigint NOT NULL,
    name character varying(1024) NOT NULL
);

多对多关联表sanctioned_entity_country_map：

CREATE TABLE sanctioned_entity_country_map (
    id bigint NOT NULL,
    sanctioned_entity_id bigint NOT NULL,
    country_id bigint NOT NULL
);

需求 #

批量插入大量SanctionedEntity及其对应的Country映射，满足：

• 若SanctionedEntity的name字段重复，直接忽略该实体，不抛出异常
• 若关联的Country映射未存储在关联表中，需要保存该映射

当前尝试的方案及问题 #

最初想通过Upsert语句（ON CONFLICT (name) DO NOTHING）忽略重复实体，再对关联表执行Upsert保存映射，但Hibernate不支持Upsert，只能用原生SQL实现。

问题在于新插入的SanctionedEntity的ID是自增生成的，关联表插入需要这个ID。我尝试用RETURNING id获取新增实体的ID，但返回的ID列表长度和原始SanctionedEntity列表长度不一致（仅返回新增实体的ID），无法直接对应到原实体，导致后续关联表插入无法正确匹配。

当前写的Java代码复杂度为O(n) + O(n*m)，而且无法正常工作：

@Transactional
public void persistAllWithMultipleCountries(List<SanctionedEntity> sanctionedEntities) {
    StringBuilder entityValuesBuilder = new StringBuilder();
    StringBuilder countryMapValuesBuilder = new StringBuilder();

    for (SanctionedEntity entity : sanctionedEntities) {
        addUpsertSanctionedEntitySqlFor(entity, entityValuesBuilder);
    }

    List<Long> ids = runSanctionedEntityUpsertFor(entityValuesBuilder);

    for (int i = 0; i < sanctionedEntities.size(); i++) {
        SanctionedEntity entity = sanctionedEntities.get(i);
        entity.setId(ids.get(i));
        addUpsertCountryMapsSqlFor(entity, countryMapValuesBuilder);
    }

    runEntityCountryMapsUpsertFor(countryMapValuesBuilder);
}

private void addUpsertSanctionedEntitySqlFor(SanctionedEntity entity, StringBuilder builder) {
    builder.append("(") 
        .append("'").append(entity.getName()).append("'")  
    .append("),");
}

private void addUpsertCountryMapsSqlFor(SanctionedEntity entity, StringBuilder builder) {
    entity.getSanctioningCountries().forEach(country -> {
        builder.append("(") 
            .append(entity.getId()).append(",")
            .append(country.getId())
        .append("),");
    });
}

@SuppressWarnings("unchecked")
private List<Long> runSanctionedEntityUpsertFor(StringBuilder builder) {
    String values = getChainedValuesWithoutLastRedundantComma(builder);
    String upsertSql = "INSERT INTO " 
        + "sanctioned_entities (name) " 
        + "VALUES " + values + " "
        + "ON CONFLICT DO NOTHING "
        + "RETURNING id";
    return entityManager.createNativeQuery(upsertSql).getResultList();
}

private void runEntityCountryMapsUpsertFor(StringBuilder builder) {
    String values = getChainedValuesWithoutLastRedundantComma(builder);
    String upsertSql = "INSERT INTO " 
        + "sanctioned_entity_country_map (sanctioned_entity_id, country_id) " 
        + "VALUES " + values + " "
        + "ON CONFLICT DO NOTHING";
    entityManager.createNativeQuery(upsertSql).executeUpdate();
}

private String getChainedValuesWithoutLastRedundantComma(StringBuilder builder) {
    return builder.deleteCharAt(builder.length() - 1).toString();
}

求助方向 #

我觉得Java端很难实现更优的方案，考虑改用PostgreSQL存储过程，但不清楚：

1. 如何连贯地传递所有批量数据到存储过程
2. 如何保证SQL语法的有效性

希望能得到更优的解决方案，或者详细解释如何用存储过程实现该需求。

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