[Spring] Paging

설명

• 우편번호 데이터를 페이징 처리해서 노출하는 예제
• 우편번호 DB 파일 다운로드
  • https://www.epost.go.kr/search/zipcode/cmzcd002k01.jsp

데이터베이스 생성

CREATE DATABASE zip_example DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

USE zip_example;

테이블 생성 후 데이터 추가

CREATE TABLE zip_info
(
    seq            INT UNSIGNED AUTO_INCREMENT,
    zip_code       INT UNSIGNED     NOT NULL,
    sido           VARCHAR(50),
    sigungu        VARCHAR(50),
    eupmyun        VARCHAR(50),
    load_code      VARCHAR(50),
    load_name      VARCHAR(50),
    is_underground TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (seq),
    INDEX (zip_code)
);

model

• PageParams

  @Data
  public class PageParams {
      private int page;
      private int rowSize;
  
      private int getStartIndex() {
          return (page - 1) * rowSize;
      }
  }
  

• ZipInfo

  @Data
  public class ZipInfo {
      private int seq;
      private String zipCode;
      private String sido;
      private String sigungu;
      private String eupmyun;
  }
  

• ZipInfoListResult

  @Data
  @Builder
  public class ZipInfoListResult {
      private int page;
      private int rowSize;
      private int totalCount;
      private List<ZipInfo> zipInfoList;
  }
  

Mapper

• ZipInfoMapper.java

  @Repository
  public interface ZipInfoMapper {
      int selectZipInfoTotalCount();
  
      List<ZipInfo> selectZipInfoList(PageParams pageParams);
  }
  

• ZipInfoMapper.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.example.springpaging.mapper.ZipInfoMapper">
      <select id="selectZipInfoTotalCount" resultType="int">
          SELECT COUNT(*) FROM zip_info
      </select>
  
      <select id="selectZipInfoList" parameterType="com.example.springpaging.model.PageParams" resultType="com.example.springpaging.model.ZipInfo">
          SELECT seq AS seq
               , zip_code AS zipCode
               , sido AS sido
               , sigungu AS sigungu
               , eupmyun AS eupmyun
            FROM zip_info
           LIMIT #{startIndex}, #{rowSize}
      </select>
  </mapper>
  

ZipInfoService

@RequiredArgsConstructor
@Service
public class ZipInfoService {
    private final ZipInfoMapper zipInfoMapper;

    // @Cacheable
    public ZipInfoListResult getZipInfoList(PageParams pageParams) {
        return ZipInfoListResult.builder()
            .page(pageParams.getPage())
            .rowSize(pageParams.getRowSize())
            .totalCount(zipInfoMapper.selectZipInfoTotalCount())
            .zipInfoList(zipInfoMapper.selectZipInfoList(pageParams))
            .build();
    }
}

ZipInfoController

@RequiredArgsConstructor
@RestController
public class ZipInfoController {
    private final ZipInfoService zipInfoService;

    @GetMapping("/api/zipinfo")
    public ZipInfoListResult getZipInfoList(PageParams pageParams) {
        return zipInfoService.getZipInfoList(pageParams);
    }
}

React Front

import React, { useCallback, useEffect, useMemo, useState } from 'react';
import axios from 'axios';
import lodash from 'lodash';

const ROW_SIZE = 20;
const PAGE_SIZE = 5;

const App = () => {
    const [response, setResponse] = useState({
        page: undefined,
        totalCount: undefined,
        zipInfoList: [],
    });

    const pages = useMemo(() => {
        const { page, totalCount } = response;

        if (lodash.some([page, totalCount], lodash.isUndefined)) {
            return [];
        }

        const maxPage = lodash.toSafeInteger((totalCount - 1) / ROW_SIZE) + 1;
        const pageOffset = lodash.toSafeInteger(PAGE_SIZE / 2);

        let startPage = page - pageOffset;
        let endPage = page + pageOffset;

        if (startPage < 1) {
            startPage = 1;
            endPage = startPage + PAGE_SIZE - 1;

            if (endPage > maxPage) {
                endPage = maxPage;
            }
        }

        if (endPage > maxPage) {
            endPage = maxPage;
            startPage = endPage - PAGE_SIZE + 1;

            if (startPage < 1) {
                startPage = 1;
            }
        }

        const prevPage = startPage - 1 < 1 ? 1 : startPage - 1;
        const nextPage = endPage + 1 > maxPage ? maxPage : endPage + 1;

        return [
            { page: 1, text: '<<' },
            { page: prevPage, text: '<' },
            ...lodash.range(startPage, endPage + 1).map((_page) => ({
                page: _page,
                text: _page,
            })),
            { page: nextPage, text: '>' },
            { page: maxPage, text: '>>' },
        ];
    }, [response]);

    const callZipInfo = useCallback(({ page }) => {
        axios
            .get('/api/zipinfo', {
                params: {
                    page,
                    rowSize: ROW_SIZE,
                },
            })
            .then(({ data }) => {
                setResponse(data);
            });
    }, []);

    useEffect(() => {
        callZipInfo({ page: 1 });
    }, [callZipInfo]);

    return (
        <div>
            <table>
                <thead>
                    <tr>
                        <th>seq</th>
                        <th>zipCode</th>
                        <th>시도</th>
                        <th>시군구</th>
                        <th>읍면</th>
                    </tr>
                </thead>
                <tbody>
                    {response.zipInfoList.map((zipInfo) => (
                        <tr key={zipInfo.seq}>
                            <td>{zipInfo.seq}</td>
                            <td>{zipInfo.zipCode}</td>
                            <td>{zipInfo.sido}</td>
                            <td>{zipInfo.sigungu}</td>
                            <td>{zipInfo.eupmyun}</td>
                        </tr>
                    ))}
                </tbody>
            </table>
            <div>
                {pages.map((_page) => (
                    <span
                        key={_page.text}
                        onClick={() => callZipInfo({ page: _page.page })}
                        style={{ 
                            margin: '10px', 
                            cursor: 'pointer', 
                            color: _page.text === response.page ? 'skyblue' : 'grey' 
                        }}
                    >
                        {_page.text}
                    </span>
                ))}
            </div>
        </div>
    );
};

export default App;