Upsert

Upsert란 Update와 Insert를 합친 것으로, 존재하지 않을 경우, Insert하고 존재할 경우, 현재 내용을 덮어쓰는 유용한 쿼리입니다.

Postgresql에서의 Upsert

저의 이전글을 보면 자세히 나와있습니다.

Spring Data Jpa에서 Merge 문으로 Upsert 구현

CREATE TABLE google_place_cache(
    google_place_id TEXT PRIMARY KEY,
    location GEOMETRY(POINT, 4326) NOT NULL ,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX ON google_place_cache USING GIST(location);

간단하게 저는 Google Place API 에서 온 결과를 캐싱하기 위해 테이블을 만들었습니다.
GEOMETRY 타입은 POSTGIS extension을 활용했고, 지리 인덱스도 걸었습니다.

예시 merge 쿼리문

MERGE INTO google_place_cache
USING (VALUES ('{PLACE_ID}','POINT (135.503039 34.669209699999996)')) s
ON google_place_cache.google_place_id = s.column1
WHEN MATCHED THEN
  UPDATE SET location = s.column2, updated_at = clock_timestamp()
WHEN NOT MATCHED THEN
  INSERT (google_place_id, location, updated_at)
  VALUES (s.column1, s.column2, clock_timestamp());

매우 직관적인 쿼리입니다. 문법이 유연합니다.
https://www.postgresql.org/docs/current/sql-merge.html

Spring data jpa native query로 매핑

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;
import org.locationtech.jts.geom.Point;

import java.time.OffsetDateTime;

@Getter @Setter
@Entity
@Table(name = "google_place_cache")
public class GooglePlaceCacheEntity {

    @Id
    @Column(name = "google_place_id", nullable = false, unique = true, columnDefinition = "text")
    private String googlePlaceId;

    @Column(name = "location", nullable = false, columnDefinition = "geometry(Point,4326)")
    private Point location;


    @Column(name = "updated_at", nullable = false, columnDefinition = "timestamp with time zone")
    private OffsetDateTime updatedAt;

    public GooglePlaceCacheEntity() {
    }

    public GooglePlaceCacheEntity(String googlePlaceId, Point location) {
        this.googlePlaceId = googlePlaceId;
        this.location = location;
    }
}

Point <-> Geometry Mapping은 hibernate spatial 사용

import click.porito.modular_travel.place.model.GooglePlaceCacheEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface GooglePlaceCacheRepository extends JpaRepository<GooglePlaceCacheEntity, String> {

    @Modifying(clearAutomatically = true)
    @Query(value = "MERGE INTO google_place_cache " +
            "USING (VALUES (:#{#place.googlePlaceId},:#{#place.location})) s " +
            "ON google_place_cache.google_place_id = s.column1 " +
            "WHEN MATCHED THEN" +
            "  UPDATE SET location = s.column2, updated_at = clock_timestamp() " +
            "WHEN NOT MATCHED THEN" +
            "  INSERT (google_place_id, location, updated_at)" +
            "  VALUES (s.column1, s.column2, clock_timestamp())", nativeQuery = true)
    int merge(@Param("place") GooglePlaceCacheEntity googlePlaceCacheEntity);
}

MERGE문의 경우 ID 값이 같아도, 변화가 일어날 수 있다. native쿼리를 사용하므로, @Modifying에 clear을 강제하자

SpEL 표현식을 사용하면 DTO나 객체에서 메서드를 통해 값을 전달 받을 수 있습니다.

테스트

간단하게 실행 결과만 볼 수 있게 설정했습니다.

import click.porito.modular_travel.place.model.GooglePlaceCacheEntity;
import jakarta.persistence.EntityManager;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Nested;
import org.junit.jupiter.api.Test;
import org.locationtech.jts.geom.Coordinate;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.Point;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.test.context.ActiveProfiles;

import java.time.OffsetDateTime;
import java.util.UUID;

@ActiveProfiles("test")
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@DataJpaTest
class GooglePlaceCacheRepositoryTest {

    @Autowired
    private GooglePlaceCacheRepository googlePlaceCacheRepository;

    @Autowired
    private EntityManager entityManager;


    @Nested
    class MergeTest{

        @Test
        @DisplayName("First Merge Test")
        public void firstMerge() {
            //given
            String uniqueId = UUID.randomUUID().toString();
            GeometryFactory geometryFactory = new GeometryFactory();
            Point point = geometryFactory.createPoint(new Coordinate(140.503039, 70.669209699999996));
            OffsetDateTime baseTime = OffsetDateTime.now();

            //when
            googlePlaceCacheRepository.merge(new GooglePlaceCacheEntity(uniqueId, point));

            //then
            GooglePlaceCacheEntity entity = googlePlaceCacheRepository.findById(uniqueId).orElseThrow();
            Assertions.assertEquals(uniqueId, entity.getGooglePlaceId());
            Assertions.assertTrue(entity.getLocation().equalsExact(point));
            Assertions.assertTrue(entity.getUpdatedAt().isAfter(baseTime));
        }

        @Test
        @DisplayName("Merge With Existing Id Test")
        public void mergeWithExistingId(){
            //given
            String uniqueId = UUID.randomUUID().toString();
            GeometryFactory geometryFactory = new GeometryFactory();
            Point point = geometryFactory.createPoint(new Coordinate(140.503039, 70.669209699999996));
            googlePlaceCacheRepository.merge(new GooglePlaceCacheEntity(uniqueId, point));

            var originalEntity = googlePlaceCacheRepository.findById(uniqueId).orElseThrow();
            Point originalPoint = originalEntity.getLocation();
            OffsetDateTime originalUpdatedAt = originalEntity.getUpdatedAt();


            Point newPoint = geometryFactory.createPoint(new Coordinate(150.503039, 80.669209699999996));
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
            }
            //when
            googlePlaceCacheRepository.merge(new GooglePlaceCacheEntity(uniqueId, newPoint));

            //then
            GooglePlaceCacheEntity entity = googlePlaceCacheRepository.findById(uniqueId).orElseThrow();
            Assertions.assertEquals(uniqueId, entity.getGooglePlaceId());
            Assertions.assertTrue(entity.getLocation().equalsExact(newPoint));
            Assertions.assertTrue(entity.getUpdatedAt().isAfter(originalEntity.getUpdatedAt()));

        }


    }

}

주의해야하는 점

• @Modifying(clearAutomatically = true)을 주지 않으면, 테스트에 실패합니다.
• Postgreql에서는 한개의 트랜잭션 안에서 now()값은 모두 같습니다. 진짜 시간을 반영하고 싶으면 clock_timestamp()을 사용해야합니다.