Spring Data / Hibernate使用Insert on Conflict Update将实体保存到Postgres中，更新某些字段

Question

我在Spring中有一个领域对象，我正在使用JpaRepository.save方法保存它，并使用Postgres的Sequence生成器自动生成id。

@SequenceGenerator(initialValue = 1, name = "device_metric_gen", sequenceName = "device_metric_seq")
public class DeviceMetric extends BaseTimeModel {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "device_metric_gen")
    @Column(nullable = false, updatable = false)
    private Long id;
///// extra fields

我的使用场景需要执行一个upsert而不是普通的save操作(我知道如果存在id时会进行更新)。我想在现有行的三列组合(假设为复合唯一键)存在时更新该行，否则创建一个新行。 这类似于这个。

INSERT INTO customers (name, email)
VALUES
   (
      'Microsoft',
      '[e-mail]'
   ) 
ON CONFLICT (name) 
DO
      UPDATE
     SET email = EXCLUDED.email || ';' || customers.email;

我可以想到在Spring-data中实现相同效果的一种方法是：

1. 在服务层编写自定义保存操作
2. 对三列进行get操作，如果存在行，则
3. 将相同的id设置为当前对象并执行repository.save操作
4. 如果不存在行，则执行普通的repository.save操作

上述方法的问题在于每次插入都会执行select和save，这样就需要两个数据库调用，而使用Postgres“插入冲突”功能只需一个数据库调用即可实现相同的效果。 有没有关于如何在Spring Data中实现此功能的提示?

一种方法是编写本地查询"insert into values (all fields here)"。但涉及到的对象有大约25个字段，因此我正在寻找另一种更好的方法来实现相同的效果。

- abstractKarshit

---

也许你可以使用@SQLInsert。插入到客户表（name，email） 值 ( ?, ? ) 在冲突时（name） 执行 更新 ... - hossein rasekhi

@hosseinrasekhi 我可以这样做，但我需要重新编写所有字段，是吗？我可以像这里的代码一样制作：https://www.baeldung.com/jpa-insert 但即使如此，我仍然需要逐个添加每个字段。我正在寻找一种方法，可以使用本地查询功能，但传递对象并避免逐个设置多个字段。 - abstractKarshit

@abstractKarshit Spring是无关紧要的。JPA才是重点，而且它不支持upserts操作。你可以使用本地SQL来实现。正如我所说，所有选项都在你的问题中。选择你最不喜欢的那个。 - JB Nizet

来晚了，但还有一个想法：您是否进行过任何真实的测试，以确定您是否真的需要避免额外的一次数据库往返所带来的性能？我不愿为过早的优化和hacky变通而牺牲易于理解、清晰的代码。 - Stefan Haberl

@StefanHaberl 考虑中的应用程序预计具有非常高的吞吐量，我们已经在其他地方使用了 INSERT ON CONFLICT。我想找到一种更清晰的方法来做这件事，如果 JPA 支持的话，那就更好了。最终，我选择了本地 SQL，但我不认为它是一个 hack。回想起来，我同意你的看法，通过负载测试比较两种方法会很有趣，然后如果性能提升不显著，可能会更喜欢可读性更好的代码。 - abstractKarshit

Answer 1

正如@JBNizet所提到的，你通过建议读取数据并在找到数据后更新，否则插入的方式回答了自己的问题。以下是如何使用spring data和Optional实现的。

在你的DeviceMetricRepository上定义一个findByField1AndField2AndField3方法。

public interface DeviceMetricRepository extends JpaRepository<DeviceMetric, UUID> {
    Optional<DeviceMetric> findByField1AndField2AndField3(String field1, String field2, String field3);
}

在服务方法中使用存储库。

    @RequiredArgsConstructor
    public class DeviceMetricService {
        private final DeviceMetricRepository repo;
        DeviceMetric save(String email, String phoneNumber) {
            DeviceMetric deviceMetric = repo.findByField1AndField2AndField3("field1", "field", "field3")
                .orElse(new DeviceMetric()); // create new object in a way that makes sense for you
            deviceMetric.setEmail(email);
           deviceMetric.setPhoneNumber(phoneNumber);
        return repo.save(deviceMetric);
    }
}

关于可观测性的建议： 您提到这是您系统中高吞吐量的使用案例。无论采取何种方法，请考虑在保存时周围进行计时器的检测。这样，您可以客观地衡量初始性能与任何调整之间的差异。将其视为实验，并准备根据需要转向其他解决方案。如果您总是一起阅读这三列，请确保它们已被索引。有了这些措施，您可能会发现阅读以确定更新/插入是可以接受的。

- theRealPierreMenard

Answer 2

我建议使用命名查询来根据候选键获取一行数据。如果存在该行数据，则更新它，否则创建一个新的行。这两个操作都可以使用save方法完成。

@NamedQuery(name="getCustomerByNameAndEmail", query="select a from Customers a where a.name = :name and a.email = :email");

你还可以在实体上使用@UniqueColumns()注解，以确保这些列在分组在一起时始终保持唯一性。

Optional<Customers> customer = customerRepo.getCustomersByNameAndEmail(name, email);

在您的存储库中实现上述方法。它将调用查询并将名称和电子邮件作为参数传递。确保如果没有行存在，则返回Optional.empty()。

Customers c;
if (customer.isPresent()) {
    c = customer.get();
    c.setEmail("[e-mail]");
    c.setPhone("9420420420");
    customerRepo.save(c);
} else {
    c = new Customer(0, "name", "email", "5451515478");
    customerRepo.save(c);
}

将ID设为0，JPA将根据序列生成器生成的ID插入新行。

虽然我从未建议使用数字作为ID，但如果可能，请使用随机生成的UUID作为主键，它将保证唯一性并避免与序列生成器可能带来的任何意外行为。

- Shantanu Srivastava

Answer 3

使用Spring JPA，使用干净的Java代码实现这个功能非常简单。 使用Spring Data JPA的方法T getOne(ID id)，您不是在查询DB本身，而是使用对DB对象（代理）的引用。因此，在更新/保存实体时，您正在执行一次性操作。

为了能够修改对象，Spring提供了@Transactional注释，它是一个方法级别的注释，声明该方法启动事务，并仅在方法本身结束其运行时才关闭它。

您需要：

• 开始JPA事务
• 通过getOne获取Db引用
• 修改DB引用
• 将其保存到数据库中
• 关闭事务

由于我无法看到您的实际代码，因此我会尽可能地进行抽象。

@Transactional
public void saveOrUpdate(DeviceMetric metric) {
    DeviceMetric deviceMetric = metricRepository.getOne(metric.getId());
    //modify it
    deviceMetric.setName("Hello World!");
    metricRepository.save(metric);
}

棘手的部分在于不要将getOne视为从数据库中选择数据。直到“保存”方法调用之前，数据库才被调用。

- L_Cleo

---

根据文档，如果ID不存在，getOne将抛出EntityNotFoundException，因此这适用于“更新”但不适用于“插入”。而且，即使修复了这个问题，Spring也将（必须）在“setName”之前获取行，因此它将在保存之前获取。即使仅在调用保存时获取，它仍然是一个两步（获取，保存）过程，因此仍然有可能在ID上存在约束违规或创建重复项-避免这样的事情是UPSERT的关键特性。 - user3067860