공공데이터 MariaDB이용하여 DB 적재하기(xml파일) - Gradle

build.gradle

plugins {
	id 'java'
	id 'org.springframework.boot' version '3.1.0'
	id 'io.spring.dependency-management' version '1.1.0'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '17'

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation group: 'com.googlecode.json-simple', name: 'json-simple', version: '1.1.1'
	implementation 'org.json:json:20220924'
	runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.7.4'

	runtimeOnly 'com.mysql:mysql-connector-j'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	annotationProcessor 'org.projectlombok:lombok'
	implementation group: 'org.jdom', name: 'jdom', version: '2.0.0'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testImplementation group: 'com.h2database', name: 'h2', version: '1.3.148'
	compileOnly group: 'javax.servlet', name: 'javax.servlet-api', version: '3.0.1'
	compileOnly group: 'javax.servlet.jsp', name: 'javax.servlet.jsp-api', version: '2.3.1'


}

tasks.named('test') {
	useJUnitPlatform()
}

 

 

 

---

application.properties

//spring.datasource.url=jdbc:maraidb://localhost:3306/<<데이터베이스이름>>
spring.datasource.url=jdbc:mariadb://localhost:3306/mydb

//spring.datasource.username=<<사용자 이름>>
spring.datasource.username=myadmin

//spring.datasource.password=<<비밀번호>>
spring.datasource.password=1234

spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
server.error.whitelabel.enabled=false
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.show-sql=true
spring.main.allow-bean-definition-overriding=true
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
logging.level.org.hibernate.type.descriptor.sql=DEBUG
logging.level.org.hibernate.SQL=DEBUG

//공공데이터 서비스키
api.serviceKey=<<공공데이터 서비스키>>

 

org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [org.hibernate.dialect.MySQL5InnoDBDialect] 오류 발생 해결

 

https://rainhyeon.tistory.com/4

org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [org.hibernate.dialect.MySQL5InnoDBD

 

 

---

HomeController

package com.example.hospital.rest;

import com.example.hospital.model.Emergency;
import com.example.hospital.repository.EmergencyRepository;
import org.jdom2.Document;
import org.jdom2.Element;
import org.jdom2.input.SAXBuilder;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;


import java.net.HttpURLConnection;
import java.net.URL;
import java.util.Date;
import java.util.List;

@Controller
public class HomeController {

    @Autowired
    private EmergencyRepository emergencyRepository;

    @Value("${api.serviceKey}")
    private String serviceKey;

    @RequestMapping("/api")
    public String load_save(Model model) throws Exception {


        StringBuffer sb = new StringBuffer("https://apis.data.go.kr/B552657/ErmctInfoInqireService/getEmrrmRltmUsefulSckbdInfoInqire?");
        sb.append("serviceKey=");
        sb.append(serviceKey);
        sb.append("&pageNo=1");
        sb.append("&numOfRows=1000");

        URL url = new URL(sb.toString());

        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setRequestProperty("Content-Type", "application/xml");
        conn.connect();


        // 응답으로 받은 XML 데이터 파싱 (JDOM 라이브러리)
        SAXBuilder builder = new SAXBuilder();
        Document document = builder.build(conn.getInputStream());

        Element root = document.getRootElement();
        Element body = root.getChild("body");
        Element items = body.getChild("items");
        List<Element> item_list = items.getChildren("item");


        for (Element item : item_list) {

            String hpid = item.getChildText("hpid"); //기관코드
            String hvidate = item.getChildText("hvidate");
            String dutyname = item.getChildText("dutyName"); //병원명
            Integer hvec = Integer.parseInt(item.getChildText("hvec") == null ? "0" : item.getChildText("hvec")); //일반 - int
            Integer hvoc = Integer.parseInt(item.getChildText("hvoc") == null ? "0" : item.getChildText("hvoc")); //내과중환자실 Long
            Integer hvcc = Integer.parseInt(item.getChildText("hvcc") == null ? "0" : item.getChildText("hvcc")); //내과중환자실 Long
            Integer hvncc = Integer.parseInt(item.getChildText("hvncc") == null ? "0" : item.getChildText("hvncc")); //내과중환자실 Long
            Integer hvccc = Integer.parseInt(item.getChildText("hvccc") == null ? "0" : item.getChildText("hvccc")); //내과중환자실 Long
            Integer hvicc = Integer.parseInt(item.getChildText("hvicc") == null ? "0" : item.getChildText("hvicc"));
            Integer hvgc = Integer.parseInt(item.getChildText("hvgc") == null ? "0" : item.getChildText("hvgc")); //내과중환자실 Long
            String hvctayn = item.getChildText("hvctayn") == null ? "0" : item.getChildText("hvctayn"); //내과중환자실 Long
            String hvmriayn = item.getChildText("hvmriayn") == null ? "N1" : item.getChildText("hvmriayn");
            String hvangioayn = item.getChildText("hvangioayn") == null ? "N1" : item.getChildText("hvangioayn");
            String hvventiayn = item.getChildText("hvventiayn") == null ? "N1" : item.getChildText("hvventiayn");
            String hvincuayn = item.getChildText("hvincuayn") == null ? "N1" : item.getChildText("hvincuayn");
            String hvamyn = item.getChildText("hvamyn") == null ? "N1" : item.getChildText("hvamyn");

            Integer hv2 = Integer.parseInt(item.getChildText("hv2") == null ? "0" : item.getChildText("hv2")); //내과중환자실 Long
            Integer hv3 = Integer.parseInt(item.getChildText("hv3") == null ? "0" : item.getChildText("hv3"));
            Integer hv4 = Integer.parseInt(item.getChildText("hv4") == null ? "0" : item.getChildText("hv4"));
            String hv5 = item.getChildText("hv5") == null ? "N1" : item.getChildText("hv5");
            Integer hv6 = Integer.parseInt(item.getChildText("hv6") == null ? "0" : item.getChildText("hv6"));
            Integer hv8 = Integer.parseInt(item.getChildText("hv8") == null ? "0" : item.getChildText("hv8"));
            Integer hv9 = Integer.parseInt(item.getChildText("hv9") == null ? "0" : item.getChildText("hv9"));
            String hv10 = item.getChildText("hv10") == null ? "N1" : item.getChildText("hv10"); //소아 유무
            String hv11 = item.getChildText("hv11") == null ? "N1" : item.getChildText("hv11");
            Integer hv13 = Integer.parseInt(item.getChildText("hv13") == null ? "0" : item.getChildText("hv13"));
            Integer hv14 = Integer.parseInt(item.getChildText("hv14") == null ? "0" : item.getChildText("hv14"));
            Integer hv15 = Integer.parseInt(item.getChildText("hv15") == null ? "0" : item.getChildText("hv15"));
            Integer hv16 = Integer.parseInt(item.getChildText("hv16") == null ? "0" : item.getChildText("hv16"));
            Integer hv17 = Integer.parseInt(item.getChildText("hv17") == null ? "0" : item.getChildText("hv17"));
            Integer hv18 = Integer.parseInt(item.getChildText("hv18") == null ? "0" : item.getChildText("hv18"));
            Integer hv19 = Integer.parseInt(item.getChildText("hv19") == null ? "0" : item.getChildText("hv19"));
            Integer hv21 = Integer.parseInt(item.getChildText("hv21") == null ? "0" : item.getChildText("hv21"));
            Integer hv22 = Integer.parseInt(item.getChildText("hv22") == null ? "0" : item.getChildText("hv22"));
            Integer hv23 = Integer.parseInt(item.getChildText("hv23") == null ? "0" : item.getChildText("hv23"));
            Integer hv24 = Integer.parseInt(item.getChildText("hv24") == null ? "0" : item.getChildText("hv24"));
            Integer hv25 = Integer.parseInt(item.getChildText("hv25") == null ? "0" : item.getChildText("hv25"));
            Integer hv26 = Integer.parseInt(item.getChildText("hv26") == null ? "0" : item.getChildText("hv26"));
            Integer hv29 = Integer.parseInt(item.getChildText("hv29") == null ? "0" : item.getChildText("hv29"));
            Integer hv30 = Integer.parseInt(item.getChildText("hv30") == null ? "0" : item.getChildText("hv30"));
            Integer hv31 = Integer.parseInt(item.getChildText("hv31") == null ? "0" : item.getChildText("hv31"));
            Integer hv32 = Integer.parseInt(item.getChildText("hv32") == null ? "0" : item.getChildText("hv32"));
            Integer hv33 = Integer.parseInt(item.getChildText("hv33") == null ? "0" : item.getChildText("hv33"));
            Integer hv36 = Integer.parseInt(item.getChildText("hv36") == null ? "0" : item.getChildText("hv36"));
            Integer hv37 = Integer.parseInt(item.getChildText("hv37") == null ? "0" : item.getChildText("hv37"));
            Integer hv38 = Integer.parseInt(item.getChildText("hv38") == null ? "0" : item.getChildText("hv38"));
            Integer hv40 = Integer.parseInt(item.getChildText("hv40") == null ? "0" : item.getChildText("hv40"));
            String dutytell3 = item.getChildText("dutytell3") == null ? "N1" : item.getChildText("dutytell3");


            Emergency em = new Emergency(hpid, hvidate, dutyname, hvec, hvoc, hvcc, hvncc, hvccc, hvicc, hvgc, hvctayn, hvmriayn, hvangioayn, hvventiayn, hvincuayn
                    , hvamyn, hv2, hv3, hv4, hv5, hv6, hv8, hv9, hv10, hv11, hv13, hv14, hv15, hv16, hv17, hv18, hv19, hv21, hv22
                    , hv23, hv24, hv25, hv26, hv29, hv30, hv31, hv32, hv33, hv36, hv37, hv38, hv40, dutytell3);
            System.out.println(em);
            emergencyRepository.save(em);
        }

        return "index";
    }
}

 

아래 xml 태그를 보면서 비교!

• <response>를 가져오고
• 그 자식인 <body>를 가져오고
• 그자식인< items>를 가져오고
• 그 자식인 <item>을 가지고온다.
• <item>의 안에 있는 자식을 여러개 가져오기 위해 for문을 돌린다(item_list를 모두 가져온다)
• <item>의 자식인 <hpid>태그 안의 text를 가지고온다.
• <item>의 자식인 <hvidate>태그 안의 text를 가지고온다.
• <item>의 자식인 <dutyName>태그 안의 text를 가지고온다.

공공데이터(xml)

 

---

Emergency(Entity)

package com.example.hospital.model;

import jakarta.persistence.Entity;
import lombok.*;
import jakarta.persistence.Id;

@NoArgsConstructor //파라미터 없는 생성자
@AllArgsConstructor //모든 플드를 파라미터로 가지는 생성자
@Entity
@Data
public class Emergency {

    @Id
    private String hpid; //기관코드
    //입력일시
    private String hvidate;
    private String dutyName; //병원명
    //일반-int
    private Integer hvec;
    //수술실-int
    private Integer hvoc;
    //신경과-int
    private Integer hvcc;
    //신생아-int
    private Integer hvncc;
    //흉부외과
    private Integer hvccc;
    //중환자실-일반
    private Integer hvicc;
    //입원실-일반
    private Integer hvgc;
    //CT가용(가/부)
    private String hvctayn;
    //MRI가용
    private String hvmriayn;
    //혈관촬영기가용
    private String hvangioayn;
    //인공호흡기가용
    private String hvventiayn;
    //인큐베이터가용
    private String hvincuayn;
    //구급차가용
    private String hvamyn;
    private Integer hv2; //내과중환자실
    private Integer hv3; //외과중환자실
    //외과입원실(정형외과)
    private Integer hv4;
    private String hv5; //신경과입원실 - 유무
    //신경와과
    private Integer hv6;
    //화상
    private Integer hv8;
    //외상
    private Integer hv9;
    private String hv10; //소아(VENTI) -유무
    //인큐베이터-유무
    private String hv11;
    //격리진료구역(음압격리병상)
    private Integer hv13;
    //일반격리병상
    private Integer hv14;
    //소아음압격리
    private Integer hv15;
    //소아일반격리
    private Integer hv16;
    //응급전용, 중환자실, 음압격리
    private Integer hv17;
    //응급전용, 중환자실, 일반격리
    private Integer hv18;
    //응급전용, 입원실, 음압격리
    private Integer hv19;
    //응급전용, 입원실, 일반격리
    private Integer hv21;
    //감염병 전담병상 중환자실
    private Integer hv22;
    //감염병 전담병상 중환자실 내 음압격리병상
    private Integer hv23;
    //[감염] 중증 병상
    private Integer hv24;
    //[감염] 준-중증 병상
    private Integer hv25;
    //[감염] 중등증 병상
    private Integer hv26;
    //응급실 음압 격리 병상
    private Integer hv29;
    //응급실 일반 격리 병상
    private Integer hv30;
    //[응급전용] 중환자실
    private Integer hv31;
    //[중환자실] 소아
    private Integer hv32;
    //[응급전용] 소아중환자실
    private Integer hv33;
    //[응급전용] 입원실
    private Integer hv36;
    //[응급전용] 소아입원실
    private Integer hv37;
    //입원실] 외상전용
    private Integer hv38;
    //[기타] 외상전용 수술실
    private Integer hv40;
    //응급실전화
    private String dutytel3;
}

 

org.hibernate.InstantiationException: No default constructor for entity 오류 해결

오류 내용

• Entity에 기본 생성자 없다는 오류
• @AllArgsConstructor : 필드를 매개변수로 가지는 생성자를 작성해도 오류 발생!

문제 해결

• @NoArgsConstructor : (매개변수가 없는) 생성자는 무조건 작성해야함!

 

 

---

EmergencyRepository(Repository)

package com.example.hospital.repository;

import com.example.hospital.model.Emergency;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmergencyRepository extends JpaRepository<Emergency, Integer>{
}

- 이것만 있어도 Controller에서 repository.save() 자동으로 됨.

 

 

---

HospitalApplication(Application)

package com.example.hospital;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class HospitalApplication {


	public static void main(String[] args) {

		SpringApplication.run(HospitalApplication.class, args);

	}

}

 

 

 

---

파일 순서

 

 

 

---

결과