[16] 답변형 게시판 (목록)

 

[답변형 게시판 테이블 설계]

 

테이블명 : tb_bbs

-------------------------------------------------------------------

CREATE TABLE tb_bbs (
       bbsno        INT                 NOT NULL AUTO_INCREMENT PRIMARY KEY
       ,wname     VARCHAR(20)   NOT NULL
       ,subject     VARCHAR(100)  NOT NULL
       ,content     TEXT               NOT NULL
       ,grpno        TINYINT           NOT NULL
       ,indent       TINYINT           DEFAULT 0    NOT NULL
       ,ansnum    TINYINT           DEFAULT 0    NOT NULL
       ,regdate      DATETIME       NOT NULL
       ,passwd    VARCHAR(15)    NOT NULL
       ,cnt           INT                  DEFAULT 0    NOT NULL
       ,ip             VARCHAR(15)   NOT NULL
);

--------------------------------------------------------------------

 

- MySQL에서 아래의 레코드 추가되는지 테스트

 

INSERT INTO tb_bbs (wname,subject,content,grpno,indent,ansnum,regdate,passwd,cnt,ip)
VALUES ('홍길동','안녕하세요','환영합니다',0,0,0,now(),'1234',1,'120.0.0.0');

 

----------------------------------------------------------------------

 

- 답변형 게시판 만들기 순서

 

1. BbsDTO

 

2. BbsDAO

 

3. 추가 페이지 bbsForm.jsp -> bbsIns.jsp

 

4. 목록 페이지 bbsList.jsp

 

5. 상세보기 페이지 bbsRead.jsp

 

6. 삭제 페이지 bbsDeleteForm.jsp -> bbsDeleteProc.jsp

 

7. 수정 페이지 bbsUpdateForm.jsp -> bbsUpdateProc.jsp

 

8. 답변페이지 bbsReplyForm.jsp -> bbsReplyProc.jsp

 

9. 페이징

 

--------------------------------------------------

파일명 : BbsDTO.java

--------------------------------------------------

package www.bbs;

public class BbsDTO {
    private int bbsno;
    private String wname;
    private String subject;
    private String content;
    private int grpno;
    private int indent;
    private int ansnum;
    private String regdate;
    private String passwd;
    private int cnt;
    private String ip;

}

-------------------------------------------------

 

파일명 : BbsDAO.java

-------------------------------------------------

package www.bbs;

 

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 www.utility.DBConnect;
import www.utility.DBClose;

 

public class BbsDAO {
    DBConnect dbconnect=null;

    public BbsDAO() {
        dbconnect=new DBConnect();
    }
    /** 테이블 이름 */
    private String table_name="tb_bbs";

    public int create(BbsDTO dto) {
        Connection con=dbconnect.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int cnt = 0;

        try{
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT MAX(grpno)+1 as maxno FROM "+table_name);
           
            pstmt = con.prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            rs.next();
            int grpno = rs.getInt("maxno");
    
            dto.setGrpno(grpno);
           
            // StringBuffer를 재사용하기위해 기존에 저장된 SQL 삭제, 글 추가 SQL
            // sql.toString().length(): 저장된 문자열의 전체 길이
            sql.delete(0, sql.toString().length()); // SQL 문자열 전체 삭제
           
            sql.append(" INSERT INTO " + table_name);
            sql.append(" (wname, subject, content, grpno, indent");
            sql.append(" ,ansnum, regdate, passwd, cnt, ip)");
            sql.append(" VALUES(?,?,?,?,?,?,now(),?,?,?)");
                     
            pstmt = con.prepareStatement(sql.toString());
   
            pstmt.setString(1, dto.getWname());
            pstmt.setString(2, dto.getSubject());
            pstmt.setString(3, dto.getContent());
            pstmt.setInt(4, dto.getGrpno());
            pstmt.setInt(5, dto.getIndent());
            pstmt.setInt(6, dto.getAnsnum());
            pstmt.setString(7, dto.getPasswd());
            pstmt.setInt(8, dto.getCnt());
            pstmt.setString(9, dto.getIp());
           
            // 추가한 레코드의 1이 retVal 변수에 저장
            cnt = pstmt.executeUpdate();
        }catch(Exception e){
            System.out.print(e.toString());
        }finally{
            DBClose.close(con,pstmt);
        }      
       

        return cnt;  // 1 or 0
    }

    /**
     * 전체 글 목록을 가져 옵니다.
     * @param con 데이터베이스 연결
     * @return 검색된 회원 목록
     */
    public ArrayList list() {
        Connection con=dbconnect.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        StringBuffer sql = null;
       
        ArrayList list = new ArrayList();  // dto 목록을 저장
       
        sql = new StringBuffer();
        sql.append(" SELECT bbsno, wname, subject, regdate, cnt") ;
        sql.append(" FROM "+table_name);
        sql.append(" ORDER BY bbsno DESC");
       
        try{
            pstmt = con.prepareStatement(sql.toString());
            rs = pstmt.executeQuery(); // SELECT 쿼리 실행
           
            // ResultSet에서 첫번째로 이동합니다.
            // 그후 다음 레코드로 이동합니다.
            // BOF --> 1 Record --> 2 Record --> EOF
            // 레코드 이동이 성공하면 true를 리턴합니다.
            while(rs.next()) {
                BbsDTO dto = new BbsDTO();
              
                dto.setBbsno(rs.getInt("bbsno")); 
                dto.setWname(rs.getString("wname"));
                dto.setSubject(rs.getString("subject"));
                dto.setRegdate(rs.getString("regdate"));
                dto.setCnt(rs.getInt("cnt"));
               
                list.add(dto);
            }
        }catch(Exception e){
            System.out.print(e.toString());
           
        }finally{
            DBClose.close(con,pstmt,rs);
        }
       
        return list;
    }
   
}

----------------------------------------------------

 

파일명 : ssi.jsp

----------------------------------------------------

<%@ page contentType="text/html; charset=utf-8"%>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.util.Iterator" %>

<%@ page import="www.bbs.*" %>

<jsp:useBean id="bbsDTO" class="www.bbs.BbsDTO" scope="page" />
<jsp:useBean id="bbsDAO" class="www.bbs.BbsDAO" scope="page" />

<%
request.setCharacterEncoding("utf-8");
%>

----------------------------------------------------

 

 

파일명 : bbsIns.jsp

----------------------------------------------------

<%@ page contentType="text/html; charset=utf-8"%>
<%@ include file="./ssi.jsp" %>

<%
String wname  =request.getParameter("wname");
String subject=request.getParameter("subject");
String content=request.getParameter("content");
String passwd =request.getParameter("passwd");
String ip     =request.getParameter("ip");

bbsDTO.setWname(wname);
bbsDTO.setSubject(subject);
bbsDTO.setContent(content);
bbsDTO.setPasswd(passwd);
bbsDTO.setIp(ip);

int cnt=bbsDAO.create(bbsDTO);
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-ｅquiv="Content-Type" content="text/html; charset=utf-8" />
  <title> sungjukIns.jsp </title>
 </head>

 <body>
<center>
<%

if (cnt == 1){
    response.sendRedirect("./bbsList.jsp");
    return;
}else{
    out.println("등록 실패!!.");
}

%>

<a href='./bbsList.jsp'>[게시판목록]</a>

</center>
 </body>
</html>

-----------------------------------------------------

 

 

파일명 : bbsForm.jsp

-----------------------------------------------------

<%@ page contentType="text/html; charset=utf-8"%>
<%
String ip = request.getRemoteAddr(); // Client IP 추출
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
  <meta http-ｅquiv="Content-Type" content="text/html; charset=utf-8" />
  <title> bbsForm.jsp </title>
 </head>

 <body>
 <center>
 ** 게시판 등록 **<br>
<form name="myform" method="post" action="bbsIns.jsp">
<input type="hidden" name="ip" value="<%=ip%>">
<table border=1>
<tr align=left>
 <td>이름:</td>
    <td><input type="text" name="wname" value="홍길동"></td>
</tr>
<tr align=left>
 <td>제목:</td>
    <td><input type="text" name="subject" value="봄의 시작"></td>
</tr>
<tr align=left>
 <td>내용:</td>
    <td><textarea name="content" rows="5" cols="30">여기에 글내용 남기세요</textarea></td>
</tr>
<tr align=left>
 <td>비번:</td>
    <td><input type="password" name="passwd" value="1234"></td>
</tr>
</table>  

<input type="submit" value="등록">
<input type="button" value="목록" onClick="javascripｔ:location='bbsList.jsp'">

</form> 
</center>
 </body>
</html>

-------------------------------------------