5/21 Homework - 게시판 만들기

문제

테이블 생성 / 시퀀스 생성

create table jdbc_board(
    board_no number not null,  -- 번호(자동증가)
    board_title varchar2(100) not null, -- 제목
    board_writer varchar2(50) not null, -- 작성자
    board_date date not null,    -- 작성날짜
    board_cnt number default 0, -- 조회수
    board_content clob,      -- 내용
    constraint pk_jdbc_board primary key (board_no)
);

create sequence board_seq
    start with 1   -- 시작번호
    increment by 1; -- 증가값

위의 테이블을 작성하고 게시판을 관리하는
전체 목록 출력, 새글작성, 수정, 삭제, 검색 기능을
아래 예시와 같이 구현하시오.(MVC패턴과 SINGLETON패턴 적용)

실행 예시

-------------------------------------------------------------
 No         제 목            작성자  조회수   
-------------------------------------------------------------
  4         네번째 게시글        홍길동            0
  3         세번째 게시글        일지매            0
  2         두번째 게시글        홍길동            0
  1         첫번째 게시글        성춘향            0
-------------------------------------------------------------
메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝
작업선택 >> 1       <-- 입력

새글 작성하기
-----------------------------------
- 제  목 : 안녕하세요?      <-- 입력
- 작성자 : 변학도           <-- 입력
- 내  용 : 연습용 입니다.   <-- 입력

새글이 추가되었습니다.


-------------------------------------------------------------
 No         제 목            작성자  조회수   
-------------------------------------------------------------
  5         안녕하세요?          변학도            0
  4         네번째 게시글        홍길동            0
  3         세번째 게시글        일지매            0
  2         두번째 게시글        홍길동            0
  1         첫번째 게시글        성춘향            0
-------------------------------------------------------------
메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝
작업선택 >> 2       <-- 입력

보기를 원하는 게시물 번호 입력 >> 5        <-- 입력

 5번글 내용
------------------------------------------------------------
- 제  목 : 안녕하세요?      
- 작성자 : 변학도           
- 내  용 : 연습용 입니다.  
- 작성일 : 2018-12-12
- 조회수 : 1
-------------------------------------------------------------
메뉴 : 1. 수정    2. 삭제    3. 리스트로 가기
작업선택 >> 1       <-- 입력

수정 작업하기
-----------------------------------
- 제  목 : 수정작업 연습         <-- 입력
- 내  용 : 수정 작업 연습 중입니다.   <-- 입력

5번글이 수정되었습니다.

-------------------------------------------------------------
 No         제 목            작성자  조회수   
-------------------------------------------------------------
  5         수정작업 연습        변학도            1
  4         네번째 게시글        홍길동            0
  3         세번째 게시글        일지매            0
  2         두번째 게시글        홍길동            0
  1         첫번째 게시글        성춘향            0
-------------------------------------------------------------
메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝
작업선택 >> 2       <-- 입력

보기를 원하는 게시물 번호 입력 >> 5        <-- 입력

 5번글 내용
------------------------------------------------------------
- 제  목 : 수정작업 연습      
- 작성자 : 변학도           
- 내  용 : 수정 작업 연습 중입니다.  
- 작성일 : 2018-12-12
- 조회수 : 2
-------------------------------------------------------------
메뉴 : 1. 수정    2. 삭제    3. 리스트로 가기
작업선택 >> 2      <-- 입력

5번글이 삭제되었습니다.

-------------------------------------------------------------
 No         제 목            작성자  조회수   
-------------------------------------------------------------
  4         네번째 게시글        홍길동            0
  3         세번째 게시글        일지매            0
  2         두번째 게시글        홍길동            0
  1         첫번째 게시글        성춘향            0
-------------------------------------------------------------
메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝
작업선택 >> 3       <-- 입력

검색 작업
--------------------------------------------
- 검색할 제목 입력 : 첫번째      <-- 입력

-------------------------------------------------------------
 No         제 목            작성자  조회수   
-------------------------------------------------------------
  1         첫번째 게시글        성춘향            0
-------------------------------------------------------------
메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝
작업선택 >> 3       <-- 입력

검색 작업
--------------------------------------------
- 검색할 제목 입력 :       <-- 입력(입력값이 없으면 전체 자료 출력)

-------------------------------------------------------------
 No         제 목            작성자  조회수   
-------------------------------------------------------------
  4         네번째 게시글        홍길동            0
  3         세번째 게시글        일지매            0
  2         두번째 게시글        홍길동            0
  1         첫번째 게시글        성춘향            0
-------------------------------------------------------------
메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝
작업선택 >> 0       <-- 입력

게시판 프로그램 종료....

BoardVo.java

package kr.or.ddit.board.vo;

import java.util.Date;

public class BoardVo {
	private int board_no;
	private String board_title;
	private String board_writer;
	private String board_date;
	private int board_cnt;
	private String board_content;
	
	public BoardVo() {
	}

	public BoardVo(String board_title, String board_writer,	String board_content) {
		super();
		this.board_title = board_title;
		this.board_writer = board_writer;
		this.board_content = board_content;
	}
	
	public BoardVo(int board_no, String board_title, String board_writer, String board_date, int board_cnt,
			String board_content) {
		this.board_no = board_no;
		this.board_title = board_title;
		this.board_writer = board_writer;
		this.board_date = board_date;
		this.board_cnt = board_cnt;
		this.board_content = board_content;
	}

	public int getBoard_no() {
		return board_no;
	}

	public void setBoard_no(int board_no) {
		this.board_no = board_no;
	}

	public String getBoard_title() {
		return board_title;
	}

	public void setBoard_title(String board_title) {
		this.board_title = board_title;
	}

	public String getBoard_writer() {
		return board_writer;
	}

	public void setBoard_writer(String board_writer) {
		this.board_writer = board_writer;
	}

	public String getBoard_date() {
		return board_date;
	}

	public void setBoard_date(String board_date) {
		this.board_date = board_date;
	}

	public int getBoard_cnt() {
		return board_cnt;
	}

	public void setBoard_cnt(int board_cnt) {
		this.board_cnt = board_cnt;
	}

	public String getBoard_content() {
		return board_content;
	}

	public void setBoard_content(String board_content) {
		this.board_content = board_content;
	}
	
}

IBoardDao.java

package kr.or.ddit.board.dao;

import java.util.List;
import java.util.Map;

import kr.or.ddit.board.vo.BoardVo;

public interface IBoardDao {
	
	/**
	 * @param vo 작성 글 내용
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int write(BoardVo vo);
	
	/**
	 * @return 전체 게시판 내용
	 */
	public List<BoardVo> Allprint();
	
	/**
	 * @param board_no
	 * @return 한 개 게시판 내용
	 */
	public BoardVo detail(int board_no);
	
	/**
	 * @param paramMap 
	 * ( key - FIELD, VALUE)
	 * @return 검색 결과
	 */
	public List<BoardVo> search(Map<String, String> paramMap);
	
	/**
	 * @param String board_title, String board_content 편집 내용
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int edit(Map<String, Object> param);
	
	/**
	 * @param board_no 삭제할 게시판 번호
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int delete(int board_no);
	
	/**
	 * 조회수 올리는 메서드
	 * @param board_no 연 게시판 번호
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int cbtadd(int board_no);
}

BoardDaoImpl.java

package kr.or.ddit.board.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import kr.or.ddit.board.vo.BoardVo;
import kr.or.ddit.util.DBUtil;

public class BoardDaoImpl implements IBoardDao {
	private static BoardDaoImpl dao;
	
	private BoardDaoImpl() {
		
	}
	
	public static BoardDaoImpl getInstance() {
		if(dao==null) dao = new BoardDaoImpl();
		return dao;
	}
	
	
	@Override
	public int write(BoardVo vo) {
		Connection conn =null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		
		try {
			conn=DBUtil.getConnection();
			String sql = "insert into jdbc_board(board_no, board_title, board_writer,"
					+ " board_date, board_cnt, board_content) "
					+ "values(board_seq.nextVal, ?, ?, SYSDATE, 0, ?)";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getBoard_title());
			pstmt.setString(2, vo.getBoard_writer());
			pstmt.setString(3, vo.getBoard_content());
			
			cnt = pstmt.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		
		return cnt;
	}

	@Override
	public List<BoardVo> Allprint() {
		Connection conn =null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<BoardVo> list = new ArrayList<BoardVo>();
		
		try {
			conn=DBUtil.getConnection();
			String sql = "select * from jdbc_board";
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				BoardVo vo = new BoardVo();
				vo.setBoard_no(rs.getInt("board_no"));
				vo.setBoard_title(rs.getString("board_title"));
				vo.setBoard_writer(rs.getString("board_writer"));
				vo.setBoard_cnt(rs.getInt("board_cnt"));
				list.add(vo);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		return list;
	}

	@Override
	public BoardVo detail(int board_no) {
		Connection conn =null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardVo vo = null;
		try {
			conn=DBUtil.getConnection();
			
			String sql = "select * from jdbc_board where board_no = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, board_no);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				String board_title = rs.getString("board_title");
				String board_writer = rs.getString("board_writer");
				Date time = (Date)rs.getTimestamp("board_date");
				SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
				String board_date = form.format(time);
				int board_cnt = rs.getInt("board_cnt");
				String board_content = rs.getString("board_content");
				vo = new BoardVo(board_no, board_title, board_writer, board_date, board_cnt, board_content);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		return vo;
	}

	@Override
	public List<BoardVo> search(Map<String, String> paramMap) {
		Connection conn =null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<BoardVo> list = new ArrayList<BoardVo>();
		
		try {
			conn=DBUtil.getConnection();
			String sql = "select * from jdbc_board where "+paramMap.get("FIELD")+" like '%'||?||'%'";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, paramMap.get("VALUE"));
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				BoardVo vo = new BoardVo();
				vo.setBoard_no(rs.getInt("board_no"));
				vo.setBoard_title(rs.getString("board_title"));
				vo.setBoard_writer(rs.getString("board_writer"));
				vo.setBoard_cnt(rs.getInt("board_cnt"));
				list.add(vo);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		return list;
	}

	@Override
	public int edit(Map<String, Object> param) {
		Connection conn =null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		
		try {
			conn=DBUtil.getConnection();
			String sql = "update jdbc_board set board_title=?, board_content=? where board_no=?";
			pstmt= conn.prepareStatement(sql);
			pstmt.setString(1, (String) param.get("board_title"));
			pstmt.setString(2, (String) param.get("board_content"));
			pstmt.setInt(3, (int) param.get("board_no"));
			cnt = pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		
		return cnt;
	}

	@Override
	public int delete(int board_no) {
		Connection conn =null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		
		try {
			conn=DBUtil.getConnection();
			String sql = "delete from jdbc_board where board_no=?";
			pstmt= conn.prepareStatement(sql);
			pstmt.setInt(1, board_no);
			cnt = pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		
		return cnt;
	}

	@Override
	public int cbtadd(int board_no) {
		Connection conn =null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		
		try {
			conn=DBUtil.getConnection();
			
			// 조회수 1증가
			String sql = "update jdbc_board "
					+ "set board_cnt = (select board_cnt from jdbc_board where board_no =?)+1"
					+ " where board_no=?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, board_no);
			pstmt.setInt(2, board_no);
			cnt = pstmt.executeUpdate();
		
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if(pstmt!=null) try {pstmt.close();} catch(SQLException e) {}
			if(conn!=null) try {conn.close();} catch(SQLException e) {}
		}
		return cnt;
	}

}

IBoardService.java

package kr.or.ddit.board.service;

import java.util.List;
import java.util.Map;

import kr.or.ddit.board.vo.BoardVo;

public interface IBoardService {

	/**
	 * @param vo 작성 글 내용
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int write(BoardVo vo);
	
	/**
	 * @return 전체 게시판 내용
	 */
	public List<BoardVo> Allprint();
	
	/**
	 * @param board_no
	 * @return 한 개 게시판 내용
	 */
	public BoardVo detail(int board_no);
	
	/**
	 * @param paramMap 
	 * ( key - FIELD, VALUE)
	 * @return 검색 결과
	 */
	public List<BoardVo> search(Map<String, String> paramMap);
	
	/**
	 * @param vo 편집 내용
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int edit(Map<String, Object> param);
	
	/**
	 * @param board_no 삭제할 게시판 번호
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int delete(int board_no);
	
	/**
	 * 조회수 올리는 메서드
	 * @param board_no 연 게시판 번호
	 * @return 작업 성공 : 1, 작업 실패 : 0
	 */
	public int cbtadd(int board_no);
}

BoardServiceImpl.java

package kr.or.ddit.board.service;

import java.util.List;
import java.util.Map;

import kr.or.ddit.board.dao.BoardDaoImpl;
import kr.or.ddit.board.dao.IBoardDao;
import kr.or.ddit.board.vo.BoardVo;

public class BoardServiceImpl implements IBoardService {
	private static BoardServiceImpl service;
	private IBoardDao dao;
	
	private BoardServiceImpl() {
		dao = BoardDaoImpl.getInstance();
	}
	
	public static BoardServiceImpl getInstance() {
		if(service==null) service = new BoardServiceImpl();
		return service;
	}
	@Override
	public int write(BoardVo vo) {
		return dao.write(vo);
	}

	@Override
	public List<BoardVo> Allprint() {
		// TODO Auto-generated method stub
		return dao.Allprint();
	}

	@Override
	public BoardVo detail(int board_no) {
		// TODO Auto-generated method stub
		return dao.detail(board_no);
	}

	@Override
	public List<BoardVo> search(Map<String, String> paramMap) {
		// TODO Auto-generated method stub
		return dao.search(paramMap);
	}

	@Override
	public int edit(Map<String, Object> param) {
		// TODO Auto-generated method stub
		return dao.edit(param);
	}

	@Override
	public int delete(int board_no) {
		// TODO Auto-generated method stub
		return dao.delete(board_no);
	}

	@Override
	public int cbtadd(int board_no) {
		// TODO Auto-generated method stub
		return dao.cbtadd(board_no);
	}

}

BoardController.java

package kr.or.ddit.board.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import kr.or.ddit.board.service.BoardServiceImpl;
import kr.or.ddit.board.service.IBoardService;
import kr.or.ddit.board.vo.BoardVo;

public class BoardController {
	// 검색 내용 저장될 session저장
	private Map<String,Object> session;
	private Scanner scan;
	private IBoardService service;

	public BoardController() {
		scan = new Scanner(System.in);
		service = BoardServiceImpl.getInstance();
		session = new HashMap<String, Object>();
	}

	public static void main(String[] args) {
		new BoardController().startBoard();
	}

	public void startBoard() {
		while (true) {
			// 검색된 내용이 있다면 검색된 내용 출력
			if(session.containsKey("list")) {
				// remove하면서 list값 반환
				List<BoardVo> list = (List<BoardVo>) session.remove("list");
				print(list);
			} else {
				listPrint();
			}
			System.out.println("메뉴 : 1. 새글작성     2. 게시글보기    3. 검색    0. 작업끝");
			System.out.print("작업선택 >> ");
			int sel = scan.nextInt();
			switch (sel) {
			case 1:
				write();
				break;
			case 2:
				detail();
				break;
			case 3:
				search();
				break;
			case 0:
				System.out.println("게시판이 종료됩니다");
				return;
			default:
				break;
			}
		}
	}

	private void search() {
		Map<String, String> paramMap = new HashMap<String, String>();
		System.out.println("검색 작업");
		System.out.println("--------------------------------------------");
		System.out.println("검색할 키워드 선택");
		System.out.println("1. 제목\t2.작성자");
		System.out.print("번호 선택 >> ");
		int sel = scan.nextInt();
		switch (sel) {
		case 1:
			paramMap.put("FIELD", "board_title");
			System.out.print("검색할 제목 입력 : ");
			break;
		case 2:
			paramMap.put("FIELD", "board_writer");
			System.out.print("검색할 작성자 입력 : ");
			break;
		default:
			break;
		}
		scan.nextLine();
		String val = scan.nextLine();
		if(val.isEmpty()) {
			return;
		}
		paramMap.put("VALUE", val);
		List<BoardVo> list = service.search(paramMap);
		// 검색된 내용 session저장
		session.put("list", list);
	}

	private void detail() {
		System.out.print("보기를 원하는 게시물 번호 입력 >>");
		int board_no = scan.nextInt();

		int cnt = service.cbtadd(board_no);
		// 번호가 있는 내용인지 확인
		if (cnt==0) {
			System.out.println("해당하는 번호가 없습니다");
			return;
		}
		BoardVo vo = service.detail(board_no);
		
		System.out.println(board_no + "번글 내용");
		System.out.println("------------------------------------------------------------");
		System.out.println("제  목 : " + vo.getBoard_title());
		System.out.println("작성자 : " + vo.getBoard_writer());
		System.out.println("내  용 : "+vo.getBoard_content());
		System.out.println("작성일 : "+vo.getBoard_date());
		System.out.println("조회수 : "+vo.getBoard_cnt());
		System.out.println("-------------------------------------------------------------");
		System.out.println("메뉴 : 1. 수정    2. 삭제    3. 리스트로 가기");
		int sel = scan.nextInt();
			switch (sel) {
			case 1:
				edit(board_no);
				break;
			case 2:
				delete(board_no);
				break;
			case 3:
				return;
			default:
				break;
			}
	}

	private void delete(int board_no) {
		int cnt = service.delete(board_no);
		if (cnt > 0) {
			System.out.println(board_no+"번글이 삭제되었습니다.");
		}
	}

	private void edit(int board_no) {
		System.out.println("수정 작업하기");
		System.out.println("-----------------------------------");
		scan.nextLine();
		System.out.print("제  목 : ");
		String board_title = scan.nextLine();
		System.out.print("내  용 : ");
		String board_content = scan.nextLine();
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("board_no", board_no);
		param.put("board_title", board_title);
		param.put("board_content", board_content);
		int cnt = service.edit(param);

		if (cnt > 0) {
			System.out.println(board_no+"번글이 수정되었습니다.");
		}

	}

	private void write() {
		scan.nextLine();
		System.out.println("새글 작성하기");
		System.out.println("-----------------------------------");
		System.out.print("제  목 : ");
		String board_title = scan.nextLine();
		System.out.print("작성자 : ");
		String board_writer = scan.nextLine();
		System.out.print("내  용 : ");
		String board_content = scan.nextLine();
		System.out.println();
		BoardVo bvo = new BoardVo(board_title, board_writer, board_content);

		int cnt = service.write(bvo);
		if (cnt > 0) {
			System.out.println("새글이 추가되었습니다.");
		}
	}

	private void listPrint() {
		List<BoardVo> list = service.Allprint();
		print(list);
	}
	
	private void print(List<BoardVo> list) {
		System.out.println("-------------------------------------------------------------");
		System.out.println("No\t제 목\t\t작성자\t조회수");
		System.out.println("-------------------------------------------------------------");
		if (list == null || list.size() == 0) {
			System.out.println("\t조회 결과가 존재하지 않습니다");
		} else {
			for (BoardVo b : list) {
				System.out.println(b.getBoard_no() + "\t" + b.getBoard_title() + "\t" + b.getBoard_writer() + "\t"
						+ b.getBoard_cnt());
			}
		}
		System.out.println("-------------------------------------------------------------");

	}
}