[Servlet/Java/JS/Oracle] Java Vo/Dao/Service,방명록 만들기 1

개요

글 작성, 삭제 등 구현

guestbook : 테이블명

 

guestbook

(로그인 없이)

방명록

글번호

작성자

작성일

글비밀번호

내용

조회수

 

 

 

Oracle 오라클의 테이블 생성

create table guestbook(
num number primary key,
writer varchar2(20) not null,
w_date date,
pwd varchar2(20) not null,
content varchar2(100),
cnt number
);

--글번호 할당할 시퀀스
create sequence seq_guestbook;

 

 

 

Vo

package guestbook;

import java.sql.Date;

public class GuestBookVo {

	private int num;
	private String writer;
	private Date w_date;
	private String pwd;
	private String content;
	private int cnt;
	
	public void GueskBookVo() {}

	public GuestBookVo(int num, String writer, Date w_date, String pwd, String content, int cnt) {
		super();
		this.num = num;
		this.writer = writer;
		this.w_date = w_date;
		this.pwd = pwd;
		this.content = content;
		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 Date getW_date() {
		return w_date;
	}

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

	public String getPwd() {
		return pwd;
	}

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

	public String getContent() {
		return content;
	}

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

	public int getCnt() {
		return cnt;
	}

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

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

}

 

 

Dao

글작성, 번호로 검색, 작성자로 검색, 전체목록, 자기 글 수정/삭제

public void insert (GuestBookVo vo){

String sql = “insert into guestbook values(seq_guestbook.nextval, ?, sysdate, ?, ?, 0)”)

public GuestBookVo select(int num)

public ArrayList<GuestBookVo> selectByWriter(String writer)

public ArrayList<GuestBookVo> selectAll()

public void update(GueskBookVo vo ) //글내용 수정

public void delete (int num)

 

 

 

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;
import product.ProductVo;

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.setString(2, vo.getPwd());
			pstmt.setString(3, vo.getContent());
			int num = 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 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()) { // rs.next() : 첫번째 행부터 마지막 행을 추출할 때 행의 유무 확인
            //객체 한줄 일때는 if, ArrayList로 여러줄 일때는 while 을 이용한다.
				return new GuestBookVo(rs.getInt(1), rs.getString(2), rs.getDate(3), rs.getString(4), rs.getString(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()) {
            //객체 한줄 일때는 if, ArrayList로 여러줄 일때는 while 을 이용한다.
				list.add(new GuestBookVo(rs.getInt(1), rs.getString(2), rs.getDate(3), rs.getString(4), rs.getString(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.getDate(3), rs.getString(4), rs.getString(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();
		System.out.println("dao:"+vo);
		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();
			}
		}
	}
		
		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 num2 = 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 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 num2 = pstmt.executeUpdate();
			System.out.println(num2 + "줄이 수정됨");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

 

 

 

Service

public void addBook(GuestBookVo vo){}

public GuestBookVo getBook(int num){}

public ArrayList<GuestBookVo> getByWriter(String writer){}

public ArrayList<GuestBookVo> getAll(){}

public void edit(GuestBookVo vo ){}

public void delBook(int num){}

 

 

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) {
		System.out.println(vo);
		dao.update(vo);
	} // 글 내용 수정

	public void delBook(int num) {
		dao.delete(num);
	}
	
	public void editCnt(int num) {
		dao.updateCnt(num);
	}
}