[mini project] 5. 도서관 관리 시스템 - 게시판 테이블

1. BoardVo 

package board;

import java.sql.Date;

public class Boardvo {
	private int num;
	private Date w_date;
	private String writer;
	private String title;
	private String content;

	public Boardvo() {}

	public Boardvo(int num, Date w_date, String writer, String title, String content) {
		super();
		this.num = num;
		this.w_date = w_date;
		this.writer = writer;
		this.title = title;
		this.content = content;
	}

	public int getNum() {
		return num;
	}

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

	public Date getW_date() {
		return w_date;
	}

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

	public String getWriter() {
		return writer;
	}

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

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getContent() {
		return content;
	}

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

	@Override
	public String toString() {
		return "Boardvo [번호 = " + num + ", 작성일 = " + w_date + ", 작성자 = " + writer + ", 제목 = " + title + ", 내용 = " + content + "]";
	}
	
}

2. BoardDao

package board;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import conn.DBConnect;

public class Boarddao {
	
	private DBConnect dbconn;

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

	// 글 입력
	public void insert(Boardvo vo) {
		Connection conn = dbconn.conn();

		String sql = "insert into Board values(seq_num.nextval, sysdate, ?, ?, ?)";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, vo.getWriter());
			pstmt.setString(2, vo.getTitle());
			pstmt.setString(3, vo.getContent());

			int num = pstmt.executeUpdate();
			System.out.println("게시글" + vo.getTitle() + "이 작성 완료 되었습니다");

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

	// 글번호로 검색하여 title, content 수정
	public void update(Boardvo vo) {
		Connection conn = dbconn.conn();

		String sql = "update Board set title = ?, content = ? where num = ?";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, vo.getTitle());
			pstmt.setString(2, vo.getContent());
			pstmt.setInt(3, 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 delete(int num) {
		
		Connection conn = dbconn.conn();
		String sql = "delete Board where num = ?";
		
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);

			int num1 = pstmt.executeUpdate();
			System.out.println("게시글" + num1 + "개가 삭제 되었습니다");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 전체검색
	public ArrayList<Boardvo> selectAll() {
		
		ArrayList<Boardvo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from Board order by num asc";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Boardvo(rs.getInt(1), rs.getDate(2), rs.getString(3), rs.getString(4), rs.getString(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	// 작성자로 검색(작성자가 작성한 글 모두 검색)
	public ArrayList<Boardvo> getByWriter(String writer) {
			
			ArrayList<Boardvo> list =  new ArrayList<>();
			
			Connection conn = dbconn.conn();
			String sql = "select * from Board where writer like ?";

			try {
				PreparedStatement pstmt = conn.prepareStatement(sql);

				pstmt.setString(1, writer);
				ResultSet rs = pstmt.executeQuery(); // select 실행

				while (rs.next()) { 
			list.add(new Boardvo(rs.getInt(1), rs.getDate(2), rs.getString(3), rs.getString(4), rs.getString(5)));
				
				}
	
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return list;
		}

	// 글 번호로 검색
	public Boardvo select(int num) {
		
		Connection conn = dbconn.conn();
		String sql = "select * from Board where num = ?";
		Boardvo vo = null;

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setInt(1, num);
			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				int num1 = rs.getInt(1);
				Date w_date = rs.getDate(2);
				String writer = rs.getString(3);
				String title = rs.getString(4);
				String content = rs.getString(5);

				vo = new Boardvo(num1, w_date, writer, title, content);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vo;
	}

	// 제목으로 검색(검색 단어가 제목에 포함된 모든 글)
	public ArrayList<Boardvo> getByTitle(String title) {
		
		ArrayList<Boardvo> list = new ArrayList<>();
		Connection conn = dbconn.conn();
		String sql = "select * from Board where title like  ? ";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, title);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Boardvo(rs.getInt(1), rs.getDate(2), rs.getString(3), rs.getString(4), rs.getString(5)));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
}

 

3. BoardService

package board;

import java.util.ArrayList;
import java.util.Scanner;

import member.MemberService;

public class Boardservice {
	private Boarddao dao;

	public Boardservice() {
		dao = new Boarddao();
	}

	// 글작성
	public void addboard(Scanner sc) {
		
		System.out.println("< 글 작성 >");
		System.out.println("제목 : ");
		sc.nextLine();
		String title = sc.nextLine();
		System.out.println("내용 : ");
		String content = sc.nextLine();
		                                                                       
		dao.insert(new Boardvo(0, null, MemberService.Loginid, title, content));                                                                       
	}

	// 전체 목록
	public void getAll() {
		
		System.out.println("< 글 전체 목록 >");
		ArrayList<Boardvo> list = dao.selectAll();

		for (Boardvo vo : list) {
			System.out.println(vo);
		}
	}

	// 글 번호로 검색
	public void getByNum(Scanner sc) {
		System.out.println("< 글 번호로 검색 >");
		System.out.println("검색할 글 번호를 입력하세요. 글 번호 : ");

		int num = sc.nextInt();

		Boardvo vo = dao.select(num);

		if (vo == null) {
			System.out.println("없는 게시글");
		} else {
			System.out.println(vo);
		}
	}

	// 작성자로 검색
	public void getByWriterAll(Scanner sc) {

		System.out.println("< 작성자 검색 >");
		String writer = sc.next();
		ArrayList<Boardvo> list = dao.getByWriter("%" + writer + "%");
		System.out.println(writer + "로 검색한 결과");
		
		if (list.size() == 0) {
			System.out.println("검색결과 없음");
			return;
		} else {
			for (Boardvo vo : list) {
				System.out.println(vo);
			}
		}
	}

	// 제목으로 검색
	public void getByTitleAll(Scanner sc) {

		System.out.println("< 제목 검색 >");
		String title = sc.next();
		ArrayList<Boardvo> list = dao.getByTitle("%" + title + "%");
		System.out.println(title + "로 검색한 결과");
		
		if (list.size() == 0) {
			System.out.println("검색결과 없음");
			return;
		} else {
			for (Boardvo vo : list) {
				System.out.println(vo);
			}
		}
	}

	// 글 수정
	public void editboard(Scanner sc) {
		
		if (MemberService.Loginid == null) {
		    System.out.println("로그인 먼저 하세요");
			return;
		} else {
			ArrayList<Boardvo> list = dao.getByWriter(MemberService.Loginid);
			if(list.size()==0) {
				if(list.size() == 0) {
					System.out.println("작성한 글 없음");
					return;}
			}
			for (Boardvo vo : list) {
				System.out.println(vo);
			}
			
			System.out.println("< 글 수정 >");
			System.out.println("수정할 게시글 번호 : ");
			int num = sc.nextInt();
			int cnt = 0;
			for (Boardvo vo : list) {
				if (num != vo.getNum()) {
					cnt += 1;
				}
			}
			if (cnt == list.size()) {
				System.out.println("수정권한 없음");
			} else {
				System.out.println("새로운 제목 : ");
				sc.nextLine(); // Buffer에 남은 엔터 제거
				String title = sc.nextLine(); // 제목 입력
				System.out.println("새로운 내용 : ");
				String content = sc.nextLine(); // 글 내용 입력
				dao.update(new Boardvo(num, null, MemberService.Loginid, title, content)); // db에서 글 수정
			}
		}
	}

	// 글 삭제
	public void delBoard(Scanner sc) {
		
		if (MemberService.Loginid == null) {
			System.out.println("로그인 먼저 하세요");
			return;
		} else if (MemberService.Loginid == "관리자") {
			ArrayList<Boardvo> list = dao.selectAll();
			for (Boardvo vo : list) {
				System.out.println(vo);
			}
			System.out.println("< 글 삭제 >");
			System.out.println("삭제할 게시글 번호 : ");
			int num = sc.nextInt();
			dao.delete(num);
		} else {
			ArrayList<Boardvo> list = dao.getByWriter(MemberService.Loginid);        
			for (Boardvo vo : list) {
				System.out.println(vo);
			}
			System.out.println("< 글 삭제 >");
			System.out.println("삭제할 게시글 번호 : ");
			int num = sc.nextInt();
			int cnt = 0;
			for (Boardvo vo : list) {
				if (num != vo.getNum()) {
					cnt += 1;
				}
			}
			if (cnt == list.size()) {
				System.out.println("삭제 권한 없음");
			} else {
				dao.delete(num);
			}
		}
	}
	
	
}

 

게시판. 제일 깔끔하고 아무 메서드도 속 안 썩이는 효자 테이블