(22.12.26)Spring 프레임워크: PageHelper를 이용한 고수준 pagination 구현, Mybatis로 Spring과 Oracle 연동하여 게시판 리스트 보기

●●PageHelper를 이용한 고수준 pagination 구현●●

 

-Oracle : ROWNUM,PageHelper

-MySQL : LIMIT 2,3, PageHelper

 

 

●●PageHelper 설정해주기●●

 

-application.properties : oralce

 

 

 

-pom.xml : dependency , PageHelper Spring boot starter

 

 

 

 

 

 

BoardVO.java

import java.util.ArrayList;
import java.util.List;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.ToString;
 
@Data
@ToString
@EqualsAndHashCode(exclude= {"title","author","regDate","contents","hit","attList"})
@AllArgsConstructor
@NoArgsConstructor
public class BoardVO {
    private int boardid;
    private String title;
    private String author;
    private java.sql.Date regDate;
    private String contents;
    private int hit;
    private List<AttachVO> attList = new ArrayList<>();
 
    public BoardVO(int intValue) {
        this.boardid=intValue;
    }
}

 

 

 

AttachVO.java

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
 
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class AttachVO {
    private int boardid;
    private int attachid;
    private String fname;
    private long fsize;
    
    public AttachVO(String file)
    {   String[] token = file.split(",");
        this.attachid = Integer.parseInt(token[0]);
        this.fname = token[1];
        this.fsize = Long.parseLong(token[2]);
    }
}

 

 

 

BoardMappper.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.ezen.spring.web.mapper.BoardMapper">
 
    <insert id="saveBoard"
       parameterType="com.ezen.spring.web.vo.BoardVO">
       <!-- Board.num 에 시퀀스 값을 보관해서 사용할 필요가 있다면 아래처럼...
      <selectKey keyProperty="num" resultType="integer" order="BEFORE">
          SELECT BOARD_SEQ.NEXTVAL FROM DUAL
      </selectKey>
       -->
      INSERT INTO board(boardid, title, contents, author)
      VALUES(BOARD_SEQ.NEXTVAL, #{title,jdbcType=VARCHAR}, #{contents,jdbcType=VARCHAR}, #{author,jdbcType=VARCHAR})
 
    </insert>
    
     <update id="saveAttach" parameterType="list">
       INSERT INTO attach (attachid,boardid , fname, fsize)
       SELECT ATTACH_SEQ.NEXTVAL AS attachid, t.* FROM
       (
           <foreach collection="list" item="item" index="index" separator="union all">
           <!-- 유니언 올 때문에 결과값 한행 한행을 하나의 집합으로 만들어준다.-->
                SELECT (SELECT MAX(boardid) FROM board) AS boardid, #{item.fname}, #{item.fsize} FROM DUAL
           </foreach>
       ) t
   </update>
   <select id="getList" 
         resultType="map">
         SELECT b.boardid, b.title, b.author,
        LISTAGG(a.attachid||','||a.fname||','||a.fsize,'/')
        WITHIN GROUP (ORDER BY a.attachid) fnames
        FROM board b LEFT OUTER JOIN attach a
        ON b.boardid=a.boardid
        GROUP BY  b.boardid, b.title, b.author
        ORDER BY b.boardid DESC
   </select>
</mapper>

 

 

 

BoardMapper.java

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import com.ezen.spring.web.vo.AttachVO;
import com.ezen.spring.web.vo.BoardVO;
 
@Mapper
public interface BoardMapper {
    void saveAttach(List<AttachVO> list);
    void saveBoard(BoardVO board);
    List<Map<String,Object>> getList();
}
 

 

 

 

BoardController.java

import java.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.ezen.spring.web.service.BoardService;
import com.ezen.spring.web.vo.AttachVO;
import com.ezen.spring.web.vo.BoardVO;
import jakarta.servlet.ServletContext;
import jakarta.servlet.http.HttpServletRequest;
 
@Controller
@RequestMapping("/board")
public class BoardController {
    @Autowired
    ResourceLoader resourceLoader;
 
    @Autowired
    private BoardService svc;
 
    @GetMapping("/add")
    public String getForm() {
        return "board/boardAdd";
    }
 
    @PostMapping("/add")
    @ResponseBody
    @Transactional
    public String upload(@RequestParam("files") MultipartFile[] mfiles, HttpServletRequest request, BoardVO board) {
        if (svc.saveBoardandAttach(mfiles, request, board)) {
            String msg = String.format("파일(%d)개 저장성공(작성자:%s)", mfiles.length, board.getAuthor().toString());
            return msg;
        } else
            return "파일 저장 실패:";
    }
    @GetMapping("/page/{pageNum}/{rowNum}")
    public String getPage(@PathVariable("pageNum") int a, @PathVariable("rowNum") int b, Model m) {
        Map<String, Object> map = svc.getPage(a, b);
        m.addAttribute("pageInfo", map.get("pageInfo"));
        m.addAttribute("blist", map.get("blist"));
 
        return "board/boardList";
    }
}

 

 

 

BoardService.java

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.multipart.MultipartFile;
import com.ezen.spring.web.mapper.BoardMapper;
import com.ezen.spring.web.vo.AttachVO;
import com.ezen.spring.web.vo.BoardVO;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.servlet.ServletContext;
import jakarta.servlet.http.HttpServletRequest;
import lombok.extern.slf4j.Slf4j;
 
@Service
@Slf4j
public class BoardService {
 
    @Autowired
    ResourceLoader resourceLoader;
 
    @Autowired
    BoardMapper dao;
 
    public boolean saveBoardandAttach(MultipartFile[] mfiles, HttpServletRequest request, BoardVO board) {
        ServletContext context = request.getServletContext();
        String savePath = context.getRealPath("/WEB-INF/files");
        dao.saveBoard(board);
        List<AttachVO> list = new ArrayList();
        try {
            for (int i = 0; i < mfiles.length; i++) {
                if (mfiles[0].getSize() == 0)
                    continue;
                mfiles[i].transferTo(new File(savePath + "/" + mfiles[i].getOriginalFilename()));
                String fname = mfiles[i].getOriginalFilename();
                long fsize = mfiles[i].getSize();
 
                AttachVO att = new AttachVO();
                att.setFname(fname);
                att.setFsize(fsize);
                list.add(att);
                dao.saveAttach(list);
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }
    public Map<String, Object> getPage(int a, int b) {
        PageHelper.startPage(a, b);
        PageInfo<Map<String, Object>> pageInfo = new PageInfo<>(dao.getList());
        List<Map<String, Object>> mlist = pageInfo.getList();
        List<BoardVO> blist = new ArrayList<>();
        for (int i = 0; i < mlist.size(); i++) {
            Map<String, Object> m = mlist.get(i);
            BigDecimal big = (java.math.BigDecimal) m.get("BOARDID");
            BoardVO board = new BoardVO(big.intValue());
            boolean found = false;
            if (blist.contains(board)) {
                board = blist.get(blist.indexOf(board));
                found = true;
            }
            board.setTitle((String) m.get("TITLE"));
            board.setAuthor((String) m.get("AUTHOR"));
 
            
            String fileNames = (String)m.get("FNAMES");
            if (fileNames.length()<=2 ) {
                if (!found)
                    blist.add(board);
                continue;
            }else {
                String[] file = fileNames.split("/");
                for (int y = 0; y < file.length; y++) {
                    AttachVO att = new AttachVO(file[y]);
                    board.getAttList().add(att);
                }
            }
            blist.add(board);
            /*
             * AttachVO att = new AttachVO(); att.setFname((String) objFname); big =
             * (BigDecimal) m.get("ATTACHID"); att.setAttachid(big.intValue()); big =
             * (BigDecimal) m.get("FSIZE"); att.setFsize(big.intValue());
             * 
             * board.getAttList().add(att); // Board에 Attach 연결 if (!found)
             * blist.add(board);
             */
        }
        Map<String, Object> map = new HashMap<>();
        map.put("pageInfo", pageInfo);
        map.put("blist", blist);
        return map;
    }
}

 

 

 

boardList.jsp

<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>게시글 목록</title>
<style type="text/css">
   main { width:fit-content; margin:0.5em auto;}
   h3 { width:fit-content; margin:0.5em auto; }
   table { border:1px solid black; width:fit-content; 
      border-spacing: 0; border-collapse: collapse;
   }
   th,td {padding:0.5em 1em; border-right:1px dashed black;}
   th { background-color:rgb(220,220,255);}
   th,td { border-bottom:1px solid black;}
   a { text-decoration: none; color:blue; }
</style>
</head>
<body>
<main>
<h3>게시글 목록</h3>
<table>
<tr><th>글번호</th><th>제목</th><th>작성자</th><th>첨부파일</th></tr>
<c:forEach var="b" items="${blist}">
   <tr>
      <td>${b.boardid}</td><td>
         <a href="/board/detailBoard/${b.boardid}">${b.title}</a>
      </td><td>${b.author}</td>
      <td>
      <c:if test="${b.attList.size()>0}">${b.attList.size()}개</c:if>
      </td>
   </tr>
</c:forEach>
</table>
<c:forEach var="n" begin="${pageInfo.pageNum-2}" end="${pageInfo.pageNum+2}">
      <span> ${n} </span>
</c:forEach>
</main>
</body>
</html>

 

 

실행결과 :