머털&보약의 기록

IT/development

[Rest API]계층구조 Rest API sample(feat. springBoot)

알 수 없는 사용자 2023. 10. 10.

목차

Oracle의 HR schema의 데이터로 계층구조로 Rest API를 만들 수 있다.

Rest API 연습 하기 좋다.

[Rest API]계층구조 Rest API sample(feat. springBoot)
oracle hr schema

depth entity 비고
1depth region 지역
2depth contry 나라
3depth location 위치
4depth department 부서
5depth employee 사원

최상위에 지역(유럽, 아메리카, 아시아 등)이 있고 그안에 나라가 있고 그 아래 5depth까지 구성할 수 있다.

위 데이터 구조를 기반으로 작성을 해 보면 아래처럼 작성 할 수 있다.(로직은 개인의 취향)

RestController 😎

package study.dev.test.controller;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import study.dev.test.service.CommonInfoService;

@RestController
@Slf4j
@RequiredArgsConstructor
@RequestMapping(value = "common/*")
public class CommonInfoController {

    private final CommonInfoService commonInfoService;

    /**
     * 오라클 hr계정 지역>나라>위치>부서>직원 검색
     *
     * @return
     */
    @GetMapping(value = "hrList")
    public ResponseEntity hrList() {
        return commonInfoService.selectRegionsList();
    }
}

Service 😄

지역, 나라, 위치 등 각각 데이터를 리스트로 받아서 자바단 루프 돌며 부모의 AND 조건 줘서 조립

(그리 좋은 코드는 아님)

package study.dev.test.service;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import study.dev.test.mapper.CommonInfoMapper;
import study.dev.test.vo.hr.*;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional(readOnly = true)
@RequiredArgsConstructor
@Slf4j
public class CommonInfoService {

    private final CommonInfoMapper commonInfoMapper;
    /**
     * 오라클 hr계정 리스트 조회
     * @return
     */
    public ResponseEntity selectRegionsList() {

        Map<String, Object> resultMap = new HashMap<>();
        //최종 결과 리스트
        List<RegionsVo> regionsList = new ArrayList<>();

        //1단계 지역 검색
        List<RegionsVo> regionsDataList = commonInfoMapper.selectRegionsList();
        //2단계 나라 검색
        List<RegionsVo> contriesDataList = commonInfoMapper.selectContriesList();
        //3단계 위치 검색
        List<RegionsVo> locationsDataList = commonInfoMapper.selectLocationsList();
        //4단계 부서 검색
        List<RegionsVo> deptDataList = commonInfoMapper.selectDeptList();
        //5단계 직원 검색
        List<RegionsVo> employeeDataList = commonInfoMapper.selectEmployeeList();

        RegionsVo regionsInfo = new RegionsVo();
        //1단계 지역 시작
        for (RegionsVo regionsData : regionsDataList) {
            regionsInfo = new RegionsVo();
            regionsInfo.setRegionId(regionsData.getRegionId());
            regionsInfo.setRegionName(regionsData.getRegionName());

            List<ContriesVo> contriesInfoList = new ArrayList<>();
            ContriesVo contriesInfo = new ContriesVo();
            //2단계 나라 시작
            for(RegionsVo contriesData : contriesDataList) {
                if (regionsInfo.getRegionId() == contriesData.getRegionId()) {
                    contriesInfo = new ContriesVo();
                    contriesInfo.setCountryId(contriesData.getCountryId());
                    contriesInfo.setCountryName(contriesData.getCountryName());

                    List<LocationsVo> locationsInfoList = new ArrayList<>();
                    LocationsVo locationInfo = new LocationsVo();
                    //3단계 위치 시작
                    for(RegionsVo locationsData : locationsDataList) {
                        if (regionsInfo.getRegionId() == locationsData.getRegionId() && contriesInfo.getCountryId().equals(locationsData.getCountryId())) {
                            locationInfo = new LocationsVo();
                            locationInfo.setLocationId(locationsData.getLocationId());
                            locationInfo.setCity(locationsData.getCity());
                            locationInfo.setStreetAddress(locationsData.getStreetAddress());
                            locationInfo.setPostalCode(locationsData.getPostalCode());
                            locationInfo.setStateProvince(locationsData.getStateProvince());

                            //4단계 부서 시작
                            List<DepartMentsVo> deptInfoList = new ArrayList<>();
                            DepartMentsVo departMentsInfo = new DepartMentsVo();
                            for(RegionsVo deptData: deptDataList) {
                                if (regionsInfo.getRegionId() == deptData.getRegionId() &&
                                    contriesInfo.getCountryId().equals(deptData.getCountryId()) &&
                                    locationInfo.getLocationId() == deptData.getLocationId()) {

                                    departMentsInfo = new DepartMentsVo();
                                    departMentsInfo.setDepartmentId(deptData.getDepartmentId());
                                    departMentsInfo.setDepartmentName(deptData.getDepartmentName());

                                    //5단계 직원 시작
                                    List<EmployeesVo> employeeInfoList = new ArrayList<>();
                                    EmployeesVo employeeInfo = new EmployeesVo();
                                    for(RegionsVo employeeData : employeeDataList) {
                                        if (regionsInfo.getRegionId() == employeeData.getRegionId() &&
                                            contriesInfo.getCountryId().equals(employeeData.getCountryId()) &&
                                            locationInfo.getLocationId() == employeeData.getLocationId() &&
                                            departMentsInfo.getDepartmentId() == employeeData.getDepartmentId()) {

                                            employeeInfo = new EmployeesVo();
                                            employeeInfo.setEmployeeId(employeeData.getEmployeeId());
                                            employeeInfo.setName(employeeData.getName());
                                            employeeInfo.setJobTitle(employeeData.getJobTitle());
                                            employeeInfo.setEmail(employeeData.getEmail());
                                            employeeInfo.setPhoneNumber(employeeData.getPhoneNumber());
                                            employeeInfo.setHireDate(employeeData.getHireDate());
                                            employeeInfo.setSalary(employeeData.getSalary());
                                            employeeInfoList.add(employeeInfo);
                                        }
                                    }
                                    departMentsInfo.setEmployeesList(employeeInfoList);
                                    if (departMentsInfo.getDepartmentId() != 0) {
                                     deptInfoList.add(departMentsInfo);
                                    }
                                }
                            }//4단계 부서 종료
                            locationInfo.setDepartMentsList(deptInfoList);
                            if (locationInfo.getLocationId() != 0) {
                                locationsInfoList.add(locationInfo);
                            }
                        }
                    }//3단계 위치 종료
                    contriesInfo.setLocationsList(locationsInfoList);
                    contriesInfoList.add(contriesInfo);
                }
            }//2단계 나라 종료
            regionsInfo.setContriesList(contriesInfoList);
            regionsList.add(regionsInfo);
        }//1단계 지역 종료
        //최종결과 return
        resultMap.put("code", "1");
        resultMap.put("list", regionsList);
        return new ResponseEntity<>(resultMap, HttpStatus.OK);
    }
}

mapper(interface) 😄

package study.dev.test.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import study.dev.test.vo.hr.RegionsVo;

import java.util.List;

@Mapper
public interface CommonInfoMapper {

    /* 1단계 전역 검색 */
    List<RegionsVo> selectRegionsList();

    /* 2단계 나라 검색 */
    List<RegionsVo> selectContriesList();

    /* 3단계 지역 검색 */
    List<RegionsVo> selectLocationsList();

    /* 4단계 부서 검색 */
    List<RegionsVo> selectDeptList();

    /* 5단계 직원 검색 */
    List<RegionsVo> selectEmployeeList();
}

mapper(xml) 😊

조회 시 부모의 PK를 자식에서 조회 하면서 조인

<?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="study.dev.test.mapper.CommonInfoMapper">

    <!--1단계 전체 지역 검색-->
    <select id="selectRegionsList" resultType="study.dev.test.vo.hr.RegionsVo">
        SELECT REGION_ID AS regionId
             , REGION_NAME AS regionName
          FROM REGIONS
         ORDER BY REGION_ID
    </select>

    <!--2단계 나라 검색-->
    <select id="selectContriesList" resultType="study.dev.test.vo.hr.RegionsVo">
        SELECT A.REGION_ID AS regionId
             , A.REGION_NAME AS regionName
             , B.COUNTRY_ID AS countryId
             , B.COUNTRY_NAME AS countryName
          FROM REGIONS A
             , COUNTRIES B
         WHERE 1=1
           AND A.REGION_ID = B.REGION_ID
         ORDER BY A.REGION_ID, B.COUNTRY_ID
    </select>

    <!--3단계 location 검색-->
    <select id="selectLocationsList" resultType="study.dev.test.vo.hr.RegionsVo">
        SELECT A.REGION_ID AS regionId
             , A.REGION_NAME AS regionName
             , B.COUNTRY_ID AS countryId
             , B.COUNTRY_NAME countryName
             , NVL(C.LOCATION_ID ,0) AS locationId
             , C.CITY AS city
             , C.STREET_ADDRESS AS streetAddress
             , C.POSTAL_CODE AS postalCode
             , C.STATE_PROVINCE AS stateProvince
          FROM REGIONS A
             , COUNTRIES B
             , LOCATIONS C
         WHERE 1=1
           AND A.REGION_ID = B.REGION_ID(+)
           AND B.COUNTRY_ID = C.COUNTRY_ID(+)
         ORDER BY A.REGION_ID, B.COUNTRY_ID, C.LOCATION_ID
    </select>

    <!--4단계 부서 검색-->
    <select id="selectDeptList" resultType="study.dev.test.vo.hr.RegionsVo">
        SELECT A.REGION_ID AS regionId
             , A.REGION_NAME AS regionName
             , B.COUNTRY_ID AS countryId
             , B.COUNTRY_NAME AS countryName
             , NVL(C.LOCATION_ID ,0) AS locationId
             , C.CITY AS city
             , NVL(D.DEPARTMENT_ID,0) AS departmentId
             , D.DEPARTMENT_NAME AS departmentName
          FROM REGIONS A
             , COUNTRIES B
             , LOCATIONS C
             , DEPARTMENTS D
         WHERE 1=1
           AND A.REGION_ID = B.REGION_ID(+)
           AND B.COUNTRY_ID = C.COUNTRY_ID(+)
           AND C.LOCATION_ID = D.LOCATION_ID(+)
         ORDER BY A.REGION_ID, B.COUNTRY_ID, C.LOCATION_ID, D.DEPARTMENT_ID
    </select>

    <!--5단계 직원검색-->
    <select id="selectEmployeeList" resultType="study.dev.test.vo.hr.RegionsVo">
        SELECT NVL(A.REGION_ID, 0) AS regionId
             , NVL(A.REGION_NAME, 'none') AS regionName
             , NVL(B.COUNTRY_ID, 'none') AS countryId
             , NVL(B.COUNTRY_NAME, 'none') AS countryName
             , NVL(C.LOCATION_ID, 0) AS locationId
             , NVL(C.CITY, 'none') AS city
             , NVL(D.DEPARTMENT_ID, 0) AS departmentId
             , NVL(D.DEPARTMENT_NAME, 'none') AS departmentName
             , NVL(E.EMPLOYEE_ID, 0) AS employeeId
             , NVL(E.FIRST_NAME || ' ' || E.LAST_NAME, 'none')  AS name
             , NVL(E.EMAIL, 'none')  AS email
             , NVL(E.PHONE_NUMBER, 0)  AS phoneNumber
             , NVL(TO_CHAR(E.HIRE_DATE,'YYYY-MM-DD'),TO_CHAR(SYSDATE,'YYYY-MM-DD')) AS hireDate
             , NVL(E.SALARY, 0)  AS salary
             , NVL(F.JOB_TITLE, 'none')  AS jobTitle
          FROM REGIONS A
             , COUNTRIES B
             , LOCATIONS C
             , DEPARTMENTS D
             , EMPLOYEES E
             , JOBS F
         WHERE 1=1
           AND B.REGION_ID = A.REGION_ID(+)
           AND C.COUNTRY_ID = B.COUNTRY_ID(+)
           AND D.LOCATION_ID = C.LOCATION_ID(+)
           AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
           AND E.JOB_ID = F.JOB_ID(+)
         ORDER BY A.REGION_ID, B.COUNTRY_ID, C.LOCATION_ID, D.DEPARTMENT_ID, E.EMPLOYEE_ID, F.JOB_ID
    </select>

</mapper>

Vo(클래스가 하위 클래스를 계층으로 참조) 🤗

package study.dev.test.vo.hr;

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;

import java.util.ArrayList;
import java.util.List;

@Data
//전체 지역
public class RegionsVo {

    @JsonIgnore
    private int employeeId;
    private String regionName;
    @JsonIgnore
    private String countryName;
    @JsonIgnore
    private String city;
    @JsonIgnore
    private String streetAddress;
    @JsonIgnore
    private String postalCode;
    @JsonIgnore
    private String stateProvince;
    @JsonIgnore
    private int departmentId;
    @JsonIgnore
    private String departmentName;
    @JsonIgnore
    private String name;    //성+이름
    @JsonIgnore
    private String email;
    @JsonIgnore
    private String phoneNumber;
    @JsonIgnore
    private String hireDate;
    @JsonIgnore
    private int salary;
    @JsonIgnore
    private String commissionPct;
    @JsonIgnore
    private String jobTitle;

    private int regionId;
    @JsonIgnore
    private String countryId;
    @JsonIgnore
    private int locationId;

    List<ContriesVo> contriesList = new ArrayList<>();
}
package study.dev.test.vo.hr;

import lombok.Data;

import java.util.ArrayList;
import java.util.List;

@Data
//나라
public class ContriesVo {

    private String countryId;
    private String countryName;

    private List<LocationsVo> locationsList = new ArrayList<>();
}
package study.dev.test.vo.hr;

import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;

import java.util.ArrayList;
import java.util.List;

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
//위치
public class LocationsVo {

    private int locationId;
    private String streetAddress;
    private String postalCode;
    private String city;
    private String stateProvince;

    private List<DepartMentsVo> departMentsList = new ArrayList<>();
}
package study.dev.test.vo.hr;

import lombok.Data;

import java.util.ArrayList;
import java.util.List;

@Data
//부서
public class DepartMentsVo {

    private int departmentId;
    private String departmentName;

    private List<EmployeesVo> employeesList = new ArrayList<>();
}
package study.dev.test.vo.hr;

import lombok.Data;

@Data
//직원
public class EmployeesVo {

    private int employeeId;
    private String name;
    private String email;
    private String phoneNumber;
    private String hireDate;
    private int salary;
    private int commissionPct;
    private int managerId;
    private String jobTitle;
}

API 호출 결과 🤑

result.json
0.09MB
[Rest API]계층구조 Rest API sample(feat. springBoot) - undefined - API 호출 결과 🤑
api 조회 결과

처음 계층구조(객체안에 리스트가 있고 그 안에 또 뭐가 있고...) API 만들 때 저 구조가 이해가 안되서

무서웠고 삽질 했던 기억이 난다.

익숙한 사람들에게는 정말 쉬운 일이겠지만 익숙하지 않은 사람에겐 정말 어려운 작업이다.

미래의 나를 위해 기록을 남긴다.

개인 스터디 기록을 메모하는 공간이라 틀린점이 있을 수 있습니다.

틀린 점 있을 경우 댓글 부탁드립니다.

저작자표시 비영리 변경금지

'IT > development' 카테고리의 다른 글

[springBoot] Spring AOP simple source  (60) 2023.10.19
[springBoot] springBoot 실행 시 dataSource 제외  (54) 2023.10.14
[JavaScript] 객체 해체 할당(Object Destructuring Assignment)(feat. ES6)  (53) 2023.10.04
[thymeleaf] 동적 url 생성(feat. @{/url})  (59) 2023.09.24
[springBoot] 인터셉터를 통해 메뉴 접근 관리  (58) 2023.09.24

댓글

티스토리툴바