(22.12.27)Spring 프레임워크: Mybatis로 Spring과 Oracle 연동하여 게시판 상세보기, 첨부파일 다운로드하기

●●Mybatis로 Spring과 Oracle 연동하여 게시판 상세보기, 첨부파일 다운로드하기●●

 

VO클래스는 (22.12.26)게시물과 동일하게 이용한다.

 

 

BoardMapper.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>
    <select id="detailBoard" 
       parameterType="Integer"
       resultType="map">
        SELECT b.boardid, b.title, b.author, b.contents,a.attachid, a.fname, a.fsize 
        FROM board b LEFT OUTER JOIN  attach a  
        ON a.boardid=b.boardid
        WHERE b.boardid=#{boardid}
    </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();
    List<Map<String,Object>> detailBoard(int boardid);
}

 

 

 

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("/detailBoard/{boardid}")
    public String getDetail(@PathVariable int boardid, Model m) {
        m.addAttribute("board", svc.detail(boardid));
        return "/board/boardDetail";
    }
 
    @GetMapping("/download/{filename}")
    public ResponseEntity<Resource> download(@PathVariable String filename, HttpServletRequest request) {
        return svc.download(request, filename);
    }
 
    @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 BoardVO detail(int boardid) {
        List<Map<String, Object>> mlist = dao.detailBoard(boardid);
        BoardVO board = new BoardVO();
        Map<String, Object> map = mlist.get(0);
        BigDecimal big = (java.math.BigDecimal) map.get("BOARDID");
        board.setBoardid(big.intValue());
        board.setTitle((String) map.get("TITLE"));
        board.setAuthor((String) map.get("AUTHOR"));
        board.setContents((String) map.get("CONTENTS"));
 
        for (int i = 0; i < mlist.size(); i++) {
            AttachVO att = new AttachVO();
            Map<String, Object> rowMap = mlist.get(i);
            String fname = (String) rowMap.get("FNAME");
            if (fname == null)
                continue;
            att.setFname(fname);
            big = (java.math.BigDecimal) rowMap.get("ATTACHID");
            att.setAttachid(big.intValue());
            big = (java.math.BigDecimal) rowMap.get("FSIZE");
            att.setFsize(big.intValue());
            board.getAttList().add(att);
        }
        return board;
    }
 
    public ResponseEntity<Resource> download(HttpServletRequest request, String filename) {
        Resource resource = resourceLoader.getResource("WEB-INF/files/" + filename);
        System.out.println("파일명:" + resource.getFilename());
        String contentType = null;
        try {
            contentType = request.getServletContext().getMimeType(resource.getFile().getAbsolutePath());
        } catch (IOException e) {
            e.printStackTrace();
        }
 
        if (contentType == null) {
            contentType = "application/octet-stream"; // http 프로토콜 약속
        }
        try {
            filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return ResponseEntity.ok().contentType(MediaType.parseMediaType(contentType))
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + resource.getFilename() + "\"")
                .body(resource);
    }
 
    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);
        }
        Map<String, Object> map = new HashMap<>();
        map.put("pageInfo", pageInfo);
        map.put("blist", blist);
        return map;
    }
}

 

 

 

boardDetail.jsp

<%@ page language="java" 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>
</head>
<body>
<h3> 게시글 상세 </h3>
<table>
 
글 번호 ${board.boardid} <br>
글쓴이 ${board.author} <br>
글제목 ${board.title} <br>
글내용 ${board.contents} <br>
   <c:forEach var="line" items="${board.attList}">
   <a href="/board/download/${line.fname}">${line.fname}</a> 
   ${line.fsize} byte <br>
   </c:forEach>
   
</table>
</body>
</html>

 

 

 

 

실행결과 : 

 

상세보기

 

 

첨부파일 다운로드