| 내용 |
1. pom.xml에 의존성 추가
http://javaspecialist.co.kr/jboard/view.do?categoryId=4&boardId=571&page=1 글 참고
2. 데이터베이스 설정(jdbc.properties, application-config.xml)
http://javaspecialist.co.kr/jboard/view.do?categoryId=4&boardId=572&page=1 글 참고
3. 게시판 테이블 생성
DROP TABLE Board CASCADE CONSTRAINTS;
CREATE TABLE Board (
BbsNo NUMBER NOT NULL,
Name VARCHAR2(20) NOT NULL,
Password VARCHAR2(12) NOT NULL,
Email VARCHAR2(60) NULL,
Subject VARCHAR2(50) NOT NULL,
Content VARCHAR2(4000) NULL,
WriteDate DATE NOT NULL,
MasterId NUMBER NULL,
ReadCount NUMBER NULL,
ReplyNumber NUMBER NULL,
ReplyStep NUMBER NULL
);
ALTER TABLE Board
ADD ( PRIMARY KEY (BbsNo) ) ;
4. 게시판 샘플 데이터 입력
delete from board;
insert into board values(1, '홍길동', '1234', 'hong@hong.com', '방가요', '내용없음', '2015-12-20', 1, 0, 0, 0);
insert into board values(2, '이순신', '1234', 'lee@lee.com', '나도', '내용없음', '2015-12-21', 2, 0, 0, 0);
insert into board values(3, '홍길동', '1234', 'hong@hong.com', '오랜만이야', '그렇지', '2015-12-22', 2, 0, 1, 1);
insert into board values(4, '무명씨', '1234', 'noname@name.com', '할루', '재미없음', '2015-12-23', 4, 0, 0, 0);
insert into board values(5, '홍길서', '1234', 'seo@hong.com', '나도', '나도나도', '2015-12-24', 2, 0, 2, 1);
insert into board values(6, '조심씨', '1234', 'josim@josim.com', '조심해', '아전하게', '2015-12-25', 2, 0, 1, 2);
insert into board values(7, '안전씨', '1234', 'an@anjeon.com', '자나깨나', '불조심', '2015-12-26', 4, 0, 1, 1);
insert into board values(8, '소심씨', '1234', 'so@so.com', '잘삐져', '조심씨', '2015-12-27', 2, 0, 1, 3);
select * from board;
commit;
5. VO 클래스 작성
package kr.co.javaspecialist.board.model;
import java.sql.Date;
public class BoardVO {
private int bbsno;
private String name;
private String password;
private String email;
private String subject;
private String content;
private Date writedate;
private int masterid;
private int readcount;
private int replynumber;
private int replystep;
private int seq; //게시글 조회시 목록에 보여지는 글 번호
public int getBbsno() {
return bbsno;
}
public void setBbsno(int bbsno) {
this.bbsno = bbsno;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getWritedate() {
return writedate;
}
public void setWritedate(Date writedate) {
this.writedate = writedate;
}
public int getMasterid() {
return masterid;
}
public void setMasterid(int masterid) {
this.masterid = masterid;
}
public int getReadcount() {
return readcount;
}
public void setReadcount(int readcount) {
this.readcount = readcount;
}
public int getReplynumber() {
return replynumber;
}
public void setReplynumber(int replynumber) {
this.replynumber = replynumber;
}
public int getReplystep() {
return replystep;
}
public void setReplystep(int replystep) {
this.replystep = replystep;
}
public int getSeq() {
return seq;
}
public void setSeq(int seq) {
this.seq = seq;
}
@Override
public String toString() {
return "BoardVO [bbsno=" + bbsno + ", name=" + name + ", password=" + password + ", email=" + email
+ ", subject=" + subject + ", content=" + content + ", writedate=" + writedate + ", masterid="
+ masterid + ", readcount=" + readcount + ", replynumber=" + replynumber + ", replystep=" + replystep
+ ", seq=" + seq + "]";
}
}
6. DAO 인터페이스 작성
package kr.co.javaspecialist.board.dao;
import java.util.List;
import kr.co.javaspecialist.board.model.BoardVO;
public interface IBoardRepository {
public int selectMaxArticleNo();
public void insertArticle(BoardVO board);
public List<BoardVO> selectArticleList(int page, int maxno);
public BoardVO selectArticle(int bbsno);
public void updateReadCount(int bbsno);
public void replyArticle(BoardVO board);
String getPassword(int bbsno);
public void updateArticle(BoardVO board);
void deleteArticle(int bbsno, int replynumber);
int selectTotalArticleCount();
}
7. DAO 클래스 구현
package kr.co.javaspecialist.board.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import kr.co.javaspecialist.board.dao.IBoardRepository;
import kr.co.javaspecialist.board.model.BoardVO;
@Repository
public class BoardRepository implements IBoardRepository {
@Autowired
JdbcTemplate jdbcTemplate;
public int selectMaxArticleNo() {
String sql = "select nvl(max(bbsno),0) from board";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
public void insertArticle(BoardVO board) {
String sql = "insert into board "
+ "(bbsno, name, password, email, subject, content, writedate, masterid, readcount, replynumber, replystep) "
+ "values (?, ?, ?, ?, ?, ?, SYSDATE, ?, 0, 0, 0)";
jdbcTemplate.update(sql, board.getBbsno(), board.getPassword(), board.getName(), board.getEmail(), board.getSubject(),
board.getContent(), board.getBbsno());
}
public List<BoardVO> selectArticleList(int page, int maxno) {
String sql = "select bbsno,name,email,subject,writedate,readcount,masterid,replynumber,replystep,rnum from ( "
+ " select bbsno,name,email,subject,writedate,readcount,masterid,replynumber,replystep, rownum rnum from ( "
+ " select bbsno,name,email,subject,writedate,readcount,masterid,replynumber,replystep from board "
+ " start with replyNumber=0 connect by prior bbsno=replyNumber "
+ " order siblings by masterid desc, replyNumber, replyStep)) "
+ " where rnum between ? and ? "; //Oracle
int start = (page-1) * 10 +1;
int end = start + maxno-1;
return jdbcTemplate.query(sql, new RowMapper<BoardVO>() {
public BoardVO mapRow(ResultSet rs, int count) throws SQLException {
BoardVO board = new BoardVO();
board.setBbsno(rs.getInt("bbsno"));
board.setName(rs.getString("name"));
board.setEmail(rs.getString("email"));
board.setSubject(rs.getString("subject"));
board.setWritedate(rs.getDate("writedate"));
board.setMasterid(rs.getInt("masterid"));
board.setReplynumber(rs.getInt("replynumber"));
board.setReplystep(rs.getInt("replystep"));
board.setSeq(rs.getInt("rnum"));
return board;
}
}, start, end);
}
public BoardVO selectArticle(int bbsno) {
String sql = "select * from board where bbsno=?";
return jdbcTemplate.queryForObject(sql, new RowMapper<BoardVO>() {
public BoardVO mapRow(ResultSet rs, int count) throws SQLException {
BoardVO board = new BoardVO();
board.setBbsno(rs.getInt("bbsno"));
board.setName(rs.getString("name"));
board.setEmail(rs.getString("email"));
board.setSubject(rs.getString("subject"));
board.setContent(rs.getString("content"));
board.setWritedate(rs.getDate("writedate"));
board.setMasterid(rs.getInt("masterid"));
board.setReplynumber(rs.getInt("replynumber"));
board.setReplystep(rs.getInt("replystep"));
return board;
}
}, bbsno);
}
public void updateReadCount(int bbsno) {
String sql = "update board set readcount=readcount+1 where bbsno=?";
jdbcTemplate.update(sql, bbsno);
}
public void replyArticle(BoardVO board) {
String sql1 = "update board set replynumber=replynumber+1 where masterid=? and replynumber > ?";
jdbcTemplate.update(sql1, board.getMasterid(), board.getReplynumber());
String sql2 = "select nvl(max(bbsno),0) from board";
board.setBbsno(jdbcTemplate.queryForObject(sql2, Integer.class) + 1);
String sql3 = "insert into board "
+ "(bbsno, name, password, email, subject, content, writedate, masterid, readcount, replynumber, replystep) "
+ "values (?, ?, ?, ?, ?, ?, SYSDATE, ?, 0, ?, ?)";
jdbcTemplate.update(sql3, board.getBbsno(), board.getName(), board.getPassword(), board.getEmail(),
board.getSubject(), board.getContent(), board.getMasterid(), board.getReplynumber()+1, board.getReplystep()+1);
}
public String getPassword(int bbsno) {
String sql = "select password from board where bbsno=?";
return jdbcTemplate.queryForObject(sql, String.class, bbsno);
}
public void updateArticle(BoardVO board) {
String sql = "update board set name=?, email=?, subject=?, content=?, writedate=sysdate where bbsno=?";
jdbcTemplate.update(sql, board.getName(), board.getEmail(), board.getSubject(), board.getContent(), board.getBbsno());
}
public void deleteArticle(int bbsno, int replynumber) {
String sql ="delete from board where masterid=?";
if(replynumber > 0) {
sql = "delete from board where bbsno=?";
}
jdbcTemplate.update(sql, bbsno);
}
public int selectTotalArticleCount() {
String sql = "select count(bbsno) from board";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
}
8. Repository 컴포넌트 스캔 설정(application-config.xml)
<context:component-scan base-package="kr.co.javaspecialist.board.dao.impl" />
9. Service 인터페이스 추가
package kr.co.javaspecialist.board.service;
import java.util.List;
import kr.co.javaspecialist.board.model.BoardVO;
public interface IBoardService {
public void insertArticle(BoardVO board);
public List<BoardVO> selectArticleList(int page, int maxno);
public BoardVO selectArticle(int bbsno);
public void updateReadCount(int bbsno);
public void replyArticle(BoardVO board);
public void updateArticle(BoardVO board);
void deleteArticle(int bbsno, int replynumber, String password);
int selectTotalArticleCount();
}
10 Service 클래스 구현
package kr.co.javaspecialist.board.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import kr.co.javaspecialist.board.dao.IBoardRepository;
import kr.co.javaspecialist.board.model.BoardVO;
import kr.co.javaspecialist.board.service.IBoardService;
@Service
public class BoardService implements IBoardService {
@Autowired
IBoardRepository boardRepository;
public void insertArticle(BoardVO board) {
board.setBbsno(boardRepository.selectMaxArticleNo() + 1);
boardRepository.insertArticle(board);
}
public List<BoardVO> selectArticleList(int page, int maxno) {
return boardRepository.selectArticleList(page, maxno);
}
public BoardVO selectArticle(int bbsno) {
boardRepository.updateReadCount(bbsno);
return boardRepository.selectArticle(bbsno);
}
public void updateReadCount(int bbsno) {
boardRepository.updateReadCount(bbsno);
}
public void replyArticle(BoardVO board) {
boardRepository.replyArticle(board);
}
public void updateArticle(BoardVO board) {
String dbpw = boardRepository.getPassword(board.getBbsno());
if(dbpw.equals(board.getPassword())) {
boardRepository.updateArticle(board);
}else {
throw new RuntimeException("비밀번호가 다릅니다.");
}
}
public void deleteArticle(int bbsno, int replynumber, String password){
String dbpw = boardRepository.getPassword(bbsno);
if(dbpw.equals(password)) {
boardRepository.deleteArticle(bbsno, replynumber);
}else {
throw new RuntimeException("비밀번호가 다릅니다.");
}
}
public int selectTotalArticleCount() {
return boardRepository.selectTotalArticleCount();
}
}
11. Service 빈 컴포넌트 스캔 추가
<context:component-scan base-package="kr.co.javaspecialist.board.service.impl" />
12. 컨트롤러 추가
package kr.co.javaspecialist.board.web;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
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 org.springframework.web.servlet.mvc.support.RedirectAttributes;
import kr.co.javaspecialist.board.model.BoardVO;
import kr.co.javaspecialist.board.service.IBoardService;
@Controller
@RequestMapping("/board")
public class BoardController {
@Autowired
IBoardService boardService;
@RequestMapping(value="/write", method=RequestMethod.GET)
public String insert() {
return "/board/write";
}
@RequestMapping(value="/write", method=RequestMethod.POST)
public String insert(BoardVO board, RedirectAttributes model) {
boardService.insertArticle(board);
return "redirect:/board/list";
}
@RequestMapping("/list")
public String selectArticleList(@RequestParam(required=false, defaultValue="1") int page, Model model) {
List<BoardVO> boardList = boardService.selectArticleList(page, 10);
model.addAttribute("boardList", boardList);
int bbsCount = boardService.selectTotalArticleCount();
int totalPage = 0;
if(bbsCount > 0) {
totalPage= bbsCount/10;
}
if( (bbsCount % 10) != 0 || totalPage==0 ) {
totalPage = totalPage+1;
}
model.addAttribute("totalPageCount", totalPage);
model.addAttribute("page", page);
return "/board/list";
}
@RequestMapping("/view")
public String selectArticle(@RequestParam(required=false, defaultValue="1") int bbsno, Model model) {
BoardVO board = boardService.selectArticle(bbsno);
board.getContent().replaceAll("\n", "<br>");
board.setContent(board.getContent().replaceAll("<", "<"));
board.setContent(board.getContent().replaceAll(">", ">"));
model.addAttribute("board", board);
return "/board/view";
}
@RequestMapping(value="/reply", method=RequestMethod.GET)
public String replyArticle(@RequestParam(required=false, defaultValue="1") int bbsno, Model model) {
BoardVO board = boardService.selectArticle(bbsno);
board.setName("");
board.setEmail("");
board.setSubject("[Re]"+board.getSubject());
board.setContent("\n\n\n----------\n" + board.getContent());
model.addAttribute("board", board);
model.addAttribute("next", "reply_do");
return "/board/reply";
}
@RequestMapping(value="/reply", method=RequestMethod.POST)
public String replyArticle(BoardVO board, Model model) {
boardService.replyArticle(board);
return "redirect:/board/list";
}
@RequestMapping(value="/update", method=RequestMethod.GET)
public String updateArticle(@RequestParam(required=false, defaultValue="1") int bbsno, Model model) {
BoardVO board = boardService.selectArticle(bbsno);
model.addAttribute("board", board);
return "board/update";
}
@RequestMapping(value="/update", method=RequestMethod.POST)
public String updateArticle(BoardVO board, Model model) {
boardService.updateArticle(board);
return "redirect:/board/list";
}
@RequestMapping(value="/delete", method=RequestMethod.GET)
public String deleteArticle(int bbsno, int replynumber, Model model) {
model.addAttribute("bbsno", bbsno);
model.addAttribute("replynumber", replynumber);
return "board/delete";
}
@RequestMapping(value="/delete", method=RequestMethod.POST)
public String deleteArticle(int bbsno, int replynumber, String password, Model model) {
try {
boardService.deleteArticle(bbsno, replynumber, password);
}catch(Exception e) {
model.addAttribute("error", e.getMessage());
return "board/delete";
}
return "redirect:/board/list";
}
}
|