38. Jsp 게시판- BoardReWriteProc ,BoardDAO

자 , 오늘은 지난시간에 BoardReWriteForm.jsp까지 만들었다. 그폼안에 쓰는 데이터를 처리하는 jsp를 하나 만들자

 

그 jsp를 이용을 해서 boardDAO에서 답변글쓰기를 완성하는 수업을 한번 해볼것이다.

 

BoardDAO.class

package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class BoardDAO {

	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	
	//데이터베이스의 커넥션풀을 사용하도록 설정하는 메소드 
	public void getCon() {
		
		try {
			
			Context initctx = new InitialContext();
			Context envctx =(Context) initctx.lookup("java:comp/env");
			DataSource ds = (DataSource)envctx.lookup("jdbc/pool");
			//datascource
			con = ds.getConnection();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
	}
	
	//하나의 새로운 게시글이 넘어와서 저장되는 메소드
	public void insertBoard(BoardBean bean) {
		
		getCon();
		
		//빈클래스에 넘어오지 않았던 데이터들을 초기화 해주어야 한다. 
		// 즉 , 클라이언트에서 인풋값으로 직접 작성하지 않고 데이터베이스에서 값을 부여하는 것을 여기서 초기화를 미리 시켜주어야만 한다.
		int ref = 0; // 글 그룹을 의미하는애 = 쿼리를 실행시켜서 가장 큰 ref 값울 가져온후 +1 을 더해주면 됨 
		int re_step = 1;//새글이기에 = 부모글이기에 
		int re_level = 1;
		try {
			
			//가장 큰 ref값을 읽어오는 쿼리 준비 
			String refsql = "select max(ref) from board2";
			//쿼리 실행 객체 
			pstmt = con.prepareStatement(refsql);
			//쿼리 실행후 결과를 리턴 
			rs = pstmt.executeQuery();
			if(rs.next()) {
				ref = rs.getInt(1)+1;//최대 값에 +1을  더해서 글 그룹을 설정 
			}
			//실제로 게시글 전체값을 테이블에 저장 
			String sql  = "insert into sys.board2 values(0,?,?,?,?,sysdate(),?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			//?에 값을 맵핑 
			pstmt.setString(1, bean.getWriter());
			pstmt.setString(2, bean.getEmail());
			pstmt.setString(3, bean.getSubject());
			pstmt.setString(4, bean.getPassword());
			pstmt.setInt(5, ref);
			pstmt.setInt(6, re_step);
			pstmt.setInt(7, re_level);
			pstmt.setInt(8, bean.getReadcount());
			pstmt.setString(9, bean.getContent());
			//쿼리를 실행하시오
			pstmt.executeUpdate();
			
			//자원반납
			con.close();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	public Vector<BoardBean> getAllList() {
		Vector<BoardBean>v = new Vector<BoardBean>();
		getCon();
		
		try {
			
			String sql = "select * from sys.board2 order by ref desc, re_step asc";
			pstmt= con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
			BoardBean bean = new BoardBean();

			bean.setNum(rs.getInt(1));
			bean.setWriter(rs.getString(2));//
			bean.setEmail(rs.getString(3));//
			bean.setSubject(rs.getString(4));//
			bean.setPassword(rs.getString(5));//
			bean.setReg_date(rs.getDate(6).toString());
			bean.setRef(rs.getInt(7));
			bean.setRe_step(rs.getInt(8));
			bean.setRe_level(rs.getInt(9));
			bean.setReadcount(rs.getInt(10));
			bean.setContent(rs.getString(11));//
			
			v.add(bean);
			}
			con.close();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return v;
	}
	
	public Vector<BoardBean>getOneBoard(int num){
		
		Vector<BoardBean>v = new Vector<BoardBean>();
		getCon();
	
		try {
			
			String sql2  = "update sys.board2 set readcount = readcount +1 where num = ?";
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, num);
			
			pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		
		
		
			String sql = "select writer,email,subject,content from sys.board2 where num = ?";
			try {
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, num);
				
				
				rs = pstmt.executeQuery();
				
				while(rs.next()) {
					BoardBean bean = new BoardBean();
					bean.setWriter(rs.getString(1));
					bean.setEmail(rs.getString(2));
					bean.setSubject(rs.getString(3));
					bean.setContent(rs.getString(4));
					
					v.add(bean);
					
				}
				
	
				
				con.close();

			} catch (Exception e) {
				e.printStackTrace();
				
	
			}

		return v;
		
	}
	
	//답변글이 저장되는 메소드 
	public void reWriteBoard(BoardBean bean) {
		
		
		//부모글그룹(ref)과 글레벨 글스텝을 읽어드림
		int ref = bean.getRef();
		int re_step = bean.getRe_step();
		int re_level = bean.getRe_level();
		
		getCon();
		
		try {
			////////////// 핵심코드 ///////////////////
//			부모글보다 큰 re_level의 값을 전부 1씩 증기시켜줌
			String levelsql = "update board2 set re_level = re_level+1 where ref = ? and re_level > ?";
			//쿼리 실행객체 선언 
			pstmt = con.prepareStatement(levelsql);
			pstmt.setInt(1,ref);
			pstmt.setInt(2, re_level);
			//쿼리 실행 
			pstmt.executeUpdate();
			//답변글 데이터를 저장 
			String sql  ="insert into board2 values(0,?,?,?,?,sysdate(),?,?,?,0,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, bean.getWriter());
			pstmt.setString(2, bean.getEmail());
			pstmt.setString(3, bean.getSubject());
			pstmt.setString(4, bean.getPassword());
			pstmt.setInt(5, ref);//부모의 ref값을 넣어줌 
			pstmt.setInt(6, re_step);//답글이기에 부모 글 re_step에 1을 더해줌 
			pstmt.setInt(7,re_level+1);
			pstmt.setString(8, bean.getContent());
			pstmt.executeUpdate();
			con.close();
			
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
	}
	
	
	
}

 

BoardReWriteProc.jsp

 

<%@page import="db.BoardDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<%
request.setCharacterEncoding("UTF-8");


%>
<!-- 데이터를 한번에 받아오는 빈클래스를 사용하도록 한다 . -->
<jsp:useBean id="boardbean" class="db.BoardBean">
<jsp:setProperty name="boardbean" property="*"/>
</jsp:useBean>



<%
//데이터베이스 객체 생성 
BoardDAO bdao = new BoardDAO();
bdao.reWriteBoard(boardbean);

//답변 데이터를 모두 저장후 전체 게시글 보기를 설정 
response.sendRedirect("BoardList.jsp");


%>





</body>
</html>

BoardList.jsp

 

<%@page import="java.util.Vector"%>
<%@page import="db.BoardBean"%>
<%@page import="db.BoardDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>


<%
BoardBean bean = new BoardBean();
BoardDAO bdao = new BoardDAO();
Vector<BoardBean> vbean = bdao.getAllList();
%>

<center>

<h2>전체 글 보기 </h2>

<table width="700" border="1" bgcolor="skyblue">
<tr height="40">
<td width="50" align="center">번호</td>
<td width="50" align="center">제목</td>
<td width="50" align="center">작성자</td>
<td width="50" align="center">작성일</td>
<td width="50" align="center">조회수</td>
</tr>

<%
//전체 게시글의 내용을 jsp쪽으로 가져와야 한다.

for(int i = 0 ; i< vbean.size();i++){
	
	bean = vbean.get(i);
	
%>
<tr height="40">
<td width="50" align="center"><%=i+1 %></td>
<td width="50" align="left"><a href="BoardInfo.jsp?num=<%=bean.getNum()%> "  style="text-decoration:none">

<%
if(bean.getRe_step()>1){
	
	for(int j = 0; j< (bean.getRe_step()-1)*5;j++){
		
		%>&nbsp;
		<% 
	}
}
%>

<%=bean.getSubject() %></a></td>
<td width="50" align="center"><%=bean.getWriter() %></td>
<td width="50" align="center"><%=bean.getReg_date() %></td>
<td width="50" align="center"><%=bean.getReadcount() %></td>
</tr>


<%
}

%>

<tr height="40" align= "center">
<td width="50" colspan="5"><input onclick="location.href='BoardWriteForm.jsp' " type="button" name="writeBoard" value="글쓰기" /></td>
</tr>
</table>

</center>

</body>
</html>