231216_SPRING CRUD 보강 7

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="kr.or.ddit.mapper.IBoardMapper">

	<sql id="boardSearch">
		<if test="searchType != null and searchType == 'title'">
			and (bo_title like '%' || #{searchWord} || '%')
		</if>
		<if test="searchType != null and searchType == 'writer'">
			and (bo_writer like '%' || #{searchWord} || '%')
		</if>
		<if test="searchType != null and searchType == 'both'">
			and (bo_title like '%' || #{searchWord} || '%')
			and (bo_writer like '%' || #{searchWord} || '%')
		</if>
	</sql>
	
	<resultMap type="boardVO" id="boardMap">
		<id property="boNo" column="bo_no" />
		<result property="boNo" column="bo_no" />
		<result property="boTitle" column="bo_title" />
		<result property="boContent" column="bo_content" />
		<result property="boWriter" column="bo_writer" />
		<result property="boDate" column="bo_date" />
		<result property="boHit" column="bo_hit" />
		<collection property="boardFileList" resultMap="boardFileMap" />
	</resultMap>
	
	<resultMap type="boardFileVO" id="boardFileMap">
		<id property="fileNo" column="file_no" />
		<result property="fileNo" column="file_no" />
		<result property="fileName" column="file_name" />
		<result property="fileSize" column="file_size" />
		<result property="fileFancysize" column="file_fancysize" />
		<result property="fileMime" column="file_mime" />
		<result property="fileSavepath" column="file_savepath" />
		<result property="fileDowncount" column="file_downcount" />
	</resultMap>

	<select id="selectBoardCount" parameterType="pagingVO" resultType="int">
		select count(bo_no) 
		from board 
		where 1=1 
		<include refid="boardSearch" />
	</select>
	
	<select id="selectBoardList" parameterType="pagingVO" resultType="boardVO">
		select 
			b.* 
		from (
			select 
				a.*, row_number() over (order by a.bo_no desc) rnum
			from (
				select 
					bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit 
				from board 
				where 1=1 
				<include refid="boardSearch" /> 
				order by bo_no desc
			) a
		) b
		<![CDATA[
			where b.rnum >= #{startRow} and b.rnum <= #{endRow}
		]]>
	</select>
	
	<update id="incrementhit" parameterType="int">
		update board 
		set 
			bo_hit = bo_hit + 1 
		where bo_no = #{boNo}
	</update>
	
	<select id="selectBoard" parameterType="int" resultMap="boardMap">
		select 
			b.bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit, 
			file_no, file_name, file_size, file_fancysize, file_mime, file_savepath, file_downcount 
		from board b left outer join boardfile bf on(b.bo_no = bf.bo_no)
		where b.bo_no = #{boNo}
	</select>
	
	<insert id="insertBoard" parameterType="boardVO" useGeneratedKeys="true">
		<selectKey keyProperty="boNo" order="BEFORE" resultType="int">
			select seq_board.nextval from dual
		</selectKey>
		insert into board (
			bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit 
		) values (
			#{boNo}, #{boTitle}, #{boContent}, #{boWriter}, sysdate, 0
		)
	</insert>
	
	<insert id="insertBoardFile" parameterType="boardFileVO">
		insert into boardFile (
			file_no, bo_no, file_name, file_size, file_fancysize, file_mime, file_savepath, file_downcount 
		) values (
			seq_boardfile.nextval, #{boNo}, #{fileName}, #{fileSize}, #{fileFancysize}, #{fileMime}, #{fileSavepath}, 0
		)
	</insert>

</mapper>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
<div class="container-fluid px-2 px-md-4">
	<div class="page-header min-height-300 border-radius-xl mt-4"
		style="background-image: url('https://images.unsplash.com/photo-1531512073830-ba890ca4eba2?ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&ixlib=rb-1.2.1&auto=format&fit=crop&w=1920&q=80');">
		<span class="mask  bg-gradient-secondary opacity-6"></span>
	</div>
	<div class="card card-body mx-3 mx-md-4 mt-n6">
		<div class="row gx-4 mb-2">
			<div class="col-md-8">
				<div class="h-100">
					<h5 class="mb-1">${board.boTitle }</h5>
				</div>
			</div>
		</div>
		<ul class="list-group">
			<li
				class="list-group-item border-0 d-flex align-items-center px-0 mb-2 pt-0">
				<div class="avatar me-3">
					<img src="${pageContext.request.contextPath }/resources/assets/img/kal-visuals-square.jpg" alt="kal"
						class="border-radius-lg shadow">
				</div>
				<div
					class="d-flex align-items-start flex-column justify-content-center">
					<h6 class="mb-0 text-sm">${board.boDate } / ${board.boHit }</h6>
					<p class="mb-0 text-xs">${board.boWriter }</p>
				</div>
			</li>
		</ul>
		<div class="row">
			<div class="col-md-12">
				<div class="card card-plain h-100">
					<div class="card-header pb-0 p-3">
						<h6 class="mb-0">내용</h6>
					</div>
					<div class="card-body p-3">${board.boContent }</div>
					<hr />
					
					<c:set value="${board.boardFileList }" var="boardFileList" />
					
					<c:if test="${not empty boardFileList }">
						<div class="col-md-12">
							<div class="row">
								<c:forEach items="${boardFileList }" var="boardFile">
									<div class="col-md-2">
										<div class="card shadow-lg">
											<div class="card-header mt-n4 mx-3 p-0 bg-transparent position-relative z-index-2">
												<a class="d-block blur-shadow-image text-center"> 
													<img src="${pageContext.request.contextPath}/resources/assets/img/icons/img.jpg" alt="img-blur-shadow" class="img-fluid shadow border-radius-lg">
												</a>
												<div class="colored-shadow" style="background-image: url(&quot;https://images.unsplash.com/photo-1536321115970-5dfa13356211?ixid=MnwxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8&amp;ixlib=rb-1.2.1&amp;auto=format&amp;fit=crop&amp;w=934&amp;q=80&quot;);"></div>
											</div>
											<div class="card-body text-center bg-white border-radius-lg p-3 pt-0">
												<h6 class="mt-3 mb-1 d-md-block d-none">
													${boardFile.fileName } (${boardFile.fileFancysize })
												</h6>
												<p class="mb-0 text-xs font-weight-bolder text-info text-uppercase">
													<button type="button" class="btn btn-primary btn-sm fileDownload" data-file-no="${boardFile.fileNo }">
														download
													</button>
												</p>
											</div>
										</div>
									</div>
								</c:forEach>
							</div>
						</div>
					</c:if>
					
					<div class="card-footer p-3">
						<button type="button" class="btn btn-outline-primary" id="delBtn">삭제</button>
						<button type="button" class="btn btn-outline-secondary" id="udtBtn">수정</button>
						<button type="button" class="btn btn-outline-success" onclick="javascript:location.href='/board/list.do'">목록</button>
					</div>
				</div>
			</div>
		</div>
	</div>
</div>
<script>
	$(function(){
		$(".fileDownload").on("click", function(){
			var fileNo = $(this).data("file-no");
			location.href = "/board/download.do?fileNo=" + fileNo;
		});
	});
</script>

package kr.or.ddit.controller.board;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import kr.or.ddit.ServiceResult;
import kr.or.ddit.controller.util.MediaUtils;
import kr.or.ddit.service.IBoardService;
import kr.or.ddit.vo.BoardFileVO;
import kr.or.ddit.vo.BoardVO;
import kr.or.ddit.vo.MemberVO;
import kr.or.ddit.vo.PaginationInfoVO;

@Controller
@RequestMapping("/board")
public class BoardController {
	
	@Inject
	private IBoardService boardService;
	
	@RequestMapping(value="/list.do", method = RequestMethod.GET)
	public String boardList(
			@RequestParam(name = "page", required = false, defaultValue = "1") int currentPage,
			@RequestParam(required = false, defaultValue = "title") String searchType,
			@RequestParam(required = false) String searchWord,
			Model model
			) {
		PaginationInfoVO<BoardVO> pagingVO = new PaginationInfoVO<BoardVO>();
		
		// 검색을 진행
		if(StringUtils.isNotBlank(searchWord)) {
			pagingVO.setSearchType(searchType);
			pagingVO.setSearchWord(searchWord);
			model.addAttribute("searchType", searchType);
			model.addAttribute("searchWord", searchWord);
		}
		
		pagingVO.setCurrentPage(currentPage); // startRow, endRow, startPage, endPage 가 결정
		int totalRecord = boardService.selectBoardCount(pagingVO); // totalRecord(총 게시글 수)
		
		pagingVO.setTotalRecord(totalRecord); // totalPage 결정
		List<BoardVO> dataList = boardService.selectBoardList(pagingVO);
		pagingVO.setDataList(dataList);
		
		model.addAttribute("pagingVO", pagingVO);
		
		return "board/list";
	}
	
	@RequestMapping(value="/detail.do", method = RequestMethod.GET)
	public String boardDetail(int boNo, Model model) {
		BoardVO boardVO = boardService.selectBoard(boNo);
		model.addAttribute("board", boardVO);
		return "board/detail";
	}
	
	@RequestMapping(value="/form.do", method = RequestMethod.GET)
	public String boardForm() {
		return "board/form";
	}
	
	@RequestMapping(value = "/insert.do", method = RequestMethod.POST)
	public String boardInsert(
			HttpServletRequest req,
			BoardVO boardVO, 
			Model model
			) throws Exception {
		
		String goPage = "";
		
		Map<String, String> errors = new HashMap<String, String>();
		if(StringUtils.isBlank(boardVO.getBoTitle())) {
			errors.put("boTitle", "제목을 입력해 주세요.");
		}
		if(StringUtils.isBlank(boardVO.getBoContent())) {
			errors.put("boContent", "내용을 입력해 주세요.");
		}
		if(errors.size() > 0) {
			model.addAttribute("errors", errors);
			model.addAttribute("boardVO", boardVO);
			goPage = "board/form";
		}else {
			HttpSession session = req.getSession();
			MemberVO memberVO = (MemberVO) session.getAttribute("sessionInfo");
			boardVO.setBoWriter(memberVO.getMemId()); // 로그인 한 사용자의 id로 작성자를 설정
			ServiceResult result = boardService.insertBoard(req, boardVO);
			
			if(result.equals(ServiceResult.OK)) {
				goPage = "redirect:/board/detail.do?boNo=" + boardVO.getBoNo();
			}else {
				model.addAttribute("message", "서버 에러, 다시 시도해주세요!");
				goPage = "board/form";
			}
		}
		
		return goPage;
		
	}
	
	@RequestMapping(value = "/download.do", method = RequestMethod.GET)
	public ResponseEntity<byte[]> fileDownload(int fileNo) throws Exception{
		InputStream in = null;
		ResponseEntity<byte[]> entity = null;
		
		String fileName = null;
		BoardFileVO fileVO = boardService.selectFileInfo(fileNo);
		if(fileVO != null) {
			fileName = fileVO.getFileName();
			
			String formatName = fileName.substring(fileName.lastIndexOf(".") + 1);
			MediaType mType = MediaUtils.getMediaType(formatName);
			HttpHeaders headers = new HttpHeaders();
			in = new FileInputStream(fileVO.getFileSavepath());
			
//			if(mType != null) {
//				headers.setContentType(mType);
//			}else {
				fileName = fileName.substring(fileName.indexOf("_") + 1);
				headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
				headers.add("Content-Disposition", "attachment; filename=\""+ new String(fileName.getBytes("UTF-8"), "ISO-8859-1") +"\"");
//			}
			entity = new ResponseEntity<byte[]>(IOUtils.toByteArray(in), headers, HttpStatus.CREATED);
		}else {
			entity = new ResponseEntity<byte[]>(HttpStatus.BAD_REQUEST);
		}
		
		return entity;
	}
	
}

package kr.or.ddit.service;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import kr.or.ddit.ServiceResult;
import kr.or.ddit.vo.BoardFileVO;
import kr.or.ddit.vo.BoardVO;
import kr.or.ddit.vo.PaginationInfoVO;

public interface IBoardService {

	public int selectBoardCount(PaginationInfoVO<BoardVO> pagingVO);
	public List<BoardVO> selectBoardList(PaginationInfoVO<BoardVO> pagingVO);
	public BoardVO selectBoard(int boNo);
	public ServiceResult insertBoard(HttpServletRequest req, BoardVO boardVO) throws Exception;
	public BoardFileVO selectFileInfo(int fileNo);

}

package kr.or.ddit.service.impl;

import java.util.List;

import javax.inject.Inject;
import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Service;

import kr.or.ddit.ServiceResult;
import kr.or.ddit.controller.util.FileUploadUtils;
import kr.or.ddit.mapper.IBoardMapper;
import kr.or.ddit.service.IBoardService;
import kr.or.ddit.vo.BoardFileVO;
import kr.or.ddit.vo.BoardVO;
import kr.or.ddit.vo.PaginationInfoVO;

@Service
public class BoardServiceImpl implements IBoardService {

	@Inject
	private IBoardMapper boardMapper;
	
	@Override
	public int selectBoardCount(PaginationInfoVO<BoardVO> pagingVO) {
		return boardMapper.selectBoardCount(pagingVO);
	}

	@Override
	public List<BoardVO> selectBoardList(PaginationInfoVO<BoardVO> pagingVO) {
		return boardMapper.selectBoardList(pagingVO);
	}

	@Override
	public BoardVO selectBoard(int boNo) {
		boardMapper.incrementhit(boNo); // 조회수 증가
		return boardMapper.selectBoard(boNo);
	}

	@Override
	public ServiceResult insertBoard(HttpServletRequest req, BoardVO boardVO) throws Exception {
		ServiceResult result = null;
		
		int status = boardMapper.insertBoard(boardVO);
		if(status > 0) {
			List<BoardFileVO> boardFileList = boardVO.getBoardFileList();
			FileUploadUtils.boardFileUpload(boardFileList, boardVO.getBoNo(), req, boardMapper);
			result = ServiceResult.OK;
		}else {
			result = ServiceResult.FAILED;
		}
		
		return result;
	}

	@Override
	public BoardFileVO selectFileInfo(int fileNo) {
		return boardMapper.selectFileInfo(fileNo);
	}

}

package kr.or.ddit.mapper;

import java.util.List;

import kr.or.ddit.vo.BoardFileVO;
import kr.or.ddit.vo.BoardVO;
import kr.or.ddit.vo.PaginationInfoVO;

public interface IBoardMapper {

	public int selectBoardCount(PaginationInfoVO<BoardVO> pagingVO);
	public List<BoardVO> selectBoardList(PaginationInfoVO<BoardVO> pagingVO);
	public void incrementhit(int boNo);
	public BoardVO selectBoard(int boNo);
	public int insertBoard(BoardVO boardVO);
	public void insertBoardFile(BoardFileVO boardFileVO);
	public BoardFileVO selectFileInfo(int fileNo);

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="kr.or.ddit.mapper.IBoardMapper">

	<sql id="boardSearch">
		<if test="searchType != null and searchType == 'title'">
			and (bo_title like '%' || #{searchWord} || '%')
		</if>
		<if test="searchType != null and searchType == 'writer'">
			and (bo_writer like '%' || #{searchWord} || '%')
		</if>
		<if test="searchType != null and searchType == 'both'">
			and (bo_title like '%' || #{searchWord} || '%')
			and (bo_writer like '%' || #{searchWord} || '%')
		</if>
	</sql>
	
	<resultMap type="boardVO" id="boardMap">
		<id property="boNo" column="bo_no" />
		<result property="boNo" column="bo_no" />
		<result property="boTitle" column="bo_title" />
		<result property="boContent" column="bo_content" />
		<result property="boWriter" column="bo_writer" />
		<result property="boDate" column="bo_date" />
		<result property="boHit" column="bo_hit" />
		<collection property="boardFileList" resultMap="boardFileMap" />
	</resultMap>
	
	<resultMap type="boardFileVO" id="boardFileMap">
		<id property="fileNo" column="file_no" />
		<result property="fileNo" column="file_no" />
		<result property="fileName" column="file_name" />
		<result property="fileSize" column="file_size" />
		<result property="fileFancysize" column="file_fancysize" />
		<result property="fileMime" column="file_mime" />
		<result property="fileSavepath" column="file_savepath" />
		<result property="fileDowncount" column="file_downcount" />
	</resultMap>

	<select id="selectBoardCount" parameterType="pagingVO" resultType="int">
		select count(bo_no) 
		from board 
		where 1=1 
		<include refid="boardSearch" />
	</select>
	
	<select id="selectBoardList" parameterType="pagingVO" resultType="boardVO">
		select 
			b.* 
		from (
			select 
				a.*, row_number() over (order by a.bo_no desc) rnum
			from (
				select 
					bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit 
				from board 
				where 1=1 
				<include refid="boardSearch" /> 
				order by bo_no desc
			) a
		) b
		<![CDATA[
			where b.rnum >= #{startRow} and b.rnum <= #{endRow}
		]]>
	</select>
	
	<update id="incrementhit" parameterType="int">
		update board 
		set 
			bo_hit = bo_hit + 1 
		where bo_no = #{boNo}
	</update>
	
	<select id="selectBoard" parameterType="int" resultMap="boardMap">
		select 
			b.bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit, 
			file_no, file_name, file_size, file_fancysize, file_mime, file_savepath, file_downcount 
		from board b left outer join boardfile bf on(b.bo_no = bf.bo_no)
		where b.bo_no = #{boNo}
	</select>
	
	<insert id="insertBoard" parameterType="boardVO" useGeneratedKeys="true">
		<selectKey keyProperty="boNo" order="BEFORE" resultType="int">
			select seq_board.nextval from dual
		</selectKey>
		insert into board (
			bo_no, bo_title, bo_content, bo_writer, bo_date, bo_hit 
		) values (
			#{boNo}, #{boTitle}, #{boContent}, #{boWriter}, sysdate, 0
		)
	</insert>
	
	<insert id="insertBoardFile" parameterType="boardFileVO">
		insert into boardfile (
			file_no, bo_no, file_name, file_size, file_fancysize, file_mime, file_savepath, file_downcount 
		) values (
			seq_boardfile.nextval, #{boNo}, #{fileName}, #{fileSize}, #{fileFancysize}, #{fileMime}, #{fileSavepath}, 0
		)
	</insert>
	
	<select id="selectFileInfo" parameterType="int" resultType="boardFileVO">
		select 
			file_no, bo_no, file_name, file_size, file_fancysize, file_mime, file_savepath, file_downcount 
		from boardfile 
		where file_no = #{fileNo}
	</select>

</mapper>

package kr.or.ddit.controller.util;

import java.util.HashMap;
import java.util.Map;

import org.springframework.http.MediaType;

public class MediaUtils {

	private static Map<String, MediaType> mediaMap;
	
	static {
		mediaMap = new HashMap<String, MediaType>();
		mediaMap.put("JPG", MediaType.IMAGE_JPEG);
		mediaMap.put("GIF", MediaType.IMAGE_GIF);
		mediaMap.put("PNG", MediaType.IMAGE_PNG);
	}

	public static MediaType getMediaType(String type) {
		return mediaMap.get(type.toUpperCase());
	}
	
}

* 파일 다운로드 가능...

---