[jsp와 servlet] 익명 방명록 만들기 ① Model 생성

익명 방명록을 만들어 보기 

 

<기능> 

• 글작성
• 전체목록
• 번호를 누르면 글 상세 페이지로 가기
• 상세 페이지에서 글 삭제
• 상세 페이지에서 글 수정 
• 작성자로 검색

 

 

① 오라클에 테이블 생성 

 

 

② Vo 객체와 Dao 객체, Service 클래스 만들어 주기 

 

package guestbook;

import java.sql.Date;

public class GuestBookVo {

	private int num;
	private String writer;
	private int pwd;
	private String content;
	private Date w_date;
	private int cnt;

	public GuestBookVo() {

	}

	public GuestBookVo(int num, String writer, int pwd, String content, Date w_date, int cnt) {
		super();
		this.num = num;
		this.writer = writer;
		this.pwd = pwd;
		this.content = content;
		this.w_date = w_date;
		this.cnt = cnt;
	}

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public String getWriter() {
		return writer;
	}

	public void setWriter(String writer) {
		this.writer = writer;
	}

	public int getPwd() {
		return pwd;
	}

	public void setPwd(int pwd) {
		this.pwd = pwd;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public Date getW_date() {
		return w_date;
	}

	public void setW_date(Date w_date) {
		this.w_date = w_date;
	}

	public int getCnt() {
		return cnt;
	}

	public void setCnt(int cnt) {
		this.cnt = cnt;
	}

	@Override
	public String toString() {
		return "GuestBookVo [num=" + num + ", writer=" + writer + ", pwd=" + pwd + ", content=" + content + ", w_date="
				+ w_date + ", cnt=" + cnt + "]";
	}

}

package guestbook;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import conn.DBConnect;


public class GuestBookDao {
	private DBConnect dbconn;

	public GuestBookDao() {
		dbconn = DBConnect.getInstance();
	}

	// 글작성
	public void insert(GuestBookVo vo) {
		Connection conn = dbconn.conn();
		String sql = "insert into guestbook values(seq_guestbook.nextval, ?,?,?,sysdate,0)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getWriter());
			pstmt.setInt(2, vo.getPwd());
			pstmt.setString(3, vo.getContent());

			int num = pstmt.executeUpdate();

			System.out.println(num + "줄이 추가됨");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 글 번호로 검색
	public GuestBookVo select(int num) {

		
		Connection conn = dbconn.conn();
		String sql = "select * from guestbook where num=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				return new GuestBookVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDate(5),
						rs.getInt(6));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	// 작성자로 검색
	public ArrayList<GuestBookVo> selectByWriter(String writer) {

		ArrayList<GuestBookVo> list = new ArrayList<GuestBookVo>();
		Connection conn = dbconn.conn();
		String sql = "select * from guestbook where writer like ?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%" + writer + "%");
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new GuestBookVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDate(5),
						rs.getInt(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	// 전체목록
	public ArrayList<GuestBookVo> selectAll() {

		ArrayList<GuestBookVo> list = new ArrayList<GuestBookVo>();
		Connection conn = dbconn.conn();
		String sql = "select * from guestbook";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				list.add(new GuestBookVo(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDate(5),
						rs.getInt(6)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	// 글 내용 수정
	public void update(GuestBookVo vo) {
		Connection conn = dbconn.conn();
		String sql = "update guestbook set content=? where num=?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getContent());
			pstmt.setInt(2, vo.getNum());

			int num = pstmt.executeUpdate();
			System.out.println(num + "줄이 수정됨");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 조회수 1up
	public void updateCnt(int num) {
		Connection conn = dbconn.conn();
		String sql = "update guestbook set cnt = cnt + 1 where num=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);

			int num1 = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	// 삭제
	public void delete(int num) {

		Connection conn = dbconn.conn();
		String sql = "delete guestbook where num=?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);

			int num1 = pstmt.executeUpdate();
			System.out.println(num1 + "줄이 삭제됨");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

}

package guestbook;

import java.util.ArrayList;

public class GuestBookService {

	private GuestBookDao dao;

	public GuestBookService() {
		dao = new GuestBookDao();
	}

	// 글 등록
	public void addBook(GuestBookVo vo) {
		dao.insert(vo);
	}

	// 글번호로 검색
	public GuestBookVo getBook(int num) {
		return dao.select(num);
	}

	// 작성자로 검색
	public ArrayList<GuestBookVo> getByWriter(String writer) {
		return dao.selectByWriter(writer);
	}

	// 전체목록
	public ArrayList<GuestBookVo> getAll() {
		return dao.selectAll();
	}

	// 글 내용 수정
	public void editBook(GuestBookVo vo) {
		dao.update(vo);
	}

	// cnt ++
	public void cntview(int num) {
		dao.updateCnt(num);
	}

	// 삭제
	public void delBook(int num) {
		dao.delete(num);
	}

}