본문 바로가기
spring

게시글 댓글 조회수를 추가한다.

컴퓨터신 2019. 11. 27. 17:21

sql>tbl_board 테이블생성

create table tbl_board(
	bno int not null auto_increment, /* 등록번호 */
    title varchar(200) not null, /* 제목 */
    content text , 
    regdate datetime not null default now(), /* 등록일 현재 시간 자동*/
    viewcnt int default 0, /* 조회수  0*/
    primary key(bno)
);

sql>tbl_reply 테이블생성

create table tbl_reply(
	rno int not null auto_increment,
    bno int not null,
    replytext varchar(100),
    regdate datetime default now(),
    updatedate datetime default now(),
    primary key(rno),
    foreign key(bno) references tbl_board(bno)
);

sql>table 수정하기?

update tbl_board set replycnt=
(select count(*) from tbl_reply where bno=tbl_board.bno) where bno>0;

BoardVO

package com.example.domain;

import java.util.Date;

public class BoardVO {
	private int bno;
	private String title;
	private String content;
	private Date regdate;
	private int viewcnt;
	private int replycnt;
	
	public int getBno() {
		return bno;
	}
	public void setBno(int bno) {
		this.bno = bno;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	public int getViewcnt() {
		return viewcnt;
	}
	public void setViewcnt(int viewcnt) {
		this.viewcnt = viewcnt;
	}
	public int getReplycnt() {
		return replycnt;
	}
	public void setReplycnt(int replycnt) {
		this.replycnt = replycnt;
	}
	
	
	@Override
	public String toString() {
		return "BoardVO [bno=" + bno + ", title=" + title + ", content=" + content + ", regdate=" + regdate
				+ ", viewcnt=" + viewcnt + ", replycnt=" + replycnt + "]";
	}
	
	
	
}

BoardDAO

package com.example.persistence;

import java.util.List;

import com.example.domain.BoardVO;
import com.example.domain.SearchCriteria;

public interface BoardDAO {
	public List<BoardVO> list(SearchCriteria cri) throws Exception;
	public int total(SearchCriteria cri) throws Exception;
	
	public BoardVO read(int bno) throws Exception;
	public void viewupdate(int bno) throws Exception;
	
	public void update(BoardVO vo) throws  Exception;
	
	public void replyupdate(int bno,int point) throws Exception;
}

BoardMapper

<?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="BoardMapper">	
	<select id="list" resultType="com.example.domain.BoardVO">
		select * from tbl_board
		<if test="searchType=='title'">
			where title like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='content'">
			where content like concat('%',#{keyword},'%')
		</if>
		order by bno desc
		limit #{pageStart},#{perPageNum}
	</select>
	<select id="total" resultType="int">
		select count(*) from tbl_board
		<if test="searchType=='title'">
			where title like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='content'">
			where content like concat('%',#{keyword},'%')
		</if>
	</select>
	
	<select id="read" resultType="com.example.domain.BoardVO">
		select * from tbl_board
		where bno=#{bno}
	</select>
	<update id="viewupdate">
		update tbl_board set
		viewcnt=viewcnt+1
		where bno=#{bno}
	</update>
	
	<update id="update">
		update tbl_board set title=#{title},content=#{content}
		where bno=#{bno}
	</update>
	<update id="replyupdate">
		update tbl_board set replycnt=replycnt+#{point}
		where bno=#{bno}
	</update>
</mapper>

BoardDAOImpl

package com.example.persistence;

import java.util.HashMap;
import java.util.List;

import javax.inject.Inject;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import com.example.domain.BoardVO;
import com.example.domain.SearchCriteria;

@Repository
public class BoardDAOImpl implements BoardDAO{
	@Inject
	SqlSession session;
	String namespace="BoardMapper";
	@Override
	public List<BoardVO> list(SearchCriteria cri) throws Exception {
		// TODO Auto-generated method stub
		return session.selectList(namespace + ".list",cri);
	}
	@Override
	public int total(SearchCriteria cri) throws Exception {
		// TODO Auto-generated method stub
		return session.selectOne(namespace+".total",cri);
	}
	@Override
	public BoardVO read(int bno) throws Exception {
		// TODO Auto-generated method stub
		return session.selectOne(namespace+".read",bno);
	}
	@Override
	public void viewupdate(int bno) throws Exception {
		// TODO Auto-generated method stub
		session.update(namespace+".viewupdate",bno);
	}
	@Override
	public void update(BoardVO vo) throws Exception {
		// TODO Auto-generated method stub
		session.update(namespace+".update",vo);
	}
	@Override
	public void replyupdate(int bno, int point) throws Exception {
		// TODO Auto-generated method stub
		HashMap<String,Object> map=new HashMap<String,Object>();
		map.put("bno", bno);
		map.put("point", point);
		session.update(namespace+".replyupdate",map);
	}

}

BoardController

package com.example.web;

import javax.inject.Inject;

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.ResponseBody;

import com.example.domain.BoardVO;
import com.example.domain.PageMaker;
import com.example.domain.SearchCriteria;
import com.example.persistence.BoardDAO;
import com.example.service.BoardService;
@RequestMapping("board")
@Controller
public class BoardController {
	@Inject
	BoardDAO dao;
	
	@Inject
	BoardService service;
	
	@RequestMapping("list")
	public String list(Model model,SearchCriteria cri) throws Exception{
		cri.setPerPageNum(10);
		
		PageMaker pm=new PageMaker();
		pm.setCri(cri);
		pm.setTotalCount(dao.total(cri));
		
		model.addAttribute("pm",pm);
		model.addAttribute("list",dao.list(cri));
		return "board/list";
		
	}
	
	@RequestMapping("read")
	public String read(int bno,SearchCriteria cri,Model model) throws Exception{
		model.addAttribute("vo",service.read(bno));
		
		return "board/read";
	}
	
	@RequestMapping("reply")
	public String reply(){
		return "board/reply";
	}
	
	//@RequestMapping(value="read",method="post")throws Exception
	@ResponseBody
	@RequestMapping(value="update",method=RequestMethod.POST)
	public void update(BoardVO vo) throws Exception{ //데이터는 만들어지지 않고 업데이트만 실행됨
		//System.out.println(vo.toString());
		dao.update(vo);
	}
	
}

BoardService

package com.example.service;

import com.example.domain.BoardVO;

public interface BoardService {
	public BoardVO read(int bno) throws Exception;
}

BoardServiceImpl

package com.example.service;

import javax.inject.Inject;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.domain.BoardVO;
import com.example.persistence.BoardDAO;

@Service
public class BoardServiceImpl implements BoardService{
	@Inject
	BoardDAO dao;
	
	@Transactional
	@Override
	public BoardVO read(int bno) throws Exception {
		// TODO Auto-generated method stub
		BoardVO vo=dao.read(bno);
		dao.viewupdate(bno);
		return vo;
	}
	
}

ReplyVO

package com.example.domain;

import java.util.Date;

import com.fasterxml.jackson.annotation.JsonFormat;

public class ReplyVO {
	private int rno;
	private int bno;
	private String replytext;
	@JsonFormat(pattern="yyyy-MM-dd kk:mm:ss",timezone="Asia/Seoul")
	private Date regdate;
	@JsonFormat(pattern="yyyy-MM-dd kk:mm:ss",timezone="Asia/Seoul")
	private Date updatedate;
	
	
	
	public int getRno() {
		return rno;
	}
	public void setRno(int rno) {
		this.rno = rno;
	}
	public int getBno() {
		return bno;
	}
	public void setBno(int bno) {
		this.bno = bno;
	}
	public String getReplytext() {
		return replytext;
	}
	public void setReplytext(String replytext) {
		this.replytext = replytext;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	public Date getUpdatedate() {
		return updatedate;
	}
	public void setUpdatedate(Date updatedate) {
		this.updatedate = updatedate;
	}
	
	
	@Override
	public String toString() {
		return "ReplyVO [rno=" + rno + ", bno=" + bno + ", replytext=" + replytext + ", regdate=" + regdate
				+ ", updatedate=" + updatedate + "]";
	}
	
	
	
}

ReplyDAO

package com.example.persistence;

import java.util.List;

import com.example.domain.Criteria;
import com.example.domain.ReplyVO;

public interface ReplyDAO {
	public List<ReplyVO> list(Criteria cri,int bno)throws Exception;
	public void insert(ReplyVO vo) throws Exception;
}

ReplyMapper

<?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="ReplyMapper">
	<select id="list" resultType="com.example.domain.ReplyVO">
		select * from tbl_reply
		where bno=#{bno}
		order by rno desc
		limit #{cri.pageStart},#{cri.perPageNum}
	</select>
	<insert id="insert">
	insert into tbl_reply(bno,replytext)
	values(#{bno},#{reply})
	</insert>
</mapper>

ReplyDAOImpl

package com.example.persistence;

import java.util.HashMap;
import java.util.List;

import javax.inject.Inject;

import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Repository;

import com.example.domain.Criteria;
import com.example.domain.ReplyVO;

@Repository //CTRL+shift+o:import 단축키
public class ReplyDAOImpl implements ReplyDAO{
	@Inject
	SqlSession session;
	String namespace="ReplyMapper";
	
	
	
	
	@Override
	public List<ReplyVO> list(Criteria cri,int bno) throws Exception {
		// TODO Auto-generated method stub
		HashMap<String,Object> map=new HashMap<String,Object>();
		map.put("bno", bno);
		map.put("cri", cri);
		return session.selectList(namespace+".list",map);
	}




	@Override
	public void insert(ReplyVO vo) throws Exception {
		// TODO Auto-generated method stub
		session.insert(namespace+".insert",vo);
	}
	
	
	
}

ReplyController

package com.example.web;

import java.util.HashMap;

import javax.inject.Inject;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.example.domain.BoardVO;
import com.example.domain.Criteria;
import com.example.domain.PageMaker;
import com.example.domain.ReplyVO;
import com.example.persistence.BoardDAO;
import com.example.persistence.ReplyDAO;
import com.example.service.ReplyService;

@RequestMapping("reply")
@RestController
public class ReplyController {
	@Inject
	ReplyDAO dao;
	
	@Inject
	BoardDAO bdao;
	
	@Inject
	ReplyService service;
	
	@RequestMapping("list")
	public HashMap<String,Object> list(int bno,Criteria cri)throws Exception{
		HashMap<String,Object> map=new HashMap<String,Object>();
		cri.setPerPageNum(5);
		
		PageMaker pm=new PageMaker();
		pm.setCri(cri);
		
		
		BoardVO vo=bdao.read(bno);
		pm.setTotalCount(vo.getReplycnt());
		
		map.put("list", dao.list(cri, bno));
		map.put("pm", pm);
		return map;
	}
	
	@RequestMapping(value="insert",method=RequestMethod.POST)
	public void insert(ReplyVO vo)throws Exception{
		service.insert(vo);
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}

ReplyService

package com.example.service;

import com.example.domain.ReplyVO;

public interface ReplyService {
	public void insert(ReplyVO vo)throws Exception;
}

ReplyServiceImpl

package com.example.service;

import javax.inject.Inject;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.domain.ReplyVO;
import com.example.persistence.BoardDAO;
import com.example.persistence.ReplyDAO;

@Service
public class ReplyServiceImpl implements ReplyService{
	@Inject
	ReplyDAO rdao;
	
	@Inject
	BoardDAO bdao;
	
	@Transactional
	@Override
	public void insert(ReplyVO vo) throws Exception {
		// TODO Auto-generated method stub
		rdao.insert(vo);
		bdao.replyupdate(vo.getBno(),1);
	}
	
}

board>list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="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 PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
	<link href="${pageContext.request.contextPath}/resources/tbl_board.css" rel="stylesheet">
	<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
</head>
<body>
	<h1 class="h1">[게시판목록]</h1>
	<div class="div-search">
		<form name="frm" action="list">
			<select name="searchType">
				<option value="title"<c:out value="${pm.cri.searchType=='title'?'selected':''}"/>>제목</option>
				<option value="content"<c:out value="${pm.cri.searchType=='content'?'selected':''}"/>>내용</option>
			</select>
			<input type="text" name="keyword" value="${pm.cri.keyword}">
			<input type="hidden" name="page" value="${pm.cri.page}">
			<input type="submit" value="검색">
			검색수:${pm.totalCount}
			<input type="text" name="bno">
		</form>
	</div>
	
	
	<table border=1 width=700 class="tbl_board">
		<thead>
			<tr>
				<th>NO.</th>
				<th>Title.</th>
				<th>Date.</th>
				<th>View.</th>
				<th>Reply.</th>
			</tr>
		</thead>
		<c:forEach items="${list}" var="vo">
			<tr class="row" onClick="funread('${vo.bno}')">
				<td>${vo.bno}</td>
				<td>${vo.title}</td>
				<td><fmt:formatDate value="${vo.regdate}" pattern="yyy-MM-dd"/></td>
				<td>${vo.viewcnt}</td>
				<td>${vo.replycnt}</td>
			</tr>
		</c:forEach>
	</table>
	<div id="pagination" class="pagination">
		<c:if test="${pm.prev}">
			<a href='${pm.startPage-1}'>이전</a>
		</c:if>
		<c:forEach begin="${pm.startPage}" end="${pm.endPage}" var="i">
			<c:if test="${pm.cri.page==i}">
				[<a href='${i}' class='active'>${i}</a>]
			</c:if>
			<c:if test="${pm.cri.page!=i}">
				[<a href='${i}'>${i}</a>]
			</c:if>
		</c:forEach>
		<c:if test="${pm.next}">
			<a href='${pm.endPage+1}'>다음</a>
		</c:if>
	</div>
</body>
	<script>
	$(frm).submit(function(){
		$(frm.page).val(1);
	});
	 $("#pagination").on("click","a",function(e){
		 e.preventDefault();
		 var page=$(this).attr("href");
		 //frm.page.value=page;
		 $(frm.page).val(page);
		 frm.submit();
	 });
	 function funread(bno){
		// alert(bno);
		$(frm.bno).val(bno);
		frm.action="read";
		frm.submit();
	 }
	</script>
</html>

board>read.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
	<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
	<link href="${pageContext.request.contextPath}/resources/tbl_board.css" rel="stylesheet">
</head>
<body>
	<h1>[게시글 읽기]</h1>
	<form name="frm" method="post">
		<input type="hidden" name="searchType" value="${param.searchType}"><!-- 상단바에 넘어오는 값을 가져오겠습니다. -->
		<input type="hidden" name="keyword" value="${param.keyword }">
		<input type="hidden" name="page" value="${param.page }">
		<input type="hidden" name="bno" value="${param.bno}">
		<table border=1 width=500 class="tbl_read">
			<tr>
				<td>제목</td>
				<td><input type="text" value="${vo.title}" name="title"></td>
			</tr>
			<tr>
				<td>내용</td>
				<td><textarea rows="5" cols="50" name="content">${vo.content}</textarea></td>
			</tr>
		</table>
		<div class="readbtn">
			<input type="button" value="수정" id="btnup">
			<input type="button" value="삭제">
			<input type="reset" value="취소">
			<input type="button" value="목록" onClick="funlist()">
		</div>
	</form>
	<div>
		<jsp:include page="reply.jsp"></jsp:include>
	</div>
</body>
<script>
	$("#btnup").on("click",function(){
		//alert("업데이트");
		if(!confirm("수정하시겠습니까?")) return;
		var bno=$(frm.bno).val();
		var title=$(frm.title).val();
		var content=$(frm.content).val();
		//alert(title +"\n"+content +"\n"+bno);
		$.ajax({
			type:"post",
			url:"update", //RequestMapping의 value 값
			data:{"bno":bno,"title":title,"content":content},
			success:function(){
				alert("수정되었습니다.");
			}
		});
		//frm.action="update";
		//frm.submit();
	
	});
	function funlist(){
		frm.action="list";
		frm.method="get";
		frm.submit();
	}
</script>
</html>

board>reply.jsp

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
	<link href="${pageContext.request.contextPath}/resources/tbl_reply.css" rel="stylesheet">
	<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
</head>
<body>
	<h1 class="h1">[댓글목록출력]</h1>
	<div class="div-total">
		전체개수:<span id="total"></span>건
	</div>
	<div class="div-insert">
		<input type="text" size=60 id="replytext">
		<input type="button" value="입력" id="btninsert">
	</div>
	<table border=1 width=600 id="tbl" class="tbl_reply"></table>
	<script id="temp" type="text/x-handlebars-template">
	{{#each list}}
	<tr>
		<td>{{rno}}</td>
		<td>{{replytext}}</td>
	</tr>
	{{/each}}
	</script>
	<div id="pagination" class="pagination"></div>
</body>
	<script>
	var bno="${vo.bno}";
	var page=1;
	
	getlist();
	

	
	function getlist(){
		$.ajax({
			type:"get",
			url:"../reply/list",
			data:{"bno":bno,"page":page},
			success:function(data){
				var temp=Handlebars.compile($("#temp").html());
				$("#tbl").html(temp(data));
				$("#total").html(data.pm.totalCount);
				
				//페이지목록 출력 시작
	            var str="";
	            if(data.pm.prev){
	               str += "<a href='" + (data.pm.startPage-1) + "'>◀</a>";
	            }
	            for(var i=data.pm.startPage;i<=data.pm.endPage;i++){
	               if(data.pm.cri.page==i){
	                  str += "[<a href='" + i + "' class='active'>" + i + "</a>]";
	               }else{
	                  str += "[<a href='" + i + "'>" + i + "</a>]";
	               }   
	            }
	            if(data.pm.next){
	               str += "<a href='" + (data.pm.endPage+1) + "'>▶</a>";
	            }
	            $("#pagination").html(str);
	            //페이지목록 출력 종료
			}
		});
	}
	
	$("#btninsert").on("click",function(){
		var replytext=$("#replytext").val();
		//alert(replytext+"\n"+bno);
		
		if(replytext==""){
			alert("댓글 내용을 입력하세요");
			$("#replytext").focus("");
		}
		
		else{
			$.ajax({
				type:"post",
				url:"../reply/insert",
				data:{"replytext":replytext,"bno":bno},
				success:function(){
					getlist();
				}
			});
		}
		
	});
	
	$("#pagination").on("click", "a", function(e){
	      e.preventDefault();
	      page=$(this).attr("href");
	      getlist();
	   });
	</script>
</html>

 

 

'spring' 카테고리의 다른 글

게시판에 댓글을 달면 게시글의 댓글수가 추가됩니다.  (0) 2019.11.28
은행계좌 프로그램 만들기  (0) 2019.11.28
맛집목록 만들기  (0) 2019.11.26
Transaction 처리  (0) 2019.11.26
게시판에 댓글만들기  (0) 2019.11.22

'spring' Related Articles

티스토리툴바