고려사항
- 게시판 조회/삽입/수정/삭제 수행할 것
- 게시글 줄을 선택하면 해당하는 게시판의 자세한 글을 볼 수 있게 할 것
- 삭제 시에 재확인할 것 (confirm)
- 수정 시에 같은 페이지에서 처리
CREATE TABLE JDBC_BOARD
(
BOARD_NO NUMBER,
BOARD_TITLE VARCHAR2(100),
BOARD_WRITER VARCHAR2(50),
BOARD_DATE DATE,
BOARD_CNT NUMBER DEFAULT 0,
BOARD_CONTENT VARCHAR2(1000),
CONSTRAINT PK_JDBC_BOARD PRIMARY KEY (BOARD_NO)
);
Insert into JDBC_BOARD (BOARD_NO,BOARD_TITLE,BOARD_WRITER,BOARD_DATE,BOARD_CNT) values (1,'첫번째 게시글 제목','홍길동',to_date('2024/05/22','yyyy/MM/DD'),1);
Insert into JDBC_BOARD (BOARD_NO,BOARD_TITLE,BOARD_WRITER,BOARD_DATE,BOARD_CNT) values (2,'두번째 게시글','이순신',to_date('2024/05/22','yyyy/MM/DD'),0);
Insert into JDBC_BOARD (BOARD_NO,BOARD_TITLE,BOARD_WRITER,BOARD_DATE,BOARD_CNT) values (6,'연습용','작성자',to_date('2024/05/22','yyyy/MM/DD'),0);
Insert into JDBC_BOARD (BOARD_NO,BOARD_TITLE,BOARD_WRITER,BOARD_DATE,BOARD_CNT) values (4,'글번호는 몇번?','강감찬',to_date('2024/05/22','yyyy/MM/DD'),1);
Insert into JDBC_BOARD (BOARD_NO,BOARD_TITLE,BOARD_WRITER,BOARD_DATE,BOARD_CNT) values (7,'mybatis게시판 연습','이몽룡',to_date('2024/05/27','yyyy/MM/DD'),2);
Insert into JDBC_BOARD (BOARD_NO,BOARD_TITLE,BOARD_WRITER,BOARD_DATE,BOARD_CNT) values (8,'log연습용','이순신',to_date('2024/05/27','yyyy/MM/DD'),0);
COMMIT;
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>
<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>
<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>
</body>
</html>
detail.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" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<!-- 상단에 작성해줍니다. -->
<%
pageContext.setAttribute("LF", "\n");
pageContext.setAttribute("BR", "<br/>");
%>
<!DOCTYPE html>
<html>
<head>
<title></title>
<script>
window.onload = function(){
let editBtn = document.querySelector('#edit');
let delBtn = document.querySelector('#delete');
let view = document.querySelector('#viewForm');
let edit = document.querySelector('#editForm');
editBtn.addEventListener('click', function(e){
view.hidden = true;
edit.hidden = false;
})
delBtn.addEventListener('click', function(e){
if(confirm("정말 삭제하시겠습니까?")){
location.href = "/jdbcBoard/delete?boardNo=${vo.boardNo}";
}
})
}
</script>
</head>
<body>
<div id="viewForm">
<h3>게시글 조회</h3>
<table border="1">
<tr>
<th>글번호</th>
<th>제목</th>
<th>작성자</th>
<th>작성일</th>
<th>조회수</th>
</tr>
<tr>
<td>${vo.boardNo}</td>
<td>${vo.boardTitle}</td>
<td>${vo.boardWriter}</td>
<td>
<fmt:formatDate value="${vo.boardDate}" pattern="yyyy-MM-dd"/> <fmt:formatDate value="${vo.boardDate}" pattern="HH:mm:ss"/>
</td>
<td>${vo.boardCnt}</td>
</tr>
<tr>
<td colspan="5">
<c:out value="${fn:replace(vo.boardContent, LF, BR)}" escapeXml="false"/>
</td>
</tr>
</table>
<input type="button" value="수정" id="edit">
<input type="button" value="삭제" id="delete">
<input type="button" value="목록" onclick="location.href='/jdbcBoard/list'">
</div>
<div id="editForm" hidden>
<h3>게시글 수정</h3>
<form action="/jdbcBoard/update" method="post">
<input type="hidden" name="boardNo" value="${vo.boardNo}">
<p>
<label>제목</label>
<input type="text" name="boardTitle" value="${vo.boardTitle}" placeholder="제목" required="required">
</p>
<p>
<label>작성자</label>
<input type="text" name="boardWriter" value="${vo.boardWriter}" placeholder="작성자" required="required">
</p>
<p>
<textarea rows="3" cols="50" name="boardContent" placeholder="내용">${vo.boardContent}</textarea>
</p>
<p>
<input type="submit" value="완료">
<input type="button" value="취소" onclick="location.href='/jdbcBoard/detail?boardNo=${vo.boardNo}'">
</p>
</form>
</div>
</body>
</html>
create.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<h3>게시글 작성</h3>
<form action="/jdbcBoard/createPost" method="post">
<p>
<input type="text" name="boardTitle" placeholder="제목" required="required">
</p>
<p>
<input type="text" name="boardWriter" placeholder="작성자" required="required">
</p>
<p>
<textarea rows="3" cols="50" name="boardContent" placeholder="내용"></textarea>
</p>
<p>
<input type="submit" value="저장">
</p>
</form>
</body>
</html>
JAVA
JdbcBoardVO.java
package kr.or.ddit.vo;
import java.util.Date;
import lombok.Data;
@Data
public class JdbcBoardVO {
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 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.servlet.ModelAndView;
import kr.or.ddit.service.JdbcBoardService;
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() {
ModelAndView mav = new ModelAndView();
mav.setViewName("jdbcBoard/list");
List<JdbcBoardVO> list = this.service.list();
mav.addObject("list", list);
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 kr.or.ddit.vo.JdbcBoardVO;
public interface JdbcBoardService {
public List<JdbcBoardVO> list();
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);
}
JdbcBoardServiceImpl.java
package kr.or.ddit.service.impl;
import java.util.List;
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() {
return this.dao.list();
}
@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);
}
}
JdbcBoardDao.java
package kr.or.ddit.dao;
import java.util.List;
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() {
return this.sst.selectList("board.list");
}
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);
}
}
Mybatis
TypeAlias설정
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--
[마이바티스] 스프링에서 "_"를 사용한 컬럼명을 사용 시(BOOK 테이블의 BOOK_ID)
카멜케이스로 읽어줌(bookId)
ex) 테이블 컬러명이 member_id인 경우 jsp화면단에서 이 값을 사용 시 memberId로 사용
-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 자주 사용하는 타입의 별칭을 세팅 -->
<typeAliases>
<typeAlias type="kr.or.ddit.vo.BookVO" alias="bookVo" />
<typeAlias type="kr.or.ddit.vo.LprodVO" alias="LprodVo" />
<typeAlias type="kr.or.ddit.vo.JdbcBoardVO" alias="boardVo" />
</typeAliases>
</configuration>
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">
<select id="list" resultType="boardVo">
select *
from jdbc_board
order by board_no desc
</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>
