Spring Data / Hibernate save entity with Postgres using Insert on Conflict Update Some fields

Question

I have a domain object in Spring which I am saving using JpaRepository.save method and using Sequence generator from Postgres to generate id automatically.

@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

My use-case requires to do an upsert instead of normal save operation (which I am aware will update if the id is present). I want to update an existing row if a combination of three columns (assume a composite unique) is present or else create a new row. This is something similar to this:

INSERT INTO customers (name, email)
VALUES
   (
      'Microsoft',
      'hotline@microsoft.com'
   ) 
ON CONFLICT (name) 
DO
      UPDATE
     SET email = EXCLUDED.email || ';' || customers.email;

One way of achieving the same in Spring-data that I can think of is:

1. Write a custom save operation in the service layer that
2. Does a get for the three-column and if a row is present
3. Set the same id in current object and do a repository.save
4. If no row present, do a normal repository.save

Problem with the above approach is that every insert now does a select and then save which makes two database calls whereas the same can be achieved by postgres insert on conflict feature with just one db call. Any pointers on how to implement this in Spring Data?

One way is to write a native query insert into values (all fields here). The object in question has around 25 fields so I am looking for an another better way to achieve the same.

Answer 1

As @JBNizet mentioned, you answered your own question by suggesting reading for the data and then updating if found and inserting otherwise. Here's how you could do it using spring data and Optional.

Define a findByField1AndField2AndField3 method on your DeviceMetricRepository.

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

Use the repository in a service method.

    @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);
    }
}

A word of advice on observability: You mentioned that this is a high throughput use case in your system. Regardless of the approach taken, consider instrumenting timers around this save. This way you can measure the initial performance against any tunings you make in an objective way. Look at this an experiment and be prepared to pivot to other solutions as needed. If you are always reading these three columns together, ensure they are indexed. With these things in place, you may find that reading to determine update/insert is acceptable.

Answer 2

I would recommend using a named query to fetch a row based on your candidate keys. If a row is present, update it, otherwise create a new row. Both of these operations can be done using the save method.

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

You can also use the @UniqueColumns() annotation on the entity to make sure that these columns always maintain uniqueness when grouped together.

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

Implement the above method in your repository. All it will do it call the query and pass the name and email as parameters. Make sure to return an Optional.empty() if there is no row present.

Customers c;
if (customer.isPresent()) {
    c = customer.get();
    c.setEmail("newemail@gmail.com");
    c.setPhone("9420420420");
    customerRepo.save(c);
} else {
    c = new Customer(0, "name", "email", "5451515478");
    customerRepo.save(c);
}

Pass the ID as 0 and JPA will insert a new row with the ID generated according to the sequence generator.

Although I never recommend using a number as an ID, if possible use a randomly generated UUID for the primary key, it will qurantee uniqueness and avoid any unexpected behaviour that may come with sequence generators.

Answer 3

With spring JPA it's pretty simple to implement this with clean java code. Using Spring Data JPA's method T getOne(ID id), you're not querying the DB itself but you are using a reference to the DB object (proxy). Therefore when updating/saving the entity you are performing a one time operation.

To be able to modify the object Spring provides the @Transactional annotation which is a method level annotation that declares that the method starts a transaction and closes it only when the method itself ends its runtime.

You'd have to:

• Start a jpa transaction
• get the Db reference through getOne
• modify the DB reference
• save it on the database
• close the transaction

Not having much visibility of your actual code I'm gonna abstract it as much as possible:

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

The tricky part is to not think the getOne as a SELECT from the DB. The database never gets called until the 'save' method.