기록해! 정리해!

JSP - 답변형 게시판(2) 본문

JAVA/JSP

JSP - 답변형 게시판(2)

zsuling 2022. 8. 22. 15:15
이전에 했던 답변형게시판이랑 유사하지만 ref(그룹), level(깊이),parent 가 있어야한다 . parent에서 root는 '0'으로 설정한다. root가 아니면 '부모의 idx'로 저장이된다. 즉 parent가 0이면 새로운 글이라는 의미 

from 꼬순언니의 아름다운 블로그 ,,,

 

 

1. top에  추가하기

2. 폴더-파일 생성

3. 오라클 테이블 생성

Create  table  member0822 (
 idx  int  not  null  primary key,
 name  nvarchar2(10) not  null,
 title  nvarchar2(100),
 etc  nvarchar2(500),
 ref number(6) , 
 board_parent number(6));
 
Create  SEQUENCE oramember
INCREMENT by 1  start  with 1001
MAXVALUE  999999;

select  *  from  member0822;

4. OraBoardVo - 게터세터 추가하기

5. 인터페이스 Dao , Service 

 

insert / reinsert / edit 

 

6. DaoImpl

package dbpkg.oraBoard;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import dbpkg.DBConnection;

public class OraBoardDaoImpl implements OraBoardDao{
	
	DBConnection DBConn=null;
	Connection conn =null;
	PreparedStatement pstmt=null;
	PreparedStatement select_pstmt=null;
	ResultSet rs =null;
	OraBoardDaoImpl(){
		DBConn = DBConnection.getInstance();
	}
	
	
	@Override
	public void insert(OraBoardVo vo) {
		try {
		 	conn = DBConn.getConnection();
		 	
		 	String SELECT_SQL= "select nvl(max(ref),1001) + 1 as ref from  member0822 ";
		 	select_pstmt = conn.prepareStatement(SELECT_SQL);
		 	rs=select_pstmt.executeQuery();
		 	rs.next();
		 	int ref = rs.getInt("ref"); 
		 	
		 	String SQL= "insert into member0822 (idx, name, title, etc, ref, board_parent)"
		 			+ " values (oramember.nextval, ?, ?, ?, ?, ?)";
		 	pstmt = conn.prepareStatement(SQL);
		 	pstmt.setString(1, vo.getName());
		 	pstmt.setString(2, vo.getTitle());
		 	pstmt.setString(3, vo.getEtc());
		 	pstmt.setInt(4, ref);  // 레코드 생성시 그룹번호와 
		 	pstmt.setInt(5, 0);    // board_parent 를 0으로 설정.
		 	
		 	pstmt.executeUpdate();
	 	
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			
			DBConn.close1(pstmt, conn);
		}
	}

	@Override
	public void reinsert(OraBoardVo vo) {
		try {
		 	conn = DBConn.getConnection();
		 	String SQL= "insert into member0822 (idx, name, title, etc, ref, board_parent)"
		 			+ " values (oramember.nextval, ?, ?, ?, ?, ?)";
		 	pstmt = conn.prepareStatement(SQL);
		 	pstmt.setString(1, vo.getName());
		 	pstmt.setString(2, vo.getTitle());
		 	pstmt.setString(3, vo.getEtc());
		 	pstmt.setInt(4, vo.getRef());
		 	pstmt.setInt(5, vo.getIdx());  // 답글 작성시 idx 값을  board_parent 에 저장
		 	
		 	pstmt.executeUpdate();
		 	
		}catch(Exception e) {
				e.printStackTrace();
		}finally {
				DBConn.close1(pstmt, conn);
		}
		
	}

	@Override
	public void update(OraBoardVo vo) {
		try {
		 	conn = DBConn.getConnection();
		 			
		 	String SQL= "update member0822 set name=?, title=?, etc=? where idx=? ";
		 	pstmt = conn.prepareStatement(SQL);
		 	pstmt.setString(1, vo.getName());
		 	pstmt.setString(2, vo.getTitle());
		 	pstmt.setString(3, vo.getEtc());
		 	pstmt.setInt(4, vo.getIdx());	
		 	
		 	pstmt.executeUpdate(); 	
		 	
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBConn.close1(pstmt, conn);
		}
		 	
		
	}

	@Override
	public void delete(int idx) {
		try {
		 	conn = DBConn.getConnection();
		 			
		 	String SQL= "delete from member0822 where idx=? ";
		 	pstmt = conn.prepareStatement(SQL);
		 	pstmt.setInt(1, idx);		 	
		 	pstmt.executeUpdate(); 	
		 	
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBConn.close1(pstmt, conn);
		}
		
	}

	@Override
	public OraBoardVo edit(int idx) {
		OraBoardVo	vo = null;
		try {
		 	conn = DBConn.getConnection();
		 			
		 	String SQL= "select * from member0822 where idx=? ";
		 	pstmt = conn.prepareStatement(SQL);
		 	pstmt.setInt(1, idx);		 	
		 	rs=pstmt.executeQuery(); 	
		 	rs.next();
		 	vo = new OraBoardVo();
		 	vo.setIdx(rs.getInt("idx"));
		 	vo.setName(rs.getString("name"));
		 	vo.setTitle(rs.getString("title"));
		 	vo.setEtc(rs.getString("etc"));
		 	vo.setRef(rs.getInt("ref"));
		 	vo.setBoard_parent(rs.getInt("board_parent"));
		 		 	
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBConn.close2(rs, pstmt, conn);
		}
		return vo;
	}

	@Override
	public List<OraBoardVo> selectAll() {
		List<OraBoardVo> li = new ArrayList<OraBoardVo>();
		try {
		 	conn = DBConn.getConnection();
		 			
		 	String SQL= " select level, idx, name, title, etc, ref, board_parent from member0822"
		 			+ " start  with board_parent=0 "
		 			+ " connect  by  prior idx=board_parent "
		 			+ " order siblings by ref desc, idx desc";
		 	pstmt = conn.prepareStatement(SQL);
		 	rs=pstmt.executeQuery(); 	
		 	while(rs.next()) {
			 	OraBoardVo vo = new OraBoardVo();
			 	vo.setIdx(rs.getInt("idx"));
			 	vo.setName(rs.getString("name"));
			 	vo.setTitle(rs.getString("title"));
			 	vo.setEtc(rs.getString("etc"));
			 	vo.setRef(rs.getInt("ref"));
			 	vo.setLevel(rs.getInt("level"));
			 	vo.setBoard_parent(rs.getInt("board_parent"));
			 	li.add(vo);
		 	}	 	
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBConn.close2(rs, pstmt, conn);
		}
		return li;
	}

}

7. ServiceImpl

package dbpkg.oraBoard;

import java.util.List;

public class OraBoardServiceImpl implements OraBoardService {
	private  OraBoardDao	dao =null;
	
	public OraBoardServiceImpl(){
		dao = new  OraBoardDaoImpl();
	}
	
	@Override
	public void insert(OraBoardVo vo) {
		dao.insert(vo);
		
	}

	@Override
	public void reinsert(OraBoardVo vo) {
		dao.reinsert(vo);
		
	}

	@Override
	public void update(OraBoardVo vo) {
		dao.update(vo);		
	}

	@Override
	public void delete(int idx) {
		dao.delete(idx);
		
	}

	@Override
	public OraBoardVo edit(int idx) {		
		return dao.edit(idx);
	}

	@Override
	public List<OraBoardVo> selectAll() {		
		return dao.selectAll();
	}

}

8.Controller

package dbpkg.oraBoard;

import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class OraBoardController
 */
@WebServlet("/OraBoardController")
public class OraBoardController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public OraBoardController() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		// response.getWriter().append("Served at: ").append(request.getContextPath());
		
		request.setCharacterEncoding("UTF-8");
		
		String sw= request.getParameter("sw");
		
		String name= request.getParameter("name");
		String title= request.getParameter("title");
		String etc= request.getParameter("etc");
		
		OraBoardVo vo = new OraBoardVo();
		vo.setName(name);
		vo.setTitle(title);
		vo.setEtc(etc);
		
		OraBoardService service = new OraBoardServiceImpl();
		
		String path="";
				
		if (sw.equals("I")) {
			service.insert(vo);			
			path="OraBoardController?sw=S";
			
		} else if(sw.equals("R")) {
			 int idx =Integer.parseInt(request.getParameter("idx")) ;
			 int ref= Integer.parseInt(request.getParameter("ref")) ;
			 vo.setIdx(idx);
			 vo.setRef(ref);
			 service.reinsert(vo);			 
			 path="OraBoardController?sw=S";
			 
		}else if(sw.equals("Re")) {
			int	idx =Integer.parseInt(request.getParameter("idx")) ;
			 OraBoardVo m= service.edit(idx);
			 request.setAttribute("m", m);
			 path="./oraBoard/re_edit.jsp";
			 
		}else if(sw.equals("U")) {
			path="";			
		} else if(sw.equals("D")) {
			path="";			
		} else if(sw.equals("E")) {
		   int	idx =Integer.parseInt(request.getParameter("idx")) ;
		   OraBoardVo m=service.edit(idx);
		   request.setAttribute("m", m);
		   path="./oraBoard/edit.jsp";
		   
		} else if(sw.equals("S")) {
			List<OraBoardVo> li = service.selectAll();
			request.setAttribute("li", li);
			path="./oraBoard/list.jsp";			
		}
		
		RequestDispatcher dispatcher
		                    = request.getRequestDispatcher(path);
		dispatcher.forward(request, response);
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

9. 폴더 생성

 

10. 

- edit.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page import="dbpkg.oraBoard.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.net.URLEncoder"%>

<%@ include file="../top.jsp" %>
  <%   
      
  OraBoardVo m = (OraBoardVo)request.getAttribute("m");
  int idx = m.getIdx();
  int ref = m.getRef();
  
  String title = m.getTitle();
  String etc = m.getEtc();
  String name = m.getName();
  
  %>
  <script>
  function writeOK(){
	location.href="<%=path%>/oraBoard/form.jsp";  
  } 
   
  function updateOk(k){
		location.href="<%=path%>/OraBoardController?sw=U&idx="+k;  
  }
	   
  function reWriteOk(k){
	  location.href="<%=path%>/OraBoardController?sw=Re&idx="+k; 
  } 	   
  
  </script>
  
<section>


  <br> <br>
  <div align="center" > <h2>  상세보기(수정하기)  </h2> </div>
  <div align="center" > 
  
  <form  method=get>
  <input  type=hidden name=sw  value="rewrite" >
  <table  border=1  width=450  height=150>

  <tr> <td>idx</td> <td> <%=idx %>  </td> </tr>
  <tr> <td>ref</td> <td> <%=ref %>  </td> </tr>
  
  <tr> <td>이 름</td> <td> <%=name %>  </td> </tr>
  <tr> <td>제 목</td> <td> <%=title %> </td> </tr>
  <tr> <td>제 목</td> <td> <textarea  cols=40  rows=5  name=etc ><%=etc %></textarea> </td> </tr>
  <tr> 
  <td colspan=2 align="center" > 
  <input  type=button value="글쓰기" onClick="writeOK()" > &emsp;
  <input  type=button value="수정하기" onClick="updateOk(<%=idx %>)" >&emsp;
  <input  type=button value="답변달기" onClick="reWriteOk(<%=idx %>)" >
  </td> 
  </tr>
  </table>
  </form>
  
  </div>
<br>
</section>
<%@ include file="../bottom.jsp" %>

- form.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ include file="../top.jsp" %>
<section>
<br>
  <div align="center" > <h2> 오라클 답변형 게시판   </h2> </div>
  <div align="center" > 
  
  <form  action="<%=path %>/OraBoardController"  method=get>
  <input  type=hidden name=sw  value="I" >
  <table  border=1  width=450  height=220>
  <tr> <td>이 름</td> <td> <input  type=text name=name > </td> </tr>
  <tr> <td>제 목</td> <td> <input  type=text name=title size=50> </td> </tr>
  <tr> <td>특이사항</td> <td><textarea  cols=40  rows=5  name=etc ></textarea> </td> </tr>
 
 
  <tr> <td colspan=2 align="center" > <input  type=submit value="글쓰기"> </td> </tr>
  </table>
  </form>
  </div>
 
<br>
</section>
<%@ include file="../bottom.jsp" %>

- list.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page import="dbpkg.oraBoard.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.net.URLEncoder"%>

<%@ include file="../top.jsp" %>
<%

   List<OraBoardVo> li=(List<OraBoardVo>)request.getAttribute("li");

%>

<section>
<br>
  <div align="center" > <h2> 목록보기  </h2> </div>
  <div align="center" > 
  <table  border=1  width=600 >
  <tr>
    <td>번호</td><td>이름</td><td>제목</td>
    <td>ref</td><td>level</td><td>board_parent</td>
  </tr>
 <% 
  for (int i=0 ; i < li.size() ; i++ ) {
	  OraBoardVo   m= li.get(i);
  %>
  <tr>
    
    <td><%=m.getIdx() %></td>
    <td><%=m.getName() %></td>
    <td>
     <% if (m.getLevel() > 1) { %>
     <img src="<%=path%>/oraBoard/img/space.jpg"  width=<%=15*m.getLevel()*0.5 %> height=15>
     <img src="<%=path%>/oraBoard/img/re.jpg"  width=15 height=15>
     <%} %>
    
    <a href="<%=path %>/OraBoardController?sw=E&idx=<%=m.getIdx() %>">
      <%=m.getTitle()%>
    </a>
    
    </td>
    <td><%=m.getRef() %></td>
    <td><%=m.getLevel() %></td>
    <td><%=m.getBoard_parent()%></td>   
    
  </tr> 	
	
  <%	  
  }  
%>  
</table>

  

 </div>
<br>
</section>
<%@ include file="../bottom.jsp" %>

- re_edit.jsp

<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page import="dbpkg.oraBoard.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.net.URLEncoder"%>

<%@ include file="../top.jsp" %>
<section>

  <%   
      
  OraBoardVo m = (OraBoardVo)request.getAttribute("m");
  int idx = m.getIdx();
  int ref = m.getRef();
  
  String tilte = m.getTitle();
  String etc = m.getEtc();
  
  %>
  <br> <br>
  <div align="center" > <h2>  답변 상세보기  </h2> </div>
  <div align="center" > 
  
  <form  action="<%=path %>/OraBoardController"  method=get>
  <input  type=hidden name=sw  value="R" >
  <table  border=1  width=450  height=150>

  <tr> <td>idx</td> <td> <input  type=text name=idx  value=<%=idx %> > </td> </tr>
  <tr> <td>ref</td> <td> <input  type=text name=ref  value=<%=ref %> > </td> </tr>
  
  <tr> <td>이 름</td> <td> <input  type=text name=name   > </td> </tr>
  <tr> <td>제 목</td> <td> <input  type=text name=title size=50  value="<%=tilte %>" > </td> </tr>
  <tr> <td>제 목</td> <td> <textarea  cols=40  rows=5  name=etc ><%=etc %></textarea> </td> </tr>
  <tr> <td colspan=2 align="center" > 
  <input  type=submit value="답글쓰기"> </td> </tr>
  </table>
  </form>
  
  </div>
<br>
</section>
<%@ include file="../bottom.jsp" %>

11. 결과

- 오라클 계층형 (I)

- 오라클 계층형 (S)

: ref(그룹)은 부모와 자식을 일치시킨다, board_parent는 부모의 번호를 가져온다

 

 

저작자표시 비영리 변경금지

'JAVA > JSP' 카테고리의 다른 글

JSP - 답변형 게시판  (0) 2022.08.19
JSP - 자료실  (0) 2022.08.08
데이터베이스와 JSP 연동  (0) 2022.08.04
JSP 교재(3) - 액션태그 (include / forward ), MVC  (0) 2022.08.04
JSP 교재 (2)  (0) 2022.08.03
'JAVA/JSP' Related Articles more
Comments