JSP
list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<title>게시글 목록</title>
<link rel="stylesheet" href="/resources/css/bootstrap.min.css">
<script>
window.onload = function(){
let board = document.querySelectorAll('.board');
board.forEach(function(e){
e.addEventListener('click',function(e){
let tr = e.target.parentNode;
let no = tr.getAttribute('idx');
console.log(no);
location.href = "/jdbcBoard/detail?boardNo="+no;
})
})
}
</script>
<style>
.board:hover{
background: skyblue;
}
</style>
</head>
<body>
<h3>게시글 목록</h3>
<a href="/jdbcBoard/create">게시글 등록</a>
<form action="/jdbcBoard/list">
<select name="gubun">
<option value="" disabled selected>선택해주세요</option>
<option value="boardTitle"
<c:if test="${param.gubun=='boardTitle'}">selected</c:if>
>제목</option>
<option value="boardWriter"
<c:if test="${param.gubun=='boardWriter'}">selected</c:if>
>작성자</option>
<option value="boardContent"
<c:if test="${param.gubun=='boardContent'}">selected</c:if>
>내용</option>
</select>
<input type="text" name="keyword">
<input type="submit" value="검색">
<input type="reset" onclick="location.href='/jdbcBoard/list'">
</form>
<table border="1">
<tr>
<th>글번호</th>
<th>제목</th>
<th>작성자</th>
<th>작성일</th>
<th>조회수</th>
</tr>
<c:forEach var="vo" items="${list}">
<tr class="board" idx="${vo.boardNo}">
<td>${vo.boardNo}</td>
<td>${vo.boardTitle}</td>
<td>${vo.boardWriter}</td>
<td><fmt:formatDate value="${vo.boardDate}" pattern="yyyy-MM-dd"/></td>
<td>${vo.boardCnt}</td>
</tr>
</c:forEach>
</table>
${data.pagingArea}
</body>
</html>
JAVA
ArticlePage.java
package kr.or.ddit.util;
import java.util.List;
import java.util.Map;
//페이징 관련 정보 + 게시글 정보
// new ArticlePage<FreeBoardVO>(total, currentPage, size, content);
public class ArticlePage<T> {
//전체글 수
private int total;
// 현재 페이지 번호
private int currentPage;
// 전체 페이지수
private int totalPages;
// 블록의 시작 페이지 번호
private int startPage;
//블록의 종료 페이지 번호
private int endPage;
//검색어
private String keyword = "";
//요청URL
private String url = "";
//select 결과 데이터
private List<T> content;
//페이징 처리
private String pagingArea = "";
//생성자(Constructor) : 페이징 정보를 생성
// 753 1 10 select결과10행
public ArticlePage(int total, int currentPage, int size, List<T> content, Map<String,Object> map) {
//size : 한 화면에 보여질 목록의 행 수
this.total = total;//753
this.currentPage = currentPage;//1
this.content = content;
String keyword = (String) map.get("keyword");
String gubun = (String) map.get("gubun");
//전체글 수가 0이면?
if(total==0) {
totalPages = 0;//전체 페이지 수
startPage = 0;//블록 시작번호
endPage = 0; //블록 종료번호
}else {//글이 있다면
//전체 페이지 수 = 전체글 수 / 한 화면에 보여질 목록의 행 수
//3 = 31 / 10
totalPages = total / size;//75
//나머지가 있다면, 페이지를 1 증가
if(total % size > 0) {//나머지3
totalPages++;//76
}
//페이지 블록 시작번호를 구하는 공식
// 블록시작번호 = 현재페이지 / 페이지크기 * 페이지크기 + 1
startPage = currentPage / 5 * 5 + 1;//1
//현재페이지 % 페이지크기 => 0일 때 보정
if(currentPage % 5 == 0) {
startPage -= 5;
}
//블록종료번호 = 시작페이지번호 + (페이지크기 - 1)
//[1][2][3][4][5][다음]
endPage = startPage + (5 - 1);//5
//종료페이지번호 > 전체페이지수
if(endPage > totalPages) {
endPage = totalPages;
}
}
pagingArea += "<div class='col-sm-12 col-md-7'>";
pagingArea += "<div class='dataTables_paginate paging_simple_numbers' id='example2_paginate'>";
pagingArea += "<ul class='pagination'>";
pagingArea += "<li class='paginate_button page-item previous ";
if(this.startPage<6) {
pagingArea += "disabled ";
}
pagingArea += "'";
pagingArea += "id='example2_previous'>";
pagingArea += "<a href='"+this.url+"?currentPage="+(this.startPage-5)+"&keyword="+keyword+"&gubun="+gubun+"' aria-controls='example2' data-dt-idx='0' tabindex='0' ";
pagingArea += "class='page-link'>Previous</a></li>";
for(int pNo=this.startPage;pNo<=this.endPage;pNo++) {
pagingArea += "<li class='paginate_button page-item ";
if(this.currentPage == pNo) {
pagingArea += "active";
}
pagingArea += "'>";
pagingArea += "<a href='"+this.url+"?currentPage="+pNo+"&keyword="+keyword+"&gubun="+gubun+"' aria-controls='example2' data-dt-idx='1' tabindex='0' ";
pagingArea += "class='page-link'>"+pNo+"</a>";
pagingArea += "</li>";
}
pagingArea += "<li class='paginate_button page-item next ";
if(this.endPage>=this.totalPages) {
pagingArea += "disabled";
}
pagingArea += "' id='example2_next'><a ";
pagingArea += "href='"+this.url+"?currentPage="+(this.startPage+5)+"&keyword="+keyword+"&gubun="+gubun+"' aria-controls='example2' data-dt-idx='7' ";
pagingArea += "tabindex='0' class='page-link'>Next</a></li>";
pagingArea += "</ul>";
pagingArea += "</div>";
pagingArea += "</div>";
}//end 생성자
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPages() {
return totalPages;
}
public void setTotalPages(int totalPages) {
this.totalPages = totalPages;
}
public int getStartPage() {
return startPage;
}
public void setStartPage(int startPage) {
this.startPage = startPage;
}
public int getEndPage() {
return endPage;
}
public void setEndPage(int endPage) {
this.endPage = endPage;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public List<T> getContent() {
return content;
}
public void setContent(List<T> content) {
this.content = content;
}
//전체 글의 수가 0인가?
public boolean hasNoArticles() {
return this.total == 0;
}
//데이터가 있나?
public boolean hasArticles() {
return this.total > 0;
}
public void setPagingArea(String pagingArea) {
this.pagingArea = pagingArea;
}
//페이징 블록을 자동화
public String getPagingArea() {
return this.pagingArea;
}
}
JdbcBoardVO.java
package kr.or.ddit.vo;
import java.util.Date;
import lombok.Data;
@Data
public class JdbcBoardVO {
private int rnum;
private int boardNo; // 번호
private String boardTitle; // 제목
private String boardWriter; // 작성자
private Date boardDate; // 작성일
private int boardCnt; // 조회수
private String boardContent; // 내용
}
JdbcBoardController.java
package kr.or.ddit.controller;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import kr.or.ddit.service.JdbcBoardService;
import kr.or.ddit.util.ArticlePage;
import kr.or.ddit.vo.JdbcBoardVO;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Controller
public class JdbcBoardController {
@Autowired
JdbcBoardService service;
@RequestMapping (value="/jdbcBoard/list")
public ModelAndView list(@RequestParam Map<String, Object> map, @RequestParam(value="currentPage", required=false, defaultValue="1") int currentPage) {
ModelAndView mav = new ModelAndView();
mav.setViewName("jdbcBoard/list");
if(map.get("keyword")==null) {
map.put("keyword","");
}
if(map.get("gubun")==null) {
map.put("gubun","");
}
int total = this.service.getTotal(map);
int size = 5;
map.put("currentPage", currentPage);
map.put("size", size);
List<JdbcBoardVO> list = this.service.list(map);
mav.addObject("list", list);
mav.addObject("data", new ArticlePage<JdbcBoardVO>(total, currentPage, size, list, map));
return mav;
}
@RequestMapping (value="/jdbcBoard/create")
public ModelAndView create() {
ModelAndView mav = new ModelAndView();
mav.setViewName("jdbcBoard/create");
return mav;
}
@RequestMapping (value="/jdbcBoard/createPost", method=RequestMethod.POST)
public ModelAndView createPost(JdbcBoardVO bvo) {
ModelAndView mav = new ModelAndView();
int result = this.service.createPost(bvo);
mav.setViewName("redirect:/jdbcBoard/list");
return mav;
}
@RequestMapping (value="/jdbcBoard/detail")
public ModelAndView detail(JdbcBoardVO bvo) {
ModelAndView mav = new ModelAndView();
int result = this.service.cntChange(bvo);
bvo = this.service.detail(bvo);
mav.setViewName("jdbcBoard/detail");
mav.addObject("vo", bvo);
return mav;
}
@RequestMapping (value="/jdbcBoard/update", method=RequestMethod.POST)
public ModelAndView update(JdbcBoardVO bvo) {
ModelAndView mav = new ModelAndView();
int result = this.service.update(bvo);
mav.setViewName("redirect:/jdbcBoard/detail?boardNo="+bvo.getBoardNo());
return mav;
}
@RequestMapping (value="/jdbcBoard/delete")
public ModelAndView delete(JdbcBoardVO bvo) {
ModelAndView mav = new ModelAndView();
int result = this.service.delete(bvo);
mav.setViewName("redirect:/jdbcBoard/list");
return mav;
}
}
jdbcBoardService.java
package kr.or.ddit.service;
import java.util.List;
import java.util.Map;
import kr.or.ddit.vo.JdbcBoardVO;
public interface JdbcBoardService {
public List<JdbcBoardVO> list(Map<String, Object> map);
public JdbcBoardVO detail(JdbcBoardVO bvo);
public int createPost(JdbcBoardVO bvo);
public int cntChange(JdbcBoardVO bvo);
public int update(JdbcBoardVO bvo);
public int delete(JdbcBoardVO bvo);
public int getTotal(Map<String, Object> map);
}
jdbcBoardServiceImpl.java
package kr.or.ddit.service.impl;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import kr.or.ddit.dao.JdbcBoardDao;
import kr.or.ddit.service.JdbcBoardService;
import kr.or.ddit.vo.JdbcBoardVO;
@Service
public class JdbcBoardServiceImpl implements JdbcBoardService {
@Autowired
JdbcBoardDao dao;
@Override
public List<JdbcBoardVO> list(Map<String, Object> map) {
return this.dao.list(map);
}
@Override
public JdbcBoardVO detail(JdbcBoardVO bvo) {
return this.dao.detail(bvo);
}
@Override
public int createPost(JdbcBoardVO bvo) {
return this.dao.createPost(bvo);
}
@Override
public int cntChange(JdbcBoardVO bvo) {
return this.dao.cntChange(bvo);
}
@Override
public int update(JdbcBoardVO bvo) {
return this.dao.update(bvo);
}
@Override
public int delete(JdbcBoardVO bvo) {
return this.dao.delete(bvo);
}
@Override
public int getTotal(Map<String, Object> map) {
return this.dao.getTotal(map);
}
}
jdbcBoardDao.java
package kr.or.ddit.dao;
import java.util.List;
import java.util.Map;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import kr.or.ddit.vo.JdbcBoardVO;
@Repository
public class JdbcBoardDao {
@Autowired
SqlSessionTemplate sst;
public int createPost(JdbcBoardVO bvo) {
return this.sst.insert("board.createPost", bvo);
}
public JdbcBoardVO detail(JdbcBoardVO bvo) {
return this.sst.selectOne("board.detail", bvo);
}
public List<JdbcBoardVO> list(Map<String, Object> map) {
return this.sst.selectList("board.list", map);
}
public int cntChange(JdbcBoardVO bvo) {
return this.sst.update("board.cnt", bvo);
}
public int update(JdbcBoardVO bvo) {
return this.sst.update("board.update", bvo);
}
public int delete(JdbcBoardVO bvo) {
return this.sst.delete("board.delete", bvo);
}
public int getTotal(Map<String, Object> map) {
return this.sst.selectOne("board.getTotal", map);
}
}
Mybatis
jdbcBoard_SQL.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="board">
<sql id="search">
<if test="gubun!=null or gubun!=''">
<choose>
<when test="gubun=='boardWriter'">
where board_writer like '%'||#{keyword}||'%'
</when>
<when test="gubun=='boardTitle'">
where board_title like '%'||#{keyword}||'%'
</when>
<when test="gubun=='boardContent'">
where board_content like '%'||#{keyword}||'%'
</when>
</choose>
</if>
</sql>
<select id="list" resultType="boardVo" parameterType="hashMap">
with t as(
select ROW_NUMBER() OVER(ORDER BY BOARD_NO DESC) RNUM, BOARD_NO, BOARD_TITLE, BOARD_WRITER, BOARD_DATE, BOARD_CNT, BOARD_CONTENT
FROM JDBC_BOARD
<include refid="search"/>
)
select t.*
from t
WHERE T.RNUM BETWEEN (#{currentPage}*#{size}) - (#{size}-1) AND (#{currentPage}*#{size})
</select>
<select id="getTotal" parameterType="hashMap" resultType="int">
select count(*)
from jdbc_board
<include refid="search"/>
</select>
<insert id="createPost" parameterType="boardVo">
<selectKey order="BEFORE" resultType="int" keyProperty="boardNo">
select nvl(max(board_no),0)+1
from jdbc_board
</selectKey>
insert into jdbc_board (board_no,board_title,board_writer,board_date,board_cnt,board_content)
values (#{boardNo}, #{boardTitle}, #{boardWriter}, sysdate, 0, #{boardContent})
</insert>
<select id="detail" resultType="boardVo" parameterType="boardVo">
select *
from jdbc_board
where board_no=#{boardNo}
</select>
<update id="cnt" parameterType="boardVo">
<selectKey order="BEFORE" resultType="int" keyProperty="boardCnt">
select board_cnt+1
from jdbc_board
where board_no=#{boardNo}
</selectKey>
update jdbc_board
set board_cnt = #{boardCnt}
where board_no = #{boardNo}
</update>
<update id="update" parameterType="boardVo">
update jdbc_board
set board_writer = #{boardWriter},
board_content = #{boardContent},
board_title = #{boardTitle}
where board_no = #{boardNo}
</update>
<delete id="delete" parameterType="boardVo">
delete jdbc_board
where board_no=#{boardNo}
</delete>
</mapper>
