공공API 데이터 파싱 후 db

검색시마다 파싱 후 db에 입력하도록하며, seq값이 unique이기에 중복된 경우 ignore처리

검색의 경우 ajax를 통하여 hear에 있는 검색창의 값을 controller에 넘겨서 처리하려 했으나, api서버가 자주 다운되는 바람에

지속적으로 데이터를 가져올 수 없어 미리 csv파일을 통하여 입력하는 방향으로 수정

 

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.btl.findjob.mapper.CompanyInfoMapper">

    <!--검색시 데이터 체크해서 없는데이터 넣기 위해
           ignore로 중복시 무시하도록 처리한다.
    -->
    <insert id="insertInfo">
        insert ignore into govdata (wkplNm, dataCrtYm, seq, wkplRoadNmDtlAddr)
        values (#{wkplNm}, #{dataCrtYm}, #{seq}, #{wkplRoadNmDtlAddr})
    </insert>


    <select id="companyList" resultType="com.btl.findjob.model.GovDataDTO">
        select *
        from govdata
    </select>
</mapper>

 

파싱을 위한 코드

package com.btl.findjob.temp;
/* Java 샘플 코드 */


import com.btl.findjob.model.GovDataDTO;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.io.BufferedReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class CompanyInfoExplorer {

    //tag값의 정보를 가져오는 메소드
    private static String getTagValue(String tag, Element eElement) {
        NodeList nList = eElement.getElementsByTagName(tag).item(0).getChildNodes();
        Node nValue = (Node) nList.item(0);
        if (nValue == null) {
            return null;
        }
        return nValue.getNodeValue();
    }

    public List<GovDataDTO> parsingData(String searchName) throws IOException, ParserConfigurationException, SAXException {

        List<GovDataDTO> list = new ArrayList<GovDataDTO>();
        // "&pageNo=100&numOfRows=100"  갯수와 로우 100까지는 테스트 10000은 404에러
        String str = "";    //return을 위해서
        String parsingUrl = "";//Parsing할 URL
        String urlBuilder = "http://apis.data.go.kr/B552015/NpsBplcInfoInqireService/getBassInfoSearch" + "?" + URLEncoder.encode("ServiceKey", "UTF-8") + "=" + "B204rJXsKzDzIedVMlL4h45QoUbhGH0fct7UD3vgb9i88%2BtSHyCcy8bsdJ7BPku6Zekn59ErheqrgdSX31ZiRg%3D%3D" + /*Service Key*/
                "&pageNo=100" + "&" + URLEncoder.encode("wkpl_nm", "UTF-8") + "=" + URLEncoder.encode(searchName, "UTF-8");/*URL*//*사업장명*/
        URL url = new URL(urlBuilder);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setRequestMethod("GET");
        conn.setRequestProperty("Content-type", "application/json");
        System.out.println("Response code: " + conn.getResponseCode());
        BufferedReader rd;
        if (conn.getResponseCode() >= 200 && conn.getResponseCode() <= 300) {
            rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
        } else {
            rd = new BufferedReader(new InputStreamReader(conn.getErrorStream()));
        }
        StringBuilder sb = new StringBuilder();
        String line;
        while ((line = rd.readLine()) != null) {
            sb.append(line);
        }
        rd.close();
        conn.disconnect();
        System.out.println(sb.toString());

        parsingUrl = url.toString();
        System.out.println(parsingUrl);

        //페이지에 접근해줄 Document객체 생성
        //doc객체를 통해 파싱할 url의 요소를 읽어들인다.
        //doc.getDocumentElement().getNodeName()을 출력하면 위 xml의 최상위 태그를 가져온다.
        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
        Document doc = dBuilder.parse(parsingUrl);

        // root tag
        doc.getDocumentElement().normalize();
        System.out.println("Root element: " + doc.getDocumentElement().getNodeName()); // Root element: result

        //파싱할 데이터  tag에 접근하는데 리스트 수 확인
        NodeList nList = doc.getElementsByTagName("item");
        System.out.println("파싱할 리스트 수 : " + nList.getLength());//파링할 리스트 수

        for (int i = 0; i < nList.getLength(); i++) {
            Node nNode = nList.item(i);
            if (nNode.getNodeType() == Node.ELEMENT_NODE) {

                Element eElement = (Element) nNode;

                GovDataDTO dto = new GovDataDTO();

                System.out.println("=================================");
                System.out.println(getTagValue("wkplNm", eElement));//사업장명
                System.out.println(getTagValue("dataCrtYm", eElement));//자료생성년월
                System.out.println(getTagValue("seq", eElement));//seq번호
                System.out.println(getTagValue("wkplRoadNmDtlAddr", eElement));//사업장명

                dto.setWkplNm(getTagValue("wkplNm", eElement));//사업장명
                dto.setDataCrtYm(getTagValue("dataCrtYm", eElement));//자료생성년월
                dto.setSeq(getTagValue("seq", eElement));//seq번호
                dto.setWkplRoadNmDtlAddr(getTagValue("wkplRoadNmDtlAddr", eElement));//사업장명

                assert false;
                list.add(dto);
            }
        }

        return list;

    }
}

 

 

Controller

package com.btl.findjob.controller;

import com.btl.findjob.model.GovDataDTO;
import com.btl.findjob.service.CompanyInfoService;
import com.btl.findjob.temp.CompanyInfoExplorer;
import lombok.extern.log4j.Log4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.xml.sax.SAXException;

import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.util.List;

@Controller
@Log4j
public class CompanyInfoController {

    private CompanyInfoService companyInfoService;

    //생성자 주입
    @Autowired
    public CompanyInfoController(CompanyInfoService apiService) {
        this.companyInfoService = apiService;
    }

    //검색시 데이터가 없으면 db에 추가하도록 처리한다.
    @GetMapping("/searchList")
    public void searchInfo(Model model) throws ParserConfigurationException, SAXException, IOException {

        log.info("파싱 스타트 체크");

        CompanyInfoExplorer apiExplorer = new CompanyInfoExplorer();

        //파싱하여 리턴한 데이터 값들을 list에 담아주기 위해 사용
        List<GovDataDTO> list = apiExplorer.parsingData("(주)");

        //List에 담겨있는 정보들을 db에 넣기 위해서 사용
        for (GovDataDTO dataDTO : list) {

            companyInfoService.insertInfo(dataDTO);

        }

        model.addAttribute("companyList",companyInfoService.companyList());

        log.info("파싱 정보 입력끝");
    }

}

 

mapper

package com.btl.findjob.mapper;

import com.btl.findjob.model.GovDataDTO;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CompanyInfoMapper {

    void insertInfo(GovDataDTO govDataDTO);

    List<GovDataDTO> companyList();
}

 

service

package com.btl.findjob.service;

import com.btl.findjob.model.GovDataDTO;
import org.xml.sax.SAXException;

import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.util.List;

public interface CompanyInfoService {
    void insertInfo(GovDataDTO govDataDTO) throws IOException, ParserConfigurationException, SAXException;

    List<GovDataDTO> companyList();
}

 

serviceImple

package com.btl.findjob.service;

import com.btl.findjob.mapper.CompanyInfoMapper;
import com.btl.findjob.model.GovDataDTO;
import lombok.extern.log4j.Log4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@Log4j
public class CompanyInfoServiceImpl implements CompanyInfoService {

    private CompanyInfoMapper companyInfoMapper;

    //생성자 주입
    @Autowired
    public CompanyInfoServiceImpl(CompanyInfoMapper companyInfoMapper) {
        this.companyInfoMapper = companyInfoMapper;
    }

    @Override
    public void insertInfo(GovDataDTO govDataDTO) {

        companyInfoMapper.insertInfo(govDataDTO);
    }

    @Override
    public List<GovDataDTO> companyList() {
        return companyInfoMapper.companyList();
    }
}

 

jsp파일

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
  Created by IntelliJ IDEA.
  User: h
  Date: 19. 9. 2.
  Time: 오후 2:23
  To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8" %>

<%@ include file="includes/header.jsp" %>

<c:forEach items="${companyList}" var="company">
    <tr>
        <td>
            <c:out value="${company.wkplNm}"/>
        </td>
        <td>
            <c:out value="${company.dataCrtYm}"/>
        </td>
    </tr>
</c:forEach>




<%@ include file="includes/footer.jsp" %>