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

IT/Jsp

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

노마드오브 2018. 11. 13. 17:39

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>

<title>Insert title here</title>

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>

<hr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</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");

'IT > Jsp' 카테고리의 다른 글

jsp - el, jstl (0)	2018.11.19
jsp - 커넥션 풀링 사용 코드 (DBCP) (0)	2018.11.14
jsp - bean, dao를 사용한 게시판 구현 (0)	2018.11.13
jsp - Bean, Dao 를 사용한 회원관리 (0)	2018.11.12
jsp - 액션태그로 자바빈 객체 생성 (0)	2018.11.12

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

노마드오브

Today :
Yesterday :

노마드오브