SpringBoot 수업 정리

SpringBoot와 DB연동

기능 설계

불러온 데이터 DB에 insert() 해보기

Architecture 설명

전체 인프라 아키텍처

API

HospitalDao insert()기능 구현

package com.springboot.hello.domain.dao;

import com.springboot.hello.domain.Hospital;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;


@Component
public class HospitalDao {

    private final JdbcTemplate jdbcTemplate;

    public HospitalDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 11만건의 데이터를 List<Hospital>에서 Hospital로 꺼내어 insert()
    public void add(Hospital hospital) {
        String sql = "insert into `testdb`.`nation_wide_hospitals` (`id`, `open_service_name`, `open_local_government_code`,\n" +
                "`management_number`, `license_date`, `business_status`, `business_status_code`, `phone`, `full_address`, `road_name_address`, `hospital_name`,\n" +
                "`business_type_name`, `healthcare_provider_count`, `patient_room_count`, `total_number_of_beds`,\n" +
                "`total_area_size`) " +
                "values (?, ?, ?, ?, ?, ?, ?,\n" +
                "?, ?, ?, ?, ?, ?, ?, ?, ?)\n" +
                " on duplicate key update `id`=?;";
        this.jdbcTemplate.update(sql, hospital.getId(), hospital.getOpenServiceName(), hospital.getOpenLocalGovernmentCode(),
                hospital.getManagementNumber(), hospital.getLicenseDate(), hospital.getBusinessStatus(), hospital.getBusinessStatusCode(), hospital.getPhone(), hospital.getFullAddress(), hospital.getRoadNameAddress(), hospital.getHospitalName(),
                hospital.getBusinessTypeName(), hospital.getHealthcareProviderCnt(), hospital.getPatientRoomCnt(), hospital.getTotalNumberOfBeds(), hospital.getTotalAreaSize(), hospital.getId());
    }
}
Java
복사

HospitalParserTest add()메서드 테스트 코드 추가

@Test
@DisplayName("Hospital이 insert가 잘 되는지")
void add() {
    HospitalParser hp = new HospitalParser();  // 껍데기 (데이터 존재❌)
    Hospital hospital = hp.parse(line1);
    hospitalDao.add(hospital);
}
Java
복사

@SpringBootApplication @ComponentScan

@Component 가 달린 클래스를 Bean으로 등록해주어 HospitalDao는 Factory가 없어도 DI를 적용할 수 있게 됨

 실행 결과

 NullPointException 이 뜬다면? (아래의 경우를 확인해보아야 함)

national_wide_hospital 테이블 수정하기

반드시 테이블 ALTER 하기 전 사용하던 DB를 백업하는 습관 가지기

데이터 입력 과정 중 정해진 데이터의 크기에 맞지 않는 데이터들이 존재하여 아래와 같이 테이블 수정

DB 테이블 명세서

getCount(), deleteAll() 구현하기

package com.springboot.hello.domain.dao;

import com.springboot.hello.domain.Hospital;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;


@Component
public class HospitalDao {

    private final JdbcTemplate jdbcTemplate;

    public HospitalDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 11만건의 데이터를 List<Hospital>에서 Hospital로 꺼내어 insert()
    public void add(Hospital hospital) {
        String sql = "insert into `testdb`.`nation_wide_hospitals` (`id`, `open_service_name`, `open_local_government_code`,\n" +
                "`management_number`, `license_date`, `business_status`, `business_status_code`, `phone`, `full_address`, `road_name_address`, `hospital_name`,\n" +
                "`business_type_name`, `healthcare_provider_count`, `patient_room_count`, `total_number_of_beds`,\n" +
                "`total_area_size`) " +
                "values (?, ?, ?, ?, ?, ?, ?,\n" +
                "?, ?, ?, ?, ?, ?, ?, ?, ?)\n" +
                " on duplicate key update `id`=?;";
        this.jdbcTemplate.update(sql, hospital.getId(), hospital.getOpenServiceName(), hospital.getOpenLocalGovernmentCode(),
                hospital.getManagementNumber(), hospital.getLicenseDate(), hospital.getBusinessStatus(), hospital.getBusinessStatusCode(), hospital.getPhone(), hospital.getFullAddress(), hospital.getRoadNameAddress(), hospital.getHospitalName(),
                hospital.getBusinessTypeName(), hospital.getHealthcareProviderCnt(), hospital.getPatientRoomCnt(), hospital.getTotalNumberOfBeds(), hospital.getTotalAreaSize(), hospital.getId());
    }

    public Hospital findById(String sId) {
        String sql = "select * from `testdb`.`nation_wide_hospitals` where `id`=?";
        RowMapper<Hospital> rowMapper = new RowMapper<Hospital>() {
            @Override
            public Hospital mapRow(ResultSet rs, int rowNum) throws SQLException {
                Hospital hospital = new Hospital(rs.getInt("id"), rs.getString("open_service_name"), rs.getInt("open_local_government_code"),
                        rs.getString("management_number"), rs.getObject("license_date", LocalDateTime.class), rs.getInt("business_status"),  rs.getInt("business_status_code"),  rs.getString("phone"),  rs.getString("full_address"),  rs.getString("road_name_address"),
                        rs.getString("hospital_name"),  rs.getString("business_type_name"),  rs.getInt("healthcare_provider_count"),  rs.getInt("patient_room_cnt"), rs.getInt("total_number_of_beds"), rs.getFloat("total_area_size"));
                return hospital;
            }
        };

        return this.jdbcTemplate.queryForObject(sql, rowMapper, sId);
    }

    public int deleteAll() {
        String sql = "delete from `testdb`.`nation_wide_hospitals`;";
        return this.jdbcTemplate.update(sql);
    }

    public int getCount() {
        String sql = "select count(id) from `testdb`.`nation_wide_hospitals`;";
        return this.jdbcTemplate.queryForObject(sql, Integer.class);
    }
}
Java
복사

package com.springboot.hello.parser;

import com.springboot.hello.domain.Hospital;
import com.springboot.hello.domain.dao.HospitalDao;
import org.junit.jupiter.api.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.annotation.Bean;

import java.io.IOException;
import java.time.LocalDateTime;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
class HospitalParserTest {

    String line1 = "\"1\",\"의원\",\"01_01_02_P\",\"3620000\",\"PHMA119993620020041100004\",\"19990612\",\"\",\"01\",\"영업/정상\",\"13\",\"영업중\",\"\",\"\",\"\",\"\",\"062-515-2875\",\"\",\"500881\",\"광주광역시 북구 풍향동 565번지 4호 3층\",\"광주광역시 북구 동문대로 24, 3층 (풍향동)\",\"61205\",\"효치과의원\",\"20211115113642\",\"U\",\"2021-11-17 02:40:00.0\",\"치과의원\",\"192630.735112\",\"185314.617632\",\"치과의원\",\"1\",\"0\",\"0\",\"52.29\",\"401\",\"치과\",\"\",\"\",\"\",\"0\",\"0\",\"\",\"\",\"0\",\"\",";
    String line2 = "\"2\",\"의원\",\"01_01_02_P\",\"3620000\",\"PHMA119993620020041100005\",\"19990707\",\"\",\"01\",\"영업/정상\",\"13\",\"영업중\",\"\",\"\",\"\",\"\",\"062-574-2802\",\"\",\"500867\",\"광주광역시 북구 일곡동 821번지 1호 2층\",\"광주광역시 북구 설죽로 518, 2층 (일곡동)\",\"61041\",\"일곡부부치과의원\",\"20170905183213\",\"I\",\"2018-08-31  11:59:59 PM\",\"치과의원\",\"190646.777107\",\"189589.427851\",\"치과의원\",\"2\",\"0\",\"0\",\"200\",\"401\",\"치과\",\"\",\"\",\"\",\"0\",\"0\",\"\",\"\",\"0\",\"\",";

    @Autowired
    ReadLineContext<Hospital> hospitalReadLineContext;

    @Autowired
    HospitalDao hospitalDao;


    @Test
    @Order(3)
    @DisplayName("Hospital이 insert가 잘 되는지")
    void add() {
        HospitalParser hp = new HospitalParser();
        Hospital hospital = hp.parse(line1);
        Hospital hospital2 = hp.parse(line2);
        hospitalDao.add(hospital);
        hospitalDao.add(hospital2);
        assertEquals(2, hospitalDao.getCount());
    }

    @Test
    @Order(4)
    @DisplayName("병의원 데이터수 세기")
    void getCount() {
        System.out.printf("전국 병의원 데이터 수 : %d", hospitalDao.getCount());
        assertEquals(2, hospitalDao.getCount());
    }

    @Test
    @Order(5)
    @DisplayName("모든 데이터 삭제 테스트")
    void deleteAll() {
        hospitalDao.deleteAll();
        assertEquals(0, hospitalDao.getCount());
    }

    @Test
    @Order(1)
    @DisplayName("10만건 이상 데이터 파싱 테스트")
    void onHundredThousandRows() throws IOException {
        String filename = "C:\\TECHIT\\fulldata_01_01_02_P_의원.csv";
        List<Hospital> hospitalList = hospitalReadLineContext.readLineParser(filename);
        System.out.printf("파싱된 데이터 개수 : ", hospitalList.size());
        assertTrue(hospitalList.size() > 1000);
        assertTrue(hospitalList.size() > 10000);
        /*for (int i = 0; i < 10; i++) {
            System.out.println(hospitalList.get(i).getHospitalName());
        }*/
        System.out.printf("파싱된 데이터 개수 : ", hospitalList.size());
    }

    @Test
    @Order(2)
    @DisplayName("csv 1줄 hospital로 잘 만드는지 테스트")
    void convertToHospital() {
        HospitalParser hp = new HospitalParser();
        Hospital hospital = hp.parse(line1);

        assertEquals(1, hospital.getId());  // 0
        assertEquals("의원", hospital.getOpenServiceName());  // 1
        assertEquals(3620000,hospital.getOpenLocalGovernmentCode());  // 3
        assertEquals("PHMA119993620020041100004",hospital.getManagementNumber());  // 4
        assertEquals(LocalDateTime.of(1999, 6, 12, 0, 0, 0), hospital.getLicenseDate()); // 5
        assertEquals(1, hospital.getBusinessStatus());  // 7
        assertEquals(13, hospital.getBusinessStatusCode());  // 9
        assertEquals("062-515-2875", hospital.getPhone());  // 15
        assertEquals("광주광역시 북구 풍향동 565번지 4호 3층", hospital.getFullAddress());  // 18
        assertEquals("광주광역시 북구 동문대로 24, 3층 (풍향동)", hospital.getRoadNameAddress());  // 19
        assertEquals("효치과의원", hospital.getHospitalName());  // 21
        assertEquals("치과의원", hospital.getBusinessTypeName());  // 25
        assertEquals(1, hospital.getHealthcareProviderCnt());  // 29
        assertEquals(0, hospital.getPatientRoomCnt());  // 30
        assertEquals(0, hospital.getTotalNumberOfBeds());  // 31
        assertEquals(52.29f, hospital.getTotalAreaSize());  // 32
    }
}
Java
복사

 실행 결과

selectById() 구현하기

public Hospital selectById(String sId) {
    String sql = "select * from `testdb`.`nation_wide_hospitals` where `id`=?";
    RowMapper<Hospital> rowMapper = new RowMapper<Hospital>() {
        @Override
        public Hospital mapRow(ResultSet rs, int rowNum) throws SQLException {
            Hospital hospital = new Hospital(rs.getInt("id"), rs.getString("open_service_name"), rs.getInt("open_local_government_code"),
                    rs.getString("management_number"), rs.getObject("license_date", LocalDateTime.class), rs.getInt("business_status"),  rs.getInt("business_status_code"),  rs.getString("phone"),  rs.getString("full_address"),  rs.getString("road_name_address"),
                    rs.getString("hospital_name"),  rs.getString("business_type_name"),  rs.getInt("healthcare_provider_count"),  rs.getInt("patient_room_count"), rs.getInt("total_number_of_beds"), rs.getFloat("total_area_size"));
            return hospital;
        }
    };

    return this.jdbcTemplate.queryForObject(sql, rowMapper, sId);
}
Java
복사

@Test
@DisplayName("찾고자 하는 병의원 데이터가 잘나오는지")
void selectById() {
    Hospital hospital = hospitalDao.selectById("1");

    assertEquals(1, hospital.getId());
    assertEquals("의원", hospital.getOpenServiceName());
    assertEquals(3620000, hospital.getOpenLocalGovernmentCode());
    assertEquals("PHMA119993620020041100004", hospital.getManagementNumber());
    assertEquals(LocalDateTime.of(1999, 06, 12, 0, 0, 0), hospital.getLicenseDate());
    assertEquals(1, hospital.getBusinessStatus());
    assertEquals(13, hospital.getBusinessStatusCode());
    assertEquals("062-515-2875", hospital.getPhone());
    assertEquals("광주광역시 북구 풍향동 565번지 4호 3층", hospital.getFullAddress());
    assertEquals("광주광역시 북구 동문대로 24, 3층 (풍향동)", hospital.getRoadNameAddress());
    assertEquals("효치과의원", hospital.getHospitalName());
    assertEquals("치과의원", hospital.getBusinessTypeName());
    assertEquals(1, hospital.getHealthcareProviderCnt());
    assertEquals(0, hospital.getPatientRoomCnt());
    assertEquals(0, hospital.getTotalNumberOfBeds());
    assertEquals(52.29f, hospital.getTotalAreaSize());
}
Java
복사

 실행 결과

selectById() Refactoring

RowMapper<Hospital> rowMapper = (rs, rowNum) -> {
  Hospital hospital = new Hospital();

  hospital.setId(rs.getInt("id"));
  hospital.setOpenServiceName(rs.getString("open_service_name"));
  hospital.setOpenLocalGovernmentCode(rs.getInt("open_local_government_code"));
  hospital.setManagementNumber(rs.getString("management_number"));
  hospital.setLicenseDate(rs.getTimestamp("license_date").toLocalDateTime());
  hospital.setBusinessStatus(rs.getInt("business_status"));
  hospital.setBusinessStatusCode(rs.getInt("business_status_code"));
  hospital.setPhone(rs.getString("phone"));
  hospital.setFullAddress(rs.getString("full_address"));
  hospital.setRoadNameAddress(rs.getString("road_name_address"));
  hospital.setHospitalName(rs.getString("hospital_name"));
  hospital.setBusinessTypeName(rs.getString("business_type_name"));
  hospital.setHealthcareProviderCnt(rs.getInt("healthcare_provider_count"));
  hospital.setPatientRoomCnt(rs.getInt("patient_room_count"));
  hospital.setTotalNumberOfBeds(rs.getInt("total_number_of_beds"));
  hospital.setTotalAreaSize(rs.getFloat("total_area_size"));
  return hospital;
};


public Hospital selectById(int sId) {
    String sql = "select * from `testdb`.`nation_wide_hospitals` where `id`=?";

    return this.jdbcTemplate.queryForObject(sql, rowMapper, sId);
}
Java
복사

@Test
@Order(4)
@DisplayName("찾고자 하는 병의원 데이터가 잘나오는지")
void selectById() {
    HospitalParser hp = new HospitalParser();
    Hospital hospital = hp.parse(line1);
    Hospital selectedHospital = hospitalDao.selectById(hospital.getId());
    assertEquals(selectedHospital.getId(), hospital.getId());
    assertEquals(selectedHospital.getOpenServiceName(), hospital.getOpenServiceName());

    assertEquals(selectedHospital.getOpenLocalGovernmentCode(),hospital.getOpenLocalGovernmentCode());
    assertEquals(selectedHospital.getManagementNumber(),hospital.getManagementNumber());
    assertEquals(selectedHospital.getBusinessStatus(), hospital.getBusinessStatus()); // idx:7
    assertEquals(selectedHospital.getBusinessStatusCode(), hospital.getBusinessStatusCode());

    assertTrue(selectedHospital.getLicenseDate().isEqual(hospital.getLicenseDate()));

    assertEquals(selectedHospital.getPhone(), hospital.getPhone());
    assertEquals(selectedHospital.getFullAddress(), hospital.getFullAddress());
    assertEquals(selectedHospital.getRoadNameAddress(), hospital.getRoadNameAddress());
    assertEquals(selectedHospital.getHospitalName(), hospital.getHospitalName());
    assertEquals(selectedHospital.getBusinessTypeName(), hospital.getBusinessTypeName());
    assertEquals(selectedHospital.getHealthcareProviderCnt(), hospital.getHealthcareProviderCnt());
    assertEquals(selectedHospital.getPatientRoomCnt(), hospital.getPatientRoomCnt());
    assertEquals(selectedHospital.getTotalNumberOfBeds(), hospital.getTotalNumberOfBeds());
    assertEquals(selectedHospital.getTotalAreaSize(), hospital.getTotalAreaSize());
}
Java
복사

 실행 결과

내일 할 내용

selectLimit()을 이용한 페이징 처리

getAll() 기능 구현

Controller() aksemfrl select, add

Docker