jsp - bean, dao를 사용한 게시판 구현 (싱글톤 패턴 적용)

MyOracle.java

package com.db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class MyOracle {

// 드라이버 클래스 이름

public static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";

// DB 연결정보

public static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

public static final String USER = "jspid";

public static final String PWD = "jsppass";

// DB연결 메소드

public static Connection getConnection() throws Exception {

Connection con = null;

// DB 연결정보

final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";

final String USER = "jspid";

final String PWD = "jsppass";

// 1 드라이버 로딩

Class.forName(DRIVER_NAME);

// 2 DB연결

con = DriverManager.getConnection(URL, USER, PWD);

return con;

} // getConnection()의 끝

public static void closeJDBC(Connection con, 

PreparedStatement pstmt, ResultSet rs) {

if (rs != null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (pstmt != null) {

try {

pstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (con != null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

} // closeJDBC()의 끝

}

BoardBean.java

package com.bean;

import java.sql.Timestamp;

public class BoardBean {

private int num;

private String name;

private String passwd;

private String subject;

private String content;

private String ip;

private Timestamp reg_date;

private int readcount;

private int re_ref;

private int re_lev;

private int re_seq;

private String filename;

public int getNum() {

return num;

}

public void setNum(int num) {

this.num = num;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

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 String getIp() {

return ip;

}

public void setIp(String ip) {

this.ip = ip;

}

public Timestamp getReg_date() {

return reg_date;

}

public void setReg_date(Timestamp reg_date) {

this.reg_date = reg_date;

}

public int getReadcount() {

return readcount;

}

public void setReadcount(int readcount) {

this.readcount = readcount;

}

public int getRe_ref() {

return re_ref;

}

public void setRe_ref(int re_ref) {

this.re_ref = re_ref;

}

public int getRe_lev() {

return re_lev;

}

public void setRe_lev(int re_lev) {

this.re_lev = re_lev;

}

public int getRe_seq() {

return re_seq;

}

public void setRe_seq(int re_seq) {

this.re_seq = re_seq;

}

public String getFilename() {

return filename;

}

public void setFilename(String filename) {

this.filename = filename;

}

}

BoardDao2.java

package com.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.bean.BoardBean;

import com.db.MyOracle;

// 싱글톤 패턴 적용(Singleton)

// 객체가 1개만 생성이 가능하도록 보장된 설계

public class BoardDao2 {

private static BoardDao2 boardDao = new BoardDao2();

private BoardDao2() {}

public static BoardDao2 getInstance() {

return boardDao;

}

// 게시판 글한개 추가하기

public void insertBoard(BoardBean boardBean) {

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "";

int num = 0; // 글번호

try {

con = MyOracle.getConnection();

// 글번호 num구하기. 글이 없을경우 1

//   글이 있을경우  최근글번호(번호가 가장큰값)+1

sql = "select max(num) from board";

pstmt = con.prepareStatement(sql);

rs = pstmt.executeQuery();

if (rs.next()) {

num = rs.getInt(1) + 1; // 글이 있는경우. 최대값+1

} else {

num = 1; // 글이 없는 경우

}

pstmt.close();

pstmt = null;

// 주글(일반글) num == re_ref 같게 입력

sql = "insert into board (num, name, passwd, subject, content, filename, re_ref, re_lev, re_seq, readcount, reg_date, ip) ";

sql += " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, num);

pstmt.setString(2, boardBean.getName());

pstmt.setString(3, boardBean.getPasswd());

pstmt.setString(4, boardBean.getSubject());

pstmt.setString(5, boardBean.getContent());

pstmt.setString(6, boardBean.getFilename());

pstmt.setInt(7, num);  // re_ref == num

pstmt.setInt(8, 0);    // re_lev

pstmt.setInt(9, 0);    // re_seq

pstmt.setInt(10, 0);   // readcount 조회수

pstmt.setTimestamp(11, boardBean.getReg_date());

pstmt.setString(12, boardBean.getIp());

// 실행

pstmt.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

}  // insertBoard()

// 게시판 글목록 가져오기(페이징)

public List<BoardBean> getBoards(int startRow, int endRow) {

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

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

StringBuilder sb = new StringBuilder();

try {

con = MyOracle.getConnection();

sb.append("select a.* ");

sb.append("from ");

sb.append("    (select rownum as rnum, a.* ");

sb.append("    from (select * from board order by re_ref desc, re_seq asc) a ");

sb.append("    where rownum <= ?) a ");

sb.append("where rnum >= ? ");

pstmt = con.prepareStatement(sb.toString());

pstmt.setInt(1, endRow);

pstmt.setInt(2, startRow);

// 실행

rs = pstmt.executeQuery();

// 데이터 있으면 자바빈 객체생성

while (rs.next()) {

// 자바빈 객체생성 준비

BoardBean boardBean = new BoardBean();

// rs => 자바빈 저장 => list에 한개 추가

boardBean.setContent(rs.getString("content"));

boardBean.setFilename(rs.getString("filename"));

boardBean.setIp(rs.getString("ip"));

boardBean.setName(rs.getString("name"));

boardBean.setNum(rs.getInt("num"));

boardBean.setPasswd(rs.getString("passwd"));

boardBean.setRe_lev(rs.getInt("re_lev"));

boardBean.setRe_ref(rs.getInt("re_ref"));

boardBean.setRe_seq(rs.getInt("re_seq"));

boardBean.setReadcount(rs.getInt("readcount"));

boardBean.setReg_date(rs.getTimestamp("reg_date"));

boardBean.setSubject(rs.getString("subject"));

// 자바빈 => list 한칸 추가

list.add(boardBean);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return list;

} // getBoards()의 끝

// 전체 글개수 가져오기

public int getBoardCount() {

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sql = "";

int count = 0;

try {

con = MyOracle.getConnection();

// sql  전체글개수 가져오기  select count()

sql = "SELECT COUNT(*) FROM board";

pstmt = con.prepareStatement(sql);

// 실행  rs

rs = pstmt.executeQuery();

// rs 데이터 있으면 count 저장

if (rs.next()) {

count = rs.getInt(1); // 데이터 있을경우

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

return count;

} // getBoardCount()

   public void updateReadCount(int num) {

        Connection con = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        String sql = "";

        

        try {

            con = MyOracle.getConnection();

            // sql  update num에 해당하는 readcount 1증가하게 수정

            sql = "UPDATE board SET readcount = readcount+1 WHERE num = ?";

            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, num);

            // 실행

            pstmt.executeUpdate();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            MyOracle.closeJDBC(con, pstmt, rs);

        }

    } // updateReadCount()

    

    // 글1개 가져오기. 글내용 상세보기.

    public BoardBean getBoard(int num) {

        Connection con = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        String sql = "";

        BoardBean boardBean = null;

        

        try {

            con = MyOracle.getConnection();

            // sql  num에 해당하는 정보 가져오기

            sql = "SELECT * FROM board WHERE num=?";

            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, num);

            // 실행  rs 저장

            rs = pstmt.executeQuery();

            // rs 데이터 있으면 자바빈 객체생성

            // rs => 자바빈 저장

            if (rs.next()) {

                // 자바빈 객체생성. 기억장소할당

                boardBean = new BoardBean();

                // rs => 자바빈 저장

                boardBean.setContent(rs.getString("content"));

                boardBean.setFilename(rs.getString("filename"));

                boardBean.setIp(rs.getString("ip"));

                boardBean.setName(rs.getString("name"));

                boardBean.setNum(rs.getInt("num"));

                boardBean.setPasswd(rs.getString("passwd"));

                boardBean.setRe_lev(rs.getInt("re_lev"));

                boardBean.setRe_ref(rs.getInt("re_ref"));

                boardBean.setRe_seq(rs.getInt("re_seq"));

                boardBean.setReadcount(rs.getInt("readcount"));

                boardBean.setReg_date(rs.getTimestamp("reg_date"));

                boardBean.setSubject(rs.getString("subject"));

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            MyOracle.closeJDBC(con, pstmt, rs);

        }

        return boardBean;

    } // getBoard()

    // 게시판 글 수정

    public int updateBoard(BoardBean boardBean) {

    Connection con = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null; 

        String sql = "";

    int check = 0;

   

    try {

con = MyOracle.getConnection();

// sql num에 해당하는 passwd 가져오기

sql = "select passwd from board where num =?";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, boardBean.getNum());

// rs 실행 저장

rs = pstmt.executeQuery();

// rs 데이터 있으면 패스워드 비교 맞으면

// update num에 해당하는 name subject content 수정

// check = 1 (수정성공 의미)

// 패스워드 틀리면 check = 0

if (rs.next()) {

if (boardBean.getPasswd().equals(rs.getString("passwd"))) {

pstmt.close();

pstmt = null;

sql = "update board set name=?, subject=?, content=? WHERE num=?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, boardBean.getName());

pstmt.setString(2, boardBean.getSubject());

pstmt.setString(3, boardBean.getContent());

pstmt.setInt(4, boardBean.getNum());

// 실행

pstmt.executeUpdate();

check = 1;

} else {

check = 0;

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

   

    return check;

    } // updateBoard()

    

    public int deleteBoard (int num, String passwd) {

    Connection con = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null; 

        String sql = "";

    int check = 0;

   

    try {

con = MyOracle.getConnection();

sql = "select * from board where num=?";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, num);

rs = pstmt.executeQuery();

if (rs.next()) {

if (rs.getString("passwd").equals(passwd)) {

pstmt.close();

pstmt = null;

sql = "delete from board where num = ?";

pstmt = con.prepareStatement(sql);

pstmt.setInt(1, num);

pstmt.executeUpdate();

check = 1;

} else {

check = 0;

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

MyOracle.closeJDBC(con, pstmt, rs);

}

   

    return check;

    }

    

    // 답글쓰기

    public void reInsertBoard(BoardBean boardBean) {

        Connection con = null;

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        String sql = "";

        int num = 0; // 글번호

        

        try {

            con = MyOracle.getConnection();

            // 트랜잭션 관리 기법

            // 자동커밋을 수동커밋으로 제어함            

            con.setAutoCommit(false);

            

            // sql 그룹내의 답글 순서 재배치

            // update  re_seq

            sql = "UPDATE board SET re_seq=re_seq+1 WHERE re_ref=? AND re_seq > ?";

            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, boardBean.getRe_ref());

            pstmt.setInt(2, boardBean.getRe_seq());

            pstmt.executeUpdate();

            

            // 글번호num 구하기     max(num)+1

            pstmt.close(); pstmt = null;

            sql = "SELECT MAX(num) FROM board";

            pstmt = con.prepareStatement(sql);

            rs = pstmt.executeQuery();

            if (rs.next()) {

                num = rs.getInt(1) + 1;

                boardBean.setNum(num);

            } else {

                num = 1;

            }

            

            // 답글 insert

            // re_ref그대로    re_lev 1증가   re_seq 1증가

            pstmt.close(); pstmt = null;

            sql = "INSERT INTO board (num, name, passwd, subject, content, filename, re_ref, re_lev, re_seq, readcount, reg_date, ip) ";

            sql += " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

            pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, num); // 글번호

            pstmt.setString(2, boardBean.getName());

            pstmt.setString(3, boardBean.getPasswd());

            pstmt.setString(4, boardBean.getSubject());

            pstmt.setString(5, boardBean.getContent());

            pstmt.setString(6, boardBean.getFilename());

            pstmt.setInt(7, boardBean.getRe_ref());     // re_ref 같은그룹

            pstmt.setInt(8, boardBean.getRe_lev()+1);   // re_lev 들여쓰기 +1

            pstmt.setInt(9, boardBean.getRe_seq()+1);   // re_seq 그룹내순서 +1

            pstmt.setInt(10, 0);  // readcount 조회수

            pstmt.setTimestamp(11, boardBean.getReg_date());

            pstmt.setString(12, boardBean.getIp());

            // 실행

            pstmt.executeUpdate();

            

            con.commit();  // commit 문장 실행

            con.setAutoCommit(true); // 기본설정으로 돌려주기

            

        } catch (Exception e) {

            e.printStackTrace();

            try {

                con.rollback(); // 원래 상태로 되돌리기

            } catch (SQLException e1) {

                e1.printStackTrace();

            }

        } finally {

            MyOracle.closeJDBC(con, pstmt, rs);

        }

    } // reInsertBoard()

    

    

} // class BoardDao

fwriteForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

<script>

function check() {

if (frm.name.value == '') {

alert('글쓴이를 입력하세요');

frm.name.focus();

return false;

}

if (frm.passwd.value == '') {

alert('패스워드를 입력하세요');

frm.passwd.focus();

return false;

}

if (frm.subject.value == '') {

alert('제목을 입력하세요');

frm.subject.focus();

return false;

}

if (frm.content.value == '') {

alert('내용을 입력하세요');

frm.content.focus();

return false;

}

}

</script>

</head>

<body>

<h1>게시판 글쓰기</h1>

<hr>

<form action="fwritePro.jsp" method="post" name="frm" enctype="multipart/form-data" onsubmit="return check()">

<table border="1">

<tr>

<th>글쓴이</th>

<td><input type="text" name="name"></td>

</tr>

<tr>

<th>패스워드</th>

<td><input type="password" name="passwd"></td>

</tr>

<tr>

<th>제목</th>

<td><input type="text" name="subject"></td>

</tr>

<tr>

<th>파일</th>

<td><input type="file" name="filename"></td> 

</tr>

<tr>

<th>내용</th>

<td><textarea rows="13" cols="40" name="content"></textarea></td>

</tr>

<tr>

<td colspan="2">

<input type="submit" value="글쓰기">

<input type="reset" value="초기화">

<input type="button" value="글목록" onclick="location.href='list.jsp'">

</td>

</tr>

</table>

</form>

</body>

</html>

fwritePro.jsp

<%@page import="com.dao.BoardDao2"%>

<%@page import="java.sql.Timestamp"%>

<%@page import="com.bean.BoardBean"%>

<%@page import="com.oreilly.servlet.multipart.DefaultFileRenamePolicy"%>

<%@page import="com.oreilly.servlet.MultipartRequest"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%

// 업로드

// 라이브러리 http://www.servlets.com

// cos.jar 배치

// 업로드 객체 생성 MultipartRequest

// 1. request

// 2. 업로드할 폴더 (실제 물리적 경로)

String realPath = application.getRealPath("/upload");

System.out.println(realPath);

// 3. 최대파일크기 (바이트단위)

// 1024byte = 1KB

// 1024KB = 1MB

int maxSize = 1024 * 1024 * 5; // 5MB

// 4. 파일이름 한글처리 "utf-8"

// 5. 파일이름이 동일할 경우 이름 변경 DefaultFileRenamePolicy() 

// 업로드 수행!

MultipartRequest multi = new MultipartRequest(request, realPath, maxSize, "utf-8", new DefaultFileRenamePolicy());

// 자바빈 객체생성

BoardBean boardBean = new BoardBean();

// 파라미터값 => 자바빈 저장

boardBean.setName(multi.getParameter("name"));

boardBean.setPasswd(multi.getParameter("passwd"));

boardBean.setSubject(multi.getParameter("subject"));

boardBean.setContent(multi.getParameter("conent"));

// 파일이름 => 자바빈 저장

// 원래 파일이름

System.out.println("원파일이름 : " + multi.getOriginalFileName("filename"));

// 실제 시스템에 저장된(올려진) 파일 이름

System.out.println("시스템에 올린 파일이름 : " + multi.getFilesystemName("filename"));

boardBean.setFilename(multi.getFilesystemName("filename"));

// reg_date ip set메소드로 저장

boardBean.setReg_date(new Timestamp(System.currentTimeMillis()));

boardBean.setIp(request.getRemoteAddr());

// DB객체생성

BoardDao2 boardDao = BoardDao2.getInstance();

// insertBoard(boardBean)

boardDao.insertBoard(boardBean);

// 이동 글목록 list.jsp

response.sendRedirect("list.jsp");

%>