SQL 을 동적으로 삽입해서 NAVER Geocoding API 에 적용하기

CREATE TABLE p_hub (
   id UUID NOT NULL DEFAULT uuid_generate_v4(),
   name VARCHAR(100) NOT NULL,
   address VARCHAR(100) NOT NULL,
   latitude DOUBLE PRECISION NOT NULL,
   longitude DOUBLE PRECISION NOT NULL,
   PRIMARY KEY (id)
);

INSERT INTO p_hub(name, address) VALUES
('서울특별시 센터', '서울특별시 송파구 송파대로 55'),
('경기 북부 센터', '경기도 고양시 덕양구 권율대로 570'),
('경기 남부 센터', '경기도 이천시 덕평로 257-21'),
('부산광역시 센터', '부산 동구 중앙대로 206'),
('대구광역시 센터', '대구 북구 태평로 161'),
('인천광역시 센터', '인천 남동구 정각로 29'),

 

실제로 hub를 생성할 때 필요한 요청값을 잘 작성했다고 생각했지만 예상치 못한 오류가 발생했다.

 

 

바로 latitude, longitude 칼럼에 null 값이 들어가서 발생한 문제였다.

 

문제는 생각보다 간단했는데, Insert 할 때 위도, 경도를 넣어주지 않아서 발생한 문제였다...

 

하지만 address를 요청받으면 주소를 기반으로 naver api에서 위도, 경도를 찾아서 데이터를 넣어주는 구조로 기능을 구현했기 때문에 아무 생각 없이 될 것 같다고 판단했었다.

 

기본적으로 사용하려고 하는 data.sql은 정적인 SQL 스크립트를 실행하는 데 사용되기 때문에, Java 코드로 작성된 Naver api와의 연동은 불가능하다는 것을 알게되었다. 이를 해결하려면 데이터 초기화 시 Java 로직을 활용하는 방법을 사용해서 해결해보겠다.

 

우선 data.sql 은 사용하지 않을 것이므로 주석처리 해놓았다.

  sql:
    init:
      mode: always
      schema-locations: classpath:db/schema.sql
#      data-locations: classpath:db/data.sql

 

hub와 hub의 id를 참조하는 company 테이블을 만들어 줄 수 있는 schema.sql을 작성한다.

CREATE TABLE p_hub (
   id UUID NOT NULL DEFAULT uuid_generate_v4(),
   name VARCHAR(100) NOT NULL,
   address VARCHAR(100) NOT NULL,
   latitude DOUBLE PRECISION NOT NULL,
   longitude DOUBLE PRECISION NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE p_company (
   id UUID NOT NULL DEFAULT uuid_generate_v4(),
   hubId UUID NOT NULL,
   name VARCHAR(255) NOT NULL,
   address VARCHAR(255) NOT NULL,
   type VARCHAR(50) NOT NULL,
   username VARCHAR(50) NOT NULL,
   FOREIGN KEY (hubId) REFERENCES p_hub(id),
   PRIMARY KEY (id)
);

 

 

이제 hub의 address를 사용해서 위도, 경도를 가져올 수 있게 

double[] coordinates = geocodingService.getCoordinates(address);

 

동적으로 작성한 address를 기존의 geocodingService의 위도, 경도를 가져오는 코드에 활용하면

package com.eleven.logistics.hub.infrastructure.config;

import com.eleven.logistics.hub.infrastructure.service.GeocodingService;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

@Component
@RequiredArgsConstructor
public class DataInitializer implements CommandLineRunner {

    private final GeocodingService geocodingService;
    private final JdbcTemplate jdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        // p_hub 초기 데이터
        String[][] hubData = {
                {"서울특별시 센터", "서울특별시 송파구 송파대로 55"},
                {"경기 북부 센터", "경기도 고양시 덕양구 권율대로 570"},
                {"경기 남부 센터", "경기도 이천시 덕평로 257-21"},
                {"부산광역시 센터", "부산 동구 중앙대로 206"},
                {"대구광역시 센터", "대구 북구 태평로 161"},
                {"인천광역시 센터", "인천 남동구 정각로 29"},
                {"광주광역시 센터", "광주 서구 내방로 111"},
                {"대전광역시 센터", "대전 서구 둔산로 100"},
                {"울산광역시 센터", "울산 남구 중앙로 201"},
                {"세종특별자치시 센터", "세종특별자치시 한누리대로 2130"},
                {"강원특별자치도 센터", "강원특별자치도 춘천시 중앙로 1"},
                {"충청북도 센터", "충북 청주시 상당구 상당로 82"},
                {"충청남도 센터", "충남 홍성군 홍북읍 충남대로 21"},
                {"전북특별자치도 센터", "전북특별자치도 전주시 완산구 효자로 225"},
                {"전라남도 센터", "전남 무안군 삼향읍 오룡길 1"},
                {"경상북도 센터", "경북 안동시 풍천면 도청대로 455"},
                {"경상남도 센터", "경남 창원시 의창구 중앙대로 300"}
        };

        // hubId를 저장할 맵
        Map<String, UUID> hubIdMap = new HashMap<>();

        // p_hub 데이터 삽입
        for (String[] hub : hubData) {
            String name = hub[0];
            String address = hub[1];
            double[] coordinates = geocodingService.getCoordinates(address);

            if (coordinates != null && coordinates.length == 2) {
                double latitude = coordinates[0];
                double longitude = coordinates[1];

                String insertSql = "INSERT INTO p_hub (id, name, address, latitude, longitude) " +
                        "SELECT uuid_generate_v4(), ?, ?, ?, ? " +
                        "WHERE NOT EXISTS (SELECT 1 FROM p_hub WHERE name = ?) " +
                        "RETURNING id";
                UUID hubId = jdbcTemplate.queryForObject(insertSql, UUID.class, name, address, latitude, longitude, name);
                hubIdMap.put(name, hubId);
            }
        }

        // p_company 초기 데이터
        String[][] companyData = {
                {"육류 생산 업체", "서울특별시 송파구 송파대로 100", "PRODUCER_COMPANY", "user111", "서울특별시 센터"},
                {"가공 식품 공장", "경기도 고양시 덕양구 권율대로 600", "PRODUCER_COMPANY", "user111", "경기 북부 센터"},
                {"수산물 수령 업체", "부산 동구 중앙대로 250", "RECEIVER_COMPANY", "user1111", "부산광역시 센터"},
                {"농산물 생산 협동조합", "경기도 이천시 덕평로 300", "PRODUCER_COMPANY", "user111", "경기 남부 센터"},
                {"도매 유통 업체", "대구 북구 태평로 200", "RECEIVER_COMPANY", "user1111", "대구광역시 센터"},
                {"유제품 생산 공장", "인천 남동구 정각로 50", "PRODUCER_COMPANY", "user111", "인천광역시 센터"},
                {"물류 수령 센터", "광주 서구 내방로 150", "RECEIVER_COMPANY", "user1111", "광주광역시 센터"},
                {"곡물 가공 업체", "대전 서구 둔산로 120", "PRODUCER_COMPANY", "user111", "대전광역시 센터"},
                {"식자재 유통 업체", "울산 남구 중앙로 220", "RECEIVER_COMPANY", "user1111", "울산광역시 센터"},
                {"과일 생산 농장", "세종특별자치시 한누리대로 2150", "PRODUCER_COMPANY", "user111", "세종특별자치시 센터"},
                {"소매 수령 업체", "강원특별자치도 춘천시 중앙로 50", "RECEIVER_COMPANY", "user1111", "강원특별자치도 센터"},
                {"야채 가공 공장", "충북 청주시 상당구 상당로 100", "PRODUCER_COMPANY", "user111", "충청북도 센터"},
                {"식품 수령 창고", "충남 홍성군 홍북읍 충남대로 50", "RECEIVER_COMPANY", "user1111", "충청남도 센터"},
                {"수산물 가공 업체", "전북특별자치도 전주시 완산구 효자로 250", "PRODUCER_COMPANY", "user111", "전북특별자치도 센터"},
                {"도소매 유통 업체", "전남 무안군 삼향읍 오룡길 20", "RECEIVER_COMPANY", "user1111", "전라남도 센터"},
                {"축산물 생산 공장", "경북 안동시 풍천면 도청대로 500", "PRODUCER_COMPANY", "user111", "경상북도 센터"},
                {"물류 수령 업체", "경남 창원시 의창구 중앙대로 350", "RECEIVER_COMPANY", "user1111", "경상남도 센터"},
                {"과자 제조 업체", "서울특별시 송파구 송파대로 80", "PRODUCER_COMPANY", "user111", "서울특별시 센터"},
                {"마트 수령 센터", "경기도 고양시 덕양구 권율대로 580", "RECEIVER_COMPANY", "user1111", "경기 북부 센터"},
                {"음료 생산 공장", "부산 동구 중앙대로 230", "PRODUCER_COMPANY", "user111", "부산광역시 센터"},
                {"창고 수령 업체", "경기도 이천시 덕평로 280", "RECEIVER_COMPANY", "user1111", "경기 남부 센터"},
                {"빵 생산 업체", "대구 북구 태평로 180", "PRODUCER_COMPANY", "user111", "대구광역시 센터"},
                {"슈퍼마켓 수령", "인천 남동구 정각로 40", "RECEIVER_COMPANY", "user1111", "인천광역시 센터"},
                {"냉동식품 공장", "광주 서구 내방로 130", "PRODUCER_COMPANY", "user111", "광주광역시 센터"},
                {"식당 수령 업체", "대전 서구 둔산로 110", "RECEIVER_COMPANY", "user1111", "대전광역시 센터"},
                {"김치 제조 업체", "울산 남구 중앙로 210", "PRODUCER_COMPANY", "user111", "울산광역시 센터"},
                {"도매 수령 센터", "세종특별자치시 한누리대로 2140", "RECEIVER_COMPANY", "user1111", "세종특별자치시 센터"},
                {"간식 생산 공장", "강원특별자치도 �춘천시 중앙로 30", "PRODUCER_COMPANY", "user111", "강원특별자치도 센터"},
                {"유통 수령 업체", "충북 청주시 상당구 상당로 90", "RECEIVER_COMPANY", "user1111", "충청북도 센터"},
                {"해산물 가공 업체", "충남 홍성군 홍북읍 충남대로 30", "PRODUCER_COMPANY", "user111", "충청남도 센터"}
        };

        // p_company 데이터 삽입
        for (String[] company : companyData) {
            String name = company[0];
            String address = company[1];
            String type = company[2];
            String username = company[3];
            String hubName = company[4];
            UUID hubId = hubIdMap.get(hubName);

            if (hubId != null) {
                String insertSql = "INSERT INTO p_company (id, hubId, name, address, type, username) " +
                        "SELECT uuid_generate_v4(), ?, ?, ?, ?, ? " +
                        "WHERE NOT EXISTS (SELECT 1 FROM p_company WHERE name = ?)";
                jdbcTemplate.update(insertSql, hubId, name, address, type, username, name);
            } 
        }
    }
}

 

정상적으로 데이터가 적용된 것을 볼 수 있다.

 

처음에는 data.sql을 사용해도 위도, 경도를 자동으로 찾아주는 줄 알았지만, data.sql은 정적인 sql을 실행하는 데 사용되기 때문에 java코드에 작성된 geocodingService를 사용할 수 없다는 것을 알게 되었고, 이전 스프링 강의에서 학습했던 jdbcTemplate를 활용할 수 있는 기회가 생겨서 좋은 경험이었다.