MVC 패턴으로 데이터베이스와 연동되는 게시판 (완성)

JSP 2016. 3. 14. 09:00

1) 데이터 베이스에 게시판의 구성요소 테이블 생성 (BOARD.sql)

delete from BOARD;

enctype

CREATE TABLE BOARD(

BOARD_NUM NUMBER,

BOARD_NAME VARCHAR2(20),

BOARD_PASS VARCHAR2(15),

BOARD_SUBJECT VARCHAR2(50),

BOARD_CONTENT VARCHAR2(2000),

BOARD_FILE VARCHAR2(50),

BOARD_RE_REF NUMBER,

BOARD_RE_LEV NUMBER,

BOARD_RE_SEQ NUMBER,

BOARD_READCOUNT NUMBER,

BOARD_DATE DATE,

PRIMARY KEY(BOARD_NUM)

);

select * from BOARD;

2) DTO: 위의 DB를 받을 자바빈 생성 (BoardBean.java)

package net.board.db;

import java.sql.Date;

public class BoardBean {

private int BOARD_NUM;

private String BOARD_NAME;

private String BOARD_PASS;

private String BOARD_SUBJECT;

private String BOARD_CONTENT;

private String BOARD_FILE;

private int BOARD_RE_REF;

private int BOARD_RE_LEV;

private int BOARD_RE_SEQ;

private int BOARD_READCOUNT;

private Date BOARD_DATE;

public int getBOARD_NUM() {

return BOARD_NUM;

}

public void setBOARD_NUM(int board_num) {

BOARD_NUM = board_num;

}

public String getBOARD_NAME() {

return BOARD_NAME;

}

public void setBOARD_NAME(String board_name) {

BOARD_NAME = board_name;

}

public String getBOARD_PASS() {

return BOARD_PASS;

}

public void setBOARD_PASS(String board_pass) {

BOARD_PASS = board_pass;

}

public String getBOARD_SUBJECT() {

return BOARD_SUBJECT;

}

public void setBOARD_SUBJECT(String board_subject) {

BOARD_SUBJECT = board_subject;

}

public String getBOARD_CONTENT() {

return BOARD_CONTENT;

}

public void setBOARD_CONTENT(String board_content) {

BOARD_CONTENT = board_content;

}

public String getBOARD_FILE() {

return BOARD_FILE;

}

public void setBOARD_FILE(String board_file) {

BOARD_FILE = board_file;

}

public int getBOARD_RE_REF() {

return BOARD_RE_REF;

}

public void setBOARD_RE_REF(int board_re_ref) {

BOARD_RE_REF = board_re_ref;

}

public int getBOARD_RE_LEV() {

return BOARD_RE_LEV;

}

public void setBOARD_RE_LEV(int board_re_lev) {

BOARD_RE_LEV = board_re_lev;

}

public int getBOARD_RE_SEQ() {

return BOARD_RE_SEQ;

}

public void setBOARD_RE_SEQ(int board_re_seq) {

BOARD_RE_SEQ = board_re_seq;

}

public int getBOARD_READCOUNT() {

return BOARD_READCOUNT;

}

public void setBOARD_READCOUNT(int board_readcount) {

BOARD_READCOUNT = board_readcount;

}

public Date getBOARD_DATE() {

return BOARD_DATE;

}

public void setBOARD_DATE(Date board_date) {

BOARD_DATE = board_date;

}

3) Controller 구현을 위한 web.xml에 서블릿 항목 추가

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">

<display-name>BOARD-Model2</display-name>

<welcome-file-list>

<welcome-file>index.html</welcome-file>

<welcome-file>index.htm</welcome-file>

<welcome-file>index.jsp</welcome-file>

<welcome-file>default.html</welcome-file>

<welcome-file>default.htm</welcome-file>

<welcome-file>default.jsp</welcome-file>

</welcome-file-list>

<servlet-name>BoardFrontController</servlet-name>

<servlet-class>

net.board.action.BoardFrontController

</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>BoardFrontController</servlet-name>

<url-pattern>*.bo</url-pattern>

</servlet-mapping>

</web-app>

4) MVC 패턴 중 Controller 부분 (BoardFrontController.java)

package net.board.action;

import java.io.IOException;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class BoardFrontController extends javax.servlet.http.HttpServlet {

private static final long serialVersionUID = 1L;

protected void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

doProcess(request,response);

}

protected void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

doProcess(request,response);

}

protected void doProcess(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

String RequestURI = request.getRequestURI();

String contextPath = request.getContextPath();

String command = RequestURI.substring(contextPath.length());

ActionForward forward=null;

Action action=null;

if(command.equals("/BoardWrite.bo")){

forward=new ActionForward();

forward.setRedirect(false);

forward.setPath("./board/qna_board_write.jsp");

}else if(command.equals("/BoardReplyAction.bo")){

action = new BoardReplyAction();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

}else if(command.equals("/BoardDelete.bo")){

forward=new ActionForward();

forward.setRedirect(false);

forward.setPath("./board/qna_board_delete.jsp");

}else if(command.equals("/BoardModify.bo")){

action = new BoardModifyView();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

}else if(command.equals("/BoardAddAction.bo")){

action = new BoardAddAction();

try {

forward=action.execute(request, response);

} catch (Exception e) {

e.printStackTrace();

}

}else if(command.equals("/BoardReplyView.bo")){

action = new BoardReplyView();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

}else if(command.equals("/BoardModifyAction.bo")){

action = new BoardModifyAction();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

}else if(command.equals("/BoardDeleteAction.bo")){

action = new BoardDeleteAction();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

}else if(command.equals("/BoardList.bo")){

action = new BoardListAction();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

}else if(command.equals("/BoardDetailAction.bo")){

action = new BoardDetailAction();

try{

forward=action.execute(request, response);

}catch(Exception e){

e.printStackTrace();

}

if(forward != null){

if(forward.isRedirect()){

response.sendRedirect(forward.getPath());

}else{

RequestDispatcher dispatcher = request.getRequestDispatcher(forward.getPath());

dispatcher.forward(request, response);

}

5) Controller 부분에서 각기 다른 액션 클래스를 action이라는 하나의 이름으로 구현하기 위한 인터페이스 구현 (Action.java)

package net.board.action;

import javax.servlet.http.*;

public interface Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response) throws Exception;

}

6) 액션 후 화면 이동을 위한 클래스 (ActionForward.java)

package net.board.action;

public class ActionForward {

private boolean isRedirect=false;

private String path=null;

public boolean isRedirect(){

return isRedirect;

}

public void setRedirect(boolean b){

isRedirect=b;

}

public String getPath(){

return path;

}

public void setPath(String string){

path=string;

}

7) 게시판에 게시물 등록을 위한 클래스 (BoardAddAction.java)

package net.board.action;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.oreilly.servlet.MultipartRequest;

import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

import net.board.db.BoardDAO;

import net.board.db.BoardBean;

public class BoardAddAction implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response) throws Exception{

BoardDAO boarddao=new BoardDAO();

BoardBean boarddata=new BoardBean();

ActionForward forward=new ActionForward();

String realFolder="";

String saveFolder="/boardUpload";

int fileSize=5*1024*1024;

realFolder = request.getSession().getServletContext().getRealPath(saveFolder);

// jsp 내장객체를 자바에서 불러서 사용. 뒤 절대경로 인식을 위해 이클립스에서 서버 경로 설정 바꿔줄 것. (아래 참조)

boolean result=false;

try{

MultipartRequest multi=null;

// servlets.com에서 다운받은 cos.jar의 클래스 이용. 파일 업로드를 위한 클래스.

multi=new MultipartRequest(request,

realFolder,

fileSize,

"utf-8",

new DefaultFileRenamePolicy());

// cos.jar에서 제공하는 동일 이름의 파일 저장시 이름을 바꿔주는 클래스.

boarddata.setBOARD_NAME(multi.getParameter("BOARD_NAME"));

boarddata.setBOARD_PASS(multi.getParameter("BOARD_PASS"));

boarddata.setBOARD_SUBJECT(multi.getParameter("BOARD_SUBJECT"));

boarddata.setBOARD_CONTENT(multi.getParameter("BOARD_CONTENT"));

boarddata.setBOARD_FILE(

multi.getFilesystemName((String)multi.getFileNames().nextElement()));

// 파일을 불러오는 부분

result=boarddao.boardInsert(boarddata);

if(result==false){

System.out.println("게시판 등록 실패");

return null;

}

System.out.println("게시판 등록 완료");

forward.setRedirect(true);

forward.setPath("./BoardList.bo");

return forward;

}catch(Exception ex){

ex.printStackTrace();

}

return null;

}

서버에서 이클립스가 설정한 폴더가 아니라 절대경로를 넣기 위해서는 서버 옵션에서 서버 모듈 부분에 체크를 해줘야 한다.

8) 게시판에 게시물 삭제를 위한 클래스 (BoardDeleteAction.java)

package net.board.action;

import java.io.PrintWriter;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.*;

public class BoardDeleteAction implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response)

throws Exception{

ActionForward forward = new ActionForward();

request.setCharacterEncoding("euc-kr");

boolean result=false;

boolean usercheck=false;

int num=Integer.parseInt(request.getParameter("num"));

BoardDAO boarddao=new BoardDAO();

usercheck=boarddao.isBoardWriter(num, request.getParameter("BOARD_PASS"));

if(usercheck==false){

response.setContentType("text/html;charset=euc-kr");

PrintWriter out=response.getWriter();

out.println("<script>");

out.println("alert('삭제할 권한이 없습니다.');");

out.println("location.href='./BoardList.bo';");

out.println("</script>");

out.close();

return null;

}

result=boarddao.boardDelete(num);

if(result==false){

System.out.println("게시판 삭제 실패");

return null;

}

System.out.println("게시판 삭제 성공");

forward.setRedirect(true);

forward.setPath("./BoardList.bo");

return forward;

}

9) 게시판 내용 보기를 위한 클래스 (BoardDetailAction.java)

package net.board.action;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.BoardDAO;

import net.board.db.BoardBean;

public class BoardDetailAction implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response) throws Exception{

request.setCharacterEncoding("euc-kr");

BoardDAO boarddao=new BoardDAO();

BoardBean boarddata=new BoardBean();

int num=Integer.parseInt(request.getParameter("num"));

boarddao.setReadCountUpdate(num);

boarddata=boarddao.getDetail(num);

if(boarddata==null){

System.out.println("상세보기 실패");

return null;

}

System.out.println("상세보기 성공");

request.setAttribute("boarddata", boarddata);

ActionForward forward = new ActionForward();

forward.setRedirect(false);

forward.setPath("./board/qna_board_view.jsp");

return forward;

}

10) 게시판 리스트 표시를 위한 클래스 (BoardListAction.java)

package net.board.action;

import java.util.*;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.BoardBean;

import net.board.db.BoardDAO;

public class BoardListAction implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response) throws Exception{

BoardDAO boarddao = new BoardDAO();

List<BoardBean> boardlist = new ArrayList<BoardBean>();

int page=1;

int limit=10;

if(request.getParameter("page")!=null){

page=Integer.parseInt(request.getParameter("page"));

}

int listcount=boarddao.getListCount(); //총 리스트 수를 받아옴

boardlist = boarddao.getBoardList(page, limit); //리스트를 받아옴

//총 페이지 수

int maxpage=(int)((double)listcount/limit+0.95); //0.95를 더해서 올림 처리

//현재 페이지에 보여줄 시작 페이지 수(1, 11, 21 등...)

int startpage = (((int) ((double)page / 10 + 0.9)) - 1) * 10 + 1;

//현재 페이지에 보여줄 마지막 페이지 수(10, 20, 30 등...)

int endpage = startpage+10-1;

if (endpage> maxpage) endpage= maxpage;

request.setAttribute("page", page); //현재 페이지 수

request.setAttribute("maxpage", maxpage); //최대 페이지 수

request.setAttribute("startpage", startpage); //현재 페이지에 표시할 첫 페이지 수

request.setAttribute("endpage", endpage); //현재 페이지에 표시할 끝 페이지 수

request.setAttribute("listcount",listcount); //글 수

request.setAttribute("boardlist", boardlist);

ActionForward forward= new ActionForward();

forward.setRedirect(false);

forward.setPath("./board/qna_board_list.jsp");

return forward;

}

11) 게시판 글 수정을 위한 클래스 (BoardModifyAction.java)

package net.board.action;

import java.io.PrintWriter;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.*;

public class BoardModifyAction implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response)

throws Exception{

request.setCharacterEncoding("euc-kr");

ActionForward forward = new ActionForward();

boolean result = false;

int num=Integer.parseInt(request.getParameter("BOARD_NUM"));

BoardDAO boarddao=new BoardDAO();

BoardBean boarddata=new BoardBean();

boolean usercheck=boarddao.isBoardWriter(num, request.getParameter("BOARD_PASS"));

if(usercheck==false){

response.setContentType("text/html;charset=euc-kr");

PrintWriter out=response.getWriter();

out.println("<script>");

out.println("alert('수정할 권한이 없습니다.');");

out.println("location.href='./BoardList.bo';");

out.println("</script>");

out.close();

return null;

}

try{

boarddata.setBOARD_NUM(num);

boarddata.setBOARD_SUBJECT(request.getParameter("BOARD_SUBJECT"));

boarddata.setBOARD_CONTENT(request.getParameter("BOARD_CONTENT"));

result = boarddao.boardModify(boarddata);

if(result==false){

System.out.println("게시판 수정 실패");

return null;

}

System.out.println("게시판 수정 완료");

forward.setRedirect(true);

forward.setPath("./BoardDetailAction.bo?num="+boarddata.getBOARD_NUM());

return forward;

}catch(Exception ex){

ex.printStackTrace();

}

return null;

}

12) 게시판 글 수정 후 수정된 뷰를 표시하기 위한 클래스 (BoardModifyView.java)

package net.board.action;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.BoardDAO;

import net.board.db.BoardBean;

public class BoardModifyView implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response) throws Exception{

ActionForward forward = new ActionForward();

request.setCharacterEncoding("euc-kr");

BoardDAO boarddao=new BoardDAO();

BoardBean boarddata=new BoardBean();

int num=Integer.parseInt(request.getParameter("num"));

boarddata=boarddao.getDetail(num);

if(boarddata==null){

System.out.println("(수정)상세보기 실패");

return null;

}

System.out.println("(수정)상세보기 성공");

request.setAttribute("boarddata", boarddata);

forward.setRedirect(false);

forward.setPath("./board/qna_board_modify.jsp");

return forward;

}

13) 게시판에 리플을 달기 위한 클래스 (BoardReplyAction.java)

package net.board.action;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.BoardDAO;

import net.board.db.BoardBean;

public class BoardReplyAction implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response)

throws Exception{

request.setCharacterEncoding("euc-kr");

ActionForward forward = new ActionForward();

BoardDAO boarddao=new BoardDAO();

BoardBean boarddata=new BoardBean();

int result=0;

boarddata.setBOARD_NUM(Integer.parseInt(request.getParameter("BOARD_NUM")));

boarddata.setBOARD_NAME(request.getParameter("BOARD_NAME"));

boarddata.setBOARD_PASS(request.getParameter("BOARD_PASS"));

boarddata.setBOARD_SUBJECT(request.getParameter("BOARD_SUBJECT"));

boarddata.setBOARD_CONTENT(request.getParameter("BOARD_CONTENT"));

boarddata.setBOARD_RE_REF(Integer.parseInt(request.getParameter("BOARD_RE_REF")));

boarddata.setBOARD_RE_LEV(Integer.parseInt(request.getParameter("BOARD_RE_LEV")));

boarddata.setBOARD_RE_SEQ(Integer.parseInt(request.getParameter("BOARD_RE_SEQ")));

result=boarddao.boardReply(boarddata);

if(result==0){

System.out.println("답장 실패");

return null;

}

System.out.println("답장 완료");

forward.setRedirect(true);

forward.setPath("./BoardDetailAction.bo?num="+result);

return forward;

}

14) 게시판에 리플을 달고 난 후의 뷰를 위한 클래스 (BoardReplyView.java)

package net.board.action;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import net.board.db.BoardDAO;

import net.board.db.BoardBean;

public class BoardReplyView implements Action {

public ActionForward execute(HttpServletRequest request,HttpServletResponse response)

throws Exception{

ActionForward forward = new ActionForward();

BoardDAO boarddao=new BoardDAO();

BoardBean boarddata=new BoardBean();

int num=Integer.parseInt(request.getParameter("num"));

boarddata=boarddao.getDetail(num);

if(boarddata==null){

System.out.println("답장 페이지 이동 실패");

return null;

}

System.out.println("답장 페이지 이동 완료");

request.setAttribute("boarddata", boarddata);

forward.setRedirect(false);

forward.setPath("./board/qna_board_reply.jsp");

return forward;

}

15) 접속에 커넥션 풀을 이용하기 위한 xml (위치는 META-INF 이하의 context.xml)

<Resource name="jdbc/OracleDB"

auth="Container"

type="javax.sql.DataSource"

username="hr"

password="tiger"

driverClassName="oracle.jdbc.driver.OracleDriver"

url="jdbc:oracle:thin:@localhost:1521:xe"

maxActive="500"

maxIdle="100"/>

</Context>

16) 접속과 위의 각 액션들을 구현하기 위한 SQL이 담긴 DAO 클래스 (BoardDAO)

package net.board.db;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.sql.DataSource;

public class BoardDAO {

DataSource ds;

Connection con;

PreparedStatement pstmt;

ResultSet rs;

public BoardDAO() {

try{

Context init = new InitialContext();

ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");

}catch(Exception ex){

System.out.println("DB 연결 실패 : " + ex);

return;

}

//글의 개수 구하기

public int getListCount() {

int x= 0;

try{

con=ds.getConnection();

//System.out.println("getConnection");

pstmt=con.prepareStatement("select count(*) from board");

rs = pstmt.executeQuery();

if(rs.next()){

x=rs.getInt(1);

}

}catch(Exception ex){

System.out.println("getListCount 에러: " + ex);

}finally{

if(rs!=null) try{rs.close();}catch(SQLException ex){}

if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}

if(con!=null) try{con.close();}catch(SQLException ex){}

}

return x;

}

//글 목록 보기

public List<BoardBean> getBoardList(int page,int limit){

String board_list_sql="select * from "+

"(select rownum rnum,BOARD_NUM,BOARD_NAME,BOARD_SUBJECT,"+

"BOARD_CONTENT,BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,"+

"BOARD_RE_SEQ,BOARD_READCOUNT,BOARD_DATE from "+

"(select * from board order by BOARD_RE_REF desc,BOARD_RE_SEQ asc)) "+

"where rnum>=? and rnum<=?";

List<BoardBean> list = new ArrayList<BoardBean>();

int startrow=(page-1)*10+1; //읽기 시작할 row 번호.

int endrow=startrow+limit-1; //읽을 마지막 row 번호.

try{

con = ds.getConnection();

pstmt = con.prepareStatement(board_list_sql);

pstmt.setInt(1, startrow);

pstmt.setInt(2, endrow);

rs = pstmt.executeQuery();

while(rs.next()){

BoardBean board = new BoardBean();

board.setBOARD_NUM(rs.getInt("BOARD_NUM"));

board.setBOARD_NAME(rs.getString("BOARD_NAME"));

board.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));

board.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));

board.setBOARD_FILE(rs.getString("BOARD_FILE"));

board.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));

board.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));

board.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));

board.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));

board.setBOARD_DATE(rs.getDate("BOARD_DATE"));

list.add(board);

}

return list;

}catch(Exception ex){

System.out.println("getBoardList 에러 : " + ex);

}finally{

if(rs!=null) try{rs.close();}catch(SQLException ex){}

if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}

if(con!=null) try{con.close();}catch(SQLException ex){}

}

return null;

}

//글 내용 보기.

public BoardBean getDetail(int num) throws Exception{

BoardBean board = null;

try{

con = ds.getConnection();

pstmt = con.prepareStatement("select * from board where BOARD_NUM = ?");

pstmt.setInt(1, num);

rs= pstmt.executeQuery();

if(rs.next()){

board = new BoardBean();

board.setBOARD_NUM(rs.getInt("BOARD_NUM"));

board.setBOARD_NAME(rs.getString("BOARD_NAME"));

board.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));

board.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));

board.setBOARD_FILE(rs.getString("BOARD_FILE"));

board.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));

board.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));

board.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));

board.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));

board.setBOARD_DATE(rs.getDate("BOARD_DATE"));

}

return board;

}catch(Exception ex){

System.out.println("getDetail 에러 : " + ex);

}finally{

if(rs!=null)try{rs.close();}catch(SQLException ex){}

if(pstmt !=null)try{pstmt.close();}catch(SQLException ex){}

if(con!=null) try{con.close();}catch(SQLException ex){}

}

return null;

}

//글 등록

public boolean boardInsert(BoardBean board){

int num =0;

String sql="";

int result=0;

try{

con = ds.getConnection();

pstmt=con.prepareStatement("select max(board_num) from board");

rs = pstmt.executeQuery();

if(rs.next())

num =rs.getInt(1)+1;

else

num=1;

sql="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,";

sql+="BOARD_SUBJECT, BOARD_CONTENT, BOARD_FILE, BOARD_RE_REF,"

+"BOARD_RE_LEV,BOARD_RE_SEQ,BOARD_READCOUNT, BOARD_DATE) "

+"values(?,?,?,?,?,?,?,?,?,?,sysdate)";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, num);

pstmt.setString(2, board.getBOARD_NAME());

pstmt.setString(3, board.getBOARD_PASS());

pstmt.setString(4, board.getBOARD_SUBJECT());

pstmt.setString(5, board.getBOARD_CONTENT());

pstmt.setString(6, board.getBOARD_FILE());

pstmt.setInt(7, num);

pstmt.setInt(8, 0);

pstmt.setInt(9, 0);

pstmt.setInt(10, 0);

result=pstmt.executeUpdate();

if(result==0)return false;

return true;

}catch(Exception ex){

System.out.println("boardInsert 에러 : "+ex);

}finally{

if(rs!=null) try{rs.close();}catch(SQLException ex){}

if(pstmt!=null) try{pstmt.close();}catch(SQLException ex){}

if(con!=null) try{con.close();}catch(SQLException ex){}

}

return false;

}

//글 답변

public int boardReply(BoardBean board){

String board_max_sql="select max(board_num) from board";

String sql="";

int num=0;

int result = 0;

int re_ref=board.getBOARD_RE_REF();

int re_lev=board.getBOARD_RE_LEV();

int re_seq=board.getBOARD_RE_SEQ();

try{

con = ds.getConnection();

pstmt=con.prepareStatement(board_max_sql);

rs = pstmt.executeQuery();

if(rs.next())num = rs.getInt(1)+1;

else num=1;

sql="update board set BOARD_RE_SEQ=BOARD_RE_SEQ+1 where BOARD_RE_REF=? ";

sql+="and BOARD_RE_SEQ>?";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1,re_ref);

pstmt.setInt(2,re_seq);

result=pstmt.executeUpdate();

re_seq = re_seq + 1;

re_lev = re_lev+1;

sql="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,";

sql+="BOARD_CONTENT, BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,BOARD_RE_SEQ,";

sql+="BOARD_READCOUNT,BOARD_DATE) values(?,?,?,?,?,?,?,?,?,?,sysdate)";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, num);

pstmt.setString(2, board.getBOARD_NAME());

pstmt.setString(3, board.getBOARD_PASS());

pstmt.setString(4, board.getBOARD_SUBJECT());

pstmt.setString(5, board.getBOARD_CONTENT());

pstmt.setString(6, ""); //답장에는 파일을 업로드하지 않음.

pstmt.setInt(7, re_ref);

pstmt.setInt(8, re_lev);

pstmt.setInt(9, re_seq);

pstmt.setInt(10, 0);

pstmt.executeUpdate();

return num;

}catch(SQLException ex){

System.out.println("boardReply 에러 : "+ex);

}finally{

if(rs!=null)try{rs.close();}catch(SQLException ex){}

if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}

if(con!=null) try{con.close();}catch(SQLException ex){}

}

return 0;

}

//글 수정

public boolean boardModify(BoardBean modifyboard) throws Exception{

String sql="update board set BOARD_SUBJECT=?,BOARD_CONTENT=? where BOARD_NUM=?";

try{

con = ds.getConnection();

pstmt = con.prepareStatement(sql);

pstmt.setString(1, modifyboard.getBOARD_SUBJECT());

pstmt.setString(2, modifyboard.getBOARD_CONTENT());

pstmt.setInt(3, modifyboard.getBOARD_NUM());

pstmt.executeUpdate();

return true;

}catch(Exception ex){

System.out.println("boardModify 에러 : " + ex);

}finally{

if(rs!=null)try{rs.close();}catch(SQLException ex){}

if(pstmt!=null)try{pstmt.close();}catch(SQLException ex){}

if(con!=null) try{con.close();}catch(SQLException ex){}

}

return false;

}

//글 삭제

public boolean boardDelete(int num){

String board_delete_sql="delete from board where BOARD_num=?";

int result=0;

try{

con = ds.getConnection();

pstmt=con.prepareStatement(board_delete_sql);

pstmt.setInt(1, num);

result=pstmt.executeUpdate();

if(result==0)return false;

return true;

}catch(Exception ex){

System.out.println("boardDelete 에러 : "+ex);

}finally{

try{

if(pstmt!=null)pstmt.close();

if(con!=null) con.close();

}catch(Exception ex){}

}

return false;

}

//조회수 업데이트

public void setReadCountUpdate(int num) throws Exception{

String sql="update board set BOARD_READCOUNT = "+

"BOARD_READCOUNT+1 where BOARD_NUM = "+num;

try{

con = ds.getConnection();

pstmt=con.prepareStatement(sql);

pstmt.executeUpdate();

}catch(SQLException ex){

System.out.println("setReadCountUpdate 에러 : "+ex);

}

finally{

try{

if(pstmt!=null)pstmt.close();

if(con!=null) con.close();

}

catch(Exception ex){}

}

//글쓴이인지 확인

public boolean isBoardWriter(int num,String pass){

String board_sql="select * from board where BOARD_NUM=?";

try{

con = ds.getConnection();

pstmt=con.prepareStatement(board_sql);

pstmt.setInt(1, num);

rs=pstmt.executeQuery();

rs.next();

if(pass.equals(rs.getString("BOARD_PASS"))){

return true;

}

}catch(SQLException ex){

System.out.println("isBoardWriter 에러 : "+ex);

}finally{

try{

if(pstmt!=null)pstmt.close();

if(con!=null) con.close();

}catch(Exception ex){}

}

return false;

}

17) MVC 패턴 중 View: 게시판 리스트 화면 (qna_board_list.jsp)

package net.board.db;

<%@ page language="java" contentType="text/html; charset=EUC-KR"%>

<%@ page import="java.util.*"%>

<%@ page import="java.text.SimpleDateFormat" %>

<%@ page import="net.board.db.*" %>

List boardList=(List)request.getAttribute("boardlist");

int listcount=((Integer)request.getAttribute("listcount")).intValue();

int nowpage=((Integer)request.getAttribute("page")).intValue();

int maxpage=((Integer)request.getAttribute("maxpage")).intValue();

int startpage=((Integer)request.getAttribute("startpage")).intValue();

int endpage=((Integer)request.getAttribute("endpage")).intValue();

<html>

<head>

</head>

<body>

if(listcount > 0){

<font size=2>글 개수 : ${listcount }</font>

</td>

</tr>

</td>

</td>

</td>

</td>

</td>

</tr>

for(int i=0;i<boardList.size();i++){

BoardBean bl=(BoardBean)boardList.get(i);

<tr align="center" valign="middle" bordercolor="#333333"

onmouseover="this.style.backgroundColor='F8F8F8'"

onmouseout="this.style.backgroundColor=''">

<%=bl.getBOARD_NUM()%>

</td>

<%if(bl.getBOARD_RE_LEV()!=0){ %>

<%for(int a=0;a<=bl.getBOARD_RE_LEV()*2;a++){ %>

<%} %>

▶

<%}else{ %>

▶

<%} %>

<%=bl.getBOARD_SUBJECT()%>

</a>

</div>

</td>

</td>

</td>

</td>

</tr>

<%} %>

<%if(nowpage<=1){ %>

[이전] 

<%}else{ %>

<%} %>

<%for(int a=startpage;a<=endpage;a++){

if(a==nowpage){%>

[<%=a %>]

<%}else{ %>

<%} %>

<%if(nowpage>=maxpage){ %>

[다음]

<%}else{ %>

<%} %>

</td>

</tr>

}

else

{

</td>

</tr>

}

</td>

</tr>

</table>

</body>

</html>

18) MVC 패턴 중 View: 게시판 본문 보기 화면 (qna_board_view.jsp)

<%@ page language="java" contentType="text/html; charset=EUC-KR"%>

<%@ page import="net.board.db.*" %>

BoardBean board = (BoardBean)request.getAttribute("boarddata");

<html>

<head>

</head>

<body>

</tr>

<tr>

</td>

<%=board.getBOARD_SUBJECT()%>

</td>

</tr>

</td>

</tr>

<tr>

</td>

<tr>

<%=board.getBOARD_CONTENT() %>

</td>

</tr>

</table>

</td>

</tr>

<tr>

</td>

<%if(!(board.getBOARD_FILE()==null)){ %>

<%-- <a href="./boardUpload/<%=board.getBOARD_FILE()%>"> --%>

<%=board.getBOARD_FILE() %>

</a>

<%} %>

</td>

</tr>

</tr>

[답변]

</a>  

[수정]

</a>  

[삭제]

</a>  

</font>

</td>

</tr>

</table>

</body>

</html>

19) MVC 패턴 중 View: 게시판 글쓰기 화면 (qna_board_write.jsp)

<%@ page language="java" contentType="text/html; charset=EUC-KR"%>

<html>

<head>

function addboard() {

boardform.submit();

}

</script>

</head>

<body>

</tr>

<tr>

</td>

<td><input name="BOARD_NAME" type="text" size="10"

maxlength="10" value="" /></td>

</tr>

<tr>

</td>

<td><input name="BOARD_PASS" type="password" size="10"

maxlength="10" value="" /></td>

</tr>

<tr>

</td>

<td><input name="BOARD_SUBJECT" type="text" size="50"

maxlength="100" value="" /></td>

</tr>

<tr>

</td>

</td>

</tr>

<tr>

</td>

</tr>

</tr>

<tr>

</tr>

</tr>

</table>

</form>

</body>

</html>

20) MVC 패턴 중 View: 게시판 글삭제 화면 (qna_board_delete.jsp)

<%@ page language="java" contentType="text/html; charset=EUC-KR"%>

int num=Integer.parseInt(request.getParameter("num"));

<html>

<head>

</head>

<body>

<form name="deleteForm" action="./BoardDeleteAction.bo?num=<%=num %>"

method="post">

<tr>

<td>

</td>

<td>

</td>

</tr>

<tr>

</td>

</tr>

</table>

</form>

</body>

</html>

21) MVC 패턴 중 View: 게시판 글수정 화면 (qna_board_modify.jsp)

<%@ page language="java" contentType="text/html; charset=EUC-KR"%>

<%@ page import="net.board.db.*" %>

BoardBean board = (BoardBean)request.getAttribute("boarddata");

<html>

<head>

function modifyboard(){

modifyform.submit();

}

</script>

</head>

<body>

</tr>

<tr>

</td>

<td>

<input name="BOARD_SUBJECT" size="50" maxlength="100"

value="<%=board.getBOARD_SUBJECT()%>">

</td>

</tr>

<tr>

</td>

<td>

</td>

</tr>

<%if(!(board.getBOARD_FILE()==null)){ %>

<tr>

</td>

<td>

  <%=board.getBOARD_FILE() %>

</td>

</tr>

<%} %>

<tr>

</td>

<td>

</td>

</tr>

</td>

</tr>

</font>

</td>

</tr>

</table>

</form>

</body>

</html>

22) MVC 패턴 중 View: 게시판 리플 화면 (qna_board_reply.jsp)

<%@ page language="java" contentType="text/html; charset=EUC-KR"%>

<%@ page import="net.board.db.*" %>

BoardBean board=(BoardBean)request.getAttribute("boarddata");

<html>

<head>

function replyboard(){

boardform.submit();

}

</script>

</head>

<body>

</tr>

<tr>

</td>

<td>

</td>

</tr>

<tr>

</td>

<td>

<input name="BOARD_SUBJECT" type="text" size="50"

maxlength="100" value="Re: <%=board.getBOARD_SUBJECT() %>"/>

</td>

</tr>

<tr>

</td>

<td>

</td>

</tr>

<tr>

</td>

<td>

</td>

</tr>

</td>

</tr>

</td>

</tr>

</table>

</form>

</body>

</html>

저작자표시

'JSP' 카테고리의 다른 글

JSTL (JSP Standard Tag Library) (0)	2016.03.15
표현 언어 (Expression Language) (0)	2016.03.14
JSP에서 입력받은 값을 데이터베이스에 저장 (0)	2016.03.10
이클립스와 데이터베이스 연동 (0)	2016.03.10
오라클 설치 후 Tomcat에 연동하기 (0)	2016.03.10